Can anyone help? I have a dimension with two levels. The lowest level
contains about 1000 members and is disabled in the cube (see below).
The dimension is joined to a fact table via Level 1 and when
processing causing duplication. Is there a way to join on the distinct
members of Level 1? I've tried adding a formula for custom rollup, but
it doesn't work. I'd like to avoid adding another dimension table with
only level 1.

Dimension 1
Level 1 – 25 Members
Level 2 – 1000 Members

I've tried the following rollup formula in the cube designer for
Dimension 1 - Level 1

Sum({Distinct({[Product].CurrentMember})}, [Measures].CurrentMember)

which gives the below error:

Unable to display cell properties.
Infinite recursion detected during execution of calculated member
Sum({Distinct({[Product].CurrentMember})}, [Measures].CurrentMember)
An error occurred during attempting to get a cell value

This 2002 post provides 2 options, snowflake and unique level key, which
work without need for custom rollups:

http://groups.google.com/groups?q=di...snowflake&hl=e
n&lr=&ie=UTF-8&oe=UTF-8&selm=3D6A3014.A0C9DB0D%40dsslab.com&rnum=2
tables,
or snowflake them so that SKU information is in a separate table from
category information. (You could do both, too.)

In order to optimize the joins, ensure that Category keys are unique
within
the level. Then, AS2K won't attempt to join in the product dimension
when it
processes the cube. The join is what throws off the numbers, as you get
N
fact rows per 1 dimension row.

Your Forecasts will still have SKU disabled and join to Prod_catg_key in
the
category table (manually ensure this is set in the cube editor). You SKU
information will join to SKU key.

HTH
...

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks! I changed the dimension structure to snowflake (multiple
tables) using a view. Works great and so simple.


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