I have a fact table that has a three dimensional hierarchy set up as a shared
dimension

Dept
Class
SubClass


The Dept/Class is a many to many relationship. The class/subclass is a many
to many relationship. As a result, I am not about to assume a class given a
subclass. I have link tables that link the Dept/Class and Class/subclass
relationships.

When I use the link table in the cube designer then my calculation will not
work because in am getting multiple counts of the same record. If I do not
use the link table then I can’t “Drill” down.

Lastly, I like the shared dimension of dept/class/subclass because I want to
see if there is “missing” subclass data.

Tables:

Fact Table (Dept, Class, Subclass, Items Purchased)

Dept (Dept ID, DeptName)

Class( ClassID, ClassName)
Link_Dept_Class( DeptID, ClassID) Many-To-Many

SubClass (SubClassID, SubClassName)
Link_Class_SubClass ( ClassID, SubClassID) Many-To-Many



Can anyone tell me how I should link the tables in the cube editor?


Thanks in advance.

--
Stewart Rogers
DataSort Software, L.C.