Drillthrough functionality is quite different in AS2k5 vs. AS2k. The latter originally queries the SQL server directly when you want to drillthrough to the data. In AS2k5, the drillthrough data is placed directly into the OLAP cube so you can drillthrough without ever connecting to the SQL database (assuming that is your source). Saying this, this functionality is on by default and does increase the size of the OLAP database. But performance is not affected and now you have this additional functionality. For more information, T.K. Anand wrote an excellent paper on this at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_anservdrill.asp
To provide an example against the AdventureWorks OLAP database (that comes with SQL Server 2005 Samples), you can query the Adventure Works Olap dB using the MDX statement:
select {
[Measures].[Internet Order Quantity]
} on columns, {
[Product].[Product Categories].children
} on rows
from [Adventure Works]
[Measures].[Internet Order Quantity]
} on columns, {
[Product].[Product Categories].children
} on rows
from [Adventure Works]
where it outputs the Internet Order quantities for all of the different categories. If you want to drillthrough, for example, just the Clothing category (one of the children of the [Product].[Product Categories] hierarchy, you need only to run the following MDX statement:
drillthrough maxrows 100
select
{[Measures].[Internet Order Quantity]} on 0,
{[Product].[Product Categories].[Clothing]} on 1
from [Adventure Works]
return
[$Product].[Model Name] as [Model Name],
[$Customer].[Full Name] as [Customer Name],
[$Customer].[Country-Region] as [Country Region],
[$Measures].[Internet Order Quantity] as [Orders],
[$Measures].[Internet Sales Amount] as [Sales],
[$Measures].[Internet Total Product Cost] as [Cost]
select
{[Measures].[Internet Order Quantity]} on 0,
{[Product].[Product Categories].[Clothing]} on 1
from [Adventure Works]
return
[$Product].[Model Name] as [Model Name],
[$Customer].[Full Name] as [Customer Name],
[$Customer].[Country-Region] as [Country Region],
[$Measures].[Internet Order Quantity] as [Orders],
[$Measures].[Internet Sales Amount] as [Sales],
[$Measures].[Internet Total Product Cost] as [Cost]
Note that you didn’t need to turn any drillthrough functionality on with the AdventureWorks OLAP dB – its already on. Cool, eh?!
Denny Lee 