Same measure group for count and distinct count in SSAS

Question

Hello there,

I need to make DistinctCount aggregation of few columns in the same fact table in SSAS. For each DistinctCount it creates a new measure group. I need to put all these under same measure group. Please let me know how to go ahead.

Regards,

Palash

Answer 1

Hi,

 The best practices shows that whenever you set a distinct count on a measure it is always good to be under a seperate MG because of performance issues. I am not sure whether you can have it in the same MG.

 HTH,

Ram

Answer 2

You cant have more then one distinct count measure in one measure group. BIDS will create new measure group when you chose distinct count aggregation.

Only "work around" is to chose max or similar aggregation function when creating new measure and then changing it to distinct count later. So, you can have one distinct count with other measures in the same measure group, but only one.

 

 

MC

Answer 3

Palash,

 

You can create a calculated member from each distinct count measure groups put in in a display folder or leave it as default. That way you won't have too many measure groups lying around. I'll always practice of using calculated member just for the sake of keeping thing organized.

 

Please remember that distinct count measures are very costly if your cube size grows exponentially. One way to re-think your design is to create a view on top of your fact table(s) to capture a distinct values and create a regular measure from it.

If your cube is small you don't have to worry..

 

 

hth.

rok

 

Answer 4

Only "work around" is to chose max or similar aggregation function when creating new measure and then changing it to distinct count later. So, you can have one distinct count with other measures in the same measure group, but only one.

This is possible, but it's not a good idea. Distinct counts should not be mixed with other measures, the way they are stored internally is different. This is why BIDS tries to force you to always put them in their own measure group.

Hiding the real distinct count and using a simple calculated measure that refers to it is probably your best option.

---

http://darren.gosbell.com - please mark correct answers

Answer 5

One way to re-think your design is to create a view on top of your fact table(s) to capture a distinct values and create a regular measure from it.

This will not work.

Take the simple example of a Distinct Count of customers. If you have one customer and they purchase from you in January and Febraury your data should look like the following:

Jan     1
Feb     1
Qtr1    1

But if you used a regular measure over a view that did the distinct you would get data like the following which is incorrect as it is double counting unique customers:

Jan     1
Feb     1
Qtr1    2

This is why distinct counts are so expensive. The only way to calculate them properly is to scan over the leaf level data for each cell. There are som tricks that SSAS uses, but it's a tough problem to optimize in a generic way.

---

http://darren.gosbell.com - please mark correct answers

Answer 6

 

One way to re-think your design is to create a view on top of your fact table(s) to capture a distinct values and create a regular measure from it.

 

Thanks For clearing that out Darren. I always thought it was possible but never had implemented.

Answer 7

SSAS doesn't allow to have 2 distinct measures in same MG. One option is to make changes to data source - fact view. Add 2 new column [Distinct Type] and [Distinct Key]. Distinct Type indicates type of distinct measure, distinct key to be populated with distinct value depending on distinct type. What we end up is adding more rows to the fact. ex. For 2 distinct measures, fact row would double.

Then add new hidden dimension [Distinct] and only attribute [Distinct Type]. Relate this dimension to fact. Say if the measure in MG [Distinct Measure], then in MDX

CREATE  MEMBER CURRENTCUBE.[Measures].[<First Distinct Measure>]

AS ([Distinct].[Distinct Type].&[<First Type>], [Distinct Measure]);

CREATE  MEMBER CURRENTCUBE.[Measures].[<Second Distinct Measure>]

AS ([Distinct].[Distinct Type].&[<SecondType>], [Distinct Measure]);

There is not much saving from cube size than having 2 MG, but I found for large fact, it saves time during cube processing since the SSAS issue only one ORDER by query. Also if the value of distinct measures don't overlap say measure1 values are from 1 to 2 million and second measure values are from 3 - 5 million, it reduces the cube size (index file size) and also aggregation size.

---

Shom