creating a time dimension

this is my first cube using 2005 analysis services. i have been using 2000 Analysis Services for about five years.

i have a cube with about 5 small dimensions and am trying to add a time dimension to include some of the Year to Year and Month to Month calculations.

i have a table with Year in column 1 and Month in column 2 mapped to the fact table.

before i add the time dimension everyting processes fine. after i add the Year-Month Hierarchy, the processing starts and gets to the SQL query to populate the cube but seems to go into a loop and never completes.

i don't have a clue what is going on.

does anyone have a suggestion?

Just want to make sure I understand the scenario ....

You have a Time dimension table with a structure similar to this:

create table DIM.Time (

TimeID int not null identity(1,1),

Year int null,

Month int null

...

)

Your fact table has a structure similar to this:

create table FACT.MyFact (

DimID int not null,

OtherDimID int not null,

TimeID int not null,

Measure1 money null,

...

)

Is this correct?

If so, do you get the "loop" when you process the Time dimension or just when you process the cube?

Thanks,

Bryan

|||

the dim time does not have a time id just year and month.

the fact table also has year and month associated with each fact.

the fact year and month are mapped to the dim year and month.

and the dim table is used for the dimension generation.

i also tried using the fact table year and month for the dimension generation with the same result.

the loop happens when i process the cube.

|||

I'd recommend using a single key for all foreign key references. If you don't have a surogate key for time, at least have a single smart key of YYYYMM in the fact table that references a single key in the dimension. Worst case, you can combine the year and month values in the fact table and the time dimension tables through the DSV.

Not 100% certain that's the source of your exact problem, but its worth giving it a shot.

B.

|||

That worked!

thanks.

i guess this version of Analysis Services is more traditionally Relational.

sql