Drill Through for Multiple Cells

"Ohjoo Kwon" schrieb im Newsbeitrag


>I understand, here, the multiple cells mean computed cell not from
> the fact table directly.
>
> In this case, there is issue of how to decide where the data of
> the cell originates from indeed.
>
> Of course, if you write your own custom code in your application,
> it'll be possible to implement some proper logics.

Thanks a lot for the answer.

I asked about the same issue that we discussed some days ago. With "computed
cell" you also mean aggregated values? Some info about the previous post...

>>>
With MS SQL Server 2005, MS Excel 2007. If I select multiple values in a
filter, drillthrough is not possible. An error message stating (translated
from German)...
"Show details" cannot be executed if several elements are selected in a
report filter. Choose a single element for each field of the report filter
before executing a drill through.
---
Same, you can drillthrough just one cell at a time.
---
I understand the [number of sold items] is already from two cells, (Germany,
[number of sold items]) and (Italy, [number of sold items])
<<<

Michael G. Schneider
 >> Stay informed about: Drill Through for Multiple Cells 

I do not mean normal aggregated values from the fact table directly.

I mean all kinds of calculated cells which some custom logics are applied
to.
For example, you can imagin calculated members, calcualted cells and so on.
Your case of Excel is also one of the cases as following.

With
member Geo.Country.SumOfTwo AS Aggregate({German, Italy})
Select ... From cube
WHERE Geo.Country.SumOfTwo



"Michael G. Schneider" wrote in message

> "Ohjoo Kwon" schrieb im Newsbeitrag
>
>
>>I understand, here, the multiple cells mean computed cell not from
>> the fact table directly.
>>
>> In this case, there is issue of how to decide where the data of
>> the cell originates from indeed.
>>
>> Of course, if you write your own custom code in your application,
>> it'll be possible to implement some proper logics.
>
> Thanks a lot for the answer.
>
> I asked about the same issue that we discussed some days ago. With
> "computed cell" you also mean aggregated values? Some info about the
> previous post...
>
>>>>
> With MS SQL Server 2005, MS Excel 2007. If I select multiple values in a
> filter, drillthrough is not possible. An error message stating (translated
> from German)...
> "Show details" cannot be executed if several elements are selected in a
> report filter. Choose a single element for each field of the report filter
> before executing a drill through.
> ---
> Same, you can drillthrough just one cell at a time.
> ---
> I understand the [number of sold items] is already from two cells,
> (Germany, [number of sold items]) and (Italy, [number of sold items])
> <<<
>
> Michael G. Schneider
>
>
 >> Stay informed about: Drill Through for Multiple Cells 

How about an URL action to call reporting services report instead of native
OLAP drillthrough ?


"Ohjoo Kwon" skrev i en meddelelse

>I do not mean normal aggregated values from the fact table directly.
>
> I mean all kinds of calculated cells which some custom logics are applied
> to.
> For example, you can imagin calculated members, calcualted cells and so
> on.
> Your case of Excel is also one of the cases as following.
>
> With
> member Geo.Country.SumOfTwo AS Aggregate({German, Italy})
> Select ... From cube
> WHERE Geo.Country.SumOfTwo
>
>
>
> "Michael G. Schneider" wrote in message
>
>> "Ohjoo Kwon" schrieb im Newsbeitrag
>>
>>
>>>I understand, here, the multiple cells mean computed cell not from
>>> the fact table directly.
>>>
>>> In this case, there is issue of how to decide where the data of
>>> the cell originates from indeed.
>>>
>>> Of course, if you write your own custom code in your application,
>>> it'll be possible to implement some proper logics.
>>
>> Thanks a lot for the answer.
>>
>> I asked about the same issue that we discussed some days ago. With
>> "computed cell" you also mean aggregated values? Some info about the
>> previous post...
>>
>>>>>
>> With MS SQL Server 2005, MS Excel 2007. If I select multiple values in a
>> filter, drillthrough is not possible. An error message stating
>> (translated from German)...
>> "Show details" cannot be executed if several elements are selected in a
>> report filter. Choose a single element for each field of the report
>> filter before executing a drill through.
>> ---
>> Same, you can drillthrough just one cell at a time.
>> ---
>> I understand the [number of sold items] is already from two cells,
>> (Germany, [number of sold items]) and (Italy, [number of sold items])
>> <<<
>>
>> Michael G. Schneider
>>
>>
>
>
 >> Stay informed about: Drill Through for Multiple Cells 

"Ohjoo Kwon" schrieb im Newsbeitrag


>I do not mean normal aggregated values from the fact table directly.

Thanks a lot for the answer.

So, when working in Excel, you see an aggregated value (for example the sum
for Germany and Italy). SQL Server is able to show the value. However, when
double clicking that value, why isn't SQL Server able to show the rows that
contributed to that value?

