The customer is a large, progressive credit union with a growing personal and business client base approaching 100,000 members. They offer a full set of services including banking, loans, mortgages, retirement funds, brokerage, and financial planning.

The solution we are going to examine builds a data warehouse designed with the ability to correctly represent historical information. We will use Integration Services to manage the loading and updating of slowly changing dimension tables and Analysis Services to analyze the facts in the context of the time it was captured.

Business Benefits

The solution will deliver the following benefits to the client:

• The effectiveness of planning and forecasting activities will be improved by having access to accurate data for all time periods, including historical data that was previously unavailable.

• The ability to track changes in customer attributes will provide accurate models of customer behavior that can be used to develop more profitable products and services.

Figure 8-1 High-level architecture

The data model for credit union transactions is for the most part fairly standard. The transactions can be analyzed by the channel, type of transaction, and source of the transaction, and by the customer responsible for the transaction with extensive attributes from the CRM system, including credit rating information, as shown in Figure 8-2.

The main modeling decision that we will need to make is how to handle the effect of dimension data changes in our data warehouse design. The key is to look carefully at the business requirements for each of the dimensions. The question that we need to answer for each dimension is “If a change to an attribute occurs, will users always want to analyze data using the latest value for that attribute, or when they look back at historical data, do they want to use the value that was in effect at that time?” The approach that we will take depends on the answer to this question.

Figure 8-2 Credit Union Transaction data model

Managing Dimensions When History Doesn’t Matter

If users always want to see the latest value for an attribute, we can just update the existing dimension record with the new value. This is known as a Type 1 SCD. This type of SCD is the easiest to implement because we don’t have to make any changes to our standard data model for dimension tables.

For example, the name attribute in the Channel dimension may change over time, but users really only want to see the latest names for the channels. When an update occurs in the source system, we can simply use the business key (which is a channel code in this case) to locate the record in the Channel dimension and replace the name (for example, replacing Internet with Internet Banking for the NET channel code, as shown in Table 8-1). When this dimension is used in a cube, only the latest name displays.

Table 8-1 Channel Dimension Records

Managing Dimensions to Preserve History

We know from the business requirements that the approach just described is not appropriate for all the attributes in the Customer dimension. When a customer’s credit rating changes, we cannot just replace the existing credit rating on the record. The reason for this is that when we look at the transactions in the cube, all the old transactions would be shown associated with the new credit rating rather than the credit rating that was in effect at the time of the transaction. What we need is a design that can accommodate the historical values for credit ratings. This type of dimension is known as a Type 2 SCD.

When the Customer dimension is initially loaded as shown in Table 8-2, it will have exactly one record for each business key (Customer Number in this case). If Emily Channing’s credit rating changes from High to Medium, for example, we will need to insert a new record that has the same business key (1000) but a new generated surrogate key (see Table 8-3).

Table 8-2 Original Customer Dimension Records

Table 8-3 Updated Customer Dimension Records

Although the data model looks somewhat unorthodox because we have multiple records for the same customer number, we can reassure you that this design is a cornerstone of the dimensional approach and is used in most data warehouses of any significant size.

Some interesting questions arise now that we have multiple records for the same business key:

• How will we identify the current or latest customer record?

• More important, how will we make sure that users see the correct view of the information when looking at transactions?

The first question is fairly easy to handle. We have a few choices of how to approach this, such as adding a status column or date columns that represent the date range for which the dimension record is valid.

The second question is even easier to handle. The major reason that surrogate keys were introduced for dimensional models is to support SCDs that need to display historical information. Because we have used these surrogate keys in all our designs, there is actually nothing else we need to do as long as our ETL process is working correctly (and we will spend most of the technical solution stage explaining how this process should work). The following example helps illustrate this.

Suppose that Emily Channing’s credit rating was changed from High to Medium on 3/April/2006. Before that date, there was only one dimension record for customer number 1000, and it had a customer surrogate key of 1. All the transactions in the fact table for Emily Channing that were loaded up until that point are associated with a surrogate key of 1.

After we add the new dimension record for customer number 1000 with a surrogate key of 4, any new transactions after that date will use the latest customer record and have a customer key of 4, as shown in Table 8-4.

Table 8-4 Transaction Fact Records for Customer 1000

You can see how this would work using an SQL query that shows the sum of amounts grouped by credit rating. If we were to execute the following SQL, the result would be as shown in Table 8-5:

SELECT C.CreditRating, SUM(Amount) AS TotalAmount
  FROM FactTransactions F
