Design Issue by Universe with lot of dimensions ..

[Former Member]

Hello,

I have 5 to 6 Fact tables with a big amount of diemnsions (about 50). These Dimensions should be used for categorizing and filtering data in different Reports. Looking for a design solution for my Universe i thougth about 2 Scenarios:

1. Dimension Tables

for every Diemnsion i have to create appropriate table and join it with fact Table (All 50). Drawback: Performance will suffer with 50 Joins to the Fact Table.

2. Without Dimension Tables

Only provide the Dimensions directly from tha fact table. Drawback: less flexibility being dependent from the Input in the foreign key of fact table second drawback we have to create same dimensions for every fact table (5 fact x 50 Dimensions)

Any proposition for an adequate solution ..??

Thanx

[Former Member · Accepted solution]

You need to consider your data warehouse design if you are suggesting that you should take dimension objects from the fact tables.

This sounds like it should be a standard multi-star universe.

Step 1:

Insert your tables

Step 2:

Create your joins, making sure that you set the one-to-many cardinalities in the right direction

Step 3:

Click on detect contexts; there should be one context suggested for each fact table. Accept them all.

Step 4:

Build your classes and objects to add value, not to simply replicate your data structure.

We can get on to best practice for universe design at a later date.

One BIG caveat when inserting dimension tables. Do they mean the same thing to each fact table? If they do, then they are conformed dimensions. If they are dimension tables that have different uses for different fact tables then they should be aliased (e.g usage of date table as order date vs. employee start date)