Michael G. Schneider
 >> Stay informed about: Drill Through for Multiple Cells 

Becuase native OLAP drillthrough has limitations. Its not sql server. Its
mdx and mdx likes tupples...

I think i saw excel code somewhere to enable drillthrough with multiple
selection. But an URL action to activate reporting services report or an
rowset action might be possible.

U need to code though.


"Michael G. Schneider" skrev i en meddelelse

> "Ohjoo Kwon" schrieb im Newsbeitrag
>
>
>>I do not mean normal aggregated values from the fact table directly.
>
> Thanks a lot for the answer.
>
> So, when working in Excel, you see an aggregated value (for example the
> sum for Germany and Italy). SQL Server is able to show the value. However,
> when double clicking that value, why isn't SQL Server able to show the
> rows that contributed to that value?
>
> Michael G. Schneider
>
>
 >> Stay informed about: Drill Through for Multiple Cells 

Although this case looks simple and you know where the data originated from
intuitively,
it'll be not so simple problem in terms of MDX query engine.

Is it possible to have some algorithm to cover all kinds of complex
calculation cases?
Of course, I also expect SSAS supports some cases for some specific cases
like this.

Anyway, if you can catch the related meta data at user interface and write
your own code,
you can implement your drillthrough function or link them to external apps.

Ohoo.

"Michael G. Schneider" wrote in message

> "Ohjoo Kwon" schrieb im Newsbeitrag
>
>
>>I do not mean normal aggregated values from the fact table directly.
>
> Thanks a lot for the answer.
>
> So, when working in Excel, you see an aggregated value (for example the
> sum for Germany and Italy). SQL Server is able to show the value. However,
> when double clicking that value, why isn't SQL Server able to show the
> rows that contributed to that value?
>
> Michael G. Schneider
>
>
 >> Stay informed about: Drill Through for Multiple Cells 

"Ohjoo Kwon" schrieb im Newsbeitrag


> Although this case looks simple and you know where the data originated
> from intuitively, it'll be not so simple problem in terms of MDX query
> engine.

Thanks a lot for the answer.

Unfortunately, I do not speak MDX. But I can imagine that there are
problems, if MDX does not allow to do this natively.

It just seems to be an easy task, at least theoretically. Suppose I have two
dimensions D1 and D2 with selected values D1a, D1b, D2a, D2b then I would
simply have to fetch the rows for

- D1a D2a
- D1a D2b
- D1b D2a
- D1b D2b

and union them together. Of course there would be performance issues, if the
number of dimensions or the number of selected values increases.

Michael G. Schneider
 >> Stay informed about: Drill Through for Multiple Cells 

"michael vardinghus" <xx> schrieb im Newsbeitrag


> Becuase native OLAP drillthrough has limitations. Its not sql server. Its
> mdx and mdx likes tupples...
>
> I think i saw excel code somewhere to enable drillthrough with multiple
> selection. But an URL action to activate reporting services report or an
> rowset action might be possible.
>
> U need to code though.

Thanks a lot for the answer.

Coding would be no problem. However, unfortunately no MDX. But I will look
into it.

I just wonder why such a limitation, which is hard to explain to an end
user, still exists. An end user, working with MS Excel, can hardly
understand why a double click on a worksheet cell works in one case but not
in another.

Michael G. Schneider
 >> Stay informed about: Drill Through for Multiple Cells 

Michael,

A few things. Yes, Ohjoo states, any cell that is the result of MDX
being applied to it, that cell is at least partly calculated (or
dynamic). This excludes normal aggregation, so if you're looking at
cells that are at a Time dimension's "Quarter" level, and Months is
underneath that level, that is an aggregated level, and you'd be able
to drill through at on those cells (assuming that no other calc were
in play).

So, any Calculated Member or Calculated Measures via MDX, whether
through an MDX Script (SCOPE...) or via a Calculated Member.

Here is one potential work around. If the calculations are relatively
simple, like taking 10% of Sales or doubling the certain product's
sales figures, you could instead of using MDX to that type of thing,
drive that work in the DSV or as a normal view or transformation (ETL)
on the fact table. That would alows you drill on those cells.

The frustrating thing is that even for simple calcs, as above, using
MDX would even prevent drillthrough on those guys, so the SSAS engine
makes no discernment between the MDX complexity...it's if the cell has
any calculations involved, no drill through. You'd be tempted to
think, "well I'm just applying some simple multiplier of sales, so
serve up the Sales fact rows...", but nothing doing. So yes, you can
code around it, or perhaps put in a custom action and just have it
show the straight-up facts related to the cell in context. You could
also potentially do a UDF function built in .Net and just have it
receive the cell context and grab what you want out of the fact table.

