Factless Fact Tables

Over the past three weeks, we've looked at three ways to design fact tables.

1.) Transaction Fact Table

2.) Period Snapshot Fact Table

3.) Accumulating Snapshot Fact Table

As the term suggests, all of these tables contain facts (measures) that pertain to the business process.  There are instances in which a fact table can be designed to contain no measures but only events.  This is referred to as a factless fact table.  Consider a user who wishes to see the org chart on a particular day.  In this case, a factless fact table may be designed to contain one row per day (if that is the grain) and the keys to the Human Resources descriptors that existed on that day.  Every column in this table will contain a foreign key to a dimension table or a degenerate dimension value.  Consider this plain-english version of this table:

Date	Name	Department	Supervisor	Job Title
1/15/2013	Jason Smith	Human Resources	Allen Wells	HR Associate I
…
5/31/2013	Jason Smith	Human Resources	Allen Wells	HR Benefits Coordinator
…
3/15/2014	Jason Smith	Information Technology	Mike Williams	HR Systems Analyst

This tells us that Jason Smith was hired as an HR Associate I on 1/15/2013.  He was promoted to a Benefits Coordinator on 5/31/2013 and then moved to Information Technology on 3/15/2014.  FYI, although the table above only shows three rows there will probably be several more.  This table will probably contain one row per day with the same data until it changes, indicated by the ellipses in the table above.

In the future we will look at writing some ETL to populate a factless fact table.

Image courtesy of watcharakun / FreeDigitalPhotos.net

Accumulating Snapshot Fact Tables

We've been looking at the various fact table design options in data warehousing.  Two weeks ago we examined the transaction fact table and last week we examined the periodic snapshot fact table.  The third and final option is called an accumulating snapshot.  This type of fact table is different from the other two in one big way.  Each row is often revisited.  Consider our banking example from the other posts.  When a deposit is added to a transaction fact table, that row is added and then left alone.  All of the data needed to add and complete that row is known.  The same is true of periodic snapshots.  An accumulating snapshot fact table begins each row and then accumulates data until that row is complete.  Let's consider an example in a different context.  Suppose that a star is built for the purposes of analyzing a help desk ticketing business process.  The following descriptors (in addition to any measures) are a part of the business process:

Date Ticket Opened
Date Ticket Assigned
Date Solution Provided To Customer
Date Customer Accepted Solution
Date Ticket Closed
Ticket Number

Suppose that ticket number 10012 is opened on 3/1/14.  At that point, this row will exist in the fact table:

Date Ticket Opened	Date Ticket Assigned	Date Solution Provided To Customer	Date Customer Accepted Solution	Date Ticket Closed	Ticket Number
3/1/2014					10012

Now, suppose that the ticket is assigned to a technician on 3/3/14.  That same row will be updated to look like this:

Date Ticket Opened	Date Ticket Assigned	Date Solution Provided To Customer	Date Customer Accepted Solution	Date Ticket Closed	Ticket Number
3/1/2014	3/3/2014				10012

If the solution is provided to the customer one day later, the row will be updated to look like this:

Date Ticket Opened	Date Ticket Assigned	Date Solution Provided To Customer	Date Customer Accepted Solution	Date Ticket Closed	Ticket Number
3/1/2014	3/3/2014	3/4/2014			10012

Assuming that the customer accepts the solution on 3/5 and the ticket is closed 3/6, the row will be updated to look like this:

Date Ticket Opened	Date Ticket Assigned	Date Solution Provided To Customer	Date Customer Accepted Solution	Date Ticket Closed	Ticket Number
3/1/2014	3/3/2014	3/4/2014	3/5/2014		10012

and then this:

Date Ticket Opened	Date Ticket Assigned	Date Solution Provided To Customer	Date Customer Accepted Solution	Date Ticket Closed	Ticket Number
3/1/2014	3/3/2014	3/4/2014	3/5/2014	3/6/2014	10012

At this point, the row is left alone.  If you so desire, an accumulating snapshot allows you to store some lags between dates to help with analysis.  This can help ease the burden of using the database to calculate the various lags.

In the future we will look at writing some ETL to populate an accumulating snapshot fact table.

Image courtesy of Vichaya Kiatying-Angsulee / FreeDigitalPhotos.net

Periodic Snapshot Fact Tables

In our last post we looked at one of three ways to design a fact table, called a transaction fact table.  Today, let's look at a second design, called the periodic snapshot.  Remember from Ralph Kimball's teaching (and last week's post) that a transaction fact table gains a row each time that something happens.  Using our banking example, from last week, each deposit or withdrawal will result in a record being inserted.  Looking at only one record will allow us to see that one event.  Adding these records will allow us to see the balance.

A periodic snapshot contains snapshots of the data as it existed at various points in time.  Unlike a transaction fact table, selecting one row (or perhaps a subset of rows if it is semi-additive) will display the current value at that point.  Our example from last week involved opening a checking account at Acme Bank on 2/1/14 and making an initial deposit of $3,000.  Three days later you withdrew $200.  Five days after that, you deposited $1,000.  If a periodic snapshot were written to show the balance at a daily level, a plain-english version may look something like this:

Date	Amount
2/1/2014	$3,000
2/4/2014	$2,800
2/9/2014	$3,800

Now, let's use this table to answer the same questions that we examined last week:

1.) What was the account balance on 2/4?

Unlike the transaction fact table, in order to find the balance on 2/4, we only need to look at the 2/4 row.  The 2/4 row contains a snapshot of the current balance on 2/4, as opposed to only the event that occured on 2/4.  By looking at the 2/4 row, we will see that the balance was $2,800.

2.) What was the account balance on 2/9?

Using the same logic that was explained in #1 above, look only at the 2/9 rows.  This will give you a value of $3,800.

3.) How much was deposited on 2/9?

This type of question cannot be answered using a periodic snapshot.  A periodic snapshot will store the current state of the business process as of the applicable period, but will not store the events leading to the current state.

In the future we will look at writing some ETL to populate a periodic snapshot fact table.  We will also take a look at an additional fact table design.

Image courtesy of cooldesign / FreeDigitalPhotos.net

Transaction Fact Tables

In his book The Data Warehouse Toolkit, Ralph Kimball explains that there are three ways to design a fact table.  The first and probably most typical (my opinion) is called a transaction fact table.  A transaction fact table is a fact table that contains measures, keys to dimension tables, and degenerate dimensions, if applicable.  When using this fact table to examine the current state of something going through the business process, all rows should be summed through the latest time period that is being examined.  This is due to the fact that a row is added to this fact table as an event in its respective business process occurs.  The most common example involves the banking industry.  Suppose you open a checking account at Acme Bank on 2/1/14 and make an initial deposit of $3,000.  Three days later you withdraw $200.  Five days after that, you deposit $1,000.  A "plain-english version" of this fact table (without the descriptors) will look something like this:

Date	Amount
2/1/2014	$3,000
2/4/2014	($200)
2/9/2014	$1,000

Now, let's use this table to answer these very simple questions:

1.) What was the account balance on 2/4?
Notice how a row was added each time that a deposit or withdrawal was made.  In order to find the balance on 2/4, we must look at everything that happened through 2/4.  If we only look at the 2/4 row, we will only see the $200 withdrawal.  However, it is important to know that $3,000 existed in the account before that withdrawal.  So, if we sum the 2/1 row and the 2/4 row, we will see that the balance on 2/4 (after that transaction posted) was $2,800.

2.) What was the account balance on 2/9?
Using the same logic that was explained in #1 above, sum the 2/1, the 2/4, and the 2/9 rows.  This will give you a value of $3,800.

3.) How much was deposited on 2/9?
When looking at only one of the events that occured as opposed to the sum of everything that occurred, only that one row should be taken into consideration.  The 2/9 row by itself will tell us that $1,000 was deposited into the account.  Unlike #2 above, we do not need to consider the $2,800 that was in the account prior to 2/9.

In the future we will look at writing some ETL to populate a transaction fact table.  We will also take a look at some additional fact table designs.

Image courtesy of twobee / FreeDigitalPhotos.net

To Constrain or Not Constrain

One of the advantages of using a relational database is that you can mandate that certain relationships MUST exist within your data.  If somebody tries to enter data into the database that does not relate to other data correctly, the database can be configured to reject that "bad data" until it is corrected.  Since many data warehouses reside in relational databases, using this feature is an option for the data warehousing team as well. 

Consider this example...suppose that a star contains a DIM_PRODUCT table that contains the products that are used by that business process.  If the Men's Raincoat product has a KEY_PRODUCT value of 27 (surrogate key) then the records in the corresponding fact tables that pertain to this product will have a KEY_PRODUCT value of 27 (foreign key).  That's a lesson from Database 101.  If somebody removes the Men's Raincoat product from the DIM_PRODUCT table while records that point to it exist in the fact table, then those fact table records will point to nothing...and become meaningless.

The advantage of enforcing this constraint within the database itself is that if somebody tries to remove the Men's Raincoat product from the DIM_PRODUCT table the database will not allow it until the "child records" from the fact table have been deleted or repointed.  Using this feature sounds like a no-brainer (and it may be) but a data warehouse provides an additional twist to this kind of decision since so much data is being inserted.

Option 1: Enforce Constraints - This will ensure that the relationship between the fact and dimension tables are always valid from a technical perspective.  However, each time that the data is loaded via the ETL job, the fact table must look to make sure that a parent record exists in the dimension.  If (and only if) it exists, it will load that record into the fact.  Doing that for each and every record will ensure good data intregity but it can also slow a job down.

Option 2: Do Not Enforce Constraints - This will probably result in a faster and more efficient ETL job.  However, the possiblity of the fact table containing some orphan records exists.

It is up to each data warehousing team to decide which is best for their particular situation.  In some cases it is appropriate to physically enforce constraints (option 1).  In other cases, it may be appropriate to logically enforce constraints (option 2), meaning that the data warehousing team will need to periodically run SQL statements that specifically look for orphaned records. 

If you are looking for more information on data warehousing and/or data that can be used to practice ETL and architecture skills, visit www.brianciampa.com and click on Career in Data Warehousing.  Also, if you are looking for a fresh way to market your data warehousing skills, consider The Data Warehouse Portfolio.

Image: FreeDigitalPhotos.net

ETL - Practice Loading A Fact - Solution

In a prior post we looked at the process by which a fact table is loaded.  I need to apologize, since the ERD of the FACT_TRANSACTION table that I posted in that prior post was incorrect.  It has since been updated.  The four things that an ETL job needs to accomplish are...

1.) Select all necessary data from the source
 a. Measures
 b. Natural Keys of the dimension objects
 c. Degenerate Dimension objects
2.) Transform the data as needed
 a. Aggregate measures and group by the appropriate objects
 b. Add foreign keys to dimension tables
3.) Delete any existing rows from the fact table that will be replaced by the new data
4.) Load the fact table

Consider this PL/SQL procedure as a procedure that will accomplish these things.

Extract

The STAGE_TRANSACTION_DATA table pulls all of the necessary elements from the source system into the staging area.  I am treating one schema as both the staging area and the presentation area.  This is probably unrealistic in a production environment but it keeps things simple for these purposes.

Transform

1.) The STAGE_TRANS_DATA_PROD_KEY table is used to grab the KEY_PRODUCT from the DIM_PRODUCT dimension.  Notice that this is done by joining the product's natural key, which is the PRODUCT_NO (product number), to the DIM_PRODUCT table.  An outerjoin is used so that if a record comes into the staging area for a product that does not exist in the DIM_PRODUCT table, that record will not be excluded from the fact table.  It will exist with a value of -1 and will need to be addressed after the ETL has run.

2.) The STAGE_TRANS_DATA_DATE_KEY table is used to transform the TRANSACTION_DATE into a key that will join to a date dimension.  I created the date dimension on my own, so I'll let you try and tackle that one.  Those dimensions typically have no source and are created as needed by the ETL developer.  Again, an outer join is used so that if a date does not exist in that dimension a value of -1 is placed into the fact table.

Load

1.) This fact table, because it is so small, is truncated and then repopulated each time that the ETL runs.  Date logic can be written so that only a certain time period will be refreshed with each run, but for simplicity's sake I did it this way.

2.) Finally, the data is loaded into the FACT_TRANSACTION table.

Prior posts have talked about turning data into valuable data.  This post shows you how to accomplish that in a hands-on way.

Remember to take a look at www.brianciampa.com (specifically, click on Career In Data Warehousing and then Grow) for larger datasets that can be used for this kind of practice. 

Image: FreeDigitalPhotos.net

ETL - Practice Loading A Fact

We looked recently at the process used to load a dimension table.  Consider this post as a way to practice loading a fact table.  An ETL job that is designed to load a fact table will accomplish these things…

1.)    Select all necessary data from the source

a.       Measures

b.      Natural Keys of the dimension objects

c.       Degenerate Dimension objects

2.)    Transform the data as needed

a.       Aggregate measures and group by the appropriate objects

b.      Add foreign keys to dimension tables

3.)    Delete any existing rows from the fact table that will be replaced by the new data

4.)    Load the fact table

As with the dimension example, no two developers will do this the same way, which is perfectly fine.  Consider this example which is to be used in conjunction with the two tables used in the ETL – Practice Loading a Dimension post (click on the image to enlarge it)…

…and this fact table needs to be populated.

Run the following scripts in Oracle to create the source data

1.)    Product data – This is the same script from the ETL – Practice Loading a Dimension post.  If you have already run this one, you are fine.

2.)    Transaction data - This script will create and populate the TRANSACTION_HEADER and TRANSACTION_DETAIL tables above.

The TRANSACTION_DETAIL.PRODUCT_ID is a foreign key to the PRODUCT.ID field from the ETL – Practice Loading a Dimension post.  If you wish, try to write an ETL job that will populate the FACT_TRANSACTION table in PL/SQL or another language of your choosing.  We will examine a possible solution in a future post.

For more practice data and additional information on dimensional modeling visit www.brianciampa.com.  Also, if you need a fresh approach to marketing your data warehousing skillset consider The Data Warehouse Portfolio.

Dimensional Modeling Video

Dimensional modeling is a very powerful technique in helping to enable excellent decision making.  This is a video that explains the business value that can be derived from using this technique.  For more information on dimensional modeling, including free data with which you can practice, look at the Learn, Grow, and Succeed links at http://www.brianciampa.com/careerindatawarehousing.html. 

Also, for those looking for a fresh way to market your skillset consider The Data Warehouse Portfolio at http://www.brianciampa.com.