Dave Rodabaugh's Cube

Integrating Analysis Services 2005 and Reporting Services 2005 with DRILLTHROUGH; Introduction

My current client has engaged my employer to implement a financial reporting solution.  The first report we chose was a cash report, which displays details not usually carried in a cube.  In the past, I never worried about uniqueness of fact rows because it was unnecessary.  Having completely identical rows in a fact table was perfectly legitimate, a fact giving indigestion to many traditional data modelers.  When Analysis Services 2000 (“MSAS”) loads duplicate rows into the cube, it simply aggregates the measures into the single designated cell identified by the dimensional tuple formed by the surrogate key combination.  This is one of the things we love about Analysis Services, that it provides (semi) detailed and aggregated information on a moment’s notice.

But sometimes you need to see more detail than the cube provides.  Sometimes you need to see the individual fact rows contributing to a cell because reports require such detail.  The aforementioned cash report is little more than a rote listing of all cash transactions for a particular period, e.g. 2006.  While the dimensions which form the cash report provide some finely grained information, there are many cash transactions with duplicate dimensionality (though different dollar amounts).  These are aggregated into a single detail cell, as expected.  But the report needs those detail fact rows.  In MSAS, drillthrough on a single cell returned the fact rows (with designated dimensional columns) from the cell’s data source.  It did this by forming an SQL statement, pushing it back through the data provider, and passing along the rowset which answered the query.  This handy technique allowed very detailed reporting and analysis without overstressing MSAS, though it carried no performance guarantees since query performance was governed by data source performance, not MSAS itself.

While developing the cash report, I needed fact rows so I turned to the trusty DRILLTHROUGH statement.  To my chagrin, it didn’t return the correct answer.  Specifically, it didn’t return the correct number of rows.  This began a frantic search to understand why drillthrough was not behaving as expected.  I was tipped off by Teo Lachev’s book, which said that drillthrough had been completely rearchitected and that Analysis Services 2005 (“SSAS”) answered all drillthrough queries from the cube.  Uh oh.  That had nasty implications that I’ll discuss later and it has forced me to change a couple of design guidelines.

A Web search yielded a lot of clues, but nothing in one place that allowed me to craft a solution.  I hope to provide a solution here, in multiple parts.  This is the first part in a series to explain how DRILLTHROUGH has changed and how you must change your design techniques to properly utilize it.  I will reference several websites when appropriate, for I stand on the shoulders of giants.  And should I err in this series, I trust my fellow cubers to provide the truth.

Part I:  Two Examples Show that SSS Drillthrough May Not Always Behave Like MSAS Drillthrough

Part II:  Design Implications of the New Drillthrough

Part III:  Query Examples for SSAS Drillthrough

Part IV:  Summary