Update Fact Tables?

I have been asked on several occasions if rows/values in a fact table should be updated. The answer to this question in almost all cases is NO! Facts should not be updated. There a several really good reasons why facts values should never be updated.

1. Consistent Reporting: If I run a report today which reports on last week’s values I should get the exact same values if I run the same report tomorrow. The end users will not be happy if their historical numbers keep changing every day that they run the report!

2. Fact Table Processing: Preforming updates on tables can be a very costly operation on the data warehouse server! Fact tables can and should be very large. They can contain 100’s millions, billions, or trillions of rows and they should have one index on each surrogate key column as well (useless you are fortunate enough to have SQL Server 2012 then just create one Columnstore Index which includes all the surrogate keys). All that makes updating anything in a fact table very costly and should be done with great care and thought.

But I also realize that we also live in the real world. And in the real world sometimes fact transaction values change (even though they shouldn’t!!). I won’t name names but there are plenty of source systems which allow fact transaction values to be updated. So what can you do? You need to record those changing facts, but you also want to preserve reporting consistency and you don’t what to exponentially increase the time it takes to process the transactional fact table.

Well there are a few options:

1. Covert the transactional fact table to a snapshot fact table (more on that later)

2. Create the ‘Adjustment’ facts in the ETL process and insert them into the Fact table

The second option works especial well if the source systems actually tells you when the fact was last updated i.e. LastUpdatedDate. Personally, I like to create another TransactionType dimension which will hold a member representing the ‘normal’ non-adjusted facts and a member for the ‘adjustment’ facts that have to be created in the ETL. In the end the ERD would look something like the following:

An example scenario using a flatten fact table for simplicity:

Let say that the fact table starts at 01/01/2013 with only 3 transactions (one for each product)

DateKey	DimProduct	DimTransType	Measure
20130101	ProdA	normal	100
20130101	ProdB	normal	100
20130101	ProdC	normal	100

However on 01/02/2013 it was realized that the data entered on 01/01/2013 for ProdA was incorrect and the real value should have been 75. The ETL should hopefully be able to identify the LastUpdatedDate of the transaction and calculate the difference between the historical ‘normal’ value and the new target value. That difference should then be inserted in the fact table as an ‘adjustment’ transaction.

DateKey	DimProduct	DimTransType	Measure
20130101	ProdA	normal	100
20130101	ProdB	normal	100
20130101	ProdC	normal	100
20130101	ProdA	adjustment	-25

Now normal + adjustment still sums to the correct value of 75 but report consistency can still be preserved because the report or cube can choose to see the fact values with or without the adjustment records. This will also greatly reduce the ETL fact load processing requirements as oppose to just updating the facts in place. Note: having a good table partitioning strategy is critical for large fact tables.

I also like preserving the adjustment date value as well, as that will allow the business can see how far back transactions are being corrected. In many cases I have seen managers shocked by the frequency of these ‘rare’ corrections

DateKey	AdjDateKey	DimProduct	DimTransType	Measure
20130101	20130101	ProdA	normal	100
20130101	20130101	ProdB	normal	100
20130101	20130101	ProdC	normal	100
20130101	20130103	ProdA	adjustment	-25

SSIS ETL Frameworks

In my years as a BI Professional I have seen many ETL Frameworks. They range from homegrown internal solution to out-of-the-box solutions developed by small and large consulting companies and other various providers.

There are many similarities and differences between, but they are all designed to address two general requirements:

1. ETL Monitoring – At it’s most basic level this consist measuring the ETLs performance in terms  row counts (how many rows where processed?)  and duration (How long did it take?). Most ETL Monitoring Frameworks also include features that allow for notifications or alerts to be sent out when a process fails or falls outside some predefined parameters. A good framework should include not only package level but control flow and data flow monitoring as well.

