Export (0) Print
Expand All

Chapter 19 - Data Extraction, Transformation, and Loading Techniques

Chapter 17, "Data Warehouse Design Considerations," discussed the use of dimensional modeling to design databases for data warehousing. In contrast to the complex, highly normalized, entity-relationship schemas of online transaction processing (OLTP) databases, data warehouse schemas are simple and denormalized. Regardless of the specific design or technology used in a data warehouse, its implementation must include mechanisms to migrate data into the data warehouse database. This process of data migration is generally referred to as the extraction, transformation, and loading (ETL) process.

Some data warehouse experts add an additional term—management—to ETL, expanding it to ETLM. Others use the M to mean meta data. Both refer to the management of the data as it flows into the data warehouse and is used in the data warehouse. The information used to manage data consists of data about data, which is the definition of meta data.

The topics in this chapter describe the elements of the ETL process and provide examples of procedures that address common ETL issues such as managing surrogate keys, slowly changing dimensions, and meta data.

The code examples in this chapter are also available on the SQL Server 2000 Resource Kit CD-ROM, in the file \Docs\ChapterCode\CH19Code.txt. For more information, see Chapter 39, "Tools, Samples, eBooks, and More."

Introduction

During the ETL process, data is extracted from an OLTP database, transformed to match the data warehouse schema, and loaded into the data warehouse database. Many data warehouses also incorporate data from non-OLTP systems, such as text files, legacy systems, and spreadsheets; such data also requires extraction, transformation, and loading.

In its simplest form, ETL is the process of copying data from one database to another. This simplicity is rarely, if ever, found in data warehouse implementations; in reality, ETL is often a complex combination of process and technology that consumes a significant portion of the data warehouse development efforts and requires the skills of business analysts, database designers, and application developers.

When defining ETL for a data warehouse, it is important to think of ETL as a process, not a physical implementation. ETL systems vary from data warehouse to data warehouse and even between department data marts within a data warehouse. A monolithic application, regardless of whether it is implemented in Transact-SQL or a traditional programming language, does not provide the flexibility for change necessary in ETL systems. A mixture of tools and technologies should be used to develop applications that each perform a specific ETL task.

The ETL process is not a one-time event; new data is added to a data warehouse periodically. Typical periodicity may be monthly, weekly, daily, or even hourly, depending on the purpose of the data warehouse and the type of business it serves. Because ETL is an integral, ongoing, and recurring part of a data warehouse, ETL processes must be automated and operational procedures documented. ETL also changes and evolves as the data warehouse evolves, so ETL processes must be designed for ease of modification. A solid, well-designed, and documented ETL system is necessary for the success of a data warehouse project.

Data warehouses evolve to improve their service to the business and to adapt to changes in business processes and requirements. Business rules change as the business reacts to market influences—the data warehouse must respond in order to maintain its value as a tool for decision makers. The ETL implementation must adapt as the data warehouse evolves.

Microsoft® SQL Server™ 2000 provides significant enhancements to existing performance and capabilities, and introduces new features that make the development, deployment, and maintenance of ETL processes easier and simpler, and its performance faster.

ETL Functional Elements

Regardless of how they are implemented, all ETL systems have a common purpose: they move data from one database to another. Generally, ETL systems move data from OLTP systems to a data warehouse, but they can also be used to move data from one data warehouse to another. An ETL system consists of four distinct functional elements:

  • Extraction

  • Transformation

  • Loading

  • Meta data 

Extraction

The ETL extraction element is responsible for extracting data from the source system. During extraction, data may be removed from the source system or a copy made and the original data retained in the source system. It is common to move historical data that accumulates in an operational OLTP system to a data

warehouse to maintain OLTP performance and efficiency. Legacy systems may require too much effort to implement such offload processes, so legacy data is often copied into the data warehouse, leaving the original data in place. Extracted data is loaded into the data warehouse staging area (a relational database usually separate from the data warehouse database), for manipulation by the remaining ETL processes.

Data extraction is generally performed within the source system itself, especially if it is a relational database to which extraction procedures can easily be added. It is also possible for the extraction logic to exist in the data warehouse staging area and query the source system for data using ODBC, OLE DB, or other APIs. For legacy systems, the most common method of data extraction is for the legacy system to produce text files, although many newer systems offer direct query APIs or accommodate access through ODBC or OLE DB.

Data extraction processes can be implemented using Transact-SQL stored procedures, Data Transformation Services (DTS) tasks, or custom applications developed in programming or scripting languages.

Transformation

The ETL transformation element is responsible for data validation, data accuracy, data type conversion, and business rule application. It is the most complicated of the ETL elements. It may appear to be more efficient to perform some transformations as the data is being extracted (inline transformation); however, an ETL system that uses inline transformations during extraction is less robust and flexible than one that confines transformations to the transformation element. Transformations performed in the OLTP system impose a performance burden on the OLTP database. They also split the transformation logic between two ETL elements and add maintenance complexity when the ETL logic changes.

Tools used in the transformation element vary. Some data validation and data accuracy checking can be accomplished with straightforward Transact-SQL code. More complicated transformations can be implemented using DTS packages. The application of complex business rules often requires the development of sophisticated custom applications in various programming languages. You can use DTS packages to encapsulate multi-step transformations into a single task.

Listed below are some basic examples that illustrate the types of transformations performed by this element:

Data Validation 

Check that all rows in the fact table match rows in dimension tables to enforce data integrity.

Data Accuracy 

Ensure that fields contain appropriate values, such as only "off" or "on" in a status field.

Data Type Conversion 

Ensure that all values for a specified field are stored the same way in the data warehouse regardless of how they were stored in the source system. For example, if one source system stores "off" or "on" in its status field and another source system stores "0" or "1" in its status field, then a data type conversion transformation converts the content of one or both of the fields to a specified common value such as "off" or "on".

Business Rule Application 

Ensure that the rules of the business are enforced on the data stored in the warehouse. For example, check that all customer records contain values for both FirstName and LastName fields.

Loading

The ETL loading element is responsible for loading transformed data into the data warehouse database. Data warehouses are usually updated periodically rather than continuously, and large numbers of records are often loaded to multiple tables in a single data load. The data warehouse is often taken offline during update operations so that data can be loaded faster and SQL Server 2000 Analysis Services can update OLAP cubes to incorporate the new data. BULK INSERT, bcp, and the Bulk Copy API are the best tools for data loading operations. The design of the loading element should focus on efficiency and performance to minimize the data warehouse offline time. For more information and details about performance tuning, see Chapter 20, "RDBMS Performance Tuning Guide for Data Warehousing."

Meta Data

The ETL meta data functional element is responsible for maintaining information (meta data) about the movement and transformation of data, and the operation of the data warehouse. It also documents the data mappings used during the transformations. Meta data logging provides possibilities for automated administration, trend prediction, and code reuse.

Examples of data warehouse meta data that can be recorded and used to analyze the activity and performance of a data warehouse include:

  • Data Lineage, such as the time that a particular set of records was loaded into the data warehouse. 

  • Schema Changes, such as changes to table definitions. 

  • Data Type Usage, such as identifying all tables that use the "Birthdate" user-defined data type. 

  • Transformation Statistics, such as the execution time of each stage of a transformation, the number of rows processed by the transformation, the last time the transformation was executed, and so on. 

  • DTS Package Versioning, which can be used to view, branch, or retrieve any historical version of a particular DTS package. 

  • Data Warehouse Usage Statistics, such as query times for reports. 

ETL Design Considerations

Regardless of their implementation, a number of design considerations are common to all ETL systems:

Modularity 

ETL systems should contain modular elements that perform discrete tasks. This encourages reuse and makes them easy to modify when implementing changes in response to business and data warehouse changes. Monolithic systems should be avoided.

Consistency 

ETL systems should guarantee consistency of data when it is loaded into the data warehouse. An entire data load should be treated as a single logical transaction—either the entire data load is successful or the entire load is rolled back. In some systems, the load is a single physical transaction, whereas in others it is a series of transactions. Regardless of the physical implementation, the data load should be treated as a single logical transaction.

Flexibility 

ETL systems should be developed to meet the needs of the data warehouse and to accommodate the source data environments. It may be appropriate to accomplish some transformations in text files and some on the source data system; others may require the development of custom applications. A variety of technologies and techniques can be applied, using the tool most appropriate to the individual task of each ETL functional element.

Speed 

ETL systems should be as fast as possible. Ultimately, the time window available for ETL processing is governed by data warehouse and source system schedules. Some data warehouse elements may have a huge processing window (days), while others may have a very limited processing window (hours). Regardless of the time available, it is important that the ETL system execute as rapidly as possible.

Heterogeneity 

ETL systems should be able to work with a wide variety of data in different formats. An ETL system that only works with a single type of source data is useless.

Meta Data Management 

ETL systems are arguably the single most important source of meta data about both the data in the data warehouse and data in the source system. Finally, the ETL process itself generates useful meta data that should be retained and analyzed regularly. Meta data is discussed in greater detail later in this chapter.

ETL Architectures

Before discussing the physical implementation of ETL systems, it is important to understand the different ETL architectures and how they relate to each other. Essentially, ETL systems can be classified in two architectures: the homogenous architecture and the heterogeneous architecture.

Homogenous Architecture

A homogenous architecture for an ETL system is one that involves only a single source system and a single target system. Data flows from the single source of data through the ETL processes and is loaded into the data warehouse, as shown in the following diagram.

Cc917646.etl01(en-us,TechNet.10).gif 

Most homogenous ETL architectures have the following characteristics:

  • Single data source: Data is extracted from a single source system, such as an OLTP system. 

  • Rapid development: The development effort required to extract the data is straightforward because there is only one data format for each record type.

  • Light data transformation: No data transformations are required to achieve consistency among disparate data formats, and the incoming data is often in a format usable in the data warehouse. Transformations in this architecture typically involve replacing NULLs and other formatting transformations. 

  • Light structural transformation: Because the data comes from a single source, the amount of structural changes such as table alteration is also very light. The structural changes typically involve denormalization efforts to meet data warehouse schema requirements. 

  • Simple research requirements: The research efforts to locate data are generally simple: if the data is in the source system, it can be used. If it is not, it cannot. 

