Should 1 denormalized fact table design be used for the above scenarios? Of course not . Take, for example, Oracle Business Intelligence Applications HR Analytics 7.9.6.3 . Oracle designers created work force fact tables of various designs to capture the business process being measured: Transactional fact tables for employee salary, periodic fact tables for headcount, and accumulating fact tables for the HR recruitment process.
Below is a primer of Kimball fact tables and the characteristics of each one. Before selecting the fact table type to use for your design, always make sure you:
1) Identify the business process you are trying to measure
2) Determine the grain of the fact table
3) Identify the attributes of the fact table (dimensions)
4) Identify the measures to be captured in the fact table.
Also highly recommended: Read Kimball's Data Warehouse Tool kit. This is a must read for any OBIEE Architect. The table below is referenced from the above book.
CHARACTERISTIC
|
TRANSACTION GRAIN
|
PERIODIC SNAPSHOT GRAIN
|
ACCUMULATING SNAPSHOT GRAIN
|
|---|---|---|---|
Time period represented
|
Point in time
|
Regular, predictable intervals
|
Indeterminate time span, typical short-lived
|
Grain
|
One row per transaction event
|
One row per period
|
One row per life
|
Fact table loads
|
Insert
|
Insert
|
Insert and update
|
Fact row updates
|
Not revisited
|
Not revisited
|
Revisited whenever activity
|
Date dimension
|
Transaction date
|
End-of-period date
|
Multiple dates for standard milestones
|
Facts
|
Transaction activity
|
Performance for predefined time interval
|
Performance over finite lifetime
|
These three fact table variations are not totally dissimilar because they share conformed dimensions, which are the keys to building separate fact tables that can be used together with common, consistent filters and labels. While the dimensions are shared, the administration and rhythm of the three fact tables are quite different.
In OBIEE it's hard to make charts out of Accumulating Snapshot fact tables. OBI seems to want the data arranged in a more vertical/normalized form.
ReplyDeleteHi Myles.
DeleteYes accumulating snapshots are difficult to implement and require a partnership with your ETL team. For advanced repository designs like an accumulating snapshot, it is important that the RPD & ETL teams have a clear understanding of how a design decision in the ETL layer can impact the RPD and vice versa.
You can definitely (and should) used denormalized star schemas within OBIEE because one of the main advantages of a star schema is the simplicity of the query that OBIEE generates.
A normalized model will have multiple nested joins that negatively impact OBIEE's performance while a star schema should only have direct joins between the fact and its dimension tables.
I would recommend downloading Oracle's Business Intelligence Application solution (OBIA) for examples on how to best implement an accumulating snapshot or read Kimball's 'Data Model Reference Guide'.