Hi,

I have a time dimension with following levels
- Year
- Week
- Day

I have store dimension with levels
- Country
- State
- City
- Store

With these two dimensions I have other 8 dimension and 3 more cubes
which are combined in virtual cube.

I want to create a calculated measure for displaying the stores (a
count) that transmitted the data for a day. This measure has to be
aggregated for other time levels i.e for weeks and Year.

Here is what I tried

Attempt 1:

Count(CrossJoin( {[Measures].[Sales Amount]},
crossjoin(descendants([Time].[Standard].currentmember,[Time].[Standard].[Day]),descendants([Store].[Standard
Geog].currentmember,[Store].[Standard Geog].[Store]))), ExcludeEmpty)

This works but was very slow.

Attempt 2:

sum(descendants([Time].[Standard].currentmember,[Time].[Standard].[Day]),sum(descendants([Restaurant].[Standard
Geog].currentmember,[Restaurant].[Standard
Geog].[Restaurant]),count({[measures].[Sales Amount]},ExcludeEmpty)))

But no imporvemnet in performance. I even tried to use the NECJ()
function but was not giving me the correct results.

I would appriciate any help in resolving the issue.

Thanks,

Vinayak

NECJ() function should improve query performance:

Descendants([Time].[Standard].currentmember,
[Time].[Standard].[Day]),
Descendants([Store].[Standard Geog].currentmember,
[Store].[Standard Geog].[Store]),
{[Measures].[Sales Amount]}, 2))

- Deepak

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

This is a well-known distinct count() issue. The only way to improve performance is to design a seperate cube containing
a measure using store id in the fact table with aggregation methond set as distinct count, then define aggregation on all time levels (and combinations of other dimension levels as much as possible)

Thanks Deepak, that worked.

Vinayak


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

Try using non empty crossjoin.

Abhinav Kumar
akumar (AT) itmag (DOT) com.au