The homogeneous ETL architecture is generally applicable to data marts, especially those focused on a single subject matter.

Heterogeneous Architecture

A heterogeneous architecture for an ETL system is one that extracts data from multiple sources, as shown in the following diagram. The complexity of this architecture arises from the fact that data from more than one source must be merged, rather than from the fact that data may be formatted differently in the different sources. However, significantly different storage formats and database schemas do provide additional complications.

Cc917646.etl02(en-us,TechNet.10).gif 

Most heterogeneous ETL architectures have the following characteristics:

  • Multiple data sources. 

  • More complex development: The development effort required to extract the data is increased because there are multiple source data formats for each record type.

  • Significant data transformation: Data transformations are required to achieve consistency among disparate data formats, and the incoming data is often not in a format usable in the data warehouse. Transformations in this architecture typically involve replacing NULLs, additional data formatting, data conversions, lookups, computations, and referential integrity verification. Precomputed calculations may require combining data from multiple sources, or data that has multiple degrees of granularity, such as allocating shipping costs to individual line items.

  • Significant structural transformation: Because the data comes from multiple sources, the amount of structural changes, such as table alteration, is significant. 

  • Substantial research requirements to identify and match data elements. 

Heterogeneous ETL architectures are found more often in data warehouses than in data marts.

ETL Development

ETL development consists of two general phases: identifying and mapping data, and developing functional element implementations. Both phases should be carefully documented and stored in a central, easily accessible location, preferably in electronic form.

Identify and Map Data

This phase of the development process identifies sources of data elements, the targets for those data elements in the data warehouse, and the transformations that must be applied to each data element as it is migrated from its source to its destination. High level data maps should be developed during the requirements gathering and data modeling phases of the data warehouse project. During the ETL system design and development process, these high level data maps are extended to thoroughly specify system details.

Identify Source Data

For some systems, identifying the source data may be as simple as identifying the server where the data is stored in an OLTP database and the storage type (SQL Server database, Microsoft Excel spreadsheet, or text file, among others). In other systems, identifying the source may mean preparing a detailed definition of the meaning of the data, such as a business rule, a definition of the data itself, such as decoding rules (O = On, for example), or even detailed documentation of a source system for which the system documentation has been lost or is not current.

Identify Target Data

Each data element is destined for a target in the data warehouse. A target for a data element may be an attribute in a dimension table, a numeric measure in a fact table, or a summarized total in an aggregation table. There may not be a one-to-one correspondence between a source data element and a data element in the data warehouse because the destination system may not contain the data at the same granularity as the source system. For example, a retail client may decide to roll data up to the SKU level by day rather than track individual line item data. The level of item detail that is stored in the fact table of the data warehouse is called the grain of the data. If the grain of the target does not match the grain of the source, the data must be summarized as it moves from the source to the target.

Map Source Data to Target Data

A data map defines the source fields of the data, the destination fields in the data warehouse and any data modifications that need to be accomplished to transform the data into the desired format for the data warehouse. Some transformations require aggregating the source data to a coarser granularity, such as summarizing individual item sales into daily sales by SKU. Other transformations involve altering the source data itself as it moves from the source to the target. Some transformations decode data into human readable form, such as replacing "1" with "on" and "0" with "off" in a status field. If two source systems encode data destined for the same target differently (for example, a second source system uses Yes and No for status), a separate transformation for each source system must be defined. Transformations must be documented and maintained in the data maps. The relationship between the source and target systems is maintained in a map that is referenced to execute the transformation of the data before it is loaded in the data warehouse.

Develop Functional Elements

Design and implementation of the four ETL functional elements, Extraction, Transformation, Loading, and meta data logging, vary from system to system. There will often be multiple versions of each functional element.

Each functional element contains steps that perform individual tasks, which may execute on one of several systems, such as the OLTP or legacy systems that contain the source data, the staging area database, or the data warehouse database. Various tools and techniques may be used to implement the steps in a single functional area, such as Transact-SQL, DTS packages, or custom applications developed in a programming language such as Microsoft Visual Basic®. Steps that are discrete in one functional element may be combined in another.

Extraction

The extraction element may have one version to extract data from one OLTP data source, a different version for a different OLTP data source, and multiple versions for legacy systems and other sources of data. This element may include tasks that execute SELECT queries from the ETL staging database against a source OLTP system, or it may execute some tasks on the source system directly and others in the staging database, as in the case of generating a flat file from a legacy system and then importing it into tables in the ETL database. Regardless of methods or number of steps, the extraction element is responsible for extracting the required data from the source system and making it available for processing by the next element.

Transformation

Frequently a number of different transformations, implemented with various tools or techniques, are required to prepare data for loading into the data warehouse. Some transformations may be performed as data is extracted, such as an application on a legacy system that collects data from various internal files as it produces a text file of data to be further transformed. However, transformations are best accomplished in the ETL staging database, where data from several data sources may require varying transformations specific to the incoming data organization and format.

Data from a single data source usually requires different transformations for different portions of the incoming data. Fact table data transformations may include summarization, and will always require surrogate dimension keys to be added to the fact records. Data destined for dimension tables in the data warehouse may require one process to accomplish one type of update to a changing dimension and a different process for another type of update.

Transformations may be implemented using Transact-SQL, as is demonstrated in the code examples later in this chapter, DTS packages, or custom applications.

Regardless of the number and variety of transformations and their implementations, the transformation element is responsible for preparing data for loading into the data warehouse.

Loading

The loading element typically has the least variety of task implementations. After the data from the various data sources has been extracted, transformed, and combined, the loading operation consists of inserting records into the various data warehouse database dimension and fact tables. Implementation may vary in the loading tasks, such as using BULK INSERT, bcp, or the Bulk Copy API. The loading element is responsible for loading data into the data warehouse database tables.

Meta Data Logging

Meta data is collected from a number of the ETL operations. The meta data logging implementation for a particular ETL task will depend on how the task is implemented. For a task implemented by using a custom application, the application code may produce the meta data. For tasks implemented by using Transact-SQL, meta data can be captured with Transact-SQL statements in the task processes. The meta data logging element is responsible for capturing and recording meta data that documents the operation of the ETL functional areas and tasks, which includes identification of data that moves through the ETL system as well as the efficiency of ETL tasks.

Common Tasks

Each ETL functional element should contain tasks that perform the following functions, in addition to tasks specific to the functional area itself:

Confirm Success or Failure. A confirmation should be generated on the success or failure of the execution of the ETL processes. Ideally, this mechanism should exist for each task so that rollback mechanisms can be implemented to allow for incremental responses to errors.

Scheduling. ETL tasks should include the ability to be scheduled for execution. Scheduling mechanisms reduce repetitive manual operations and allow for maximum use of system resources during recurring periods of low activity.

SQL Server 2000 ETL Components

SQL Server 2000 includes several components that aid in the development and maintenance of ETL systems:

  • Data Transformation Services (DTS): SQL Server 2000 DTS is a set of graphical tools and programmable objects that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations. 

  • SQL Server Agent: SQL Server Agent provides features that support the scheduling of periodic activities on SQL Server 2000, or the notification to system administrators of problems that have occurred with the server. 

  • Stored Procedures and Views: Stored procedures assist in achieving a consistent implementation of logic across applications. The Transact-SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure. A view can be thought of as either a virtual table or a stored query. The data accessible through a view is not stored in the database as a distinct object; only the SELECT statement for the view is stored in the database. 

  • Transact SQL: Transact-SQL is a superset of the SQL standard that provides powerful programming capabilities that include loops, variables, and other programming constructs. 

  • OLE DB: OLE DB is a low-level interface to data. It is an open specification designed to build on the success of ODBC by providing an open standard for accessing all kinds of data. 

  • Meta Data Services: SQL Server 2000 Meta Data Services provides a way to store and manage meta data about information systems and applications. This technology serves as a hub for data and component definitions, development and deployment models, reusable software components, and data warehousing descriptions. 

The ETL Staging Database

In general, ETL operations should be performed on a relational database server separate from the source databases and the data warehouse database. A separate staging area database server creates a logical and physical separation between the source systems and the data warehouse, and minimizes the impact of the intense periodic ETL activity on source and data warehouse databases. If a separate database server is not available, a separate database on the data warehouse database server can be used for the ETL staging area. However, in this case it is essential to schedule periods of high ETL activity during times of low data warehouse user activity.

For small data warehouses with available excess performance and low user activity, it is possible to incorporate the ETL system into the data warehouse database. The advantage of this approach is that separate copies of data warehouse tables are not needed in the staging area. However, there is always some risk associated with performing transformations on live data, and ETL activities must be very carefully coordinated with data warehouse periods of minimum activity. When ETL is integrated into the data warehouse database, it is recommended that the data warehouse be taken offline when performing ETL transformations and loading.

Most systems can effectively stage data in a SQL Server 2000 database, as we describe in this chapter. An ETL system that needs to process extremely large volumes of data will need to use specialized tools and custom applications that operate on files rather than database tables. With extremely large volumes of data, it is not practical to load data into a staging database until it has been cleaned, aggregated, and stripped of meaningless information. Because it is much easier to build an ETL system using the standard tools and techniques that are described in this chapter, most experienced system designers will attempt to use a staging database, and move to custom tools only if data cannot be processed during the load window.

What does "extremely large" mean and when does it become infeasible to use standard DTS tasks and Transact-SQL scripts to process data from a staging database? The answer depends on the load window, the complexity of transformations, and the degree of data aggregation necessary to create the rows that are permanently stored in the data warehouse. As a conservative rule of thumb, if the transformation application needs to process more than 1 gigabyte of data in less than an hour, it may be necessary to consider specialized high performance techniques, which are outside the scope of this chapter.