INNER JOIN DimCustomer C ON C.CustomerKey = F.CustomerKey
GROUP BY C.CreditRating

Table 8-5. Transactions Group by Credit Rating for Customer 1000

Another common example of a Type 2 SCD occurs in Sales databases. If we were to update the sales rep name attribute on a customer record, all transactions for that customer would be assigned to the new sales rep. Because this would undoubtedly affect the sales rep’s total bonus for the year, the business requirement would probably be to make sure that any transactions that occurred while the original sales rep was assigned to the customer are credited to that sales rep, and only the new transactions would be assigned to the new sales rep. This kind of situation occurs often enough that we can give you the following tip.

TIP:

Any Dimension Attribute That Affects Someone’s Bonus Is a Candidate for Type 2 SCD

This is a somewhat light-hearted statement but is usually born out in practice. During the modeling phase, it is common for users to claim that they are not interested in tracking attribute history. If that attribute affects how important metrics such as revenue or risk (as in the credit union example) are assigned, give the user concrete examples of what the ramifications would be.

When an Analysis Services cube is used to analyze the data, it will also take advantage of the join on the surrogate key and present the information correctly. So as you can see, Type 2 SCDs are a simple and powerful way to correctly represent historical information in a data warehouse.

We will start with the Channel dimension, which does not track historical information because any new values for attributes will overwrite existing values. (This is a Type 1 SCD.) There will not be any changes required for the Channel dimension table because we have followed the best practices of using a surrogate key and creating a unique key constraint on the business key.

The Slowly Changing Dimension transformation in Integration Services automates much of what used to be a fairly complex and time-consuming job for ETL designers. In the past, you would usually need to copy all the source records into a staging area, compare the staging table to the dimension table and insert any new records, compare all the attribute values that might have changed, and then retrieve and update the record using the business key.

To use the Slowly Changing Dimension transformation, you need to specify the source of the dimension records as well as the destination table in the data warehouse. The wizard also needs to know information such as which column is the business key and which columns may have changed values.

When a customer record has an updated value for credit rating, we will be inserting a new dimension record that has the same business key but a new surrogate key. The first table change we need to make is to remove the unique key constraint for the Customer business key column, because each business key may now have multiple records. As discussed in the “Data Model” section, any new facts that are added will be using the new surrogate key and so they will automatically link to the correct credit rating.

However, when we are processing the facts, we only know the business key, so how do we know which one of the two dimension records to pick? The answer is to add some columns to the dimension table that enable us to indicate which one of the records is the latest dimension record, so that we can look up the correct surrogate key. One possible approach to this is to add a Status column that contains a value such as Current for the latest record and Expired for all the older records.

The problem with this approach is that it only tells you which record is the latest one. If at any point you need to reload all your historical facts (which should not happen if you have good backups), you need to know which dimension record is appropriate for each fact record. For example, a transaction from January 12, 1998, should use the dimension record that was in effect at that point.

In our Customer dimension, we will be using the approach of adding StartDate and EndDate columns to the table. These are simply DateTime columns, and only EndDate is nullable. When the dimension table is first loaded, all records will have the same initial StartDate and a null EndDate. When a changed dimension record is received, the existing record has its EndDate set to the date of the load, and a new record is created with a null EndDate and the StartDate set to the current load date, as shown in Table 8-6.

Table 8-6. Customer Dimension Records

We can then see which dimension record is the “current” record for each business key by looking for all the records with null end dates. Also, we can reload historical facts by joining to the dimension table using the transaction date and selecting dimension records that fall into the correct range.

In Integration Services, the logic needed to detect changed attributes, to set the end date for the old record, and to add a new record is all provided for you by the same type of SCD transformation that we used for the Channel table. To implement the Customer load package, we can just follow the same steps as for the Channel table; but when the SCD wizard wants to know the type of changes that are required for Credit Rating, we can select Historical Attribute rather than Changing Attribute. This lets the wizard know that it will need to generate the steps that are required to support Type 2 dimension changes.

One thing to note is that we can still select Changing Attribute for the Customer Name column, which we always want to overwrite, even though the Credit Rating column has Historical Attribute. What will happen with this is that if the customer name changes, the Integration Services package will simply update the current dimension record with the correct customer name. However, if the credit rating changes, because it was marked as a Historical Attribute, the package will go through the steps required to expire the old record and create a new one with the changed credit rating.

Updating All the Existing Records with a Changed Attribute

