SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Analysis Services 2005 Drillthrough Action


Analysis Services 2005 Drillthrough Action

Author
Message
Michael Earl-395764
Michael Earl-395764
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21747 Visits: 23078
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?
Michael Earl-395764
Michael Earl-395764
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21747 Visits: 23078
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search