This section provides general information about configuring the SQL Server 2000 database server and the database to support an ETL system staging area database with effective performance. ETL systems can vary greatly in their database server requirements; server configurations and performance option settings may differ significantly from one ETL system to another.

ETL data manipulation activities are similar in design and functionality to those of OLTP systems although ETL systems do not experience the constant activity associated with OLTP systems. Instead of constant activity, ETL systems have periods of high write activity followed by periods of little or no activity. Configuring a server and database to meet the needs of an ETL system is not as straightforward as configuring a server and database for an OLTP system.

For a detailed discussion of RAID and SQL Server 2000 performance tuning, see Chapter 20, "RDBMS Performance Tuning Guide for Data Warehousing."

Server Configuration

Disk storage system performance is one of the most critical factors in the performance of database systems. Server configuration options offer additional methods for adjusting server performance.

RAID

As with any OLTP system, the RAID level for the disk drives on the server can make a considerable performance difference. For maximum performance of an ETL database, the disk drives for the server computer should be configured with RAID 1 or RAID 10. Additionally, it is recommended that the transaction logs, databases, and tempdb be placed on separate physical drives. Finally, if the hardware controller supports write caching, it is recommended that write caching be enabled. However, be sure to use a caching controller that guarantees that the controller cache contents will be written to disk in case of a system failure.

Server Configuration Options (sp_configure)

No specific changes need to be made to the server configuration options in order to optimize performance for an ETL system. It is recommended that these options be left at their default settings unless there is a specific reason to modify them.

Database Configuration

In SQL Server 2000, database performance can be tuned by proper selection of settings for data file growth and adjusting database configuration options.

Data File Growth

When creating a database, an initial size for the data files for the database and transaction log must be specified. By default, SQL Server 2000 allows the data files to grow as much as necessary until disk space is exhausted. It is important to size the database appropriately before loading any data into it to avoid the I/O intensive operation of auto-growing data files. Failure to appropriately size the data files initially for the database means that SQL will be forced to frequently increase the size of the data files, which will degrade performance of the ETL processes.

Appropriate initial sizing of the data files can reduce the likelihood of SQL being forced to increase the size of the database, which eliminates an intensive I/O operation. If a data file is allowed to automatically grow, the file growth may be specified by a percentage or a set number value. The growth value can be specified in megabytes (MB), kilobytes (KB), or percent. If percent is specified, the increment size is the specified percentage of the file size at the time the increment occurs. If the data file is too small, the growth increments will be frequent. For example, if a data file is initially created at 10 MB and set to grow in 10 percent increments until it reaches 20 MB, SQL Server 2000 will perform eight auto-grow operations as the data file size increases to 20 MB. Therefore, it is recommended that a fixed MB value be chosen for data file growth increments.

Finally, if the server uses SCSI disks, special care should be paid to preventing disk space consumption from increasing beyond 85 percent of the capacity of the drive. Beyond 85 percent consumption, SCSI disk performance begins to degrade. Therefore, it is recommended that the data files for the database are set to grow automatically, but only to a predefined maximum size, which should be no more than 85 percent capacity of the drive.

Database Configuration Options

Several database options can be adjusted to enhance the performance of an ETL database. For a complete discussion of these options, see SQL Server Books Online. For more information about database performance tuning, see Chapter 20, "RDBMS Performance Tuning Guide for Data Warehousing."

The following table lists some database options and their setting that may be used to increase ETL performance.

Option name

Setting

AUTO_CREATE_STATISTICS

Off

AUTO_UPDATE_STATISTICS

On

AUTO_SHRINK

Off

CURSOR_DEFAULT

LOCAL

RECOVERY Option

Bulk_Loaded

TORN_PAGE_DETECTION

On

Caution Different recovery model options introduce varying degrees of risk of data loss. It is imperative that the risks be thoroughly understood before choosing a recovery model.

Managing Surrogate Keys

Surrogate keys are critical to successful data warehouse design: they provide the means to maintain data warehouse information when dimensions change. For more information and details about surrogate keys, see Chapter 17, "Data Warehouse Design Considerations."

The following are some common characteristics of surrogate keys:

  • Used as the primary key for each dimension table, instead of the original key used in the source data system. The original key for each record is carried in the table but is not used as the primary key. 

  • May be defined as the primary key for the fact table. In general, the fact table uses a composite primary key composed of the dimension foreign key columns, with no surrogate key. In schemas with many dimensions, load and query performance will improve substantially if a surrogate key is used. If the fact table is defined with a surrogate primary key and no unique index on the composite key, the ETL application must be careful to ensure row uniqueness outside the database. A third possibility for the fact table is to define no primary key at all. While there are systems for which this is the most effective approach, it is not good database practice and should be considered with caution.

  • Contains no meaningful business information; its only purpose is to uniquely identify each row. There is one exception: the primary key for a time dimension table provides human-readable information in the format "yyyymmdd ".

  • Is a simple key on a single column, not a composite key. 

  • Should be numeric, preferably integer, and not text. 

  • Should never be a GUID. 

The SQL Server 2000 Identity column provides an excellent surrogate key mechanism.

ETL Code Examples

Code examples in these sections use the pubs sample database included with SQL Server 2000 to demonstrate various activities performed in ETL systems. The examples illustrate techniques for loading dimension tables in the data warehouse; they do not take into consideration separate procedures that may be required to update OLAP cubes or aggregation tables.

The use of temporary and staging tables in the ETL database allows the data extraction and loading process to be broken up into smaller segments of work that can be individually recovered. The temporary tables allow the source data to be loaded and transformed without impacting the performance of the source system except for what is necessary to extract the data. The staging tables provide a mechanism for data validation and surrogate key generation before loading transformed data into the data warehouse. Transformation, validation, and surrogate key management tasks should never be performed directly on dimension tables in the data warehouse.

The code examples in this chapter are presented as Transact-SQL, in order to communicate to the widest audience. A production ETL system would use DTS to perform this work. A very simple system may use several Execute SQL tasks linked within a package. More complex systems divide units of work into separate packages, and call those subpackages from a master package. For a detailed explanation of how to use DTS to implement the functionality described in this chapter, please see SQL Server Books Online.

Tables for Code Examples

The examples use the authors table in the pubs database as the source of data. The following three tables are created for use by the code examples.

Table name

Purpose

Authors_Temp

Holds the data imported from the source system.

Authors_Staging

Holds the dimension data while it is being updated. The data for the authors will be updated in this table and then the data will be loaded into the data warehouse dimension table.

Authors_DW

Simulates the Authors dimension table in the data warehouse.

These are key points regarding the structures of these tables:

  • There is no difference between the structure of the authors table in the pubs database and the Authors_Temp table in the staging area. This allows for straightforward extraction of data from the source system with minimum impact on source system performance.

  • The Author_Staging table is used to generate the surrogate key (Author_Key column) that is used by the data warehouse. This table is also used to validate any data changes, convert data types, and perform any other transformations necessary to prepare the data for loading into the data warehouse. 

  • The structure of the Author_Staging table in the staging area is the same as that of the Authors_DW table in the data warehouse. This allows for straightforward loading of the dimension data from the staging database to the data warehouse. If the dimension table in the data warehouse is small enough, it can be truncated and replaced with data from the staging table. In many data warehouses, dimension tables are too large to be efficiently updated by dropping and reloading them in their entirety. In this case, the tables in both the staging area and data warehouse should contain a datetime column, which can be used to determine which records need to be updated, inserted, or deleted in the data warehouse table. 

  • The staging and data warehouse tables are identical after the data is loaded into the data warehouse. This fact can be considered for use in backup strategy planning. 

Define Example Tables

The following three Transact-SQL statements create the Authors_Temp and Authors_Staging tables, and the Authors_DW table that simulates the Authors dimension table in the data warehouse:

Code Example 19.1 

CREATE TABLE [Authors_Temp] (
[au_id] [varchar] (11) PRIMARY KEY CLUSTERED,
[au_lname] [varchar] (40) DEFAULT ('Missing'),
[au_fname] [varchar] (20) DEFAULT ('Missing'),
[phone] [char] (12) DEFAULT ('000-000-0000'),
[address] [varchar] (40) DEFAULT ('Missing'),
[city] [varchar] (20) DEFAULT ('Missing'),
[state] [char] (2) DEFAULT ('XX'),
[zip] [char] (5) DEFAULT ('00000'),
[contract] [bit] NOT NULL DEFAULT (0)) 
ON [PRIMARY]
GO

CREATE TABLE Authors_Staging (
[Author_Key] int NOT NULL IDENTITY (1,1) PRIMARY KEY CLUSTERED ,
[au_id] varchar (11) NOT NULL ,
[au_lname] varchar (40) NOT NULL DEFAULT ('Missing'),
[au_fname] varchar (20) NOT NULL DEFAULT ('Missing'),
[phone] char (12) NOT NULL DEFAULT ('000-000-0000'),
[address] varchar (40) NULL DEFAULT ('Missing'),
[city] varchar (20) NOT NULL DEFAULT ('Missing'),
[state] char (2) NOT NULL DEFAULT ('XX') ,
[zip] char (5) NOT NULL DEFAULT ('00000') ,
[contract] bit NOT NULL,
[DateCreated] smalldatetime NOT NULL DEFAULT (getdate()),
[DateUpdated] smalldatetime NOT NULL DEFAULT (getdate())
)ON [PRIMARY]
GO

