Distinct Count Problem

I am using a standard star schema structure with a sales fact table and dimension tables joined by surrogate foreign keys. My product's dimension contains a hierarchy for SKU (the lowest level) and product class. I created a distinct count measure for "SKU Count" using the product key in the sales fact table. This works fine with no problem. Now I need to create a second distinct count measure for product class. I cannot get this to work. Product class exists as an attribute in the product dimension. I have tried several ways to get this to work but each time it results in an error. For example, I created an MDX calculation using both COUNT and DISTINCT COUNT and none of these worked. I also added the product class attribute into the fact table to see if this could work by creating a new distinct count against this column. This resulted in an error message.

Can anyone help with this? Basically, I am looking to create a distinct count measure against the product key in the fact table (which works fine) and than I want to create a second distinct count measure against the product class attribute that comes from the product dimension but is based off of the unique occurences of each product class in the fact table (referenced through the product class key).

David

Both these approaches should work, the MDX Calc would not perform as well as adding the product class attribute to the fact table. Can you tell us exact what errors you were getting.

With the fact table approach I think you will need to create a second measure group (based on the same fact table) as you can only have one distinct count measure in each measure group.

|||

Darren,

In order to create a second fact group based on the same table, would I need to create an alias table in the dsv first. This approach seems like it would be more resource intensive since I'm technically adding another 700,000 records with the "new" fact table.

David

|||

Yes, that's right you would have to scan through the fact table again, but each distinct count measure you create means another scan through the fact table. It depends how often you are processing your cube as this sort of volume should only take minutes. Technically you don't need to create an alias table, but I think it is the only way to do this through the UI, through an XMLA script you can create multiple measure groups off the one dsv object.

The alternative is to use the DistinctCount() function in a calculated measure, you would have to test it and see, but it can be quite slow depending on the size of your queries.