Posts

Implementing a data warehouse: Fact tables

Originally published on ColleenMorrow.com.

This post is part of a series on 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

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.

 

About the Author

SQL Server Consultant

Colleen Morrow

Colleen Morrow is SQL Server Consultant at UpSearch.

Colleen is a database strategist, community advocate, author, blogger and public speaker. She is passionate about helping technology leaders use Microsoft’s SQL Server to protect, optimize and unlock data’s value.

Colleen has been working with relational database for almost 20 years. Since 2000, Colleen has specialized in SQL Server and Oracle database management solutions. She excels at performance tuning, troubleshooting mission critical SQL Server environments, and training technology professionals and business users of all levels.

Since 2011, Colleen has maintained a SQL Server focused blog at http://colleenmorrow.com. She is an active member of the Ohio North SQL Server User Group, as well as a volunteer for the Professional Association for SQL Server (PASS). Colleen earned a Bachelor of Science in Computer and Information Systems from Cleveland State University.

Learn more about Colleen Morrow at https://upsearch.com/colleen-morrow/.

 

Implementing a data warehouse: More about Dimensions

Originally published on ColleenMorrow.com.

This post is part of a series on 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

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.

 

About the Author

SQL Server Consultant

Colleen Morrow

Colleen Morrow is SQL Server Consultant at UpSearch.

Colleen is a database strategist, community advocate, author, blogger and public speaker. She is passionate about helping technology leaders use Microsoft’s SQL Server to protect, optimize and unlock data’s value.

Colleen has been working with relational database for almost 20 years. Since 2000, Colleen has specialized in SQL Server and Oracle database management solutions. She excels at performance tuning, troubleshooting mission critical SQL Server environments, and training technology professionals and business users of all levels.

Since 2011, Colleen has maintained a SQL Server focused blog at http://colleenmorrow.com. She is an active member of the Ohio North SQL Server User Group, as well as a volunteer for the Professional Association for SQL Server (PASS). Colleen earned a Bachelor of Science in Computer and Information Systems from Cleveland State University.

Learn more about Colleen Morrow at https://upsearch.com/colleen-morrow/.

 

Implementing a data warehouse: Tables and Schemas

Originally published on ColleenMorrow.com.

The PASS Summit has come and gone, and besides attending sessions and networking, I also managed to pass 2 of my certification exams.  That leaves the much-dreaded 70-463 exam.  So it’s time to start preparing for that exam, and as I go along I’ll post here so you can study along with me.  Ready?

Exam 70-463 focuses on implementing a data warehouse in SQL Server.  Obviously this is a pretty big topic that goes way beyond even what the exam covers, but let’s set the foundation with some fundamentals.  Most of us are very familiar with OLTP database design.  It’s highly normalized and great for line of business (LOB) applications that are primarily using lots of small transactions of very short duration.  The problem with a normalized design is that it’s not so great for analytical reporting.  First of all, normalization means joining many tables which leads to poorer query performance and also makes for much more complex queries.  The other problem is that all of your LOB data reside in different databases, so comparing or aggregating data between different systems is difficult.  To get around this, we build a data warehouse where all of our disparate OLTP data can be merged for analysis and historical reporting.  What makes the data warehouse so great for this type of reporting is its fundamental design, and that’s what we’re going to cover in this post.

Tables

There are two main types of tables in the data warehouse environment: fact tables and dimension tables.

  • Fact – Fact tables store quantitative information for analysis or reporting.  An example of this would be a sales table like the FactInternetSales table in the AdventureWorksDW2012 database.  Every row in the FactInternetSales table records a sale that happened.  Customer A purchased Product B on date C, and so on.  However, your typical sales fact table wouldn’t contain the actual customer information.  Take a look at FactInternetSales and you won’t see customer data. Rather it stores a foreign key reference to the customer data.  And that’s where dimension tables come in.
  • Dimension – A dimension table contains descriptive attributes that can be used to filter, group, and label facts in the fact table.  They provide context.  For example, the FactInternetSales table mentioned earlier would likely be joined to a Customers dimension table, as well as a Products dimension.  The more dimensions a fact table is associated with, the more ways you can slice the data. This is referred to as dimensionality or granularity.

Schemas

It’s the logical design or schema of the data warehouse, or how those fact and dimension tables are laid out and connected to each other, that makes the data warehouse so great for reporting.  And your data warehouse will typically use one of two schemas: the Star schema and the Snowflake schema.

  • Star schema – in a Star schema,  a central fact table is surrounded by a single layer of dimension tables.  One Star schema will cover one business area in the DW, but you can (and probably will) have multiple Star schemas in your DW.  These schemas should be connected by sharing dimension tables (shared dimensions).   If the various Star schemas in your DW don’t share dimension tables, you lose the connection between fact tables, making it difficult, if not impossible, to compare data between different fact tables.
Star schema example

Star schema example

  • Snowflake schema – a Snowflake schema will start out as a Star schema, but then some of the dimensions are normalized, creating the snowflake-like pattern.  An advantage of the Snowflake schema is that is can make hierarchies more apparent.  For example, a Product dimension might be normalized to join to a ProductSubCategory dimension, and that might in turn be joined to a ProductCategory dimension.  In general, Snowflake schemas are discouraged for performance (more joins) and maintainability reasons.
The Snowflake schema starts to add complexity

The Snowflake schema starts to add complexity

Summary

Today we covered the two primary types of table found in the data warehouse, the fact table that holds your quantitative data and the dimension table that holds your qualitative, contextual data.  We also reviewed the two most common schema designs, the Star schema and the Snowflake schema.  Next time we’ll talk more about dimension tables, their columns, and how to handle updates.

 

About the Author

SQL Server Consultant

Colleen Morrow

Colleen Morrow is SQL Server Consultant at UpSearch.

Colleen is a database strategist, community advocate, author, blogger and public speaker. She is passionate about helping technology leaders use Microsoft’s SQL Server to protect, optimize and unlock data’s value.

Colleen has been working with relational database for almost 20 years. Since 2000, Colleen has specialized in SQL Server and Oracle database management solutions. She excels at performance tuning, troubleshooting mission critical SQL Server environments, and training technology professionals and business users of all levels.

Since 2011, Colleen has maintained a SQL Server focused blog at http://colleenmorrow.com. She is an active member of the Ohio North SQL Server User Group, as well as a volunteer for the Professional Association for SQL Server (PASS). Colleen earned a Bachelor of Science in Computer and Information Systems from Cleveland State University.

Learn more about Colleen Morrow at https://upsearch.com/colleen-morrow/.