CREATE TABLE [Authors_DW] (
[Author_Key] [int] NOT NULL PRIMARY KEY CLUSTERED,
[au_id] [varchar] (11) NOT NULL,
[au_lname] [varchar] (40) NOT NULL DEFAULT ('Missing'),
[au_fname] [varchar] (20) NOT NULL DEFAULT ('Missing'),
[phone] [char] (12) NOT NULL DEFAULT ('000-000-0000'),

[address] [varchar] (40) NULL DEFAULT ('Missing'),
[city] [varchar] (20) NOT NULL DEFAULT ('Missing'),
[state] [char] (2) NOT NULL DEFAULT ('XX'),
[zip] [char] (5) NOT NULL DEFAULT ('00000'),
[contract] [bit] NOT NULL,
[DateCreated] smalldatetime NOT NULL DEFAULT (getdate()),
[DateUpdated] smalldatetime NOT NULL DEFAULT (getdate())
) ON [PRIMARY]
GO
Populate Example Tables

The following three Transact-SQL statements populate the temporary, staging, and data warehouse sample tables by loading all of the author records except the record for author Johnson White, which will be inserted later to illustrate a technique for adding records to the data warehouse dimension table:

Code Example 19.2 

--Populate the Authors_Temp table with all author records except Johnson White's
INSERT INTO Authors_Temp
SELECT * FROM Authors
WHERE AU_ID <> '172-32-1176'
GO

--Populate the Authors_Staging table from Authors_Temp
INSERT INTO Authors_Staging (au_id, au_lname, au_fname, phone, address, city, state, 
zip, contract)
SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract
FROM Authors_Temp
GO

--Populate the simulated data warehouse dimension table, Authors_DW
INSERT INTO Authors_DW
SELECT * FROM Authors_Staging
GO

The contents of the three tables now simulate the state following the completion of all previous ETL processing before the author Johnson White is added to the source data table.

Inserting New Dimension Records

Loading new author records is a relatively simple task. If the extraction method is capable of generating a change set (a set of records that have been altered since the last data extraction) from the source system, we load the change set into the temporary table. If we cannot generate a change set from the source system, we will have to load the entire data set from the source system into the temporary table, even if only a single record has changed.

The following Transact-SQL code demonstrates a simple technique for loading new rows into the Authors dimension. This example assumes that there is a primary key on the source system that we can use and it assumes that we do not have a change set.

Code Example 19.3 

--Truncate any data that currently exists in the Authors_Temp table
TRUNCATE TABLE Authors_Temp
GO

--Load all of the data from the source system into the Authors_Temp table 
INSERT INTO Authors_Temp
SELECT * FROM Authors
GO

--Set a starting value for the Contract field for two records
-- for use by future examples
UPDATE Authors_Temp
SET Contract = 0
WHERE state = 'UT'
GO

--Locate all of the new records that have been added to the source system by
--comparing the new temp table contents to the existing staging table contents
--and add the new records to the staging table
INSERT INTO Authors_Staging (au_id, au_lname, au_fname, phone, address, city, state, 
zip, contract)
SELECT T.au_id, T.au_lname, T.au_fname, T.phone, T.address, T.city, T.state, T.zip, 
T.contract
FROM Authors_Temp T LEFT OUTER JOIN
Authors_Staging S ON T.au_id = S.au_id
WHERE (S.au_id IS NULL)
GO

--Locate all of the new records that are to be added to the data warehouse
--and insert them into the data warehouse by comparing Authors_Staging to Authors_DW 
INSERT INTO Authors_DW (Author_Key, au_id, au_lname, au_fname, phone, address, city, 
state, zip, contract,
DateCreated, DateUpdated)
SELECT S.Author_Key, S.au_id, S.au_lname, S.au_fname, S.phone, S.address, S.city, 
S.state, S.zip, S.contract,
S.DateCreated, S.DateUpdated
FROM Authors_Staging S LEFT OUTER JOIN
Authors_DW D ON S.au_id = D.au_id
WHERE (D.au_id IS NULL)
GO

Managing Slowly Changing Dimensions

This section describes various techniques for managing slowly changing dimensions in the data warehouse. "Slowly changing dimensions" is the customary term used for dimensions that contain attributes that, when changed, may affect grouping or summarization of historical data. Design approaches to dealing with the issues of slowly changing dimensions are commonly categorized into the following three change types:

  • Type 1: Overwrite the dimension record 

  • Type 2: Add a new dimension record 

  • Type 3: Create new fields in the dimension record 

Type 1 and Type 2 dimension changes are discussed in this section. Type 3 changes are not recommended for most data warehouse applications and are not discussed here. For more information and details about slowly changing dimensions, see Chapter 17, "Data Warehouse Design Considerations."

Type 1 and Type 2 dimension change techniques are used when dimension attributes change in records that already exist in the data warehouse. The techniques for inserting new records into dimensions (discussed earlier in the section "Inserting New Dimension Records") apply to all dimensions regardless of whether changes to dimension attributes are incorporated using Type 1 or Type 2 change techniques.

The code examples in the following sections demonstrate techniques for managing Type 1 and Type 2 dimension changes. The examples have been kept simple to maintain clarity for technique illustration purposes—the examples assume that all changes for a dimension will be of the same type, whereas, in reality, most dimensions include some attributes that require Type 2 changes and other attributes that can be maintained using Type 1 changes. For example, a retailer may decide that a change in the marital status of a customer should be treated as a Type 2 change, whereas a change of street address for the same customer should be treated as a Type 1 change. Therefore, it is important to document all of the attributes in a dimension and, for each attribute, whether a value change should be applied as Type 1 or a Type 2 change.

Type 1: Overwrite the Dimension Record

A change to a dimension attribute that is never used for analysis can be managed by simply changing the data to the new value. This type of change is called a Type 1 change. For example, a change to a customer's street address is unlikely to affect any summarized information and the previous street address can be discarded without consequence.

Type 1 dimension changes are straightforward to implement. The following Transact-SQL code demonstrates a simple Type 1 technique for updating existing rows in the Authors dimension. For this example, we will change some data in the Authors_Temp table to simulate changed records received as a result of updates to the authors table in the source database. The value for the Contract field is assumed to be eligible for Type 1 changes in this example. In a later section, the Contract field will be updated using a Type 2 change. The following example assumes that there is a primary key on the source system that we can use and it assumes that we do not have a change set:

Code Example 19.4 

--Change the Authors_Temp table to simulate updates received from the source system
UPDATE Authors_Temp
SET Contract = 0
WHERE state = 'UT'
GO

--Update the Authors_Staging table with the new values in Authors_Temp
UPDATE Authors_Staging
SET Contract = T.Contract,
DateUpdated = getdate()
FROM Authors_Temp T INNER JOIN Authors_Staging S 
ON T.au_id = S.au_id
WHERE T.Contract <> S.Contract
GO

--Update the Author_DW with the new data in Authors_Staging
UPDATE Authors_DW
SET Contract = S.Contract,
DateUpdated = getdate()
FROM Authors_Staging S INNER JOIN Authors_DW D
ON S.Author_Key = D.Author_Key
WHERE S.Contract <> D.Contract
GO
Type 2: Add a New Dimension Record

Type 2 changes cause history to be partitioned at the event that triggered the change. Data prior to the event continues to be summarized and analyzed as before; new data is summarized and analyzed in accordance with the new value of the data. The technique for implementing a Type 2 change is to keep the existing dimension record and add a new record that contains the updated data for the attribute or attributes that have changed. Values are copied from the existing record to the new record for all fields that have not changed. A new surrogate key value is created for the new record and the record is added to the dimension table. Fact records that apply to events subsequent to the Type 2 change must be related to the new dimension record.

Although it is relatively straightforward to implement Type 2 change techniques in the ETL process to manage slowly changing dimensions, the data associated with a dimension member becomes fragmented as such changes are made. Data warehouse analysis and reporting tools must be capable of summarizing data correctly for dimensions that include Type 2 changes. To minimize unnecessary fragmentation, a Type 2 change should not be used if a Type 1 change is appropriate.

The techniques used to insert new records into a Type 2 dimension are the same as the ones used to insert new records into a Type 1 dimension. However, the techniques used to track updates to dimension records are different.

The following Transact-SQL code demonstrates a simple technique for applying Type 2 changes for existing rows in the Authors dimension. Unlike a Type 1 change, existing records are not updated in a Type 2 dimension. Instead, new records are added to the dimension to contain the changes to the source system records. In this example, the values of the contract field changed in the Type 1 example are changed to different values and we now assume the contract field is to be managed as a Type 2 change.

Notice that the Transact-SQL statement used to load updated records into the staging table is the same as the one used to insert new records into the staging table except that the predicate clause in the two statements differ. When loading new records, the WHERE clause uses the auth_id field to determine which records are new. When inserting records for Type 2 changes, the WHERE clause causes new records to be added when the value of the attribute of interest (contract) in the temporary table differs from the attribute value in the staging table.

Code Example 19.5 

--Change the Authors_Temp table to simulate updates received from the source system
--This change reverses the change made in the Type 1 example by setting Contract to 1
UPDATE Authors_Temp
SET Contract = 1
WHERE state = 'UT'
GO

--For example purposes, make sure the staging table records have a different value
--for the contract field for the UT authors
UPDATE Authors_Staging
SET Contract = 0
WHERE state = 'UT'
GO

--Insert new records into the Staging Table for those records in the temp table
--that have a different value for the contract field
INSERT INTO Authors_Staging (au_id, au_lname, au_fname, phone, address, city, state, 
zip, contract)
SELECT T.au_id, T.au_lname, T.au_fname, T.phone, T.address, T.city, T.state, T.zip, 
T.contract
FROM Authors_Temp T 
LEFT OUTER JOIN Authors_Staging S ON T.au_id = S.au_id
WHERE T.Contract <> S.Contract
GO

