Dimension based on 2 different tables 1

[drkestrel]

I have an Organisation table with org_code and parent_org_code to link the hirerarchy information

I also have a Buildings table, with parent_org_code linking to an organisation. Only an organisation on Hirerarchy level 4 could have 0 or N buildings.

So effectively, I need a dimension based on 2 different tables. Is this possible? If so, how?

 

[ram1975]

Yes u can do that using Parent child dimension. Use the wizard and select the third option, which will guide you in doing so. I hope you are referring to analysis services 2000 only.

 

[drkestrel]

This is effectively the kind of structures I have

Org A being parent of OrgB (Hirererachy defined using org_code and parent_org_code in organisation table)

Org B being parent of OrgC (Hirererachy defined using org_code and parent_org_code in organisation table)
OrgC being the 'parent' of Buildings1, Buildings2 and Buildings3 (Hirererachy defined using parent_org_code in building table)

So effectively, I am using 2 types of schema.

Not possible to use a view combining the 2 tables together, because my cube needs to be ROLAP (I need to see real time updates).

 

[MDXer]

This would not be a parent child dimension but rather a standard dimension based on a snowflake schema. You would build this dimension the same as any other except you would have both tables in the editor. using one of the following methods

Wizard:
1) Right Click and select Wizard
2) Choose Snowflake Schema
3) Select the Appropriate Tables
4) Specify the Joins if not already done So
5) Select The Levels
6) Specify the key columns
7) Set options
8) Name and Process

Editor:
1) Right Click and select Wizard
2) Select Primary Table
3) In the area where the tables are displayed right click
and click on "Add Table"
4) Select the table that joins to your primary table
5) Select the column to join the tables if not already
created.
6) Create your levels as you would any other dimension.

NOTE:
A parent child dimension is a dimension created from a single table where 2 related columns form the hierarchy. Parent Child dimensions are typically used for Employee - Manager relationships the table looks somthing like the following

Employee Manager
-------- -------
Bob
Tom Bob
Julie bob
Peter Julie
Frank Tom
Jill Tom

Specifying the Manager as the parent and employee as the child the hierarchy will be built with the appropriate members reporting to their parents such as

Bob
Tom
Frank
Jill
Julie
Peter


"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck