Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Analysis Services 2005 Drillthrough Action Expand / Collapse
Author
Message
Posted Monday, May 19, 2008 10:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
I want to be able to drill into the cube cells in Analysis Services, but I do not want the returned data to be at such a low level of detail. Because I am incrementally processing data, I have reversal records and changes in the fact table. Rather than displaying each individual fact row, I want to basically group by the selected dimensions I have selected and sum the measures.

Drillthrough does not seem to give me any control over the detail being returned. Since I am using Excel as my UI, I may be able to program something to group my data there, but I was hoping for something that I could do at the cube level.

I started looking at rowset actions, but I cannot find any examples to help me figure out how to build the WHERE for my MDX based on the MDX statement being drilled into.

Has anyone else tried to do any of this?
Post #502955
Posted Monday, May 19, 2008 1:57 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
Bummer - no replies yet.

I'll reply myself because I think I found something that is going to work. If anyone sees an issue, please let me know.



At first, I figured this would be a disaster of complicated expressions and probably a reporting services report to support.
I found out quickly that I was correct except for one option that I found.

When setting up a drillthrough action, the UI tries to drive you to have the action target and the returned measures from the same measure group.
I figured that this may not necessarily be a requirement, so I tried it and found that I could make the target different than the returned measures (without the GUI) – the measures all must be from a single measure group, but that measure group does not need to be the action target.
The only requirement is that the drillthrough measure group of the drillthrough action be associated with all of the dimensions that make up the cell (which makes sense).

Since drillthrough shows only the level of detail to the distinct dimension keys, this allowed me to create a measure group that did not include the EndDateKay or ActivityDateKey and this rolled up the returned drillthrough data.
Outdated records appear as 0 units / 0 seconds.

This is clearly outside of the box for drillthrough, but it appears to work fine.
The biggest down side to me is that there is a hidden measure group that will be an aggregation of the spot data at a detailed level – nearly doubling the size of the cube data.
On the other hand, creating aggregations on this measure group would be useless, so it is really just the data that would be added to the cube.
Post #503118
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse