SQL SERVER -SCD Solution

Index Optimizations

Since the dimension lookups and updates both are row-by-row operations, be sure to check the indexes on your dimension table to speed up the identification of the current record. If you are seeing very poor performance with the SCD (anything less than approximately 2,000 rows per minute), then chances are the SCD lookups are requiring relational table scans or bookmark lookups after identifying the record key. For best ETL optimization, create your dimension table’s clustered index on the business key, rather than the dimension surrogate key. Including the current indicator flag or end date as the second column in the index will improve the performance even more.

Update Optimizations

Dimension table updates to handle Type 1 changing attributes are a common occurrence in any dimension table ETL process. Although the inferred member output also requires updates, the number of rows will typically be a fraction of the number of Type 1 changing attributes, since inferred members are considered an exception to the rule. Since the updates are row by row (this is the way the OLE DB Command transformation works), then dealing with thousands of updates will create a processing bottleneck. One way to improve performance is to replace the OLE DB Command update with a set-based update approach. InFigure 4-29, the OLE DB Command that handles the Type 1 changing output has been replaced with an OLE DB Destination to a staging table.

Figure 4-29: OLE DB Destination to a staging table

Using a staging table for this scenario improves performance, because it allows a single set-basedUPDATE statement to be run. TheUPDATE statement is handled with an Execute SQL Task in the control flow.

The set-based statement is comprised of an inner join between the staging table and the dimension table across the business key, where any matching rows (already identified as requiring a Type 1 changing update) will require the attributes to be updated. The following highlights the TSQL code that performs this operation:

UPDATE [dbo].[DimProduct]
SET
 [Color] = STG.[Color]
,[DaysToManufacture] = STG.[DaysToManufacture]
,[EnglishProductName] = STG.[EnglishProductName]
,[FinishedGoodsFlag] = STG.[FinishedGoodsFlag]
,[FrenchProductName] = STG.[FrenchProductName]
,[ProductLine] = STG.[ProductLine]
,[ProductSubcategoryKey] = STG.[ProductSubcategoryKey]
,[ReorderPoint] = STG.[ReorderPoint]
,[SafetyStockLevel] = STG.[SafetyStockLevel]
,[SizeRange] = STG.[SizeRange]
,[SizeUnitMeasureCode] = STG.[SizeUnitMeasureCode]
,[SpanishProductName] = STG.[SpanishProductName]
,[StandardCost] = STG.[StandardCost]
,[Style] = STG.[Style]
,[Weight] = STG.[Weight]
,[WeightUnitMeasureCode] = STG.[WeightUnitMeasureCode]
FROM [dbo].[DimProduct]
INNER JOIN [SSISOps].[dbo].[stgDimProductUpdates] STG
ON [DimProduct].[ProductAlternateKey]
 = STG.[ProductAlternateKey]

Be sure to truncate your staging table for every ETL run (by adding an Execute SQL Task at the start of the control flow); otherwise, you will be updating data from old rows from a prior execution.

Snowflake Dimension Tables

A snowflake table, as briefly described in the beginning of this chapter with the product dimension (seeFigure 4-2 earlier), requires some unique ETL handling aspects. In a snowflake dimension, the higher-level tables (subcategory and category in this example) also have surrogate keys. With the product snowflake dimension, theProductCategoryKey cascades down as a foreign key in theDimProductSubCategory table, and theProductSubCategoryKey cascades down to a foreign key relationship in theDimProduct table. TheProductKey itself relates directly to the fact table, whereas the surrogate keys in the category and subcategory tables do not relate directly to the reseller fact table.

The design is called a snowflake because when viewed in relationship to the fact table, the table layout looks like a snowflake as opposed to a star. (A star schema has dimension tables one level out from the fact table. A single table dimension is often called a star dimension.) Generally, most dimensions are designed as a star dimension. However, there are two very valuable reasons to break out a dimension table into a snowflake design:

• When a dimension table has several attributes that relate directly to a higher level within a dimension hierarchy, managing those dimension changes can be a lot easier with a snowflake design. For example, say the product subcategory table contains the English, Spanish, and French names of the subcategory. If these columns were included in the base product table, and the subcategory changed for the dimension, it would be much more difficult to ensure that the Spanish and French names were in synch with the subcategory. Otherwise, if they were not, reporting of these attributes would be misleading with the incorrect association.

• A second and more compelling reason to use a snowflake is when you have multiple fact tables related to the same dimension table at different levels. For example, if the Reseller Sales fact table tracked sales at the Product Level, but the Sales Quota facts were assigned to a Product Category, if the Product dimension only had one table, the Category could not be effectively joined. Using a snowflake design, the fact tables can be related to one another because they share a common table at different levels of the product dimension hierarchy. Sharing dimension tables between fact tables is a driving emphasis of dimensional modeling calledconforming dimensions.

A straightforward method to processing snowflake dimension in SSIS is to use multiple SCDs embedded in different data flows, linked by precedence constraints in the control flow starting at the top level of the snowflake tables and working down to the lowest level. Figure 4-30 shows the control flow of the product dimension package. Note the very first task is an Execute SQL Task that truncates the staging table used for the set-based update, followed by the three Data Flow Tasks.

Figure 4-30: Control flow of the product dimension package

The first data flow shown is the product category dimension table, followed by the subcategory dimension table, and concluding with the product data flow. The final Execute SQL Task handles the set-based updates for Type 1 changing attributes as discussed earlier.

The product category data flow is the most straightforward because it only requires one Lookup transformation to get the category translation attributes, and, furthermore, it only contains Type 1 changing attributes.Figure 4-31 shows the product category data flow.

Figure 4-31: Product category data flow

Next, you process the product subcategory. When processing data within intermediate snowflake tables in the hierarchy, a lookup is also required to reference the parent table surrogate key. For example, as you process the product subcategory table, include a Lookup transformation to pull the surrogate key of the category, as shown inFigure 4-32.

Figure 4-32: Subcategory lookup to pull the surrogate key of the category

The ETL processing in the lowest-level product table has already been discussed. Not mentioned, but also included in the data preparation steps for the product dimension, is the need to pull the surrogate key of the subcategory table.

Parent-Child Dimension ETL

The next dimension design that we will consider is the parent-child dimension. A parent-child is a self-referencing dimension table and also has special ETL requirements. Simply put, a parent-child dimension has a surrogate key and a parent surrogate key, which gives the dimension a self reference. In addition, parent-child dimensions usually have a business key and a parent business key. The self-referencing business key nicely illustrates the parent relationship in the table.

An organizational structure is a great example. An organization typically has common attributes for all the individuals in the organization, such as location, office, salary, and so on. All of these attributes relate to higher levels in the hierarchy. A parent-child enables you to build a hierarchy where members at different levels have common attributes. Secondly, a parent-child dimension also allows the hierarchy to be unbalanced, where not every drill path in the hierarchy goes down to the same level.

The Employee dimension table as shown in the beginning of the chapter (seeFigure 4-2) is an example of an organizational structure.Figure 4-33 shows a subset of data and columns within the Employee dimension table.

Figure 4-33: Subset of data and columns within the Employee dimension table

As you can see, some dimension members relate to other dimension members. For example, Dan Bacon reports to Jean Trenary (Dan’s parent employee key is 44, which is the employee key of Jean). At the top of the table, the Chief Executive Officer, Ken Sanchez has no parent key and, therefore, no manager. Every member in the table is a member at a different level in the hierarchy. Taking this subset of data and building the hierarchy for the parent-child relationship turns into the hierarchy shown inFigure 4-34.

Figure 4-34: Building the hierarchy for the parent-child relationship

Note a few points about this parent-child dimension:

• This is an unbalanced hierarchy. The levels within this dimension don’t all extend to the lowest level (Laura Norman, for example, has no direct reports). In a USA-based geography dimension, for example, everything typically goes to the same state or city level. Within an unbalanced hierarchy, there are levels that don’t cascade all the way down.

• A parent-child dimension can also be a ragged hierarchy. A ragged hierarchy has holes in the hierarchy; you could be at the top level, skip the next level, and go directly to the level below. You must be aware of these variations when processing ETL.

• Parent-child dimensions have shared attributes. Most of the records, except at the top level, share common attributes, such as Employee Address.

When processing parent-child dimensions in SSIS, a couple of methods can be applied for acquiring the parent record key.

The first approach is to use a Lookup transformation to acquire the parent record for the parent-child relationship.Figure 4-35 shows the data flow used to process the Employee dimension table.

Figure 4-35: Data flow used to process the Employee dimension table

This data flow has similarities to the product dimension data flow, as the first several transformations are used to prepare the data for the SCD transformation. Note that the third data flow object is a Lookup transformation used to acquire the parent surrogate key. In this case, the Lookup transformation joins the parent employee business key of the source to the matching employee business key from the employee table.Figure 4-36 shows the Columns tab of the Lookup editor.

Figure 4-36: Columns tab of the Lookup editor

Besides the join being across the parent business key to the business key in the reference table, the surrogate key that is returned is aliased to match theParentEmployeeKey.

A second approach involves using an Execute SQL Task in the control flow and performing a set-based update to associate the employee with the employee’s manager record. This SQL Task needs to be run after the data flow that adds new dimension records. The following SQL code is used to perform the operation:

UPDATE dbo.DimEmployee
   SET ParentEmployeeKey = Parent.EmployeeKey
FROM dbo.DimEmployee
INNER JOIN dbo.DimEmployee Parent
      ON DimEmployee.ParentEmployeeNationalIDAlternateKey =
            Parent.EmployeeNationalIDAlternateKey
      AND Parent.EndDate IS NULL
WHERE DimEmployee.ParentEmployeeKey IS NULL

The query could be handled by a self join or a correlated subquery. In this case, a join is used between theEmployee table and theEmployee table (aliased asParent) matching across the child employee with the parent manager record. TheParentEmployeeKey is updated with theEmployeeKey of theEmployee table joined as the parent table. Also important to note is that the only records affected are those with theParentEmployeeKey set toNULL, which targets and, therefore, optimizes the updates.

