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.
I am really interested in taking this exam so will like to follow you in this journey of yous. Any recommendations on books or resources?
Thanks.
A M – I’m using the Microsoft training kit book, myself, but there are a ton of resources online, too.
Colleen,
Star Schema: The Complete Reference by Christopher Adamson is an excellent book on dimensional design. He explains Kimbal and Inman and the differences and similarities as well as pretty much everything else I can think of about designing a dimensional data warehouse. If you have any database development/design exposure at all, you will appreciate this matter of fact explanation of how it is done. Having had a little exposure to dimensional design, this book provided quite a few “aha” moments, where I was able to put 2 and 2 together and gain a clear understanding of the items I was foggy on.
Hope this helps.
Pingback: Exam Prep 70-463: SSIS Connection Managers - SQL Server - SQL Server - Toad World
I am reading this book too. Frankly, I was confused by the description. The foreign keys, the surrogate and the business keys. In the book, the author describe that ProductID, CustomerID are foreign keys and are replaced with surrogate DW keys.
The authors also mentioned that the surrogate key is usually the business key from the table that was used as the primary source for the table. for example: OrderID.
So my questions is what is the surrogate key? My understanding is that surrogate key can be used to replace the foreign key or can consist of one or two business keys? Can anyone help me
Hi Ji – I can see how you’d find it confusing, the authors use the term surrogate a couple ways, but let’s break it down: A business key is the primary key column(s) from your source database. Examples of this are the ProductID in the Production.Product table of AdventureWorks2012, or the SalesOrderID and SalesOrderDetailID in Sales.SalesOrderDetail. You can store these in the DW as an easy way to trace a row back to the source database, but they’re generally not used for referential integrity in the DW. My definition of a surrogate key is one that you generate in the DW for the purposes of referential integrity. It is scoped to the DW only, and will not have any meaning outside of that. The fact table will include foreign keys referencing the primary surrogate keys in the dimension tables. Now, sometimes the combination of foreign keys in the fact table will be enough to uniquely identify a record in the fact table. When they’re not unique, however, you can potentially use the business keys instead. The authors refer to this as an “additional surrogate key”, which I agree is a confusing use of the word “surrogate”. To me, a surrogate key in the fact table would be defined the same way as a surrogate key in the dimension table: a unique identifier (e.g. int, bigint) generated exclusively for referential integrity.
Hi Colleen, there is so many info about DW on the internet, but nowhere is there a database that you can use as source, with instructions on how to create a staging database from the source, and instructions on how to create a DW from the staging, including dimensions, facts, etc. Just chucking all the theory into a book, does not help very much with creating a DW – practically.
Hi Colleen,
Thank you for bring this interesting discussion. To handle the slowly changing properties, multiple rows are added for a primary key. Primary key can be a business key or something like int sequence. To uniquely identify each row (referential integrity) in a dimension, a surrogate key (normally int identity type) was added. The dimension surrogate key will server as foreign key in fact table. To uniquely identify each row of a fact table, Fact table can also have its own surrogate key. To maintain to connection with OLTP database, each dimension has to keep the primary key from OLTP table. Date dimension is a special dimension. It does not have surrogate key.
Pingback: Exam Prep 70-463: Data Flow Source Adapters - SQL Server - SQL Server - Toad World
Pingback: Exam Prep 70-463: SSIS Transactions and Checkpoints - SQL Server - SQL Server - Toad World