MS SQL Server OLAP - Dimensions required for a valid drillthrough

Author

Dimensions required for a valid drillthrough

badlydressedboy

2005-09-22, 11:24 am

Hi,
Im having a lot of trouble with what *should* be straightforward
drillthrough. My situation is this:

I am using MDX to author a report that has a city dimension on ROWS, so down
the left hand side I get London, Bristol, Nottingham, Leeds etc...

With me so far?? Good :-)

I have a couple of measures on COLUMNS, such as leavers and joiners. This
results in a usefull enough report but I would like to be able to
drillthrough on the cells - the problem is for every cell in a row I am
getting exatly the same drillthrough results - meaning I am getting 'row
specific' drillthrough, instead of the desired 'cell specific'. E.g. - The
Leavers cell for a row has a value of 10 and the Joiners cell has a value of
5 - if I drillthrough on either of these cells I get a resultset containing
15 rows.

Still with me?? maybe?

I understand that this is due to drillthrough needing to be passed 2 tuples,
1 representing the row axis and 1 representing the column axis. In the report
I have outlined I have no dimension on the COLUMNS dimension, just measures,
so I can see how just 1 tuple is available to the drillthrough and that
explains how every cell in a row returns the same data.

So what I need at this stage is a dimension to go on the COLUMNS axis to
represent the measures. I have implemented this but it just doesn't feel
right doing it - has anyone else had to implement a measures axis before just
to enable accurate drillthrough???

Finally the MDX I am left with to query both the city and measure axis as
well as the correct measures is this:

SELECT {[Measures].[Joiners], [Measures].[Leavers]} ON COLUMNS,
NON EMPTY [City].[CityName].members on rows
FROM weeklystats
WHERE
([date].[year].[2005].[Q3].[September].[week-39],[KPIMeasure].[Joiners &
Leavers].[Joiners])

This works but only for the Joiners - I can't find a way to add 'Leavers' to
the WHERE clause.

Any experience/help with this area in general would be hugely appreciated!

Cheers

Al

Darren Gosbell

2005-09-26, 3:23 am

What you are seeing is a "by design" behavour. The measures dimension is
treated a bit different to the other dimensions. Measure columns are not
used in filtering for drillthough as they are the result columns.
Usually each row has some sort of value in each measure.

In actual fact your "leaver" and "joiners" are attributes of an employee
dimension, not really a measure or a standalone dimension. (although a
stand alone dimension is probably your only choice for accurate
drillthrough in AS2k)

> - has anyone else had to implement a measures axis before just
> to enable accurate drillthrough???

I have not done it for drillthrough, but I do have an old cube that was
built back in the OLAP 7 days when there was a 64 measure limit per cube
and we had more than 64 measures - so we created a cube with 1 measure
and a "variables" dimension - not pretty but it works.

I think it would be easier in your situation as you already have the
correct amount of rows, you just need a new dimensions to indicate
employee status. Which given the current technology is a valid approach.
(the treatment of attributes and dimensions is a lot better in AS'05)

What you will have to watch is how you handle the changing nature of
these attributes over time. ie. new employees this month are not still
new next month. In this regards adding a "real" dimension makes it
easier to handle as you can record an employee status against each time
period.

Hope this Helps.

--
Regards
Darren Gosbell [MCSD]
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <51A63923-ED81-42F8-89BA- D0A2EDD412D2@microso
ft.com>,
badlydressedboy@disc
ussions.microsoft.com says...
> Hi,
> Im having a lot of trouble with what *should* be straightforward
> drillthrough. My situation is this:
>
> I am using MDX to author a report that has a city dimension on ROWS, so down
> the left hand side I get London, Bristol, Nottingham, Leeds etc...
>
> With me so far?? Good :-)
>
> I have a couple of measures on COLUMNS, such as leavers and joiners. This
> results in a usefull enough report but I would like to be able to
> drillthrough on the cells - the problem is for every cell in a row I am
> getting exatly the same drillthrough results - meaning I am getting 'row
> specific' drillthrough, instead of the desired 'cell specific'. E.g. - The
> Leavers cell for a row has a value of 10 and the Joiners cell has a value of
> 5 - if I drillthrough on either of these cells I get a resultset containing
> 15 rows.
>
> Still with me?? maybe?
>
> I understand that this is due to drillthrough needing to be passed 2 tuples,
> 1 representing the row axis and 1 representing the column axis. In the report
> I have outlined I have no dimension on the COLUMNS dimension, just measures,
> so I can see how just 1 tuple is available to the drillthrough and that
> explains how every cell in a row returns the same data.
>
> So what I need at this stage is a dimension to go on the COLUMNS axis to
> represent the measures. I have implemented this but it just doesn't feel
> right doing it - has anyone else had to implement a measures axis before just
> to enable accurate drillthrough???
>
> Finally the MDX I am left with to query both the city and measure axis as
> well as the correct measures is this:
>
> SELECT {[Measures].[Joiners], [Measures].[Leavers]} ON COLUMNS,
> NON EMPTY [City].[CityName].members on rows
> FROM weeklystats
> WHERE
> ([date].[year].[2005].[Q3].[September].[week-39],[KPIMeasure].[Joiners &
> Leavers].[Joiners])
>
> This works but only for the Joiners - I can't find a way to add 'Leavers' to
> the WHERE clause.
>
> Any experience/help with this area in general would be hugely appreciated!
>
> Cheers
>
> Al
>

Sponsored Links