2. ETL Data Management – This a very difficult to define precisely because there are some many variations and the requirements can be very specific. But generally the purpose of ETL Data Management is to control how and when data flows through the ETL solution. This can cover everything from data cleansing (Data Quality Services) to conforming/validating the data.

ETL Data Management is especially important for dimension tables. Because dimension tables are critical to good consistent analysis. Depending on your specific requirements there are many other things to consider as well (see Kimball resources at the bottom)

Now that you have few ideas about what an ETL Framework might do. Here are several resources that I have used in the past. I won’t go into detail about all there features but they all have specific issues that they address.

Build Your Own

Obviously this would take the longest to develop but can address very complex and specific requirements. Whether or not you plan on doing it yourself I would still highly recommend feasting on Microsoft SQL Server 2008 Integration Services: Problem, Design, Solution. It will cover the details of how to build your own. Brian has built a good business out this ETL Framework.

My Metadata Driven ETL Process is something unique that has saved me a lot of headaches! Hopefully it can help you as well. Combine that with Change Data Capture and it scales very nicely.

Out-Of-The-Box

BIxPress is an awesome product with with too many  features to mention here and is very reasonably priced.  (and no Brian is not paying me to say that it just happens to be true)

SQL Server 2012 SSIS Framework

SQL Server 2012 is going to ship with it’s own built in ETL Framework called SSIS Catalog. If you can upgrade I highly recommend doing it!

Other Important Stuff

Dimension Merge SCD gives you unparalleled insight into the most important process in you ETL… The dimensional build process. And best of all it is free!

The Microsoft Data

Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset. You absolutely have to read this book. It even includes Kimball’s version of an ETL Framework!!!

 

BIDS Helper a great FREE BIDS plugin also has the ability to programmatically generate SSIS packages by using BiML.

It could reduce developed time and help standardize SSIS package development. Best of all it is  FREE.

These are the best resources that I have found to date. Look at your requirements and find out which combination the above works best for you.

ETL from Oracle to SQL Server

I was recently asked to provide some performance tuning recommendations for an ETL process that extracts data from Oracle using Informatica (Unix) and loads it into some SQL Server Staging tables.

Initially the ETL process was taking  20+ hrs to complete. After some preliminary analysis it was determined that the first major bottle neck was that Informatica was doing row-by-row inserts into SQL Server stage tables !

Informatica could read data  at about 3,500 rows per second from a single Oracle table but was only inserting into SQL Server at about 300 rows per second. The night ETL load is  4+ terabytes so then it became apparent why it was taking 20+ hours to complete!

Note: This installation of  Informatica was on a Unix server and so it did not have the SQL drivers to do bulk inserts. If you install informatica on a windows server then you can take advantage of the SQL drivers and do bulk inserts.

Our recommendations came in the form of three options:

1.  Informatica Oracle flatfile dump with SSIS Load to SQL Server Staging Tables. see image below

While this technique greatly improved ETL performance it would require completely redesigning the existing Informatica mappings and buying another server for just holding the flatfiles.

Plus it seems a tad outdated to still being doing flatfile dumps in 2011 between 2 perfectly good Database Engines simply because the ETL tool can’t do bulk inserts!

2. Upgrade DataDirect to version 6.1 which has a driver which can do a bulk insert into SQL Server. (Actually, DataDirect 6 ships with the driver disabled!!!). The downside is that costs some serious $$$ to upgrade but there is no additional development work needed once the upgrade in complete.

3. Use the SSIS Oracle Connector to pull directly form Oracle. Here are the performance results. There are many factors which can effect performance. So your results will vary.

Source	Destination	rows	time min	rows/sec
Oracle	SQL Server DB Local Laptop	1,000,000	6	2,778
Oracle	SQL Server DB	1,000,000	6.1	2,732
Oracle	flatfile	1,000,000	5.4	3,086

Of course I prefer using the SSIS option because it performs roughly equal to Informatica but without all the additional cost. With more optimizations SSIS can even out perform Informatica. See links at the bottom of the post for more information.