--Insert the new records into the data warehouse Table
INSERT INTO Authors_DW (Author_Key, au_id, au_lname, au_fname, phone, address, city, 
state, zip, contract,
DateCreated, DateUpdated)
SELECT S.Author_Key, S.au_id, S.au_lname, S.au_fname, S.phone, S.address, S.city, 
S.state, S.zip, S.contract,
S.DateCreated, S.DateUpdated
FROM Authors_Staging S LEFT OUTER JOIN
Authors_DW D ON S.Author_Key = D.Author_Key
WHERE (D.Author_Key IS NULL)
GO

Managing the Fact Table

After all dimension records have been loaded and updated, the fact table also must be loaded with new data. The fact table must be loaded after the dimension tables so the surrogate keys added to the dimension records during the ETL processes can be used as foreign keys in the fact table. This section demonstrates techniques for loading the fact table.

For purposes of these examples, a table (Fact_Source) is created that simulates a data table in a source system from which fact data can be extracted. The Fact_Source table data is a combination of data found in the Sales and TitleAuthor tables in the pubs database.

The following table lists definitions of the tables created for use with the examples that follow.

Table name

Purpose

Fact_Source

A simulated source data table that will be used in the example code. This table is a combination of Sales and TitleAuthor in the pubs database.

Fact_Temp

Receives data imported from the source system.

Fact_Staging

Holds the fact table data during transformation and surrogate key operations. Data is loaded to the data warehouse fact table after ETL operations are complete.

Fact_DW

The fact table in the data warehouse.

Titles_DW

A dimension table for Titles to demonstrate the use of surrogate keys in the fact table.

Store_DW

A dimension table for Stores to demonstrate the use of surrogate keys in the fact tables.

Several key points about the structures of these tables should be noted:

  • There is no difference between the structures of the Fact_Source table and the Fact_Temp tables. This allows for the easiest method to extract data from the source system so that transformations on the data do not impact the source system.

  • The Fact_Staging table is used to add the dimension surrogate keys to the fact table records. This table is also used to validate any data changes, convert any data types, and so on. 

  • The structures of the Fact_Staging and Fact_DW tables do not match. This is because the final fact table in the data warehouse does not store the original keys—just the surrogate keys.

  • The fact table key is an identity column that is generated when the transformed data is loaded into the fact table. Since we will not be updating the records once they have been added to the Fact_DW table, there is no need to generate the key prior to the data load into the fact table. This is not how the key column is generated in dimension tables. As discussed above, the decision to use an identity key for a fact table depends on the complexity of the data warehouse schema and the performance of load and query operations; this example implements an identity key for the fact table. 

The following Transact-SQL statements create the tables defined above:

Code Example 19.6 

--Create the simulated source data table
CREATE TABLE [Fact_Source] (
[stor_id] [char] (4) NOT NULL ,
[ord_num] [varchar] (20) NOT NULL ,
[ord_date] [datetime] NOT NULL ,
[qty] [smallint] NOT NULL ,
[payterms] [varchar] (12) NOT NULL ,
[title_id] [tid] NOT NULL
) ON [PRIMARY]
GO

--Create the example temporary source data table used in the ETL database
CREATE TABLE [Fact_Temp] (
[stor_id] [char] (4) NOT NULL ,
[ord_num] [varchar] (20) NOT NULL ,
[ord_date] [datetime] NOT NULL ,
[qty] [smallint] NOT NULL ,
[payterms] [varchar] (12) NOT NULL ,
[title_id] [tid] NOT NULL
) ON [PRIMARY]
GO

--Create the example fact staging table
CREATE TABLE [Fact_Staging] (
[stor_id] [char] (4) NOT NULL ,
[ord_num] [varchar] (20) NOT NULL ,
[ord_date] [datetime] NOT NULL ,
[qty] [smallint] NOT NULL DEFAULT (0),
[payterms] [varchar] (12) NOT NULL ,
[title_id] [tid] NOT NULL,
[Store_Key] [int] NOT NULL DEFAULT (0),
[Title_Key] [int] NOT NULL DEFAULT (0)
) ON [PRIMARY]
GO

--Create the example data warehouse fact table
CREATE TABLE [Fact_DW] (
[Store_Key] [int] NOT NULL DEFAULT (0),
[Title_Key] [int] NOT NULL DEFAULT (0),
[ord_num] [varchar] (20) NOT NULL ,
[ord_date] [datetime] NOT NULL ,
[qty] [smallint] NOT NULL DEFAULT (0),
[payterms] [varchar] (12) NOT NULL ,
[Fact_Key] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED 
) ON [PRIMARY]
GO

--Create the example titles dimension table
CREATE TABLE [Titles_DW] (
[title_id] [tid] NOT NULL,
[title] [varchar] (80) NOT NULL,
[type] [char] (12) DEFAULT ('UNDECIDED'),
[pub_id] [char] (4) NULL,
[price] [money] NULL,
[advance] [money] NULL,
[royalty] [int] NULL,
[ytd_sales] [int] NULL,
[notes] [varchar] (200) NULL,
[pubdate] [datetime] NOT NULL DEFAULT (getdate()),
[Title_Key] [int] NOT NULL IDENTITY (1,1) PRIMARY KEY CLUSTERED
) ON [PRIMARY]
GO

--Create the example stores dimension table
CREATE TABLE [Stores_DW] (
[stor_id] [char] (4) NOT NULL,
[stor_name] [varchar] (40) NULL,
[stor_address] [varchar] (40) NULL,
[city] [varchar] (20) NULL,
[state] [char] (2) NULL,
[zip] [char] (5) NULL,
[Store_Key] [int] IDENTITY (1,1) PRIMARY KEY CLUSTERED
) ON [PRIMARY]
GO 

The following statements populate the sample fact and dimension tables and provide a base set of data for the remainder for the examples. The Fact_Temp and Fact_Source may appear to be redundant, but Fact_Source is only used to simulate a source table in an OLTP system.

Code Example 19.7 

--Load the simulated Fact_Source table with example data
INSERT INTO Fact_Source
SELECT S.*
FROM titleauthor TA INNER JOIN sales S ON TA.title_id = S.title_id
GO

--Load the Fact_Temp table with data from the Fact_Source table
INSERT INTO Fact_Temp
SELECT *
FROM Fact_Source
GO

--Load the example dimension for Titles
INSERT INTO Titles_DW
SELECT *
FROM Titles
GO

--Load the example dimension for Stores
INSERT INTO Stores_DW
SELECT *
FROM Stores
GO

This completes the preparation of the sample data. The remaining examples demonstrate the tasks that prepare data for loading and load it into the data warehouse fact table.

The following code loads the Fact_Staging table. Notice that the Store_Key and Title_Key columns that are used for surrogate keys contain zeros when the data is first loaded into the staging table. This is because NULLs are not allowed in these columns. The prevention of NULLs allows for a very clean data load and it negates the need to do NULL logic checks in the ETL code or the reporting system. The zeros in the column also provide an easy mechanism for locating invalid data in the dimension and fact table data. If a zero appears in either column in the final fact table, then the ETL logic failed to handle a dimension attribute. It is good practice to always add a dimension record with zero key and assign it the description of "unknown." This helps preserve relational integrity in the data warehouse and allows reporting systems to display the invalid data, so that corrections can be made to the ETL logic or the source data.

Code Example 19.8 

--Load the Fact_Staging table with data in the Fact_Temp table
INSERT INTO Fact_Staging (stor_id, ord_num, ord_date, qty, payterms, title_id, 
Store_Key, Title_Key)
SELECT stor_id, ord_num, ord_date, qty, payterms, title_id, 0, 0
FROM Fact_Temp
GO

Now that the Fact_Staging table is loaded, the surrogate keys can be updated. The techniques for updating the surrogate keys in the fact table will differ depending on whether the dimension contains Type 2 changes. The following technique can be used for Type 1 dimensions:

Code Example 19.9 

--Update the Fact_Staging table with the surrogate key for Titles 
--(Type 1 dimension)
UPDATE Fact_Staging
SET Title_Key = T.Title_Key
FROM Fact_Staging F INNER JOIN
Titles_DW T ON F.title_id = T.title_id
GO

--Update the Fact_Staging table with the surrogate key for Store
--(Type 1 dimension)
UPDATE Fact_Staging
SET Store_Key = S.Store_Key
FROM Fact_Staging F INNER JOIN
Stores_DW S ON F.Stor_id = S.Stor_id
GO

The technique above will not work for dimensions that contain Type 2 changes, however, because there may be more than one dimension record that contains the original source key. The following technique is appropriate for Type 2 dimensions:

Code Example 19.10 

--Add a few new rows to the Stores_DW table to demonstrate technique
--Duplicate Store records are added that reflect changed store names
INSERT INTO Stores_DW (stor_id, stor_name, stor_address, city, state, zip)
SELECT stor_id, 'New ' + stor_name, stor_address, city, state, zip
FROM Stores_DW
WHERE state = 'WA'
GO

--Add some new rows to fact table to demonstrate technique
INSERT INTO Fact_Staging (stor_id, ord_num, ord_date, qty, payterms, title_id, 
Store_Key, Title_Key)
SELECT stor_id, ord_num, ord_date, qty, payterms, title_id, 0, 0
FROM Fact_Temp
GO

--Update the fact table. Use the maximum store key 
--to relate the new fact data to the latest store record.
BEGIN TRANSACTION
--get the maximum store_key for each stor_id
SELECT MAX(STORE_KEY) AS Store_Key, stor_id
INTO #Stores
FROM Stores_DW
GROUP BY stor_id
ORDER BY stor_id

--update the fact table
UPDATE Fact_Staging
SET Store_Key = S.Store_Key
FROM Fact_Staging F INNER JOIN
#Stores S ON F.stor_id = S.stor_id
WHERE F.Store_Key = 0

--drop the temporary table
DROP TABLE #Stores
COMMIT TRANSACTION
GO

