MS OLAP 2005: DrillThru

So the Boise Code Camp 1.0 was last week. I gave a presentation on using ADOMD.NET 2.0. Very basic demonstration: how to open a connection, how to browse a cube/dimension/hierarchy/level/members/etc, how to execute a command, how to display the command via the CellSet object, why to avoid the AdomdDataReader, etc.

But, as is standard, I received one question I could not fully answer: "Hey, I heard they (Microsoft) removed Drill Thru from 2005 (Analysis Services 2005). Is that true?"

My stock answer was: “No, it is there.” Drill Thru is too needed a feature to just remove on the spur of the moment (a new release). I even opened a cube browser to show that drill-thru worked. And I was right -- or, mostly right anyway.

I did some stumbling around the web (read: google search) and I found this article: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_anservdrill.asp

Read it if you wish, I can wait. The jist of the article is this, Drill Thru is there in 2005, and turned on by default, but it is different now -- ever so slightly different.

In the olden days of MSOLAP 2000 when you performed a Drill Thru, Analysis Services would send a SQL query back to the source database asking for data, and then passing that data back to the client. And that worked well, it was often horribly slow (especially against non-MSSQL databases, but it worked. Oh, and as a bonus feature, you could select which columns were displayed to the user, but not the order. Yup, that was fun. “Why is the Product ID in the middle of the display? Well the OLAP gods just wanted it there, that’s all. Move it? Shhhhhh, they might hear you!”

Now, in 2005, it seams that Analysis Services does not go back to the source database at all. Instead it returns data from the inner darkness of the cube itself. The side caveat is that if you want to have some data available via Drill-Thru, it has to be available IN the cube. Uhm, minor change there – better read the article above.

How can this possibly be a good thing? That is an easy question. If you are creating a MOLAP cube off of a live database (not a data warehouse that is built right before you build the cube), you should always get matching data between the cube and the drill-thru. Whereas in 2000, there was always the risk that the data might be out of sink between the database and the cube, causing inconsistencies between the drill-thru results and the cube. And trust me, if the cube says there are 5 of something and the drill-thru shows 6, people will notice.

A couple of good things did come out of this new version though: instead of having one drill-thru option per cube (select your columns and go away), now you can specify which the drill thru options on a measures by measure basis. This is because all drill-thrus are now handled by cell level actions.

This is something that many of us talked about when cell level actions became available in 2000 by way of one of the services packs (sp2 or sp3, I don’t remember). If any of the 3rd party tools had implemented it, things would have been cool. Note: I’m sure the VERY first question I would have received would have been: “Can you put the results straight into Excel?” (sigh) You might be wondering if I’m trying to be funny there (yes and no), but I also know that the first remark once I was done would have been: “Why is it so darned SLOW?” (sigh)

Another new toy in Analysis Services 2005 is a Report Action. This is an action that loads up a specific Report Server report. You can even key off of a cell. But I’m not sure if Report Server is really ready for all of the options needed for true drill-thru yet – mostly because of parameter limitations. But this would be perfect for a number of options where a limited set of filters is a viable solution.

So where does that leave us? A bit further than where we were in 2000. We can now specify drill-thru options on a per measure basis, and we have multiple drill-thru options, we can even create a Report Server report to do some limited drill-thru.

But customers will still want to get more out of their drill-thru: contextual information, links to other systems, related documents, etc. It isn't just about the raw data anymore to give the customers a full view of what there is to know (I've had to help write two drill-thru web sites because the customers did not like the standard output). I fear I will still be creating custom drill-thru web sites for a while to come. I guess I should be happy about that.