Here's one approach that we are working with, one that was suggested to us
by Amir Netz:
Create a single dimension that includes both hierarchies. For any members
that appear in more than one hierarchy, create a separate row in the
dimension table for each instance and assign it a unique member ID. Then,
you apply a view to your fact table that creates a separate copy of any data
row that refers to a dimension member that has multiple instances. This
allows you to enter data once for each member but display the same values
for each member instance. For purposes of creating the view, you will need
to add a column in your dimension table to map a secondary member instance
to the original one.
We have experimented with this method, and it seems to work well thus far.
The one drawback is that the end result appears in Analysis Services as a
single dimension with multiple top-level members rather than multiple
hierarchies.
> Hi,
> How to handle to following situation ?
> Two hierarchies on PRODUCT (PRODUCT.HIER1 and PRODUCT.HIER2, both
> parent-child dimensions) do not share the same group of leaf-level PRODUCT
> values. Some leaf-level values are present in both hierarchies, other are
> only present in the first hierarchy, again other are only present in the
> second hierarchy.
> When I do not include the all leaf-level values of hier1 in PRODUCT.HIER2
> and vice-versa, not all fact-table rows are processed. When I include
them,
> I cannot hide them.
> Anyone some ideas how to solve this ?
> Mark Longin
by