Monday, May 28, 2012

FYI: Kimball Fact Table Primer (Star Schemas)

9:16 PM

Share it Please
One fact table does not fit all, depending on your requirements you may have a need to identify all invoices transactions for a given point in time.  Maybe the end user wants to identify sales revenue for a regular, predictable date interval (such as quarter).  Also consider the order fulfillment pipeline which creates a scenario where a supply chain specialist must identify the span of time it takes a product to be processed through the pipeline.

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.

keywords: dimensional modeling, kimball dimensional modeling, fact table, scd, slowly changing dimensions, dimension tables, obiee star schemas, physical star schemas

2 comments:

  1. 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.

    ReplyDelete
    Replies
    1. Hi Myles.

      Yes 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'.

      Delete