A final approach is to use a combination of a Lookup transformation and an Execute SQL Task. The Lookup transformation approach may fall short of handling all situations if an employee is added at the same time as the assigned manager. In this case, the parent employee key would not yet be available. This combined approach would give better scalability in higher-volume situations by reducing the rows affected by the update.

Date Dimension ETL

The date dimension is probably the most common and conformed dimension in any data warehouse or data mart structure. In other words, most fact tables have relationships to a Date table. In the sampleAdventureWorksDW, the date dimension table is called DimTime. Honestly, it’s poorly named. The wordTime has connotations of time of day-hour or minute, not the date grain, which is actually what is stored in theDimTime table.DimDate would have been a better name.

A date dimension can have many different attributes, besides the year, month, and date. It might include the following:

• Day of week

• Week of year

• Holiday

• Fiscal hierarchy

The attributes combine to create different hierarchies. An example of a natural calendar hierarchy would be Year-Quarter-Month-Date or Year-Week-Date. In the natural calendar, weeks do not line up with months (a week can span two months). Therefore, there are two natural calendar hierarchies. Because of this challenge, organizations have come up with different, and often unique, fiscal hierarchies. Your organization may have its own fiscal hierarchy.

A common approach to a custom fiscal hierarchy is to break a quarter into three periods: four weeks in period 1, four weeks in period 2, and five weeks in period 3 (commonly called a 4-4-5 fiscal date hierarchy). A period essentially replaced a month to accommodate the week-month challenge. One organization we worked with had at least 10 different fiscal hierarchies-every branch of the organization wanted to see the sales data from a different perspective.

When it comes to handling the date dimension ETL, the dimension is relatively easy to process because once a new date record is added, it is usually never updated. The Date dimension is commonly not snowflaked into multiple tables.

There are a few different ways to process date-dimension ETL. The following are some common mechanisms:

1. Handle the date dimension population through a stored procedure. Whether you’re calculating one date at a time every day or out into the future once a year, a stored procedure can load all of the records and handle all the date logic.

2. Use a data flow with Derived Column transformations and date part expressions. The data flow generates a set of dates where additional attributes are added using the date functions in SSIS updated.

3. Use a For Loop Container to iterate through a range of dates with SQL Inserts. The For Each Loop container could be configured to increase the date variable by one every loop and then leverage an Execute SQL Task to manage the inserts, one row at a time.

Any of these approaches are viable. Since the date dimension usually contains a small number of records, this package will process quickly regardless of the approach chosen.

As an example, if the second approach is taken, the process would involve identifying the first new date and the last date to add, followed by a data flow to handle the inserts.Figure 4-37 shows the control flow of a package with an Execute SQL Task followed by a data flow.

Figure 4-37: Control flow of a package with an Execute SQL Task followed by a data flow

Defined in the package are two user variables, FromDate and ToDate, which are populated by the Execute SQL Task using a single row resultset. TheFromDate is populated by querying theMAX date in the time dimension plus one day, and the ToDate is populated from theMAX date in the sales header table source. As you would guess, the data flow only needs to run if theToDate is greater than theFromDate. This is handled through the Precedence Constraint after the Execute SQL Task.Figure 4-38 shows the Precedence Constraint Editor.

Figure 4-38: Precedence Constraint Editor

If the Execute SQL Task is successful and the@ToDate is greater than@FromDate, then the Precedence Constraint will allow the Data Flow Task to execute. At this point, if the criteria are met, then there are rows that need to be inserted into the time dimension table. The data flow, shown inFigure 4-39, contains five components:

• A Script Component that generates a row for every day starting with theStart Date and ending with theEnd Date

• A Derived Column transformation that adds the required date attributes

• Two Lookup transformations to pull in some date translations from a translation table

• A destination adapter that inserts the rows into the Time dimension table.

Figure 4-39: Five components of the data flow

Since the Script Component can be used as a source (as described inChapter 2), it is a great candidate to generate rows from theFromDate to theToDate values. The following code takes the SSIS variables and, using aDo While loop, iterates over the dates from theFromDate to theToDate, adding a date for every loop:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
  Inherits UserComponent

  Public Overrides Sub CreateNewOutputRows()

        Dim vFromDate As Date = Me.Variables.FromDate
        Do While vFromDate <= Me.Variables.ToDate
            With NewDatesOutputBuffer
                .AddRow()
                .FullDateAlternateKey = vFromDate
            End With
            vFromDate = DateAdd(DateInterval.Day, 1, vFromDate)
        Loop

    End Sub

End Class

The second component to highlight is the Derived Column transformation.Figure 4-40 shows the Derived Column editor, which contains several additional columns using theDATEPART SSIS expression function to pull out common attributes.

Figure 4-40: Derived Column editor

Before the OLE DB Destination, which is just an insert into the DimTime table, two Lookups handle the translation, because the time dimension table supports multiple languages for the month- and day-related attributes.

Overall, the package is straightforward, performs well, and leverages SSIS capabilities.