Drillthrough results

the descriptions of the Drillthrough vary somewhat, but this is the one I need interpretation on: "Drillthrough is the operation in which a user specifies a single cell and the Analysis server returns the most detail level data that contributed to that cell." This is from a page entitled 'enabling drillthrough in analysis services 2005.' I'm new to SSAS but have some history with other OLAP. I interpret the statement as meaning that the Drillthrough function will return the leaf-level data from the cube. Is this correct?

Basically what I want to do is get to the leaf node for all dimensions so that I can grab the keys and query the transactions files that support the value. This is the query i am running from Excel 2003:

Drillthrough maxrows 2500

Select

{[Dim Acct].[Account].&[1090]} on 0,

{[Dim Period].[Time].[Yr].&[YR].&[Q1].&[1]} on 1,

{[Dim CC].[Cost Center].[all]} on 2,

{[Dim Function].[Function].[All]} on 3,

{[Dim Grant].[Grant].[All]} on 4,

{[Dim Dept].[Department].[All]} on 5,

{[Dim Fund].[Fund].[All]} on 6,

{[Dim Scen].[Dim Scen].&[2007]} on 7

From [Dm GL]

return

key([$Dim Fund].[fund]) as [Fund],

key([$Dim Dept].[Department]) as [Department],

key([$Dim Grant].[Grant]) as [Grant],

key([$Dim Function].[Function]) as [Func],

key([$Dim CC].[Cost Center]) as [CC],

key([$Dim Acct].[Account]) as [Account],

key([$Dim Scen].[Dim Scen]) as [Year],

key([$Dim Period].[Month]) as [Period],

[$Dim Fund].[Fund] as [Fund],

[$Dim Dept].[Department] as [Department],

[$Dim Grant].[Grant] as [Grant],

[$Dim Function].[Function] as [Func],

[$Dim CC].[Cost Center] as [CC],

[$Dim Acct].[Account] as [Account],

[$Dim Scen].[Dim Scen] as [Year],

[$Dim Period].[Month] as [Period],

[$measures].[amount] as [amount]

The query runs reat, but the key values returned are for parent levels - not leaf. All of my dimensions except scenario and time are built using parent-child data source files.

Any insight would be appreciated.

Sully

My theory was that the drillthrough does not return the correct member names or keys when the dimension is built using a parent-child data source. Two of my dimensions, period and scenario, are not parent-child, and the correct information is returned. To test the theory on a parent-child dimension, I used my accounts dimension and performed the drillthrough on a leaf member. The parent of this leaf member had > 1 child. The drillthrough returned the parent instead of the member information for both name and key for the account dimension but it returned the correct amount.

This is a rather important feature. Is my drillthrough statement incorrect? I'd hate to try to convert a ragged parent-child. Has anyone else had problems with this or know a workaround?

Thanks, Sully

|||

As frequently happens, the problem was self-inflicted. In the process of debugging, I found the the query was in error. I'm just learning MDX and I just stumbled on the solution. Here is what works...

Drillthrough maxrows 3500

Select {[Dim Acct].[Account].&[BK6029]} on 0,

{[Dim Period].[Time].[Yr].&[YR].&[Q1]} on 1,

{[Dim CC].[Cost Center Index].[All]} on 2,

{[Dim Grant].[Grant].&[FPP]} on 3,

{[Dim Dept].[Department].&[3200]} on 4,

{[Dim Fund].[Fund].&[990]} on 5,

{[Dim Function].[Dim Function].[All]} on 6,

{[Dim Scen].[Dim Scen].&[2007]} on 7

From [testGL]

return

key([$Dim Fund].[dim Fund]) as [Fund],

key([$Dim Dept].[dim Dept]) as [Department],

key([$Dim Grant].[dim Grant]) as [Grant],

key([$Dim Function].[dim Function]) as [Func],

key([$Dim CC].[dim cc]) as [CC],

key([$Dim Acct].[dim Acct]) as [Account],

key([$Dim Scen].[Dim Scen]) as [Year],

key([$Dim Period].[month]) as [Period],

[$Dim Fund].[dim Fund] as [Fund],

[$Dim Dept].[dim dept] as [Department],

[$Dim Grant].[dim Grant] as [Grant],

[$Dim Function].[dim Function] as [Func],

[$Dim CC].[dim cc] as [CC],

[$Dim Acct].[dim Acct] as [Account],

[$Dim Scen].[Dim Scen] as [Year],

[$Dim Period].[month] as [Period],

[$measures].[amount] as [Amount]

I haven't determined why a parent-child dimension has to be referenced differently, but at least this works.

Thanks...