Since SQL Server Analysis Services 2000, the product has supported a feature called drillthrough. Drillthough enables a user to select a cell and then retrieve an underlying result set from the source data for that particular cell. This functionality provides users with the ability to get a more detailed explanation as to why a certain aggregate or cell contains the value that it does. It combines the pre-aggregated nature of OLAP with the detailed records of a relational data store.

In Analysis Services 2000 you configure the drillthough settings via the Cube Editor dialog and then going Tools->drillthrough options. You specify the tables, columns and an optional drillthrough filter. In Analysis Services 2005 you configure drillthrough settings by creating a drillthough action in the cube editor’s Actions tab. Like its predecessor, in Analysis Services 2005 you can optionally provide a filtering condition. Because the drillthrough settings are significantly different in Analysis Services 2000 compared to Analysis Services 2005/2008, this issue only applies to 2000 to 2008 in-place upgrades. I have personally tested Analysis Services 2005 to 2008 in-place upgrades with OLAP cubes that contain drillthough settings and they do in fact get migrated.

In my Analysis Services 2000 test case I am using the out-of-the-box FoodMart 2000 AS database. It contains two objects that have drillthough settings defined, the HR & Sales cubes. Upgrade Advisor will detect and alert you when it scans an Analysis Services 2000 instance which contains drillthrough settings. The Upgrade Advisor corresponding help topic simply states that ‘While drillthrough exists in Microsoft SQL Server 2005 Analysis Services, drillthrough settings are not migrated from previous versions of Analysis Services’. Since Analysis Services 2005 is not subjected to this issue the Upgrade Advisor does not report anything back when it scans an AS 2005 instance.   

Prior to performing the actual SQL Server 2008 upgrade from a SQL Server 2000 instance, make note of the existing drillthough settings. Perform the actual upgrade (we always recommend using a test machine configured to mimic your production environments to test upgrade first) and then recreate your drillthough settings in your SQL Server 2008 Analysis Services instances.

Derek Comingore