Hi,

My fact table has sales data down to transaction detail. Each
transaction can have multiple records. Each store can have multiple
transaction.
I want to get Transaction Count and Store Count. Since I cannot use two
distinct count in CUBES, please help me with MDX for calculated members.

Thanks in advance



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

You can create separate cubes for each distinct count measure, and then bring
them together in a virtual cube.
HTH,
Brian
www.geocities.com/brianaltmann/olap.html

"Master" wrote:

Brian,

Thanks for your response. If I can avoid creating seperate cubes I want
to do that. Sooner or later there may be another distinct count measure
they may want.

Is there any way I could acheive this using member custom rollup or
using MDX to count at higher level in hierarchy?

Thank you


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

Once you have loaded aggregated data into a cube there is no way for MDX to
perform a distinct count.
You would have to have a transaction dimension, with a member for every
transaction, or create a fact table with pre-calculated measures to do that.
It is much easier to build and mantain cubes for each distinct count
measure. Performance is good too. You can add additional cubes for new
measures if necessary.

From BOL(Using Aggregate functions):

Because distinct count measures are nonadditive, the presence of a distinct
count measure significantly restricts the ability of Microsoft® SQL Server™
2000 Analysis Services to preaggregate the cube. For this reason, it is
recommended that each distinct count be placed in its own cube with no other
measures. These cubes with distinct count measures can then be joined
together with other cubes in a virtual cube that efficiently manages all of
the measures.

Regards,
Brian

"Master" wrote: