This post is part of a series on this blog that will help me, and hopefully you, pass exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012. So far, we’ve covered:
Today we’re exploring fact tables in more depth. If you’ll recall fact tables store the quantitative data that lies at the heart of the data warehouse. Without fact tables, our data warehouse would really just be a bunch of mostly unrelated dimension tables, and how boring would that be?
Fact columns
The InternetSalesFact fact table in the AdventureWorksDW2012 database
Just like dimension tables, fact tables have their own column types. They are:
- Foreign Keys – As in an OLTP database, foreign key columns reference primary key values in the dimension tables with which our fact table is associated. In the FactInternetSales table, these would include ProductKey, CustomerKey, and OrderDateKey, among others.
- Business Keys – This is usually used as a surrogate key from the table we’re using as our primary source for this fact table. They’re not strictly necessary, but they make it easy to compare and link back to the source data. For example, the SalesOrderNumber and SalesOrderLineNumber columns comprise a composite surrogate key in the FactInternetSales table. They were pulled from the OrderDetails source table.
- Lineage columns – Just as with dimension tables, these are strictly for auditing purposes.
- Measures – These columns store measurements of interest associated with a business process. I’m talking about columns like SalesAmount, OrderQuantity, etc. They are the root of our fact table and the values we’re going to aggregate for reporting. Therefore, they’re usually of a numeric data type.
Additivity
The primary benefit of a data warehouse is the ability to aggregate data for reporting, forecasting, etc. We don’t often report row-level information, rather we want to be able to slice and dice sales by quarter, by region, by product type, etc. So when we design our fact tables and start adding measures, we need to keep in mind how those measures will be aggregated. Because sometimes how we aggregate a particular measure will depend on what dimension we’re looking at.
For some measures, it’s pretty straightforward. When it comes to a measure like sales amount, we can sum it by date, by region, by product type. If a measure can be summed across all dimensions, it’s referred to as an additive measure.
Sometimes, however, we can sum a measure across all dimensions except for time. An example of this is a balance amount. If Bob has $2000 in his checking account and Betty has $3000, then we can sum across the customer dimension and say that they have a sum of $5000. However, if Bob buys a new laptop today and his checking account balance drops to $500, Bob obviously doesn’t have $2500 total. We can’t sum the account balance across the time dimension. We would need to do something like take the average instead, or simply use the last value. Measures like this are called semi-additive measures.
Finally, some measures can’t ever be summed. These are called non-additive measures, and include measures like discount percentages and prices.
Summary
In this installment, we reviewed the different types of fact columns and the additivity of measures. Next time, we’ll talk about implementing fact and dimension tables in the data warehouse.
