I have a dimension table called [Case] that has a comprenensive picture of
legal cases. In the cube Editor I have created two shared dimensions that
are built off of [Case]. The decision to do this has caused the measures in
the cube to be double counted. When I tryed merging the two shared
dimensions together, I faced issues with getting case counts. The counts
would only work if the user didn't slice on other members of the dimension
which is why I had originally created two shared dimensions. One for getting
case counts and the other for slicing on case status and casetype/subtype.
Has anyone run into this problem or found a solution for this type of
problem?

Two comments:
1) first, the double counting is a result of the way we form SQL statements.
The best way to eliminate it is to create a view which exposes the FK for
[CASE] in two fields. One for each dimension. That should be OK.
2) I think that what you are seeing with the slicing issue is not uncommon
with mulitple hierarchies. Think of the case of Time.Fiscal and
Time.Calendar. IF you are going to drill down into one, then the other must
be set to the ALL level. What happens in a multiple hierarchy dimension
(with SQ2K) is that they are treated as independent dimensions. Like any
multi-dimensional query, slices retrict the final result across all
dimensions. Thus if you were slicing by Time.Calendar.March and
Time.Fiscal.March, you end up getting the *intersection* of the two
dimensions (i.e. just those members in common between the two slices)
something you probably don't want. Thus the requirement is that if you are
dealing with Time.Calendar.March then Time.Fiscal must be at the ALL level
so you are guaranteed to capture all of Time.Calendar.March. This is simply
an artifact of the way multiple hierarcies work.

--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Hossy" <Hossy (AT) discussions (DOT) microsoft.com> wrote

Dave,

I think I resolved the problem. The solution was to make sure both shared
dimensions had dimension members that were the foreign key to the fact table
(CaseID). I'm 99% sure this problem of double summing is resolved. Thanks
for your help.

"Dave Wickert [MSFT]" wrote: