2

I was browsing through the AdventureWorks DW 2012 sample, along with the accompanying SSAS cube sample, and noticed some interesting behavior related to drillthrough when a many-to-many relationship existed.

When looking at the Internet Sales Amount measure for a particular date (ie. July 1, 2005), and then performing a drillthrough to see the related lines, I'd notice that sums of the sales amount on the drillthrough lines didn't match the reported total sales amount. Looking at the base fact table in the warehouse, I found either lines were missing or sometimes were duplicated.

If I did a query combining the FactInternetSales table with the FactInternetSalesReason table, then I'd receive the same results that I got when looking at the drillthrough. Conversely, if I removed the Sales Reason dimension from the set of returned columns for the drillthrough, then I'd see "all" the rows and the totals would match up.

So I'm left with 2 questions:

  1. Is there a way to have drillthrough show "unresolved" rows when a many-to-many relationship exists, but the source row doesn't have a matching attribute?
  2. How do I remove duplicate rows in the drillthrough when a source row has multiple matches in the many-to-many relationship?
Improve this question

1 Answer 1

Reset to default
3

If your drillthrough command includes a dimension attribute from a many-to-many dimension then it behaves as described: showing a row zero to many times.

The easiest solution is to create a drillthrough action and mark that action as the default drillthrough action and specify the columns you want to include in the drillthrough results. If you don't include the many-to-many dimensions then it will stop skipping or duplicating rows.

Improve this answer
2
  • Thanks Greg, that's what I was assuming. I guess the one work around for the first scenario would be to add an "unassociated" option in the dimension, and then during the ETL process, add and association to that dimension so that every fact has at least item joined in the many to many relationship. I can train my users to deal with "duplicate" entries, but missing lines would be a deal breaker.
    – Evan M.
    Commented Jan 5, 2017 at 4:48
  • @EvanM. Definitely agree that in most scenarios tying every row to something in the many-to-many (like "unassociated") makes sense. That's how you would handle a regular dimension.
    – GregGalloway
    Commented Jan 5, 2017 at 5:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.