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:
- 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?
- How do I remove duplicate rows in the drillthrough when a source row has multiple matches in the many-to-many relationship?