Steps to Install SSIS Oracle Connector on dev box which uses BIDS

1. Download and install 32bit and 64bit SSIS Oracle Connector. BIDS debug runs in 32bit ! so you need both!

2. Download and install Oracle SQL Developer and Java Platform JDK

3. Setup the connection to the Oracle DB in SQL Developer

4. Setup the Oracle Connection in BIDS.

**Make sure that TNS service name is in the following format <IP>:<port>/<service name>. Also think about how to manage the credentials used to access oracle.

Also for ’Data access mode’ in the ‘Oracle Source’ dialogue box use “SQL command”. It performs a lot better!

**To install the SSIS Oracle connector follow the same step above but you won’t need to install the 32bit stuff

Good Performance Tuning Articles that are related to SSIS and ETL:

The Data Loading Performance Guide:

This document described techniques for bulk loading large data sets into SQL Server. It covers both the available techniques as well as methodologies to performance tune and optimize the bulk loading process.

We Loaded 1TB in 30 Minutes with SSIS, and So Can You

In February 2008, Microsoft announced a record-breaking data load using Microsoft® SQL Server® Integration Services (SSIS): 1 TB of data in less than 30 minutes. That data load, using SQL Server Integration Services, was 30% faster than the previous best time using a commercial ETL tool. This paper outlines what it took: the software, hardware, and configuration used. We will describe what we did to achieve that result, and offer suggestions for how to relate these techniques to typical scenarios. Even for customers who don’t have needs quite like this benchmark, such efforts can teach a lot about getting optimal performance.

Considerations for High Volume ETL Using SQL Server Integration Services

Providing for high volume extract, transformation, and load (ETL) throughput requires consideration of both best practices for standard relational databases, and of high performance techniques for ETL.

This article presents a number of suggestions relating to high volume ETL throughput, specifically using SQL Server Integration Services. Aspects of both ETL performance and management are described

Fast Track Data Warehouse 3.0 Reference Guide

This paper defines a reference configuration model (known as Fast Track Data Warehouse) using an I/O balanced approach to implementing a symmetric multiprocessor (SMP)-based SQL Server data warehouse with proven performance and scalability expectations for data warehouse workloads. The goal of a Fast Track Data Warehouse reference configuration is to achieve a cost-effective balance between SQL Server data processing capability and realized component hardware throughput.

Best Practices for Data Warehousing with SQL Server 2008 R2

There is considerable evidence that successful data warehousing projects often produce a very high return on investment. Over the years a great deal of information has been collected about the factors that lead to a successful implementation versus an unsuccessful one. These are encapsulated here into a set of best practices, which are presented with particular reference to the features in SQL Server 2008 R2. The application of best practices to a data warehouse project is one of the best investments you can make toward the establishment of a successful Business Intelligence infrastructure.

Data Integration at Microsoft: Technologies and Solution Patterns

Microsoft SQL Server Integration Services Performance Design Patterns

SSIS ETL Scalability

SSIS is a vertically scalable ETL solution as demonstrated by Project REAL (I suggest reading and re-reading every MS / SQL CAT  technical article).  In terms of raw throughput, with the right hardware it is more than capable of handling 100s of gigs to multiple terabytes of data in a short time frame. This is usually referred to as scaling up, in other words, as long as you get better hardware and networking equipment, SSIS throughput increases roughly at a linear rate. However, a lot of work still has to be done for planning for High Volume ETL Processing.

One of the most important considerations has to do with change/delta detection. Delta detection is absolutely critical for dimesionalizing a normalized source data. However, in most cases it also represents the most resource intensive step of an ETL process (it is typically the ‘T’transform step). This is due to the fact that it involves sorting, transforming, and joining all sources for a target dimension table together and then comparing that dataset to the current row versions of the target dimension table to find the deltas.