After the fact data has been successfully scrubbed and transformed, it needs to be loaded into the data warehouse. If the ETL database is not on the same server as the data warehouse database, then the data will need to be transferred using DTS, bcp, or another mechanism. An efficient approach is to use bcp to export the data from the ETL database, copy the data to the target server, and then use BULK INSERT to update the target database. However, if the databases are on the same server, a simple INSERT statement will load the new fact table rows:

Code Example 19.11 

--Load the new fact table rows into the data warehouse
INSERT INTO Fact_DW (ord_num, ord_date, qty, payterms, Store_Key, Title_Key)
SELECT ord_num, ord_date, qty, payterms, Store_Key, Title_Key
FROM Fact_Staging
GO

Finally, the following SELECT statement shows the data warehouse fact table, complete with Type 2 dimension for the stores dimension:

Code Example 19.12 

--Demonstrate the success of the technique
SELECT S.stor_id, S.Store_Key, S.stor_name, F.ord_num, F.ord_date, F.qty, F.payterms
FROM Stores_DW S INNER JOIN
Fact_DW F ON S.Store_Key = F.Store_Key
ORDER BY S.stor_id, S.Store_Key
GO

Advanced Techniques

While the sample techniques described above will work for small to medium-sized dimensions, they will not work for large dimensions. For large dimensions, variations of these techniques can provide greater efficiency. The code examples in this topic show some advanced techniques for Type 2 dimensions.

One of the key design decisions in the above techniques centers on the use of the staging tables. In the techniques illustrated above, the staging tables are exact copies of the final data warehouse dimension tables. However, the efficiency of the above techniques decreases as the number of rows in the dimension increase due to records added for Type 2 changes. For very large dimensions (millions of rows), the above technique will require massive amounts of processing power to complete. Therefore, for large dimensions, we need to introduce a variation of the above technique that will allow the system to scale with the data warehouse.

This variation involves creating a "current version" dimension table for use in the ETL process that contains only a single row for each of the dimension members. This record contains the current attributes of the dimension member. For example, if we have a Type 2 dimension for stores, and the data for the store Bookbeat has undergone three Type 2 changes, then the current version table would not contain all four records for the store. Instead, the table contains a single row for Bookbeat that contains all of the current information for it, including the current surrogate key value for the dimension member. This creates a smaller table with fewer rows that allows for faster access during the ETL process.

The following code incorporates a Store_Current table to demonstrate this technique for the Stores dimension. The table below describes each of the tables used in the example.

Table name

Purpose

AuthorsStores_Temp

Holds the data imported from the source system.

Stores_Staging

Holds the dimension data while it is being updated. The data for the stores will be updated in this table and then the data will be loaded into the data warehouse dimension table.

Stores_Current

Contains a single record for each store to track the current information for the store.

Stores_DW

Simulates the Stores dimension table in the data warehouse.

The following statements create the four tables:

Code Example 19.13 

DROP TABLE Stores_DW
GO

CREATE TABLE [Stores_Temp] (
[stor_id] [char] (4) NOT NULL,
[stor_name] [varchar] (40) NULL,
[stor_address] [varchar] (40) NULL,
[city] [varchar] (20) NULL,
[state] [char] (2) NULL,
[zip] [char] (5) NULL
) ON [PRIMARY]
GO

CREATE TABLE [Stores_Staging] (
[stor_id] [char] (4) NOT NULL,
[stor_name] [varchar] (40) NULL,
[stor_address] [varchar] (40) NULL,
[city] [varchar] (20) NULL,
[state] [char] (2) NULL,
[zip] [char] (5) NULL,
[DateCreated] smalldatetime NOT NULL DEFAULT (getdate()),
[DateUpdated] smalldatetime NOT NULL DEFAULT (getdate()),
[Store_Key] [int] IDENTITY (1,1) PRIMARY KEY CLUSTERED
) ON [PRIMARY]
GO

CREATE TABLE [Stores_Current] (
[stor_id] [char] (4) NOT NULL,
[stor_name] [varchar] (40) NULL,
[stor_address] [varchar] (40) NULL,
[city] [varchar] (20) NULL,
[state] [char] (2) NULL,
[zip] [char] (5) NULL,
[DateCreated] smalldatetime NOT NULL DEFAULT (getdate()),
[DateUpdated] smalldatetime NOT NULL DEFAULT (getdate()),
[Store_Key] [int] PRIMARY KEY CLUSTERED
) ON [PRIMARY]
GO

CREATE TABLE [Stores_DW] (
[stor_id] [char] (4) NOT NULL,
[stor_name] [varchar] (40) NULL,
[stor_address] [varchar] (40) NULL,
[city] [varchar] (20) NULL,
[state] [char] (2) NULL,
[zip] [char] (5) NULL,
[DateCreated] smalldatetime NOT NULL DEFAULT (getdate()),
[DateUpdated] smalldatetime NOT NULL DEFAULT (getdate()),
[Store_Key] [int] PRIMARY KEY CLUSTERED
) ON [PRIMARY]
GO

The following statements populate the Stores_Temp, Stores_Staging, and Stores_Current sample tables to provide a base set of data that will be used in the remainder of the example:

Code Example 19.14 

--Load the Stores_Temp table with the default set of data
INSERT INTO Stores_Temp
SELECT * FROM Stores
GO

--Load the Stores_Staging table with the default set of data
INSERT INTO Stores_Staging (stor_id, stor_name, stor_address, city, state, zip, 
DateCreated, DateUpdated)
SELECT stor_id, stor_name, stor_address, city, state, zip, getdate(), getdate()
FROM Stores_Temp
GO

--Load the Stores_Current table with the default set of data
INSERT INTO Stores_Current (stor_id, stor_name, stor_address, city, state, zip, 
DateCreated, DateUpdated, store_key)
SELECT stor_id, stor_name, stor_address, city, state, zip, DateCreated, DateUpdated, 
store_key
FROM Stores_Staging
GO

The following code adds some new records into the Stores_Staging table to simulate Type 2 changes to the Stores dimension. The new records reflect changes to existing store data; no new store records are added.

Code Example 19.15 

--Insert some change records into Store_Staging to demonstrate the technique
--Duplicate records are added that reflect changes to store names for some stores
INSERT INTO Stores_Staging (stor_id, stor_name, stor_address, city, state, zip)
SELECT stor_id, stor_name + ' New', stor_address, city, state, zip
FROM Stores_staging
WHERE state <> 'ca'
GO

Records for new stores are loaded into Stores_Current before starting to process stores with change records. The following Transact-SQL code loads new stores in the Stores_Staging table into the Stores_Current table. This technique is the same as the one documented earlier in the chapter.

Code Example 19.16 

--Insert any new stores in Stores_Staging into the Stores_Current table
--In this example there should not be any new stores
INSERT INTO Stores_Current (stor_id, stor_name, stor_address, city, state, zip, 
DateCreated, DateUpdated)
SELECT S.stor_id, S.stor_name, S.stor_address, S.city, S.state, S.zip, S.DateCreated, 
S.DateUpdated
FROM Stores_Staging S LEFT OUTER JOIN Stores_Current C ON S.stor_id = C.stor_id
WHERE (c.Store_Key IS NULL)
GO

The real change in this technique involves changing the way that dimension members are updated. The following Transact-SQL code demonstrates the alternative way to update the dimension members and load them into the data warehouse dimension. Once the new members of the dimension have been loaded, the next step is to check existing members for attributes changes that require Type 2 changes to the dimension. This example checks the stor_name attribute and updates the row in the Stores_Current table for every store that has had a name change (in this example, all stores that do not exist in CA).

Code Example 19.17 

--Update Store_Current table for all stores that have had a name change
UPDATE Stores_Current
SET stor_name = S.stor_name,
Store_key = S.Store_Key,
DateUpdated = getdate()
FROM Stores_Staging S
INNER JOIN Stores_Current C ON S.stor_id = C.stor_id
WHERE S.stor_name <> C.stor_name
GO

Now that all of the dimension records have been updated with the latest data, the surrogate keys can be updated for the fact table data with the following Transact-SQL statement. This technique is more efficient because a temporary table does not have to be created to determine the current value of the dimension table key.

Code Example 19.18 

--generate some fact data rows that do not have a store_key
INSERT INTO Fact_Staging (stor_id, ord_num, ord_date, qty, payterms, title_id, 
store_key, title_key)
SELECT stor_id, ord_num, getdate(), qty, payterms, title_id, 0, 0
FROM Fact_Staging
WHERE QTY < 20
GO

--Update the fact data using the Store_Key key from the Store_Current table
--to relate the new fact data to the latest store record
UPDATE Fact_Staging
SET Store_Key = C.Store_Key
FROM Fact_Staging F INNER JOIN
Stores_Current C ON F.stor_id = C.stor_id
WHERE F.Store_Key = 0
GO

Meta Data Logging

A critical design element in successful ETL implementation is the capability to generate, store and review meta data. Data tables in a data warehouse store information about customers, items purchased, dates of purchase, and so on. Meta data tables store information about users, query execution times, number of rows retrieved in a report, etc. In ETL systems, meta data tables store information about transformation execution time, number of rows processed by a transformation, the last date and time a table was updated, failure of a transformation to complete, and so on. This information, if analyzed appropriately, can help predict what is likely to occur in future transformations by analyzing trends of what has already occurred.

In the code examples that follow, the terms "Job" and "Step" are used with the following meanings:

  • A "Job" is an ETL element that is either executed manually or as a scheduled event. A Job contains one or more steps. 

  • A "Step" is an individual unit of work in a job such as an INSERT, UPDATE, or DELETE operation. 

  • A "Threshold" is a range of values defined by a minimum value and a maximum value. Any value that falls within the specified range is deemed acceptable. Any value that does not fall within the range is unacceptable. For example, a processing window is a type of threshold. If a job completes within the time allotted for the processing window, then it is acceptable. If it does not, then it is not acceptable. 