If you had an alternative front-end, there might be one out there that
does a better job of handling this. Were the UI smart enough, it
could side-step a good deal of this problem by providing such custom
functionality as a built-in value-add. I know that proClarity, at
least v6, had this limitation, as did RS.

Good luck!

-exologic

::: Pain with Essbase? Use CubePort, The BI Porting App :::
::: web: exologic.com/products.htm :::
 >> Stay informed about: Drill Through for Multiple Cells 

schrieb im Newsbeitrag


Thanks a lot for the detailed answer.

> Here is one potential work around. If the calculations are relatively
> simple, like taking 10% of Sales or doubling the certain product's
> sales figures

In the scenario that I struggle with, there are no obvious calculations
involved. Suppose there is a dimension Country and a measure Amount. The
fact table contains...

Germany 100
Italy 200
Spain 300
France 400

If the user looks at the aggregated amount, he sees 1000. He executes "show
details" in MS Excel and the above four rows are displayed.

Now the user does some filtering. He only selects Germany and Italy. He then
sees 300. He again executes "show details" in MS Excel, but an error message
is given.

Michael G. Schneider
 >> Stay informed about: Drill Through for Multiple Cells 

I don't speak MDX either. I miss the same function as you want, but it's
upon Microsoft as of now.
Refer to the comments from fimiani.DeleteThis@exologic.co.kr about the issue.

I found good information on this issue at
http://www.biblogs.com/index.php?s=codeplex
Refer to "Drillthrough on calculated measures" part.

Ohjoo


"Michael G. Schneider" wrote in message

> "Ohjoo Kwon" schrieb im Newsbeitrag
>
>
>> Although this case looks simple and you know where the data originated
>> from intuitively, it'll be not so simple problem in terms of MDX query
>> engine.
>
> Thanks a lot for the answer.
>
> Unfortunately, I do not speak MDX. But I can imagine that there are
> problems, if MDX does not allow to do this natively.
>
> It just seems to be an easy task, at least theoretically. Suppose I have
> two dimensions D1 and D2 with selected values D1a, D1b, D2a, D2b then I
> would simply have to fetch the rows for
>
> - D1a D2a
> - D1a D2b
> - D1b D2a
> - D1b D2b
>
> and union them together. Of course there would be performance issues, if
> the number of dimensions or the number of selected values increases.
>
> Michael G. Schneider
>
 >> Stay informed about: Drill Through for Multiple Cells 

Michael,

Is there a way you can isolate and determine if it is a limitation is
SSAS or the Excel UI?
Do you have some other UI that has drill-through capabiltiy? It might
be helpful to know whether 1) there is a calc in there somewhere that
you're not aware of (which SSAS would restrict from drill-through), or
2) Excel is dropping the ball. If the other UI (such as
ProClarity...you can download a quickie eval or trial) and if that
also has issues, then I would say there must be some calc at play.

Do you perhaps have an MDX Expression setup in the measure itself?
Are there other measures or just "Amount". If so, try one of the
other measures and see if that measure has the same issue.

This would at least attempt to isolate the issue.

-exologic

::: Pain with Essbase? Use CubePort, The BI Porting App :::
::: web: exologic.com/products.htm :::
 >> Stay informed about: Drill Through for Multiple Cells 

On the German thing, it could be 1) that you (unwittingly?) created a
SSAS Translation and you tag it in language A, or 2) if you use an
connection string to the SSAS server that uses LCID tag that relates
to a specific language, or 3) if you have a machine that has as its
Windows locale setting in a certain langage...if any of the above 3
are true, it should display error messages in that language, in your
case German. Does any of that ring a bell? 2 is unlikely unless
someone went out of there way to do that.

There are several german LCIDs, see http://www.microsoft.com/globaldev/reference/lcid-all.mspx,
but the primary German LCID is 1031. English is 1033, FYI.

If you can flip it back to English, or find a German-speaking chap,
you could know what the error message is. I would also suggest using
Babelfish on the web to type in or cut & paste in portions of the
error message to glean what it means...http://babelfish.altavista.com/

MDX has nothing in particular to do with drillthrough. It is more
about calcs. The SSAS engine itself is really the server that
provides the facility for drillthrough, just yielding the correct
context for any given cell (DimA.CurrentMember, DimB.CurrentMember,
etc.) and that just dynamically builds a SQL statement behind the
scenes for the cell you picked and presents a resultset from the fact
table (not directly with 2005, it does not do a real-time drillthough
back to the fact table, just takes an all-leaf level set of base cells
in the cube as of the state when the cube was last processed).

-exologic

::: Pain with Essbase? Use CubePort, The BI Porting App :::
::: web: exologic.com/products.htm :::
 >> Stay informed about: Drill Through for Multiple Cells