By default, when a customer name or other field marked Changing Attribute changes, only the current dimension record is updated by the Integration Services package. This means that when you look at older facts records that link to expired dimension records, you will still see the old customer name. This is often not the behavior that you want—if an attribute is marked as Changing rather than Historical, the reason is usually that you always want to see the most current information for that attribute.

To make sure that the SCD transformation updates all the records for a changing attribute, when running the SCD wizard, you need to check the “Change all the matching records, including outdated records” check box in the Options page, as shown in Figure 8-4.

Figure 8-4 Change all matching records option

Running the Package

To understand how the SCD transformation for historical attributes actually works, it is helpful to load your dimension once and then go back and make a few changes to the source table to see what happens. For example, try updating a customer name on one record, changing a credit rating on another record, and adding a record with a new business key. When you run the Integration Services package, you can see the counts of records that flow to each of the steps, as shown in Figure 8-5.

Figure 8-5 Slowly changing dimension package

All the records in the source dimension table flow from the source to the SCD transformation and then are split up depending on their characteristics. If changing attributes such as Customer Name have changed, the record is sent to the Changing Attribute Updates Output to update the existing customer record. If a historical attribute has changed, the record is sent to the Historical Attribute Inserts Output, which will set a variable to determine the end date and then update the end date on the current record. The record is then sent on to the Union All transformation, which combines any new records from the source and sets a value for the start date. Finally, one record is inserted for the new source record, and another record is inserted for the new version of the record with changed historical attribute.

Applications often include a “Last Update Time” time stamp on any records that are updated through the application. This can prove helpful for determining which records should be loaded into the data warehouse. At the start of the load operation, you check the latest data warehouse load date and time and then load any source records with a Last Update Time since the last load.

This approach only works if the application strictly enforces the updating of the time stamp and no manual processes can bypass this process. If you are going to rely on a time stamp, it is worth spending extra time confirming the behavior with the owners of the source system. If there is any doubt at all, we recommend simply loading all records; otherwise, the “missing updates” will become a data quality issue for the data warehouse.

If an application database includes transaction sequence numbers such as automatically generated identity columns, these are an efficient method to detect additions to the fact tables. You can check for the highest sequence number in the fact table being loaded and then load any source facts with a higher transaction sequence number.

A trigger on the source table is a good way to capture changes because it doesn’t rely on developers to maintain time stamps or other indicators on the rows. They fire every time, regardless of which application loaded the data. Because every change is captured, even if your data source does not preserve history, your triggers can capture historical rows.

In the code for a trigger, you can insert the affected record into one of three tables depending on whether the operation is an insert, update, or delete. Or you can put all affected rows into one table with an operation type indicator.

Triggers can only be applied to data sources that are relational databases, not flat files or spreadsheets. Sometimes, the database administrator for the data source won’t let you add triggers for a variety of reasons, including application performance, so a trigger isn’t always an option.

To handle any changes or additions to the dimension tables, the Analysis Services processing will need to include running a Process Update on the dimensions. This is usually performed using an Analysis Services Processing Task in an Integration Services package that is executed after the new data has been loaded into the data warehouse. Process Update will read all the data in the dimension table and add any new members to the dimension (as well as update any changed attribute values for existing members).

Financial systems may restate the figures for historical time periods, or records in transaction systems occasionally need to be corrected. These applications may then retransmit the updated facts to the ETL process.

As we have seen for dimensions, there are a number of potential solutions depending on the business requirements. If each updated transaction has a unique key that can be used to locate the original record in the data warehouse, you could simply update the record in place. In this case, you need to reprocess any cubes (or specific partitions within a cube, as described in Chapter 11, “Very Large Data Warehouses”) that reference those time periods. This approach would not be appropriate for applications that need to show that an update has taken place, because the original information is lost.

If users need to be able to see original versus updated facts, for each update that is received, you could work out the difference for each measure between the original record and the updated record and then append a new record that has the (positive or negative) adjustment required with a flag that states that this is an update.

If users need to be able to see multiple versions of the data such as for financial restatements, you could add a Version dimension to the cube. This is somewhat complex to model, however, because if a user selects a specific version, the only information they will see will relate to that one version. One technique is to combine the Version dimension with the Date dimension so that each period can have multiple versions.

As you saw in Chapter 6, “Reporting,” Reporting Services enables administrators to store snapshots of reports at certain points in time, which can be retrieved by users. These snapshots are complete representations of the data required to display the report as it currently exists, even if the underlying data source is changed in the future.

This is a useful feature because we do not have to make any changes to the data warehouse to support it. For systems that have a high volume of fact changes and need to accurately reprint reports for historical periods, this might be the easiest approach.