Sql-server – SSAS Multidimentional Drillthrough with Many-to-Many Relationships

sql serverssas

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?

Best Answer

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.