Designing meta data storage requires careful planning and implementation. There are dependencies between tables and order of precedence constraints on records. However, the meta data information generated by ETL activities is critical to the success of the data warehouse. Following is a sample set of tables that can be used to track meta data for ETL activities.

Job Audit

ETL jobs produce data points that need to be collected. Most of these data points are aggregates of the data collected for the job steps and could theoretically be derived by querying the job step audit table. However, the meta data for the job itself is important enough to warrant storage in a separate table. Below are sample meta data tables that aid in tracking job information for each step in an ETL process.

tblAdmin_Job_Master 

This table lists all of the jobs that are used to populate the data warehouse. These are the fields in tblAdmin_Job_Master:

Field

Definition

JobNumber

A unique identifier for the record, generally an identity column.

JobName

The name (description) for the job. For example, "Load new dimension data."

MinThreshRecords

The minimum acceptable number of records affected by the job.

MaxThreshRecords

The maximum acceptable number of records affected by the job.

MinThreshTime

The minimum acceptable execution time for the job.

MaxThreshTime

The maximum acceptable execution time for the job.

CreateDate

The date and time the record was created.

tblAdmin_Audit_Jobs 

This table is used to track each specific execution of a job. It is related to the tblAdmin_Job_Master table using the JobNumber column. These are the fields in tblAdmin_Audit_Jobs:

Field

Definition

JobNumber

A unique identifier for the record, generally an identity column.

JobName

The name (description) for the job. For example, "Load new dimension data."

StartDate

The date and time the job was started.

EndDate

The date and time the job ended.

NumberRecords

The number of records affected by the job.

Successful

A flag-indicating if the execution of the job was successful.

This data definition language will generate the above audit tables:

Code Example 19.19 

CREATE TABLE [dbo].[tblAdmin_Job_Master] (
[JobNumber] [int] IDENTITY (1, 1) NOT NULL 
CONSTRAINT UPKCL_Job PRIMARY KEY CLUSTERED,
[JobName] [varchar] (50) NULL DEFAULT ('Missing'),
[MinThreshRecords] [int] NOT NULL DEFAULT (0),
[MaxThreshRecords] [int] NOT NULL DEFAULT (0),
[MinThreshTime] [int] NOT NULL DEFAULT (0),
[MaxThreshTime] [int] NOT NULL DEFAULT (0)
GO

CREATE TABLE [dbo].[tblAdmin_Audit_Jobs] (
[JobNumber] [int] IDENTITY (1, 1) NOT NULL 
CONSTRAINT UPKCL_Job PRIMARY KEY CLUSTERED,
[JobName] [varchar] (50) NULL DEFAULT ('Missing'),
[StartDate] [smalldatetime] NOT NULL DEFAULT (getdate()),
[EndDate] [smalldatetime] NOT NULL DEFAULT ('01/01/1900'),
[NumberRecords] [int] NOT NULL DEFAULT (0),
[Successful] [bit] NOT NULL DEFAULT (0),
GO
Step Audit

Many ETL jobs are multi-step, complicated transformations that involve INSERT, UPDATE, and DELETE statements with branching logic and an execution dependency. It is important to record meta data that tracks the successful completion of an operation, when it happened and how many rows it processed. This information should be stored for every step in an ETL job. Below are sample meta data tables that aid in tracking information for each step in an ETL job.

tblAdmin_Step_Master 

This table lists all of the steps in a job. These are the fields in tblAdmin_Step_Master:

Field

Definition

JobNumber

The unique number of the job that this step is associated with.

StepSeqNumber

The step number within the object that executed the unit of work. Frequently, ETL jobs contain more than a single unit of work and storing the step number allows for easy debugging and specific reporting. If the object only has a single step, then the value of this field is "1".

StepDescription

A description of the step. For example, "Inserted records into tblA."

Object

The name of the object. For example, the name of a stored procedure or DTS package that accomplishes the step.

NumberRecords

The number of records affected by the step.

MinThreshRecords

The minimum "acceptable" number of records affected by the step.

MaxThreshRecords

The maximum "acceptable" number of records affected by the step.

MinThreshTime

The minimum "acceptable" execution time for the step.

MaxThreshTime

The maximum "acceptable" execution time for the step.

CreateDate

The date and time the record was created.

StepNumber

A unique value assigned to the record, generally an identity column.

tbl_Admin_Audit_Step 

This table is used to track each specific execution of a job step. It is related to the tblAdmin_Step_Master table using the StepNumber column. These are the fields in tblAdmin_Audit_Step:

Field

Definition

RecordID

A unique value assigned to the record, generally an identity column.

JobAuditID

Used to tie the specific execution on a job step to the specific execution of a job.

StepNumber

The step number executed.

Parameters

Any parameters sent to the job step for the specific execution instance. These are the parameter values, not a list of the parameters.

NumberRecords

The number of records affected by the job step.

StartDate

The date and time the job step was started.

EndDate

The date and time the job step ended.

UserName

The name of the user that executed the job step.

Below is the data definition language that will generate the job step audit tables above.

Code Example 19.20 

CREATE TABLE [dbo].[tblAdmin_Step_Master] (
[JobNumber] [int] NOT NULL DEFAULT (1),
[StepSeqNumber] [int] NOT NULL DEFAULT (1),
[StepDescription] [varchar] NOT NULL DEFAULT ('Missing'),
[Object] [varchar] (50) NULL DEFAULT ('Missing'),
[MinThreshRecords] [int] NOT NULL DEFAULT (0),
[MaxThreshRecords] [int] NOT NULL DEFAULT (0),
[MinThreshTime] [int] NOT NULL DEFAULT (0),
[MaxThreshTime] [int] NOT NULL DEFAULT (0),
[StartDate] [smalldatetime] NOT NULL DEFAULT (getdate()),
[StepNumber] [int] IDENTITY (1, 1) NOT NULL 
CONSTRAINT UPKCL_PrimaryKey PRIMARY KEY CLUSTERED) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblAdmin_Audit_Step] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL 
CONSTRAINT UPKCL_PrimaryKey PRIMARY KEY CLUSTERED,
[Object] [varchar] (50) NULL DEFAULT ('Missing'),
[StepNumber] [tinyint] NOT NULL DEFAULT (1),
[StepDescription] [varchar] NOT NULL DEFAULT ('Missing'),
[Parameters] [varchar] (100) NULL,
[NumberRecords] [int] NOT NULL DEFAULT (1),
[StartDate] [smalldatetime] NOT NULL DEFAULT (getdate()),
[EndDate] [smalldatetime] NOT NULL DEFAULT ('01/01/1900'),
[UserName] [varchar] (20) NOT NULL DEFAULT ('Missing')) ON [PRIMARY]
GO
Error Tracking

Another important type of meta data about transformations is information that tracks what failed and why. ETL jobs produce errors. Just as tracking successful execution is important, tracking failures is equally important. Below is a sample meta data table that aids in tracking error information for each step in an ETL job. This table is designed to track SQL Server 2000 errors, although it could be modified to track OLE DB errors as well.

Note In SQL Server 2000, only the error number can be trapped, not the generated error message.

tblAdmin_Audit_Errors 

This table lists all of the errors that are generated during a job step. These are the fields in tblAdmin_Audit_Errors:

Field

Definition

RecordID

A unique value assigned to the record, generally an identity column.

StepNumber

The step number executed that generated the error.

Parameters

Any parameters sent to the job step for the specific execution instance. These are the parameter values, not a list of the parameters.

ErrorNumber

The error number raised by SQL Server 2000 (generally the @@ERROR number).

RecordCount

The number of records affected by the job step.

UserName

The name of the user that executed the job step.

EndDate

The date and time the job step ended.

Code Sample: Job Audit

The following stored procedures demonstrate one method of logging job level meta data. The usp_Admin_Audit_Job_Start procedure indicates the start of an ETL job and should be the very first stored procedure executed in the ETL job:

Code Example 19.21 

ALTER PROCEDURE usp_Admin_Audit_Job_Start
@JobNumber int = 1 --The number of the job being executed (from the mast job table)
AS
SET NOCOUNT ON --SET NoCount ON

--DECLARE variables
DECLARE @ErrorNumber int --the number of the SQL error generated
DECLARE @ErrorRowCount int --the number of rows in the unit of work affected by 
error
DECLARE @Startdate smalldatetime --the datetime the load job started
DECLARE @EndDate smalldatetime --the datetime the load job ended

--INSERT the first record (start time) for the job into the tblAdmin_Audit_Jobs table
BEGIN TRANSACTION
SET @StartDate = getdate() --set a start date for the batch
SET @EndDate = '01/01/1900' --set a bogus endate for the batch
insert into tblAdmin_Audit_Jobs (JobNumber, StartDate, EndDate, NumberRecords, 
Successful)
values (@JobNumber, @StartDate, @EndDate, 0, 0)

SELECT @ErrorNumber = @@error, @ErrorRowCount = @@rowcount

If @ErrorNumber <> 0 
BEGIN
ROLLBACK TRANSACTION
GOTO Err_Handler
END
COMMIT TRANSACTION
RETURN (0)

Err_Handler:
exec usp_AdminError @@ProcID, 'none', @ErrorNumber, @ErrorRowCount
RETURN (1)
GO

The following stored procedure indicates the end of an ETL job and should be the last stored procedure executed in the ETL job. It is important to note that in addition to updating the tblAdmin_Audit_Jobs table, this stored procedure also updates the tblAdmin_Audit_Step table with the threshold information for each step. The threshold information is stored with each step in the table because over time, the acceptable thresholds for the step may change. If the threshold information is only stored in the master step table (a Type 1 dimension), any changes to the table affect meta data generated for historical steps.

