Interesting Join Issue 2

[rohan77]

We are currently trying to find out as to what is the best practice to try to do joins between Fact tables and dimension tables.

Lets take an e.g.
We have 7 tables

1. ITEM Table (I1 as PK)
2. VNDR Table (V1 as PK)
3. WHSE Table (W1 as PK)
4. WHSE_ITEM table ( W1 and I1 as PK)
5. VNDR_ITEM table (V1 and I1 as PK)
6. WHSE_ITEM_VNDR Table ( W1, I1 and V1 as PK)
7. Fact Table (It is at WHSE, ITEM and VNDR level)

Now how can we join these tables

We need to get objects from all the dimension tables as well as the fact table.

There will be lot of adhoc reporting from the universe, so the design should be as generic as possible


One way is to have
1) Fact stays in middle and have joins to all dimension and relationship tables from fact. In this case any queries involving only dimension will have to go thru fact or have shortcut joins.

F ---> I , F--> W, , F--> V. , F--> WI, , F--> VI, , F--> WVI
and Shortcut Joins between WVI TO W, V ,I AND SHORTCUT BETWEEN WI TO W AND I ALSO VI TO V AND I


2) Other is to Join fact with Relationship table and from relationship tables have joins to Base dimension table

F--> WVI , WVI --> IW , WVI --> VI , IW --> I , IW --> W , IV --> I , IV ---> V
( To Resolve the loop we can have alias of Item table )
we are following one context per Fact ( there are many other fact tables)

and Shortcut Joins between F to W, I , V , IW AND IV

These are the things we are trying out. Can some one tell us the best way to handle joins between base dimension tables, relationship tables and fact tables.
Any other suggestion.......

Thanks and Regards
Rohan

 

[blom0344]

Basically, if you adhere to a starschema, with dimensiontables on the facttables and every facttable its own context, you will have the cleanest set-up.

For query and analysis, what do you expect from the ' relationship' tables you mention?


Ties Blom
Information analyst

 

[rohan77]

The user might want to see the report based on attribute from WHSE_ITEM, ITEM and WHSE table.
For e.g. From relationship tables the user would like to see, reports like whats the case size, the arrangement of the cases from a particular WHSE, for a particular Item.
Now the shortcut joins will not work in the above scenario and if we go through the facts it might filter out some records.

But we expect around 10% of such kind of query and analysis from the dimension tables from the users otherwise for 90% of analysis, the users will go through the fact tables.

Thanks and Regards
Rohan
Programmer Analyst

 

[skrandel]

You can't build a universe that will make everyone happy.

Do it right and train the users on how to use it. Dimension-only queries will (and should) prompt users for the appropriate context.

Steve Krandel
Symantec