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 going to review dimension tables in more depth. As I stated last time, dimension tables store qualitative, or descriptive data. For example, the DimCustomer table in the AdventureWorksDW2012 database stores information about each customer; data like first and last name, address, income, marital status, and number of children. What DimCustomer does not store is any information regarding a customer’s previous purchases.
Dimension Columns
The DimCustomer dimension table in AdventureWorksDW2012
What may not be obvious right off the bat is that there are several types of columns in a dimension table. And each of these column types has a purpose.
- Keys – Just like they would in an OLTP environment, key columns are used to uniquely identify rows in the dimension. In the DimCustomer table, this would be the CustomerKey column.
- Name columns – Name columns provide a more human-readable way of identifying a row, rather than using the key value. You probably wouldn’t refer to customer 12345, you would refer to the customer as Alison Raje.
- Member properties – These are descriptive columns that are typically displayed on reports, but they’re not used for pivoting or analyzing data. For example, in DimCustomers, member properties would be AddressLine1, Phone, EmailAddress, etc.
- Lineage columns – These columns are strictly for auditing purposes.
- Attributes – These are the columns you’ll use for pivoting data during analysis. The thing you want to keep in mind with attributes is you want to restrict your data to a small number of distinct values. This usually means grouping values into discrete buckets, also known as discretizing. Take an income column, for example. If you pivoted your data based on actual income values, your resultset would be way too wide to be useful. More often, income values are grouped into ranges and the ranges are what’s used for pivoting. That process is called discretizing and the resulting ranges are your attributes. You may still store the actual income value in your dimension, but it becomes a member property.
Slowly Changing Dimensions
Keep a data warehouse around for long enough and you’re eventually going to have to update your dimension data. Customers move, products are discontinued, employees are promoted. How you decide to handle those updates will have a big impact on whether you’re able to report on historical data. Let’s go back to the DimCustomer table. Customer 12345, aka Alison Raje, lives in Townsville, Queensland, Australia. But suppose Alison was offered a job in Chicago and decided to move. Alison’s row in DimCustomer needs to be updated to reflect her new address, but how? This is known as a Slowly Changing Dimension (SCD), and there are a few options.
- Type 1 SCD – In a Type 1 SCD you simply overwrite the history of an attribute. If a customer moves from one city to another, the row for that customer is updated in the dimension and any record of that customer living anywhere else is eradicated. The upside of this method is its simplicity. The downside is it will wreak havoc on any historical reporting. For instance, purchases that Alison made while living in Townsville will now be reported for Chicago.
- Type 2 SCD – In a Type 2 SCD updates are handled by inserting a new row into the dimension. This has its caveats. First of all, a surrogate key must be used to uniquely identify each record. Second, there needs to be a way to identify the current record. This can be done by adding a flag column or by using “valid from” and “valid to” dates.
- Type 3 SCD – In a Type 3 SCD, a limited amount of history is preserved by using additional historical columns. For instance, you might have a CurrentCity column and a PreviousCity column. Because the amount of history is restricted by the number of historical columns in the table design, Type 3 SCD has pretty limited usability.
The business users will have a big say in what type of SCD you implement in your data warehouse. And keep in mind that Type 1 and Type 2 aren’t necessarily mutually exclusive. Depending on the data, you may be ok with overwriting history on some columns, but need to maintain history for other columns.
Summary
Today we covered dimensions in more detail, including the different types of columns that make up a dimension, and how we can handle updates to dimension data. Next time we’ll look at fact tables in more depth.