Techniques such as Change Data Capture (CDC) and source metadata columns like ‘LastChangeDatetime’   can help to greatly reduce the impact of finding deltas. However they have their limitations.  For CDC the data source must be SQL Server 2008 + and it is completely reliant on the transaction log. Metadata columns on the source side may be too difficult to implement (think ERP systems) or too resource intensive to implement (think triggers on production systems).

As an alternative I suggest a scale out (horizontal) option to be combined with SSIS’s vertical scaling capabilities as well. A scale out metadata driven ETL process pushes the delta/change detection to the staging process for each source table. With the appropriate metadata in the staging tables, the delta detection can then be scaled out to the level of each source table. In other words you can have separate SSIS (stage) servers performing delta detection for each source table (or group of source tables) without impacting the Data Warehouse server (or Parallel Data Warehouse).

If done correctly, building the target dimension in the Data Warehouse Server is simply a matter of joining the staging table’s batch rows together and inserting them into the dimension table. I created two demonstrations of this scale out option in two past posts. There are plenty of additional benefits to architecting the ETL this way a well. See links for all the details.

First read, download, and walk through the SQL Script.

Metadata Driven ETL Process

Then read, download, and walk through the SQL Script.

Metadata Driven ETL Process with Change Data Capture

Metadata Driven ETL Process with Change Data Capture

Note: This post is a continuation of my previous post entitled ‘Metadata Driven ETL Process’. If you have not already done so, download Metadata ETL Demo.zip and walk through step-by-step the SQL script.

As a review, the purpose of the Metadata Driven ETL Process was to create a robust and flexible Metadata Driven ETL Process that addresses some the most challenging situations that I have found. This is primarily through the use of metadata columns which allow for a portion (or the entire) Dimension table to be re-created in the event of a disaster (or bad ETL logic) without any data loss, even when the source system does not contain historical changes.

While there are many positives to this approach there is one big downside.  The Metadata Driven ETL Process still has to load the entire source system stage tables into memory, sort and join them together, and do a Row-by-Row comparison of each field in each row to identify which field caused the row to be changed.( If your source system table is on a SQL Server 2008 or greater DB you can use the MERGE statement, thereby completely bypassing the SSIS lookup task)

<rant> I know that in some cases you are provided with a ‘Last Change’ datetime stamp metadata column from the source table. However, most of these are implemented through triggers which can and often are by-passed by a DBA for performance reasons, especially in data recovery situations. Unless you can get the DBA to sign in blood. I suggest not relying on triggers</rant>

Even then, only doing a lookup between a single source system table and a single stage table preforms a lot better than the standard ETL Process (sorting and joining all the source systems tables together and then sorting joining that to the target dimension table and finding all the changes). See the SSIS ETL Scalability for more details.

But for large dimensions (millions of members) this can still be a lot of processing. Change Data Capture was developed to make finding changes in even large dimensions take only a few seconds! Read the following  to get the best possible performance out of CDC:   Tuning the Performance of Change Data Capture in SQL Server 2008

It was the output to CDC that gave me the first idea for the Metadata Driven ETL Process but as great as CDC is there are a few negatives. It is only as accurate as the transaction log file :)  There times when the transaction log file doesn’t have the changes that you need (the initial load of the stage table) or you don’t want changes in the transaction log to be reflect in the dimension table.

I recently ran into a situation where, as part of a source system upgrade, the tables were truncated and completed reloaded. Initially the data was bad even after reloading the source table. It took them a few tries to get it right (yes this happened in production .  As a result even though my dimension table was accurate in terms of preserving all the changes, it made analysis inaccurate for the time period that the upgrade was occurring.

To address this situation and many more I combined CDC for performance with ability to use Row-By-Row on an ad-hoc basis for flexibility.

How does this all work together? The answer is spectacularly!!!

Download Metadata Driven ETL with Change Data Capture.zip and follow each step of the Change Data Capture Demo.sql script

Enjoy!