August 6, 2018 at 7:46 am
I have a fact table. with three dimensional keys, one for the calendar, one for the shipping warehouse and a third for the customer shipped to.
I want to see the unique combinations of shipping warehouse and customers for a given time period (day, month, quarter, etc).
I know one way to solve this would be to add an extra column the fact table with the pairings of the keys for the shipping warehouse and customer shipped to, and just perform a basic Distinct Count
Is there another, better way to generate the distinct pairings of warehouse and customer than to add the additional measures column and distinct count it?
August 7, 2018 at 4:34 am
mtassin - Monday, August 6, 2018 7:46 AMI have a fact table. with three dimensional keys, one for the calendar, one for the shipping warehouse and a third for the customer shipped to.I want to see the unique combinations of shipping warehouse and customers for a given time period (day, month, quarter, etc).
I know one way to solve this would be to add an extra column the fact table with the pairings of the keys for the shipping warehouse and customer shipped to, and just perform a basic Distinct Count
Is there another, better way to generate the distinct pairings of warehouse and customer than to add the additional measures column and distinct count it?
Off of the top of my head, I think I would create a composite key on the fact (either in the DSV or in the view - if you are using views) which I would join to a dimension table (which you could either do as a view or incorporate into the ETL, depending on how much data you are dealing with) which I would create from the distinct combinations of the two keys. Then I would use a many to many join to get the distinct counts of the combinations (as this will outperform a native distinct count). More informatio can be found in this paper: https://www.sqlbi.com/wp-content/uploads/The_Many-to-Many_Revolution_2.0.pdf
Have a look from page 33 onwards.
I hope that helps.
August 7, 2018 at 8:31 am
That's pretty much what I did... and slapped a Distinct Count base measure on the composite value.
I was wondering if there was a better solution.
August 7, 2018 at 8:52 am
mtassin - Tuesday, August 7, 2018 8:31 AMThat's pretty much what I did... and slapped a Distinct Count base measure on the composite value.I was wondering if there was a better solution.
Yes, there is - use the method described above in the many to many paper, just modified to your needs. I never suggested putting a distinct count on the composite key - this will not result in good performance.
Essentially, using the method described from page 33 you end up with a dimension table, posing as a fact, where you use a simple "Count of Rows" and then do a many to many join to your fact table to get the distinct count. This is the simplest way of doing it using many to many. You could investigate using the Multiple Groups method (page 48 onwards) which would work but might be a touch messier to maintain.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply