Hello experts,

I am recreating a AS2000 cube in the new SQL2005 environment and everything
is working find except for dates.

When I add my time dimension table which joins to my Fact table through the
Date columns everything looks fine... However upon browsing the cube I see
the following:

Total: 1000000
2003: 1000000
2004: 1000000
2005: 1000000

So it is just giving me the total each time...

The primary key on the Time Dimension table was not the date so I changed
this...
No I get

Total: 1000000 (which is correct)
2003: 200000
2004: 400000
2005: 400000

But these figures are incorrect!!! The total is right but the break down is
incorrect. What am I doing wrong? Why have MS changed the time dimension
so drastically AS2000 just worked!!!!!!!

I hope someone can help me as I am at a loss.

Clint

When I join the two tables in SQL it works perfectly fine. So it must be
something I am doing incorrectly in AS. Please help.

Clint

Hi Clint,

What is the aggregation function of the measure that you're describing -
is it defined just as a "Sum"?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak, thanks for responding!

Yes it is... Sum is correct. I just dont understand how the total can be
wrong but the aggregates for year are incorrect..

All my other dimensions work fine.. I have about 8 other dimensions.

Clint
"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Clint,


Starting at the bottom, then: since you changed the key for your time
dimension to the date field that joins to the fact table, is the sum for
individual dates, at least, correct? And if so, can you describe the
hierachy from date to year in the dimension table - is the next level
above date rolling up correctly, for example?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi,

Yes the totals are correct. However it is not aggregating correctly.

Do I even need a Time Dimension table?

Initially I had the hierarchy of:
all
Year
Quarter:
Month:
Day

After this failed I tried just with:

All
Year

But no success...

Clint

As an example, i have just tried to use the Server Time Dimension... But it
is not aggregating.. Thus every member and level has the same value. How do
I make it aggregate?

Only thing that comes to mind is to check whether the Granularity
attribute for the Time dimension is properly set, since this controls
the aggregation of data:

http://msdn2.microsoft.com/en-us/library/ms175686.aspx

Use the Dimension Usage tab to define the relationship between
dimensions and measure groups. For each of the fact tables in a cube,
this tab specifies whether a set of measures applies to a dimension. It
also characterizes the relationship between the dimension and the
measure group. For more information about the types of relationships,
see Dimension Relationships (SSAS).
...
Granularity attribute

Specifies the attribute that corresponds to the column in the dimension
table that joins the dimension table to the fact table. In most cases
this is the key attribute of the dimension.

If you select a non-key granularity attribute, all other attributes in
the dimension must either be directly related as member properties of
the granularity attribute or be indirectly related as member properties
of attributes that are member properties of the granularity attribute.
The server assumes that data is aggregated from the granularity
attribute, so for the server to aggregate data properly, relationships
between the granularity attribute and the rest of the attributes in the
dimension must be defined by member properties.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

I used to use the Date field in the fact table. But this seems to be no
longer possible. Is a real shame as it worked fine.

Thanks for you assistance Deepak.