Fact constellation schema

building semantic layer
1

Hi BO Gurus,

We have the 5 fact tables with aggregation as:

Fact Table1 - Hourly ; Fields - Hour, Invoice Num, PO Num, Qty, Amount
Fact Table2 - Daily ; Fields - Date, Invoice Num, PO Num, Qty, Amount
Fact Table3 - Weekly ; Fields - Week, Invoice Num, PO Num, Qty, Amount
Fact Table4 - Monthly ; Fields - Month, Invoice Num, PO Num, Qty, Amount
Fact Table5 - Quarterly ; Fields - Qtr, Invoice Num, PO Num, Qty, Amount

The above fact tables are shared by different dimension tables such as PO Dept, Sales Dept, Mktg Dept, FI dept. The schema looks like fact constellation schema.

Can anyone please tell me these;

  1. When creating the classes and objects for fact tables in the universe, do I need to create all the objects in those 5fact tables for 5 times, such as:

Class1 - Hourly
Objects - Hour, Invoice Num, PO Num, Qty, Amount

Class2 - Daily
Objects - Date, Invoice Num, PO Num, Qty, Amount

Class5 - Qtrly
Objects - Qtr, Invoice Num, PO Num, Qty, Amount

  1. Do I need the context for fact table.

  2. How to tune the performance of fact constellation schema.

Thanks in advance
Madhuri

madhum1078 (BOB member since 2007-07-20)

2

Your best bet is to create 1 set of objects, with Aggregate Awareness. That’s what it’s for.

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

3

Madhuri,

Look at these 2 columns in all the 5 facttables: ‘Invoice Num’ and ‘PO Num’.
So this is probably the smallest granularity in all the 5 facttables.

Maybe it’s not necessary to use the facttables with daily, weekly, monthly and quarterly ‘aggregates’.
Assuming all information is present in the ‘hourly’ facttable and ‘hourly’ means a column with date-time and not just a time.

When you have a date-time column you can calculate all date-types (day, week, month and quarter). And year and century…
Don’t forget to aggregate (sum()) the Qty and Amount. :mrgreen:

I wouldn’t be supprised if the facttables with daily, weekly, monthly and quarterly ‘aggregates’ were just views on the hourly table. :lol:

Hope this helps,
Johan

JdVries :netherlands: (BOB member since 2006-02-06)