Saturday, January 10, 2009

Drill Through in Analysis Services

Drill through functionality was not included in the first version of OLAP technology from Microsoft (SQL Server 7). I found it such sought after requirement that I created my own drill through for ThinSlicer (the first thin client cube browser). It would simply generate an SQL statement based on the dimension members and assumed that the level names were the same as the column names. Then in 2000, drill though functionality was included in Analysis Services, so I removed my custom code.

However, things changed in 2005 (SQL Server 2005) when drill through architecture was changed so that a drill through request did not go to the relational database (for the atomic records) rather, it returned the most detailed cell data from the cube. This, was a huge change in functionality, and some would argue that is not "drill through" but "drill bottom". As you can imagine, there are security implications with drilling through to relational data. In the cube, you can include quite complex security functionality, which would be difficult to honor in a drill through. This might be the reason drill through was dramatically changed in SQL Server 2005.

I hope that one day SQL Server will bring back drill through to relational data. It is such a useful function for many cubes. In the meantime, if you want to include drill through to the atomic records, you will need to create a cube action and write some custom code.
One thing to take into account when you do drill through to atomic data in the relational database is SQL performance. As we know, cube queries tend to have more reliable response times than ad hoc SQL queries. If you write your own customer drill through functionality, I have two suggestions
  1. Use "Top n" in your query (or equivalent from a non ms db). The optimiser takes this into account.
  2. Create a non clustered index on each of the foreign keys
For an example of how easy drill through is simply go to my demonstrations home page http://RichardLees.com.au/Sites/Demonstrations, right click on one of the charts and select "show details". Here is an example of the "drill through" records you will be returned.

I didn't need to write any code for this drill through. The graph is presented by PerformancePoint, and like any good cube browser, it simply honors the drill through functionality of Analysis Services.
Enabling drill through on the cube, couldn't be simpler, you simply enable drill through on the role.

Richard

at

5 comments:

  1. Steve WFebruary 5, 2009 at 8:02 AM

    Hi Richard. The columns on your drillthrough have very user friendly names. Mine have names like server.db.table.column... did you modify yours? if so, how? any tips, especially as it applies to Proclarity drill through to details?

    ReplyDelete
  2. Richard LeesFebruary 7, 2009 at 3:06 PM

    The column names you see are raw from Analysis Services. The application that is displaying the data can "format" the column names to take out the square brackets, dollar signs etc. This is what PerformancePoint had done for me. What application were you using?

    ReplyDelete
  3. Matej GolobFebruary 27, 2009 at 9:06 PM

    I have same question. How to modify column names in drillthrough (AS 2005/2008). I use excel 2007 as client application.

    ReplyDelete
  4. Steve WMarch 1, 2009 at 10:34 AM

    So you are formating your column names in MPP? Where do you modify the drill through column names in MPP?

    ReplyDelete
  5. Sam KaneMarch 19, 2011 at 1:48 AM

    Here are this and some other articles on SSAS Drillthrough:

    http://ssas-wiki.com/w/Articles#Drillthrough

    ReplyDelete