Re: dimension creation

Hi Joe,

You poor thing, no one seems to have replied so I'll do my best.

For a Dimension to valid, all the levels in the hierarchy must consist of 1 to many relationships, so that Oracle knows how to round up appropriately (eg. a year can have many quarters but a quarter can only be in the one year, a quarter can have many months but a month can only belong to the one quarter, a month can have many dates but a date can belong to the one month).

Are you sure you have set up your dimension table such that a year can consist of many months but a month can belong only to the one year, a month can have many days but a day can belong only to the one month, a day can have many hours but an hour can belong to only the one day, etc).

Looking at your data values, this doesn't appear to be the case....

Cheers

Richard
"Joe Sath" <u705413818_at_spawnkill.ip-mobilphone.net> wrote in message news:l.1030376009.1490600585@[64.94.198.252]...
> *** post for FREE via your newsreader at post.newsfeed.com ***
>
> I have a table like this
> SQL> desc kdate
> Name Null? Type
> ----------------------------------------- -------- --------------
> KDATE NUMBER
> POLLTIME DATE
> YEAR VARCHAR2(4)
> MONTH VARCHAR2(2)
> DAY VARCHAR2(2)
> HOUR VARCHAR2(2)
> MINUTE VARCHAR2(2)
>
> typical row will be like this
>
> KDATE YEAR MO DA HO MI
> --------- ---- -- -- -- --
> 1 2002 03 01 00 00
> 2 2002 03 01 00 01
> 3 2002 03 01 00 02
> 4 2002 03 01 00 03
> 5 2002 03 01 00 04
> 6 2002 03 01 00 05
> 7 2002 03 01 00 06
> 8 2002 03 01 00 07
> 9 2002 03 01 00 08
> 10 2002 03 01 00 09
> 11 2002 03 01 00 10
> I created my dimension like this
>
> create dimension dim_test
> level min is kdate.minute
> level h is kdate.hour
> level d is kdate.day
> level mon is kdate.month
> level yr is kdate.year
> hierarchy calendar_rollup (
> min child of
> h child of
> d child of
> mon child of
> yr);
>
> Dimension created.
>
> SQL> execute dbms_olap.validate_dimension
> ('DIM_TEST','SCOTT',false,true);
>
> PL/SQL procedure successfully completed.
>
> SQL> select count(*) from mview$_exceptions;
>
> COUNT(*)
> ----------
> 1337
>
> which is all that I have in table kdate.
>
> What that I did was wrong?
>
> Thanks for your hlep.
>
>
>
>
>
>
>
>
>
>
>
> --
> Sent by joebayerii from hotmail piece from com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new
>
>
> -----= Posted via Newsfeed.Com, Uncensored Usenet News =-----
> http://www.newsfeed.com - The #1 Newsgroup Service in the World!
> -----== 100,000 Groups! - 19 Servers! - Unlimited Download! =-----
>
Received on Wed Aug 28 2002 - 06:36:21 CDT