I have a star dimension that has 2 levels (the higher level is specifically
to break up the members of the lower levels to avoid the 64,000 per parent
limit).
level 1 = key value/100 (field name xxx_lvl_key)
level 2 = key value (field name xxx_key)

The link between the dimension table and the fact table in the cube is from
the level 2 key value (xxx_key) of the dimension to the fact key value in
the fact table (fact_xxx_key).

when I drill through on this cube in analysis manager, the join between the
fact table and dimension table is executed in SQL is the level 1 field
dimension.xxx_lvl_key = fact.fact_xxx_key.

this gives the incorrect results because either
1) the values do not match and I get nothing
2) the keys that match the key value/100 are displayed showing too many
results

join in SQL for drillthrough should be the level 2 key joined to the fact
table
dimension.xxx_key = fact.fact_xxx_key.

Is there a way I can control this behavior in the set up of the cube in
analysis manager (we use OWC and pivot table services to render the cube for
the user).

thanks - Sandy

A couple of things to try out:

- Confirm in the Cube Editor that the "Member Key Column" for level 1 is
from the dimension, not the fact table (the level 2 leaf-level "Member
Key Column" can be from the fact table, if the dimension schema is
optimized)

- Disable drill-through, save the cube and then re-enable drill-through
(original settings should be restored).


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks

- confirmed that the Member Key column for level 1 is set to the dimension
and level 2 was set to the fact table key column (optimized) - no change
needed

- I disabled drill through, saved and re-enabled drill through - tested
again and that fixed the issue (thank you) - when should I try this
technique in the future? Its an easy fix, just not sure why it worked!

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Glad that worked out for you - don't really know how and when Cube
Drill-Through MetaData gets out of sync with updated schema info (maybe
[MS] folks can comment?), but disabling, saving and re-enabling seems to
regenerate it.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***