Therefore, storing the threshold with the step (a Type 2 dimension) allows us to maintain historical execution records without affecting their integrity if the master step information is changed. For example, if a step initially loads 1,000 rows but over time the number of rows increases to 1 million, the acceptable threshold information for that step must be changed as well. If the threshold data is stored only in the tblAdmin_Step_Master table and not stored with each record, the context of the data will be lost, which can cause inaccuracies in reports built on the meta data information. For simplicity, to illustrate the technique, the sample code does not maintain threshold information automatically. In order to change the threshold information for a step, an administrator will need to modify the master step record manually. However, it would be possible to automate this process.

Code Example 19.22 

CREATE PROCEDURE usp_Admin_Audit_Job_End
@JobNumber int = 1, --The number of the job (from the master job table) being 
executed
@Successful bit --A flag indicating if the job was successful
AS

SET NOCOUNT ON --SET NoCount ON

--DECLARE variables
DECLARE @ErrorNumber int --the number of the SQL error generated
DECLARE @ErrorRowCount int --the number of rows in the unit of work affected by 
error
DECLARE @Startdate smalldatetime --the datetime the load job started
DECLARE @EndDate smalldatetime --the datetime the load job ended
DECLARE @JobAuditID int --the # for the instance of the job
DECLARE @RowCount int --the number of rows affected by the job

--UPDATE the job record (end time) in the Audit table
BEGIN TRANSACTION
SET @EndDate = getdate() --set the end date for the batch
SET @JobAuditID = (SELECT MAX(JobAuditID) FROM tblAdmin_Audit_Jobs 
where JobNumber = @JobNumber) --get the job number
SET @RowCount = (SELECT SUM(NumberRecords) --get the total job record count
FROM tblAdmin_Audit_Step WHERE JobAuditID = @JobAuditID)

UPDATE tblAdmin_Audit_Jobs --Update the Job record with the end time
SET EndDate = @EndDate, 
NumberRecords = @RowCount,
Successful = 1
WHERE JobAuditID = @JobAuditID

SELECT @ErrorNumber = @@error, @ErrorRowCount = @@rowcount
UPDATE tblAdmin_Audit_Step --Update all steps for the job with the 
SET MinRecords = T.MinThreshRecords, --threshold information for each step
MaxRecords = T.MaxThreshRecords,
MinTime = T.MinThreshTime,
MaxTime = T.MaxThreshTime,
TimeTarget = CASE 
WHEN DATEDIFF(mi, A.StartDate, A.EndDate) BETWEEN T.MinThreshTime AND T.MaxThreshTime 
THEN 'On'
WHEN DATEDIFF(mi, A.StartDate, A.EndDate)< T.MinThreshTime THEN 'Under'
WHEN DATEDIFF(mi, A.StartDate, A.EndDate) > T.MaxThreshTime THEN 'Over'
ELSE 'Unknown'
END,
RecordTarget = CASE 
WHEN A.NumberRecords BETWEEN T.MinThreshRecords AND T.MaxThreshRecords THEN 'On'
WHEN A.NumberRecords < T.MinThreshRecords THEN 'Under'
WHEN A.NumberRecords > T.MaxThreshRecords THEN 'Over'
ELSE 'Unknown'
END
FROM tblAdmin_Step_Master T
RIGHT OUTER JOIN tblAdmin_Audit_Step A ON T.StepNumber = A.StepNumber
WHERE A.JobAuditID = @JobAuditID
SELECT @ErrorNumber = @@error, @ErrorRowCount = @@rowcount
If @ErrorNumber <> 0 
BEGIN
ROLLBACK TRANSACTION
GOTO Err_Handler
END
COMMIT TRANSACTION
RETURN (0)
Err_Handler:
exec usp_AdminError @@ProcID, 'none', @ErrorNumber, @ErrorRowCount
RETURN (1)
GO
Code Sample: Step Audit

The following stored procedures demonstrate one method of logging step records from within ETL stored procedures. Notice that the @@ProcID is used to retrieve the object id of the executing stored procedure. Also note that the values of @@error and @@rowcount are retrieved immediately after the INSERT statement.

Code Example 19.23 

ALTER PROCEDURE usp_Admin_Audit_Step
@StepNumber tinyint = 0, --the uniue number of the step
@Parameters varchar(50) = 'none', --any parameters used in the SP
@RecordCount int = 0, --the number of records modified by the step
@StartDate smalldatetime, --the date & time the step started
@EndDate smalldatetime --the date & time the step ended
AS
SET NOCOUNT ON --SET NoCount ON

--DECLARE variables
DECLARE @ErrorNumber int
DECLARE @ErrorRowCount int
DECLARE @JobAuditID int

BEGIN TRANSACTION --INSERT the audit record into the tblAdmin_Audit_Step table
SET @JobAuditID = (SELECT MAX(JobAuditID) FROM tblAdmin_Audit_Jobs)

INSERT INTO tblAdmin_Audit_Step ( JobAuditID, StepNumber, Parameters, NumberRecords, 
StartDate, EndDate, Username)
VALUES (@JobAuditID, @StepNumber, @Parameters, @RecordCount, 
@StartDate, @EndDate, user_name())

SELECT @ErrorNumber = @@error, @ErrorRowCount = @@RowCount

If @ErrorNumber <> 0 
BEGIN
ROLLBACK TRANSACTION
GOTO Err_Handler
END
COMMIT TRANSACTION
RETURN (0)

Err_Handler:
exec usp_Admin_Log_Error @@ProcID, 1, 'none', @ErrorNumber, @ErrorRowCount
RETURN (1)
GO

The following stored procedure demonstrates the use of the auditing stored procedure detailed above:

Code Example 19.24 

CREATE PROCEDURE usp_AuditSample
AS
SET NOCOUNT ON --SET NoCount ON

--DECLARE variables
DECLARE @ErrorNumber int
DECLARE @RecordCount int
DECLARE @StartDate smalldatetime
DECLARE @EndDate smalldatetime

BEGIN TRANSACTION
SET @StartDate = getdate() --get the datetime the step started
insert into tblTest
select * from tblTest

SELECT @ErrorNumber = @@error, @RecordCount = @@rowcount

If @ErrorNumber <> 0 --error handler
BEGIN
ROLLBACK TRANSACTION
GOTO Err_Handler
END

SET @EndDate = getdate() --get the datetime the step finished

--log the audit record into the tblAdmin_Audit_Step table
exec usp_Admin_Audit_Step 1 , 'test from SP', @RecordCount, @StartDate, @EndDate
COMMIT TRANSACTION
RETURN (0)

Err_Handler:
exec usp_Admin_Log_Error @@ProcID, 'none', @ErrorNumber, @RecordCount
RETURN (1)
GO
Code Sample: Error Tracking

The following stored procedures demonstrate one possible method of logging errors in ETL stored procedures. Notice that the stored procedure uses the OBJECT_NAME function to retrieve the name of the object (table, view, stored procedure, and so on). This introduces a level of abstraction so that the code is only useful for stored procedures.

Code Example 19.25 

CREATE PROCEDURE usp_Admin_Log_Error
@ObjectID int,
@StepNumber int,
@Parameters varchar(50) = 'none',
@ErrorNumber int = 0,
@RecordCount int = 0
AS

--SET NoCount ON
SET NOCOUNT ON

--RETRIEVE the NAME of the object being audited
DECLARE @ObjectName varchar(50)
SET @ObjectName = OBJECT_NAME(@ObjectID)

--INSERT the audit record into the tblAdmin_Audit_Errors table
BEGIN TRANSACTION
insert into tblAdmin_Audit_Errors (Object, StepNumber, Parameters, ErrorNumber, 
RecordCount, UserName, RecordDate)
values (@ObjectName, @StepNumber, @Parameters, @ErrorNumber, @RecordCount, 
user_name(), getdate())
COMMIT TRANSACTION
GO

Once an error is generated and passed to the error logging stored procedure, it is logged to the tblAdmin_Audit_Errors table. Notice that the @@ProcID is used to retrieve the objected of the executing stored procedure. Also note that the values of @@error and @@rowcount are retrieved immediately after the INSERT statement. With the exception of modifying the value of @Step, this logic may be deployed with no other alterations to the code. The following stored procedure demonstrates how to deploy the error logging method detailed above:

Code Example 19.26 

CREATE PROCEDURE usp_ErrorSample
AS
--SET NoCount ON
SET NOCOUNT ON

--DECLARE Variables
DECLARE @ObjectName varchar(50)
DECLARE @ErrorNumber int, @RecordCount int
DECLARE @Step int

--INSERT the audit record into the Authors table
BEGIN TRANSACTION
insert into Authors
Select * from authors

SELECT @ErrorNumber = @@error, @RecordCount = @@rowcount, @Step = 2

If @ErrorNumber <> 0 
BEGIN
ROLLBACK TRANSACTION
GOTO Err_Handler
END
COMMIT TRANSACTION
RETURN (0)

Err_Handler:
exec usp_Admin_Log_Error @@ProcID, @Step, 'none', @ErrorNumber, @RecordCount
RETURN (1)
GO

Conclusion

The ETL system efficiently extracts data from its sources, transforms and sometimes aggregates data to match the target data warehouse schema, and loads the transformed data into the data warehouse database. A well-designed ETL system supports automated operation that informs operators of errors with the appropriate level of warning. SQL Server 2000 Data Transformation 1048527521Services can be used to manage the ETL operations, regardless of the techniques used to implement individual ETL tasks.

While it is tempting to perform some transformation on data as it is extracted from the source system, the best practice is to isolate transformations within the transformation modules. In general, the data extraction code should be designed to minimize the impact on the source system databases.

In most applications, the key to efficient transformation is to use a SQL Server 2000 database for staging. Once extracted data has been loaded into a staging database, the powerful SQL Server 2000 database engine is used to perform complex transformations.

The process of loading fact table data from the staging area into the target data warehouse should use bulk load techniques. Dimension table data is usually small in volume, which makes bulk loading less important for dimension table loading.

The ETL system is a primary source of meta data that can be used to track information about the operation and performance of the data warehouse as well as the ETL processes.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft