PART III
CHAPTER 7
“By failing to prepare, you are preparing to fail.”
Benjamin Franklin
If you have been reading this book in sequence, you now have a detailed model of the challenges you face building your ETL system. We have described the data structures you need (Chapter 2), the range of sources you must connect to (Chapter 3), a comprehensive architecture for cleaning and conforming the data (Chapter 4), and all the target dimension tables and fact tables that constitute your final delivery (Chapters 5 and 6). We certainly hope that you can pick and choose a subset of all this for your ETL system!
Hopefully, you are at the point where you can draw a process-flow diagram for your proposed ETL system that clearly identifies at a reasonable level of detail the extracting, cleaning, conforming, and delivering modules.
Now it’s time to decide what your ETL system development platform is and how to go about the development. If you have the luxury of starting fresh, you have a big fork in the road: Either purchase a professional ETL tool suite, or plan on rolling your own with a combination of programming and scripting languages. We tried to give you an even handed assessment of this choice in Chapter 1. Maybe you should go back and read that again.
PROCESS CHECK Planning & Design:
Requirements/Realities → Architecture → Implementation → Test/Release
Data Flow : Extract → Clean → Conform → Deliver
In the next section, we give you a brief listing of the main ETL tool suites, data-proofing systems, data-cleansing systems, and scripting languages available as of this writing, but in writing a book intended to have a useful shelf life of several years, please understand that we intend this only as a general guide. We invite you to perform an Internet search for each of these vendors and scripting languages to get their latest offerings.
The first half of this chapter is a spirited and we hope entertaining tour through a number of basic low-level transforms you must implement. We have chosen to illustrate these with simple UNIX utilities like ftp, sort, gawk, and grep, keeping in mind that the professional ETL tool suites would have proprietary data-flow modules that would replace these examples.
The second half of this chapter focuses on DBMS specific techniques for performing high-speed bulk loads, enforcing referential integrity, taking advantage of parallelization, and troubleshooting performance problems.
Current Marketplace ETL Tool Suite Offerings
In Chapter 1, we discussed the pros and cons of purchasing a vendor’s ETL tool suite or rolling your own ETL system with hand-coding. From the data warehouse point of view, the ETL marketplace has three categories: mainline ETL tool, data profiling, and data cleansing.
In alphabetical order, the main ETL tool suite vendors as of this writing, with product names where the company has products in other categories, are:
Ab Initio
Ascential DataStage
BusinessObjects Data Integrator
Cognos DecisionStream
Computer Associates Advantage Data Transformation
CrossAccess eXadas
Data Junction Integration Studio (acquired by Pervasive)
DataHabitat ZeroCode ETL
DataMirror Transformation Server
Embarcadero DT/Studio
ETI (Evolutionary Technologies International)
Hummingbird ETL
IBM DB2 Data Warehouse Manager
Informatica (PowerCenter and SuperGlue)
Information Builders iWay
Mercator Inside Integrator (acquired by Ascential)
Microsoft SQL Server DTS (Data Transformation Services)
Oracle9i Warehouse Builder
Sagent Data Flow Server (acquired by Group 1)
SAS Enterprise ETL Server
Sunopsis
Most of the names in these lists are copyright, their owners. The main data-profiling vendors at the time of this writing are:
Ascential (ProfileStage)
Evoke Software
SAS
Trillium/Harte Hanks (with the Avelino acquisition)
The main data cleansing vendors at the time of this writing are:
Ascential (acquisition of Vality)
First Logic
Group 1
SAS DataFlux
Search Software America
Trillium (acquired Harte Hanks)
If you perform an Internet search for each of these products, you will get a wealth of information and their current statuses.
ETL tool suites typically package their functionality as a set of transforms. Each performs a specific data manipulation. The inputs and outputs of these transforms are compatible so that the transforms can easily be strung together, usually with a graphical interface. Typical categories of transforms that come built with dozens of examples in each category include:
Aggregators
General expressions
Filters
Joiners
Lookups
Normalizers
Rankers
Sequence generators
Sorters
Source readers (adapters)
Stored procedures
Updaters
XML inputers and outputers
Extensive facilities for writing your own transforms in a variety of languages
Current Scripting Languages
Interesting scripting languages available on a variety of platforms (typically UNIX, Linux, Windows, and, in some cases, IBM mainframes) include:
JavaScript
Perl
PHP
Python
Tcl
All of these scripting languages excel at reading and writing text files and invoking sort routines including native OS sorting packages as well as commercial packages like SyncSort and CoSort. Several have good interfaces to commercial DBMSs as well.
Of course, one can always drop down to C or C++ and do anything. While all the ETL alternatives eventually allow escapes into C or C++, it would be unusual to build the entire ETL system at such a low level.
Time Is of the Essence
PROCESS CHECK] Planning & Design:
Requirements/Realities → Architecture → Implementation → Test/Release
Data Flow: Extract → Clean → Conform → Deliver
Throughout the ETL system, time or, more precisely, throughput, is the primary concern. Mainly, this translates to devising processing tasks that ultimately enable the fastest loading of data into the presentation tables and then the fastest end user response times from those tables. Occasionally, throughput rears its head when cleaning up unwieldy or dirty data.
Push Me or Pull Me
In every data warehouse, there inevitably is data that originates from flat-file systems. The first step to incorporating this data into the data warehouse is moving it from its host server to the ETL server. Flat files can either be pushed from the source host systems or pulled by the ETL server.
Which approach works best? The honest answer to this question is, well, both. However, the more important question to ask is when?—as in “When is the source file available to be moved?”
In many cases, the source files that must be moved are from operational business systems, and the files are often not available to be moved to the ETL server until after the operational systems nightly batch processes are completed. If the ETL server attempts to pull the file, it risks attempting to start the file transfer before the file is ready, in which case the data loaded into the warehouse might be incorrect or incomplete. In these situations, having the host system push the source files has the following advantages:
The FTP step to push the source file can be embedded into the operational system’s batch process so that the file is pushed as soon as it is prepared by the host system, thereby starting the ETL process at the earliest possible time and minimizing idle time during the load window.
Errors in the process of preparing the source file can prevent the file transfer from being initiated, thereby preventing incorrect or incomplete data from being loaded into the data warehouse.
The ETL server must have an FTP host service running in order to support pushing source files from the host systems.
In many cases, an interrupted FTP process must be restarted. The larger the download file and the tighter the batch window, the riskier relying on simple FTP becomes. If this is important to you, you should try to find a resumable FTP utility and/or verify the capabilities of your ETL tool suite to resume an interrupted transfer. Also, when looking at these added-value, higher-end, FTP-like capabilities, you may be able to get compression and encryption at the same time.
It is equally likely that some of the files needed by the ETL process are available at any time the ETL process needs them. In these cases, the ETL server can pull the files when it needs them. The ETL server must establish an FTP connection to the host file server. Running FTP from a Unix shell script or Windows batch file is quite simple. On both platforms, the file-transfer commands can be passed to FTP via an external command file, as in the following example.
In the following pages, we describe many low-level data-manipulation commands including sorting, extracting subsets, invoking bulk loaders, and creating aggregates. For clarity, we show command-line versions of each of these commands. Obviously, in a commercial ETL tool suite, all of these commands would be invoked graphically by clicking with the mouse. Keep that in mind!
You can embed the following command in a Windows batch file:
ftp -n -v -s:transfer.ftp
-n options turns off login prompting
-v turns off remote messages
-s: specifies the command file, In this case “transfer.ftp”
The content of command file transfer.ftp might be something like:
open hostname
user userid password
cd /data/source
lcd /etl/source ascii
get source_1.dat
get source_2.dat
get source_3.dat
… … …
get source_n.dat
bye
On a UNIX system, the commands are the same, but the syntax for passing a command file is slightly different.
Ensuring Transfers with Sentinels
Whether you are pushing or pulling your flat-file sources, you need to be sure that your file transfer completes without any errors. Processing a partially transferred file can lead to corrupt or incomplete data being loaded into the data warehouse.
An easy way to ensure your transfers are complete is to use sentinel (or signal) files. The sentinel file has no meaningful content, but its mere existence signifies the readiness of the file(s) to which it relates.
In the push approach, a sentinel file is sent after the last true source file is pushed. When the ETL receives the sentinel file, it signifies that all of the source files have been completely received and that the ETL process can now safely use the source files. If the transfer of the source files is interrupted, the sentinel file is not sent, and the ETL process suspends until the error is corrected and the source files are resent.
Sentinel files can also be used in a pull environment. In this case, the source host sends a sentinel file only to notify the ETL server that the source files are available. Once the ETL server receives the sentinel, it can initiate the FTP process to pull the source files to begin the ETL process.
Either way, the ETL process must include a method to poll the local file system to check for the existence of the sentinel file. Most dedicated ETL tools include this capability. If you are manually developing the ETL process, a Windows NT/2000 server scheduled task or Unix cron job can accomplish this task.
Sorting Data during Preload
PROCESS CHECK Planning & Design:
Requirements/Realities → Architecture → Implementation → Test/Release
Data Flow: Extract → Clean → Conform → Deliver
Certain common ETL processes call for source data to be sorted in a particular order to achieve the desired outcome. Such ETL processes as aggregating and joining flat-file sources require the data to be presorted. Some ETL tools can handle these tasks in memory; however, aggregating or joining unsorted data is significantly more resource-intensive and time-consuming than doing so on sorted data.
When the source data is contained in a database, sorting is easily accomplished by including an order by clause in the SQL that retrieves the data from the database. But if the source data is from flat files, you need to use a sort utility program to arrange the data in the correct order prior to the ETL process.
Sorting on Mainframe Systems
Every mainframe system includes either IBM’s DFSORT or, SyncSort’s SORT utility program. Syncsort and DFSORT commands are virtually identical and are quite simple. With few exceptions, mainframe data files are formatted in fixed widths, so most sorts are accomplished by simply specifying the positions and lengths of the data elements on which the data are to be sorted. We use the sample sales file that follows to show how mainframe sorts are accomplished.
The COBOL copybook for this file would be:
The basic structure of the SORT command is:
SORT FIELDS=(st,len,dt,ad)
where st denotes the starting position, len denotes the length, dt denotes the data type, and ad denotes the sort order (ascending or descending). So, sorting our sales file by customer-id is coded as follows:
SORT FIELDS=(9,3,BI,A)
meaning, sort on positions 9 to 11 in ascending order, treating the data as binary. To perform sorts on multiple fields, simply supply the st,len,dt,ad parameters for each additional sort field.
For example, suppose your ETL task is to aggregate this sale data by year, product, and customer. The source data is at a daily grain, and its natural order is also by day. Aggregating data from its natural order would be a quite complex task, requiring creating, managing, and navigating arrays of memory variables to hold the aggregates until the last input record is processed and again navigating the memory arrays to load the aggregates into the warehouse. But by presorting the source data by the aggregate key (year + product-id + customer-id), the ETL task to aggregate the data becomes fairly simple. The command for sorting the data by the aggregate key is as follows:
SORT FIELDS=(1,4,BI,A,39,5,BI,A,9,3,BI,A)
Once sorted in this way, the ETL process to aggregate the data can be made extremely efficient. As source records are read, the values of the key fields year, product-id, and customer-id are compared to the key values of the preceding record, which are held in memory variables. As long as the keys are the same, the units and sales amounts are added to cumulative memory variables. When the keys change, the aggregate values for the preceding key are loaded to the warehouse from the memory variables, and the memory variables are reset to begin accumulating the aggregates for the new keys.
As discussed in earlier chapters, mainframe data often poses certain challenges unique to mainframes. The SORT utility has a rich set of data types to help you handle these challenges. While using BI (binary) as the data type works in many situations, there are a number of alternate data types that handle special situations, including those listed in Table 7.1.
Table 7.1 Alternate Mainframe Data Types
|
DATA TYPE |
USAGE |
|
PD |
Use to properly sort numeric values stored in packed decimal (or COMP-3) format. |
|
ZD |
Use to properly sort numeric values stored in zoned decimal format. |
|
AC |
Use to properly sort data by the ASCII codes associated with the data, rather than by the mainframe native EBCDIC codes. Use this format for mixed (alphanumeric) fields when the data is transferred from a mainframe to an ETL process on a Unix or Windows system. |
|
dates |
Believe it or not, you will likely encounter legacy system files with dates still in pre-Y2K formats (that is, without explicit centuries). SORT has a rich set of data types for handling such dates and assigning them to the proper century. |
This table represents just a small subset of the available data types. Many others are available for the multitude of numeric and other data formats you might encounter.
You can also mix data formats on a compound index. So, for example, sorting sales file by year and descending unit-cost uses the following command:
SORT FIELDS = (1,4,BI,A,72,4,PD,D)
Sorting on Unix and Windows Systems
Flat files on Unix and Windows systems, which are ASCII character-based, are not plagued by the antiquated data formats (packed-decimal, and so on) concocted on the mainframe systems of old to save disk space. But these systems present challenges of their own.
Among the most common sorting challenge you’ll face is sorting delimited or other unstructured data files. In this context, unstructured refers to the fact that the data is not arranged in neat columns of equal width on every record. As such, unlike the mainframe examples, you can’t specify the sort keys positionally.
Instead, the sort utility must be able to parse the records using the delimiters. (Of course, the mainframe utilities SyncSort and CoSort are available on Unix and Windows platforms, too.) The following extract shows the same sales data used earlier in the chapter now formatted as a comma delimited file.
04/05/2000,026,Discount Electronics, 00014,SUBWOOFER,124.74,15,198.00,2970.00
04/06/2000,005,City Electronics,00008,AMPLIFIER,164.43,35,261.00,9135.00
04/07/2000,029,USA Audio and Video,00017,KARAOKE MACHINE, 55.57,20,88.20,1764.00
04/10/2000,010,Computer Audio and Video,00017,KARAOKE MACHINE, 55.57,10,88.20,882.00, 04/11/2000,002,Computer Audio and Video,00017,KARAOKE MACHINE, 55.57,35,88.20,3087.00, 04/11/2000,011,Computer Audio and Video,00008,AMPLIFIER,164.43,10,261.00,2610.00
04/15/2000,019,Computer Discount,00018,CASSETTE PLAYER/RECORDER,43.09,20,68.40,1368.00
04/18/2000,013,Wolfe’’s Discount, 00014,SUBWOOFER,124.74,25,198.00,4950.00
04/18/2000,022,USA Audio and Video,00008,AMPLIFIER,164.43,15,261.00,3915.00
04/19/2000,010,Computer Audio and Video,00023,MP3 PLAYER,111.13,10,176.40,1764.00
04/19/2000,014,Edgewood Audio and Video,00006,CD/DVD PLAYER,277.83,20,441.00,8820.00
04/19/2000,016,Computer Audio and Video,00014,SUBWOOFER,124.74,30,198.00,5940.00
04/19/2000,021,Computer Audio and Video,00014,SUBWOOFER,124.74,35,198.00,6930.00
04/19/2000,028,Bayshore Electronics,00020,CD WALKMAN, 16.08,15,21.40,621.00
The basic syntax for the Unix sort command is as follows:
sort +start_field_number -stop_field_number file
Fields are numbered beginning from zero, so in the sales file, the field numbers are as follows:
(0) SALE-DATE
(1) CUSTOMER-ID
(2) CUSTOMER-NAME
(3) PRODUCT-ID
(4) PRODUCT-NAME
(5) UNIT-COST
(6) UNITS
(7) UNIT-PRICE
(8) SALE-AMOUNT
The default delimiter for the sort program is white space. The –t option allows you to specify an alternate delimiter. To replicate the first example again, sorting by customer-id, the sort command would be:
sort -t, +1 -2 sales.txt > sorted _sales.txt
which means, begin sorting on column 1 (customer-id) and stop sorting on column 2 (customer-name). The sort output is currently directed to standard output (terminal), so you redirect the output to a new file: sorted_sales.txt.
If the stop column is not specified, sort sorts on a compound key consisting of every column beginning with the start column specified.
Look at how to perform the aggregate key sort (year + product-id + customer-id) used earlier in the chapter to prepare the data for an aggregation ETL process. Year is last part of the field 0, product-id is field 3, and customer-id is field 1. The main challenge is limiting the sort to use only the year portion of the date. Here’s how:
sort -t, +0.6 -1 +3 -4 +1 -2 sales.txt > sorted_sales.
To sort on the year portion of the date (field 0), you specify the starting byte within the field following a period. As with the field numbers, byte numbers start with 0, so +0.6 means to sort on the seventh byte of the date.
Up to this point, these Unix sort examples have used alphabetic sorts. However, alphabetic sorts won’t yield the desired results on quantitative numeric fields. For example, sorting the sales-file unit cost alphabetically would yield incorrect results—the CD WALKMAN, with a cost of 16.08, would be placed after the SUBWOOFER, with a cost of 124.74. To solve this problem, you need to specify that the unit-cost field is numeric, as follows:
sort -t, +5n -6 sales.txt > sorted_sales.txt
To change the sort order from ascending to descending, use the –r (reverse) option. For example, sorting the sale data by descending year + unit cost is specified by the following:
sort -t, +0.6r -1 +5n -6 sales.txt > sorted_sales.txt
Other useful sort options are listed in Table 7.2.
Table 7.2 Switches for the UNIX sort command
|
DATA TYPE |
USAGE |
|
-f |
Ignore case in alphabetic sort fields |
|
-b |
Ignore leading blanks in sort fields |
|
-d |
Ignore punctuation characters |
|
-i |
Ignore nonprintable characters |
|
-M |
Sort 3-letter month abbreviations (for example, JAN precedes FEB, and so on) |
A rich set of Unix utility commands, including bfseries sort, grep, and bfseries gawk to name few key utilities, have been ported to the Windows operating system and are available as freeware. There are many Web sites from which you can obtain these utilities. We found a relatively complete set in a zipped file at unxutils.sourceforge.net.
Trimming the Fat (Filtering)
PROCESS CHECK] Planning & Design:
Requirements/Realities → Architecture → Implementation → Test/Release
Data Flow: Extract → Clean → Conform → Deliver
Source files often contain loads of data not pertinent to the data warehouse. In some cases, only a small subset of records from the source file is needed to populate the warehouse. Other times, only a few data elements from a wide record are needed. One sure way to speed up the ETL process is to eliminate unwanted data as early in the process as possible. Creating extract files on the source host system provides the greatest performance gain, because, in addition to the improvement in the ETL process itself, the time spent on file transfers is reduced in proportion to the reduction in file size. Whether you shrink a source file by picking, say, half the records in the file or half the fields on each record, you save time transferring the data to the ETL server and save I/O time and memory processing the smaller file during the ETL process.
The easiest extract files to create are those where only a subset of the source file records is needed. This type of extract can generally be created using utility programs, which are also typically the most efficient running programs on the system.
The following sections discuss creating extracts on mainframe systems and Windows and Unix systems.
Extracting a Subset of the Source File Records on Mainframe Systems
On mainframe systems, the SORT utility happens to be perhaps the fastest and easiest way to create extract files without writing COBOL or fourth-generation language (SAS, FOCUS, and so on) programs.
The simplest case is to create an extract in which only a subset of the records is needed. SORT allows to you specify source records to either include or omit from the extract file.
INCLUDE COND=(st,len,test,dt,val) OMIT COND=(st,len,test,dt,val)
The st indicates the start position of the input field, len is its length, test is the Boolean test to perform, dt is the data type of the input field, and val is the value to compare against. For this example, we use the sample sales file from the prior sort examples in the chapter. Here’s how to extract only records for sales from the year 2000 and higher.
SORT FIELDS=COPY
INCLUDE COND=(1,4,GE,CH,C’2000’)
This could also be coded with an EXCLUDE as:
SORT FIELDS=COPY
OMIT COND=(1,4,LT,CH,C′2000’)
Compound conditions are created by joining condition sets with AND or OR clauses. To select only records from the year 2000 and later for customer-ids over 010, use the following:
SORT FIELDS=COPY
INCLUDE COND=(1,4,GE,CH,C′2000′, AND,9,3,GT,CH,C′010′)
More complex conditions can be created using a combination of ANDs, Ors, and parentheses to control the order of execution. You can even search for fields containing a certain value. For example, to choose customers with the word Discount in their names, code the INCLUDE statement as follows:
SORT FIELDS=COPY INCLUDE COND=(12,27,EQ,CH,C′Discount’)
Because the 27-byte input field specified is longer than the constant Discount, SORT searches through the entire input field for an occurrence of the constant. (This is equivalent to coding a SQL where clause of LIKE ‘Discount‘)
Extracting a Subset of the Source File Fields
Creating an extract file containing only the fields necessary for the data warehouse ETL process can have an enormous impact on the size of the ETL source files. It is not at all uncommon to have source files with dozens and dozens of data elements of which only a small handful are needed for the ETL process. The impact of extracting only the required fields can have an enormous impact on file size even when the source file record is relatively small. Considering that some source files have millions of records, extracting only the required fields can shave tens or hundreds of megabytes off the data to be transferred to and processed by the ETL server.
Lo and behold, SORT can also handle the task of selecting a subset of the fields in a source file to shrink the amount of data that must be transferred to the ETL server using the OUTFIL OUTREC statement. The sales file we have used in the examples thus far has a record length of 100 bytes. Suppose your ETL process required only the sale-date, customer-id, product-id, unit-cost, units, and sale-amount fields, which total 36 bytes. An extract with only these fields would be about one-third the size of the full source file. To shrink it further, you can choose only records from the year 2000 and later.
SORT FIELDS=COPY
INCLUDE COND=(1,4,CH,GE,C′2000’)
OUTFIL OUTREC=(1,8,9,3,39,5,72,4,76,7,92,9)
In this simplest form, the OUTREC clause comprises simply pairs of starting positions and lengths of the fields to copy to the extract file. However, this still leaves you with some undesirable remnants of mainframe days. The unit-cost, units, and sale-amount are still in their mainframe storage formats. These fields are not usable in these native mainframe formats when transferred to the ETL server. To be usable on the Unix or Windows ETL server, you must reformat these numeric fields to display format.
SORT FIELDS=COPY
INCLUDE COND=(1,4,CH,GE,C′2000’)
OUTFIL OUTREC=(1,8,9,3,39,5,
72,4,PD,EDIT=IT.TT,LENGTH=7,
76,7,ZD,EDIT=IT,LENGTH=7,
92,9,ZD,EDIT=IT.TT,LENGTH=10)
In this format, the unit-cost, which is stored in packed numeric format on the source file, is exploded to a 7-byte display field taking the form 9999.99. Likewise, the units and sale-amount are reformatted to display as 9999999 and 9999999.99, respectively.
Clearly, the mainframe SORT utility is a powerful ally in your pursuit of mainframe data. The techniques cited in the preceding examples demonstrate just a subset of its rich functionality. Proficiency with SORT can be your ticket to self-sufficiency when you need to acquire mainframe data.
Extracting a Subset of the Source File Records on Unix and Windows Systems
Now let’s look at how to accomplish these same extract tasks on Unix and Windows systems. Again, we use a Unix utility, gawk, that has been ported to Windows. Gawk is the GNU version of the programming language awk. The basic function of awk is to search files for lines (or other units of text) that contain certain patterns.
The syntax we use for gawk is as follows:
gawk -f{cmdfile} {infile} > {outfile}
The –f option specifies the file containing the gawk commands to execute. The infile specifies the input source file, and outfile specifies the file to which the gawk output is redirected.
The first extract task is to select only the sales from 2000 and later. The gawk command would be something like the following:
gawk -fextract.gawk sales.txt > sales_extract.txt
The extract.gawk command file contains the following:
BEGIN {
FS=“,”;
OFS=“,”{
substr($1,7,4) >= 2000 {print $0}
The BEGIN {…} section contains commands to be executed before the first source record is processed. In this example, FS=“,”, and OFS=“,”, stipulate that fields in the input and output files are delimited by commas. If not specified, the default delimiters are spaces.
The extract logic is contained in the statement substr($1,7,4) > = 2000. It says to select records where the seventh through tenth bytes of the first ($1) field are greater than or equal to 2000.
The {print $0} statement says to output the entire source record ($0) for the selected records.
Compound conditions are created by joining condition sets with && (and) or || (or) clauses. To select only records from the year 2000 and later for customer-ids over 010, use the following:
BEGIN {
FS=“,”;
OFS=“,”{
substr($1,7,4) >= 2000 && $1 > “010” {print $0}
And to find the records where the customer-name contains Discount:
BEGIN {
FS=“,”;
OFS=“,”{
$3∼/Discount/ {print $0}
As you can see, with a bit of knowledge of the gawk command, you can very easily create these time-saving and space-saving extracts.
Extracting a Subset of the Source File Fields
You can also use gawk to create field extracts. As before, suppose you want to create an extract containing only the sale-date, customer-id, product-id, unit-cost, units, and sale-amount fields. Again, you want only to extract records from 2000 and later. Here’s how:
BEGIN {
FS=“,”;
OFS=“,”}
substr($1,7,4) >= 2000 {print $1,$2,$4,$6,$7,$9}
The print statement specifies the columns to include in the output.
Take note that in bfseries gawk, the fields are numbered starting with bfseries $1, and bfseries $0 refers to the entire input record. This differs from the bfseries sort command, where bfseries $0 connotes the first field.
Now suppose you want to create an extract file that has fixed field widths rather than a delimited file. Well, gawk can do that as well. Here’s how you make the prior extract into a fixed-width file:
BEGIN {
FS=“,”;
OFS=“,”}
{substr($1,7,4) >= 2000
{printf “%-11s%-4s%-6s%07.2f%08d%010.2f$\ $n”, $1,$2,$4,$6,$7,$9}}
Here, the printf command (formatted print) takes the place of the regular print command. printf is followed by a format string. The % sign denotes the beginning of a format, and the number of formats must match the number of output fields. The formats you commonly use are:
ns: For text strings, n is the minimum output length
0nd: For decimal numbers, n is the minimum output length
0n.mf: For floating point numbers, n is the total number of digits, m the decimal digits.
By default, data is right-justified. To left-justify data (as you would for most text fields), precede the field length with a dash as in the preceding example. To left-pad numeric formats with zeros, precede the length with a zero, (for example, %07.2f) The newline indicator \ n at the end of the format string tells gawk to put each output record on a new line.
Creating Aggregated Extracts on Mainframe Systems
Suppose you want to summarize the sample sales files by month, customer, and product, capturing the aggregate units and sales-amounts. Here’s the mainframe SORT commands to accomplish this:
INREC FIELDS=(1,4,5,2,9,3,39,5,3Z,76,7,3Z,91,9) SORT FIELDS=(1,14,CH,A) SUM FIELDS=(15,10,ZD,25,12,ZD)
You can see two new commands here—INREC and SUM. INREC work the same way as OUTREC, except that it operates on the input records before any sort operations are processed. In this case, the input records are reformatted to include only the fields needed for the aggregate—year, month, customer-id, product-id, units, and sale-amount. Take note as well of the two 3Z entries. These left-pad units and sale-amount with zeros prevent arithmetic overflows from occurring from the SUM operation. The effect is to increase the size of these fields by three bytes each.
Next, the SORT command specifies the fields used to sort the file. The SORT FIELDS act as the key for the SUM operation, essentially acting like a SQL GROUP BY clause. But note that the SORT FIELDS use the reformatted record layout—so the SORT FIELDS can be simply defined as positions 1 through 14, which now contain year, month, customer-id, and product-id.
Finally, the SUM command specifies the quantitative fields to be summed (or aggregated). Again, the reformatted field positions (and lengths, in this case) are used. So whereas units occupied positions 76–82 (a total of 7 bytes) in the source file, in the reformatted records, units occupies positions 15–24 (10 bytes).
Using this technique, the output file is only 36 bytes wide (versus the original 100 byte source records) and contains only one record per combination of year, month, customer-id, and product-id in the file. The network and ETL server will thank you for shrinking the size of the source file in this way.
Note, however, that transaction systems are often configured with minimal temp space available. This may affect your decision to compress data at the source during extraction. See the discussion on this topic later in this chapter under “Using Aggregates and Group Bys.”
Creating Aggregated Extracts on UNIX and Windows Systems
Accomplishing the same aggregation using the UNIX/Windows utilities is a bit more complex but not much. You need to use both the sort and gawk utilities together. The sort output is piped to the gawk command using the |pipe character. Here’s the command:
sort -t, +0.6 -1 +0.0 -0.2 +1 -2 +3 -4 | gawk -fagg.gawk > agg.txt
First, review the sort.
The agg.gawk command file follows. We’ve added comments (preceded by #) to explain how it works:
# set delimiters
BEGIN {
FS=“,”;
OFS=“,”}
#initialize variables for each record
{
{inrec += 1}
{next_year=substr($1,7,4) substr($1,1,2)}
{next_cust=$2}
{next_product=$4}
#after a new year and month record
#write out the accumulated total_units and total_sales for the prior year inrec > 1 && (\
next_year != prev_year \
|| next_cust != prev_cust \
|| next_product != prev_product ) \
{print prev_year,prev_cust,prev_product,total_units,total_sales}
#accumulate the total_sales sales and count of records
{total_units += $7}
{total_sales += $9}
#if the year changed reinitialize the aggregates next_year != prev_year {
total_units = $7;
total_sales = $9}
#store the year (key) of the record just processed
{prev_year = next_year}
{prev_cust = next_cust}
{prev_product = next_product}
#after the last record, print the aggregate for the last year END {print prev_year,prev_cust,prev_product,total_units,total_sales}
It’s a bit more complex than the mainframe sort but not very much. It is simply a matter of keeping track of the key values as records are processed and writing out records as the keys change. Note also the END command. This command ensures that the last aggregate record is output. Once you get familiar with the operators and learn how the flow of control works, you’ll be aggregating in no time.
Using Database Bulk Loader Utilities to Speed Inserts
PROCESS CHECK Planning & Design:
Requirements/Realities → Architecture → Implementation → Test/Release
Data Flow: Extract → Clean → Conform → Deliver
If after using sorting, extracting, and aggregating techniques to get your source data to the ETL server as quickly as possible, you still face a daunting amount of data that needs to be loaded into the data warehouse regularly, it’s time to master the bulk-load functionality of your database management system. Bulk loaders can interact with the database system in a more efficient manner than plain old SQL can and give your ETL a tremendous performance boost. We use Oracle’s SQL*LOADER utility to discuss the benefits of bulk loaders. You can find similar bulk-load functionalities in most other database management systems.
One important caveat is that many bulk loaders are limited to handling inserts into the database. As such, they can provide a real benefit for inserting large volumes of data, but if your process involves updating existing records, you may be out of luck. Depending on the number of rows you need to insert and update, you may find that with careful preprocessing of your input data, you can separate the updates from the inserts so that at least the inserts can be run in pure bulk-loader mode. Note that IBM’s Red Brick system supports UPDATE else INSERT logic as part of its bulk loader.
In its basic conventional path method, SQL*LOADER uses INSERT statements to add data to tables, and the database operates in the same manner as if the inserts were part of a regular SQL procedure. All indexes are maintained; primary key, referential integrity, and all other constraints are enforced; and insert triggers are fired. The main benefit to using SQL*LOADER in this mode is that it provides a simple way to load data from a flat file with minimal coding.
A variety of syntax styles for invoking SQL*LOADER exist. Here’s an example for loading the sales file we used in the previous examples into an Oracle table with SQL*LOADER.
sqlldr userid=joe/etl control=sales.ctl data=sales.txt log=sales.log bad=sales.bad rows=1000
The control file sales.ctl would contain the following:
LOAD DATA
APPEND INTO TABLE SALES
FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”’
(SALE_DATE DATE(20) “MM/DD/YYYY”,
CUSTOMER_ID,
CUSTOMER_NAME,
PRODUCT_ID,
PRODUCT_NAME,
UNIT_COST,
UNITS, UNIT_PRICE,
SALE_AMOUNT)
Of course, the target table SALES would have to already exist in Oracle. Again, this simple example uses conventional SQL INSERT functionality to load data into the database. Next, we want to look at ways to improve performance.
The second, performance-enhancing mode for SQL*LOADER is direct mode. Changing the prior load to use direct mode is achieved by simply adding the direct=true clause to the sqlldr command, as shown in the following.
sqlldr userid=joe/etl control=sales.ctl data=sales.txt log=sales.log bad=sales.bad rows=1000 direct=true
Here’s how direct mode increases performance:
1. SQL*LOADER places an exclusive lock on the table, preventing all other activity.
2. Database constraints (primary and unique key constraints, foreign key constraints, and so on) are not enforced during direct loads. If violations occur, the associated indices are left in an unstable state and require manual clean up before rebuilding the indices.
3. Foreign key constraints are disabled by the direct load and must be re-enabled after the load. All rows are checked for compliance with the constraint, not just the new rows.
4. Insert triggers do not fire on rows inserted by direct loads, so a separate process must be developed to perform the actions normally handled by the triggers if necessary.
So the efficiencies of direct load don’t come free. We’d be particularly wary of using direct loads if you expect dirty data that will prevent your primary and foreign key constraints from being re-enabled after the load. But if you have robust processes for ensuring that the data to be loaded is clean, direct loads of large source files is the way to go.
Whether you are using conventional or direct path mode, SQL*LOADER has a fairly rich set of functionality beyond the simple example shown previously. Some key functions include the following:
Handles fixed-width, delimited, and multiline input
Accepts input from multiple files
Loads to multiple target table
Loads partitioned tables
Manages and updates indexes efficiently
A number of other, more programmatic features allow conditional processing, value assignments, and so on. However, these features should be avoided, since they generally operate on each input row and can considerably degrade the performance of the bulk load. After all, performance is what using the bulk loader is all about.
Preparing for Bulk Load
Many of the ETL tools on the market today can stream data directly from their tool through the database bulk-load utility into the database table. But not all of the tools utilize the bulk-load utilities the same way. Some are more efficient than others, and some require extra plug-ins to make them compatible with bulk loaders. Regardless of how you pass data, as an ETL developer, it is important that you understand how to prepare your data to be processed by a bulk-load utility.
Bulk loading is the most efficient way to get data into your data warehouse. A bulk loader is a utility program that sits outside of the database and exists for the sole purposes of getting large amounts of data into the database very quickly. Each database management system has a different, proprietary, bulk-load utility program. The popular ones are listed in Table 7.3.
Table 7.3 Bulk Load Utilities
|
DBMS |
BULK LOAD UTILITY NAME |
COMMENTS |
|
Oracle |
SQL*Loader |
Requires a control file that describes the data file layout. |
|
Two important parameters for optimal performance are: |
||
|
DIRECT={TRUE | FALSE} PARALLEL={TRUE | FALSE} |
||
|
Microsoft SQL Server |
Bulk Copy Program (BCP) |
Microsoft also offers BULK INSERT that can be faster than BCP. It saves a significant amount of time because it doesn’t need to utilize the Microsoft NetLib API. |
|
IBM DB2 |
DB2 Load Utility |
DB2 accepts Oracle Control and Data files as input sources. |
|
Sybase |
Bulk Copy Program (BCP) |
Also supports DBLOAD with the parameter BULKCOPY = ‘Y’. |
Generally speaking, the various bulk-load utilities work in the same way. For the purpose of illustrating the functionality of a bulk-load utility, we’ll discuss Oracle’s SQL*Loader; at the time of this writing, we believe it is the common denominator of bulk loaders in the domain of data warehouses. Once you understand the general concepts of bulk loading, the similarities among the loaders makes learning each specific utility a breeze.
Bulk loaders typically need two files to function properly:
Data file. The data file contains the actual data to be loaded into the data warehouse. Data can be in various file formats and layouts, including a variety of delimiters. All of these parameters are defined in the control file.
Control file. The control file contains the metadata for the data file. The list of the various parameters is extensive. Following is a list of the basic elements of SQL*Loader control file.
The location of the source file
Column and field layout specifications
Data-type specifications
The data mapping from the source to the target
Any constraints on the source data
Default specifications for missing data
Instructions for trimming blanks and tabs
Names and locations of related files (for example, event log, reject, and discarded record files)
For a comprehensive guide to SQL*Loader command syntax and usage, refer to Oracle SQL*Loader: The Definitive Guide, by Jonathan Gennick and Sanjay Mishra (O’Reilly & Associates, 2001).
Even if you must pay the penalty for the I/O of writing data to a physical file before it is bulk loaded into the data warehouse, it is still likely to be faster than accessing the database directly and loading the data with SQL INSERT statements.
Many ETL tools can pipe data directly into the database via the bulk-load utility without having to place data on disk until it hits its final destination—the data warehouse fact table. Others can create the required control and data files on your files system. From there, you need to write a command-line script to invoke the bulk loader and load the data into the target data warehouse.
The main purpose of purchasing an ETL tool is to minimize hand-coding any routines, whether extracting, transforming, or loading data. But no tool on the market can solve every technical situation completely. You’ll find that seamlessly pipelining data through bulk loaders or any third-party load utilities will be a bit of a challenge. Experiment with tool plug-ins and other application extenders such as named pipes, and exhaust all options before you determine bulk loading is not feasible.
If you have not yet purchased your ETL tool, make sure to test potential products for their compatibility with your DBMS bulk-load utility during your proof-of-concept. If you already own an ETL tool and it cannot prepare your data for bulk loading, do not throw it away just yet. You need to prepare the data manually. Configure your ETL tool to output your data to a flat file, preferably comma delimited. Then, create a control file based on the specifications of the output file and required load parameters. The control file should need to be changed only when physical attributes change within the source or target, like when new columns are added or data types are modified.
Managing Database Features to Improve Performance
PROCESS CHECK] Planning & Design:
Requirements/Realities → Architecture → Implementation → Test/Release
Data Flow:Extract → Clean → Conform → Deliver
As we all know, there’s more to the database than tables and the data contained therein. Powerful features like indexes, views, triggers, primary and foreign key constraints, and column constraints are what separate a database management system from a flat-file system. Managing these features can consume significant amounts of system resources as your database grows and, as a result, can drag down the performance of the ETL load process.
With this in mind, the first thing to do is review the database design and remove any unnecessary indexes, constraints, and triggers. Then consider the following options to improve load performance:
1. Disable foreign key (referential integrity) constraints before loading data. When foreign key constraints are enabled, for each row loaded the database system compares the data in foreign key columns to the primary key values in the parent table. Performance can be enhanced considerably by disabling foreign key constraints on fact tables having several foreign key constraints.
Remember, though, that the database validates every row in the table (not just new ones) when you enable foreign key constraints after the load. Make sure your foreign key columns are indexed to ensure that the re-enabling the constraints does not become a bottleneck in itself.
2. Keep database statistics up to date. Database statistics managed by the database management system track the overall sizes of tables, the sizes and number of unique values in indexes, and other facts about the efficiency of how data is stored in the database. When an SQL SELECT statement is submitted to the database management system, it uses these statistics to determine the fastest access path to supply the requested data. Optimally, you should update the statistics after each load. However, if your load process is frequent (daily) and the daily percentage change in the size of the database is relatively small, updating statistics weekly or monthly should be sufficient to keep performance levels high. Partitioning large tables decreases the time it takes to update statistics, since the statistics need not be refreshed on the static (or near-static) partitions but only on the current partition.
3 Reorganize fragmented data in the database. Tables become fragmented when rows are frequently updated and/or deleted, and response time degrades as a result.
When dealing with large fact tables, one way to minimize the occurrence of such fragmentation is to create partitioned tables. Partitioned tables are typically organized by time period (for example, a sales table with separate partitions for each year). Once a year is complete, the partition containing the sales data for that year will in all likelihood remain static and thus no longer be susceptible to fragmentation. ETL tools have the ability to automatically streamline loads based on the partition scheme specified in the DBMS dictionary.
Data for the current year, though, is constantly being deleted and reloaded, and so the current partition becomes fragmented. Reorganizing a fragmented table rewrites the data in the table in contiguous storage blocks and eliminates dead space that arises when rows are updated and deleted. If your load process performs updates and deletes on large (fact) tables, consider reorganizing the table every month or so or more frequently if warranted. The reorganization can be set to run each time data is loaded, if significant fragmentation occurs with each load.
Again, partitioning reduces the time it takes to reorganize tables. The older, static partitions will rarely, if ever, need to be reorganized. Only the current partition will need reorganizing.
The Order of Things
PROCESS CHECK Planning & Design:
Requirements/Realities → Architecture → Implementation → Test/Release
Data Flow:Extract → Clean → Conform → Deliver
The ordinal position of jobs within a batch is crucial when you are loading a data warehouse, primarily because the ETL needs to enforce referential integrity in the data warehouse. Referential integrity (RI) means that a primary key must exist for every foreign key. Therefore, every foreign key, which is known as the child in a referential relationship, must have a parent primary key. Foreign keys with no associated parents are called orphans. It is the job of the ETL process to prevent the creation of orphans in the data warehouse.
In transaction systems, RI is usually enforced within the database management system. Database-level RI enforcement is required in a transaction environment because humans enter data one row at a time—leaving a lot of room for error. Errors or actions that create RI violations cause data to become corrupt and of no use to the business. Users find amazing ways to unintentionally corrupt data during data entry. Once data is corrupt, it is worthless—a cost that cannot be overturned.
Enforcing Referential Integrity
Unlike transaction systems vulnerable to volatile data-entry activity, the data warehouse has its data loaded in bulk via a controlled process—the ETL system. The ETL process is tested and validated before it ever actually loads production data. The entry of data into the data warehouse is in a controlled and managed environment. It’s common practice in the data warehouse to have RI constraints turned off at the database level, because it depends on the ETL to enforce its integrity.
Another reason RI is typically disabled in the DBMS is to minimize overhead at the database level to increase load performance. When RI is turned on within the database, every row loaded is tested for RI—meaning every foreign key has a parent in the table that it references—before it is allowed to be inserted.
RI in the data warehouse environment is much simpler than in transaction systems. In transaction systems, any table can essentially be related to any other table, causing a tangled web of interrelated tables. In a dimensional data warehouse, the rules are simple:
Every foreign key in a fact table must have an associated primary key in a dimension.
Every primary key in a dimension does not need an associated foreign key on a fact table.
Those trained in normalization know this is called a zero-to-many relationship. If you already have a dimensional data warehouse implemented, or have read any of the Toolkit books, you know that not all dimensional models are that straightforward. In reality, a fact can be associated to many records in a dimension (with a bridge table as described in Chapters 5 and 6) and dimensions can be snowflaked. In addition to facts and dimensions, the ETL must contend with outriggers and hierarchy tables. The ETL team must understand the purpose and functions of each of the types of tables in the dimensional data model to effectively load the data warehouse. Review Chapter 2 for more information on the different types of tables found in a dimensional model.
The following list is offered as a guide to the ordinal position of load processes for a given data mart.
1. Subdimensions (outriggers)
2. Dimensions
3. Bridge tables
4. Fact tables
5. Hierarchy mappings
6. Aggregate (shrunken) dimensions
7. Aggregate fact tables
Subdimensions
A subdimension, as discussed in Chapter 5, is simply a dimension attached to another dimension, when the design is permissibly snowflaked. A subdimension may play the role of a primary dimension in some situations. The calendar date dimension is a good example of an entity that is frequently a primary dimension as well as a subdimension.
Subdimensions are usually the first to be loaded in the data warehouse because the chain of dependency starts with the outermost tables, namely the subdimensions. Facts depend on dimensions, and dimensions depend on subdimensions. Therefore, subdimensions must be loaded, and their keys defined, before any other table downstream in the structure can be populated. The caveat is that depending on business requirements and your particular environment, it’s possible that some subdimensions are rarely used and not considered mission critical. That means if a failure occurs to prevent the subdimension from loading successfully, it may be acceptable to continue with the load process of its associated dimension anyway.
Dimensions
Once the subdimensions are loaded, you can load the dimensions. Dimensions that have subdimensions need to lookup the surrogate key in the subdimension so it can be inserted into the dimension during the load process. Naturally, dimensions that do not have subdimensions can be loaded at once, without waiting for anything else to complete.
Smaller dimensions without dependencies should be loaded concurrently and utilize parallel processing. Larger dimensions can also be loaded in this fashion, but test their performance for optimal results before you commit to this strategy. Sometimes, it is faster to load large dimensions individually to alleviate contention for resources. Unfortunately, trial and error is the best rule for action in these cases.
Dimension loads must complete successfully before the process continues. If a dimension load fails, the scheduler must halt the load process from that point forward to prevent the rest of the jobs from loading. If the process continues to load without the dimension information populated, the data warehouse will be incomplete and viewed as corrupt and unreliable. Enforcing the dependencies between jobs is crucial for the data warehouse to maintain a respectable reputation.
Bridge Tables
A bridge table sits between a dimension and a fact table when a single fact record can be associated to many dimension records. Bridge tables are also used between a dimension and a multivalued subdimension. For example, a bridge table is needed when a fact is at the grain of a patient treatment event in a medical billing database and many patient diagnoses are valid at the moment of the treatment. After the patient diagnosis dimension is loaded, the treatment transaction table is scanned to determine which diagnoses occur together. Then the bridge table is loaded with a surrogate key to assemble the diagnoses ordered together into groups.
Not all data marts contain bridge tables, but when they do, the tables must be loaded immediately after the dimensions but before the fact table load starts. If a fact table depends on a bridge table, the bridge table load must complete successfully before the fact table load can be executed. If you attempt to load the fact table with the bridge table partially loaded, groups will be missing from the table, and data from the fact table will become suppressed when it is joined to the bridge table.
CROSS-REFERENCE Information on loading bridge tables can be found in Chapter 5; techniques for using a bridge table to find the groups while loading facts are found in Chapter 6.
Fact Tables
Fact tables are dependent on virtually all other tables in the dimensional data model and are usually loaded last. Once the subdimensions, dimensions, and bridge tables are loaded, the fact table has all of the look-ups it needs and is ready to be loaded. Remember, RI is enforced here, so you must ensure that every foreign key in the fact table has an associated primary key in its relative dimension or bridge table.
Fact tables typically take longest of all the different types of tables in the data warehouse to load; you should begin the fact table load process as soon as all of its related tables are loaded. Do not wait for all of the dimensions in the data warehouse to load before kicking off the fact load. Only the dimensions and bridge tables directly related to the fact table need to complete before the associated fact table load can begin.
Because of the extreme volume of data usually stored in fact tables, it’s a good idea to process their loads in parallel. The scheduler should spawn the ETL process into multiple threads that can run concurrently and take advantage of parallel processing. The next chapter discusses more about optimizing your fact table loads.
Hierarchy Mapping Tables
Hierarchy mapping tables are specially designed to traverse a hierarchy that lives within a dimension. See Chapter 5. Hierarchy mapping tables are not dependent on facts or bridge tables (unless, of course, the fact table itself contains the hierarchy). Technically, hierarchy tables can be loaded immediately following their relative dimension load, but we recommend loading them at the end of the data-mart process to enable the long-running fact table loads to begin, and finish, sooner.
Regardless of where the hierarchy is physically placed in a batch, its success or failure should have no bearing on the other processes in the batch. Don’t kill the launch of a fact table process because of a failure in a hierarchy mapping table. The mapping table can be restarted independently of any fact table load.
The Effect of Aggregates and Group Bys on Performance
Aggregate functions and the Group By clause require databases to utilize a tremendous amount of temp space. Temp space is a special area managed by the DBMS to store working tables required to resolve certain queries that involve sorting. Most DBMSs attempt to perform all sorting in memory and then continue the process by writing the data to the temp space after the allocated memory is full. If you attempt to build aggregates for the data warehouse with SQL, you have a few issues to address.
SQL is processed on the server where it is executed. That means that if you attempt to aggregate data in your extract query, you will likely blow-out the allocated temp space in the source transaction system. By design, transaction systems keep their temp space very small compared to the space allocated on data warehouses. When you need to build aggregate tables, it’s good practice to utilize the ETL engine or a third-party tool specifically dedicated to sorting data at lightning-fast speeds.
You should adjust your aggregates incrementally with a dedicated tool that supports incremental updates to aggregates.
Do not attempt to execute aggregating SQL with a bfseries Group By clause in your data extraction query. The bfseries Group By clause creates an implicit sort on all of the columns in the clause. Transaction systems are typically not configured to handle large sort routines, and that type of query can crash the source database. Extract the necessary atomic-level data and aggregate later in the ETL pipeline utilizing the ETL engine or a dedicated sort program.
Performance Impact of Using Scalar Functions
Scalar functions return a single value as output for a single input value. Scalar functions usually have one or more parameters. As a rule, functions add overhead to query performance, especially those that must evaluate values character by character. The following functions are known performance inhibitors:
SUBSTR()
CONCAT()
TRIM()
ASCII()
TO_CHAR()
This list is not exhaustive. It is served as an example to get you thinking about the different types of functions available in your database. For example, TO_CHAR() is a data-type conversion function. If TO_CHAR() inhibits performance, you can imagine that TO_DATE() and TO_ NUMBER() also do. Try to substitute database functions with operators. For example, in Oracle, the CONCAT() function can be replaced with the double pipe || to concatenate two strings.
Databases are getting better at handling functions. Oracle has introduced function-based indexes that speed up response time for function-based constraints on queries. Look for more advanced functionality from the database vendors as they integrate the ETL with their base products.
Avoiding Triggers
Database triggers are stored procedures executed by the occurrence of an event in the database. Events such as deleting, inserting, or updating data are common events related to database triggers. The problem is that each event is the occurrence of a record trying to get into the database, and the database must fire off the stored procedure between each record. Triggers are notorious for slowing down transactions as well.
If you should need event-based execution of a process, use the ETL engine to accomplish the task, especially for performing such tasks as appending audit metadata to records or enforcing business rules. ETL engines can perform such tasks in memory without requiring I/O.
Overcoming ODBC the Bottleneck
Chapter 3 offers insight into the layers within the Open Database Connectivity (ODBC) manager, but it’s worth mentioning again here that ODBC is usually an unnecessary layer in your communication between the ETL engine and the database that can—and should—be avoided. ODBC adds layers of code to each SQL statement. It is equivalent to using a translator while teaching a class. The message eventually gets across but is a much slower process. And at times, things do get lost in translation.
Try to obtain native drivers to communicate between the ETL engine and the databases in that participate in process. Remember, just as a chain is only as strong as its weakest link, the ETL is only as fast as its slowest component. If you include ODBC in your ETL solution, you will not achieve optimal performance.
Benefiting from Parallel Processing
Processing the ETL in parallel is probably the most powerful way to increase performance. Each time you add another process, the throughput proportionally increases. This section does not discuss the technical architecture options (SMP, MPP, NUMA, and so on). Instead, we offer the benefits of processing the ETL in parallel versus sequential processing.
Parallel processing, in its simplest definition, means that more than one operation is processed at a time. As you can imagine, three major operations exist in any given ETL process—extract, transform, and load. You can, and should, take advantage of parallel processing in as many of them as possible.
Parallelizing Extraction Queries
The effective way to parallelize extraction queries is to logically partition the data set into subsets of equal size. We say logically partition because partitioning data is usually a physical database function. In this case, you divide the data based on ranges of an attribute. For example, you can divide the effective_date by year. Therefore, if you have ten years of data, you have ten logical partitions. Each partition is retrieved by a separate SQL statement and executed concurrently. The potential problem with this approach is that the database identifies each SQL statement as a separate process and attempts to maximize the memory allocated to each. Therefore, if you have very memory-intensive extraction queries, you can bring the server to its knees by replicating and executing such intensive processes.
Fortunately, most DBMSs have the capability to process a query in parallel, realizing it is the same process and managing memory accordingly. Optimal parallel solutions usually combine the two techniques—spawn several extract queries, each with a different range of values, and then parallelize each of those processes with database-specific parallel query techniques.
Each database—those that support it—has its own syntax for executing queries in parallel. In Oracle, you enable parallelization by setting the degree parameter when you create a table, or you can alter the table after it’s created to enable parallelized queries. Run the following query to check to see what the parallel parameters for a table are:
Select table_name, degree, instances from all_tables where table_name = ‘’
The preceding query returns three columns:
Table Name. The name of the table being checked for parallelism
Degree. The number of concurrent threads that would be used on each instance to resolve a query
Instances. The number of database instances that the query can span to resolve a query
You do not need Oracle Parallel Server to run parallel processes. As long as you have the parallel degree set greater than 1, the query runs in as many processes as are indicated. However, to span instances, you must have multiple instances active and have Oracle Parallel Server running.
Unfortunately, most transaction tables have the parallel degree set to 1 by default. And as you have probably found out, the source system DBA is not about to alter tables for the data warehouse team. Luckily, you don’t need them to. Since the extraction query is a static, reusable SQL statement, it is permissible to insert a hint to override the physical parallel degree to tell the DBMS to parallelize the query on the fly! Dynamic parallelization is a robust mechanism invaluable for speeding up extract queries.
To dynamically parallelize a query, insert a hint that specifies the number of threads that you want to run concurrently and the number of instances you want to span.
select /*+ full(products) parallel(products,4,1) */
product_number, product_name, sku, unit_price from products where product_status = ‘Active’
The hint in the query is marked by a proceeding /*+ and is terminated with */. Notice that the hint made the query execute on four different threads on a single instance dynamically. By quadrupling the execution threads, you can usually come awfully close to quadrupling the total throughput for the process. Obviously, other variables, such as memory and the physical attributes on the source system and tables, which the ETL team has no control over, also affect performance. So, don’t expect performance increases to be 100-percent proportional to the number of parallel degrees specified. Refer to your DBMS user’s manual for the calculation to determine the optimal parallel degree setting for your specific situation.
Parallelizing Transformations
If you are using SQL for your transformation logic, you can use the hint offered in the last section for any SQL DML statement. However, if you are using a dedicated ETL tool, and by now you probably are, you have two options to parallelize your transformations:
1. Purchase a tool that can natively parallelize an operation.
2. Manually replicate a process, partition the input data, and execute the processes in parallel.
Obviously, you want to strive for the first option. However, some tools do not natively support parallelism within jobs. If you have very large data sets, parallelism is not a nice option but a requirement. Luckily, the ETL vendors realize that data volumes are growing at a rapid pace, and they are quickly adding parallelization functionality to their tool sets.
If you have a tool (or an add-on to a tool) that enables transformations to be processed in parallel, simply follow the guidelines set by the vendor to achieve optimal results.
On the other hand, if you need to replicate processes manually, you should take the following steps:
1. Analyze the source system to determine the best way to partition data. If the source table is partitioned, use the column that the partition is based on. If it is not partitioned, examine the date fields, that is, effective_date, add_date, and so on. Usually, partitioning by date makes a nice, even distribution of volume across partitions. Often, in cases such as Orders, the volume can increase across partitions over time (a sign that business is good). In those cases, consider range partitioning the primary key or creating a hash partition, perhaps doing MODs on the primary key, which is a simple way to split data evenly.
2. The next step is to replicate the ETL process as many times as you want parallel threads to run concurrently. Look for a tool that minimizes the amount of redundant code. Remember, if you have four copies of an ETL process, all four copies need to be maintained. It’s better to utilize a tool that can execute the same job with different batches that feed the job different data sets.
3. Finally, set up several batch jobs, one for each process, to collect and feed the appropriate data sets based on the ranges of values determined in step one. If you have an extremely volatile source system, we recommend that you run a preprocess that scans the source data and determines the best ranges to evenly distribute the data sets across the replicated ETL jobs. Those ranges (start value and end value) should be passed to the ETL jobs as parameters to make the process a completed automated solution.
If you have a substantial amount of data being fed into your data warehouse, processing all of your ETL operations sequentially will not suffice. Insist on an ETL tool that can natively process multiple operations in parallel to achieve optimal throughput (where parallelization is built directly into the transformation engine, not implemented as parallel extenders).
Parallelizing the Final Load
In the earlier section discussing parallelizing extraction queries, we assume that you do not have control over the structures in the database and that you need to add a database hint to have your query spawn multiple threads that run concurrently. However, in the target, the presentation area of the data warehouse, you do—or at least should—have some say in how the structures are built. It’s in the best interest of the data warehouse team to architect the tables to have multiple degrees of parallelization when they are created.
Earlier in this chapter, we recommend that you minimize SQL inserts, updates, and deletes and utilize the bulk-load utility. Furthermore, when using Oracle’s SQL Loader, you should make sure to set the DIRECT parameter to TRUE to prevent unnecessary logging.
Now we want to introduce one more technique to extend the extract and transform parallel processing: Spawn multiple processes of SQL Loader—one for each partition—and run them in parallel. When you run many SQL Loader processes concurrently, you must set the PARALLEL parameter to TRUE. No faster way exists—at least at the time of this writing—to load a data warehouse than following these three rules:
1. Utilize the bulk loader.
2. Disable logging.
3. Load in parallel.
More information about using bulk loaders can be found in Chapter 8. For an exhaustive reference for the Oracle SQL Loader utility, read Oracle SQL*Loader: The Definitive Guide by Jonathan Gennick and Sanjay Mishra (O’Reilly & Associates 2001).
Troubleshooting Performance Problems
No matter how efficient you make your ETL system, you still stand a chance of having performance issues. However, as Robin Williams says so eloquently in the film Good Will Hunting, “It’s not your fault.’’ When you are dealing with very large data sets, sometimes they decide to make their own rules. On more than one occasion, we’ve come across a situation where everything is configured correctly, but for some unexplainable reason, it just doesn’t work!
When a job catches you by surprise and performs with lackluster results, don’t fight it. Simply take a pragmatic approach to find the operation within the process causing the bottleneck and address that specific operation. Monitor areas such as CPU, memory, I/O, and network traffic to determine any high-level bottleneck.
If no substantial bottlenecks are detected outside of the actual ETL process, you need to dive inside the code. Use the process of elimination to narrow down potential bottlenecks. To eliminate operations, you must have the ability to isolate each operation and test it separately. Code isolation tends to be quite difficult if you are hand-coding the entire process in SQL or another procedural language. Virtually all of the ETL tools provide a mechanism to isolate components of a process to determine undesired bottlenecking.
The best strategy is to start with the extraction process; then work your way through each calculation, look-up, aggregation, reformatting, filtering, or any other component of the transformation process; and then finally test the I/O of the actual data load into the data warehouse.
To begin the isolation process for detecting bottlenecks, copy the ETL job and modify the copy of the job to include or exclude appropriate components as needed. As you step through the process, you will likely need to delete the copy and recopy the job to restore changes made to test preceding components. Follow these steps to isolate components of the ETL process to identify bottlenecks:
1. Isolate and execute the extract query. Usually, the extraction query is the first operation in the process and passes the data directly into the next transformation in the pipeline. To isolate the query, temporarily eliminate all transformations and any interaction with databases downstream from the extract query and write the result of the query directly to a flat file. Hopefully, the ETL tool can provide the duration of the query. If not, use an external monitoring tool, or, in Oracle, use the SET TIMING ON command before you execute the process. That setting automatically displays the elapsed time of the query once it completes. If the extract query does not return the rows substantially faster than when the whole process is enabled, you’ve found your bottleneck, and you need to tune your SQL; otherwise, move on to Step 2.
NOTE In our experience, badly tuned SQL is by FAR the most common reason for slowness.
1. Disable filters. Believe it or not, sometimes feeding data in an ETL job and then filtering the data within the job can cause a bottleneck. To test this hypothesis, temporarily disable or remove any ETL filters downstream from the extract query. When you run the process, watch the throughput. Keep in mind that the process might take longer, but its how much data is processed during that time that’s important. If the throughput is substantially faster without the filter, consider applying a constraint in the extract query to filter unwanted data.
2. Eliminate look-ups. Depending on your product, reference data is cached into memory before it is used by the ETL process. If you retrieve a lot of data in your look-ups, the caching process can take an inordinate amount of time to feed all of the data into memory (or to disk). Disable each look-up, one at a time, and run the process. If you notice an improvement in throughput with one or more look-ups disabled, you have to minimize the rows and columns being retrieved into cache. Note that even if you are not caching your look-up, you may still need to minimize the amount of data that the look-up query returns. Keep in mind that you need only the column being referenced and the column being selected in your look-ups (in most cases, the natural key and surrogate key of a dimension). Any other data is usually just unnecessary I/O and should be eliminated.
4. Watch out for sorters and aggregators. Sorters and aggregators tend to hog resources. Sorters are especially bad because they need the whole dataset in memory to do their job. Disable or remove any resource-intensive transformations such as sorters and aggregators and run the process. If you notice a substantial improvement without the components, move those operations to the operating system. Quite often, it’s much faster to sort or presort for aggregates outside of the database and ETL tool.
5. Isolate and analyze each calculation or transformation. Sometimes the most innocent transformations can be the culprit that causes ETL performance woes. Remove each remaining transformation, one at a time, and run the process. Look for things such as implicit defaults or data-type conversions. These seemingly harmless operations can have substantial impact on the ETL process. Address each operation independently for the best bottlenecking detection and remedy.
6. Eliminate any update strategies. As a general rule, the update strategies that come packaged in ETL tools are notoriously slow and are not recommended for high-volume data loads. The tools are getting better, so test this process before removing it. If the update strategy is causing a bottleneck, you must segregate the inserts, updates, and deletes and run them in dedicated streams.
7. Test database I/O. If your extraction query and the rest of the transformations in your ETL pipeline are running efficiently, it’s time to test the target database. This is a simple test. Redirect the target to load to a flat file instead of a database. If you see a noticeable improvement, you must better prepare your database for the load. Remember to disable all constraints, drop all indexes, and utilize the bulk loader. If you still cannot achieve desired performance, introduce a parallel process strategy for the data-load portion of the ETL.
Increasing ETL Throughput
This section is a summary of sorts. It can be used as a quick reference and a guideline for building new processes. The ETL development team is expected to create ETL jobs that obtain the maximum possible throughput. We recommend the following ten rules, which are applicable for hand-coded solutions as well as for various ETL tools for boosting throughput to its highest level:
1. Reduce I/O. Minimize the use of staging tables. Pipeline the ETL to keep the data in memory from the time it is extracted to the time it is loaded.
2. Eliminate database reads/writes. When staging tables are necessary, use flat files instead of database tables when you must touch the data down to disk.
3. Filter as soon as possible. Reduce the number of rows processed as far upstream in the process as you can. Avoid transforming data that never makes its way to the target data warehouse table.
4. Partition and parallelize. The best way to increase throughput is to have multiple processes process the data in parallel.
Parallelize the source system query with parallel DML.
Pipeline and parallelize transformations and staging.
Partition and load target tables in parallel.
5. Update aggregates incrementally. Rebuilding aggregates from scratch is a process-intensive effort that must be avoided. You should process deltas only and add those records to existing aggregates.
6. Take only what you need (columns and rows). Similar to the filtering recommendation, do not retrieve rows unessential to the process. Likewise, do not select unessential columns.
7. Bulk load/eliminate logging.
Utilize database bulk-load utility.
Minimize updates; delete and insert instead.
Turn off logging.
Set DIRECT=TRUE.
8. Drop database constraints and indexes. Foreign key (FK) constraints are unnecessary overhead; they should be dropped—permanently (unless they are required by your aggregate navigator). If FKs are required, disable them before the ETL process and enable them as a post-process. Leave indexes for updates and deletes to support WHERE clauses only. Drop all remaining indexes for inserts. Rebuild all indexes as a post-process.
9. Eliminate network traffic. Keep working files on local disk drives. Also, place the ETL engine on the data warehouse server.
10. Let the ETL system do the work. Minimize any dependency on DBMS functionality. Avoid stored procedures, functions, database key generators, and triggers; determine duplicates.
Many of the top ten rules have already been discussed in previous chapters in this book. For a more comprehensive examination, you’ll find that Chapters 5, 6, and 7 offer especially great details on optimal design strategies.
Each of the top ten rules for boosting ETL productivity is discussed briefly in the following sections.
TIP Rebuilding indexes can take a lot of time. It’s recommended that you partition high-volume target tables. Not only can you truncate and reload the data in a partition, while leaving the rest of the table intact, but indexes local to a partition can be dropped and rebuilt, regardless of how data is maintained. Rebuilding the subset of the index can save a substantial amount of time during the post-load process.
Reducing Input/Output Contention
Because databases and operating systems each interact with input and output so differently, we don’t attempt to explain the technical operations of I/O in this section. However, we do maintain the stance that I/O must be reduced to an absolute minimum. Obviously, you may need to touch down data for various reasons. The number-one permissible reason to touch down data is when you need to minimize access to the source system or if your source system allows only one-shot to retrieve the data you need from it. In those cases, it’s good practice to write the extraction result set to disk as soon as it is retrieved. That way, in case of failure, you can always reprocess data from the saved copy instead of penetrating the source system again.
Excessive I/O is a remarkably common offender. In most cases, intermediary tables or files can be omitted without any loss of functionality while their respective processes benefit from increased throughput. If you find yourself creating staging tables and many jobs to read and write to them, stop! Step back and analyze the total solution. By eliminating staging tables, you not only reduce I/O—the biggest performance hit—but also you reduce the number of jobs that need to be maintained and simplify the batch and scheduling strategy.
Eliminating Database Reads/Writes
The ETL process often requires data to be touched down to disk for various reasons. It can be to sort, aggregate, or hold intermediate calculations or just retain for safekeeping. The ETL developer has a choice of using a database for these purposes of flat files. Databases require much more overhead than simply dumping data into a flat file. And ETL tools can manipulate data from a flat file just as easily as database data. Therefore, it’s a preferred practice to utilize sequential or flat files in the data-staging area whenever possible.
The irony of this recommendation is that the ultimate goal of the data warehouse is to present data in a way that it has optimal query response time and that the solution is a relational database management system. However, even though the ETL may need to read intermediary data, it does not query data in the same sense end users do in the data warehouse’s presentation layer. ETL staging processes are static and repeated, whereas the data warehouse must support unpredictable, ad-hoc queries.
Dramatic performance improvements can be obtained by simply redirecting the staging database tables to flat files. The downside to eliminating the database in the staging area is that the associated metadata that comes for free by the nature of the database is lost. By choosing to use flat files, you must maintain any metadata related to the files manually (unless your ETL tool can capture the metadata).
Filtering as Soon as Possible
This tip addresses what is possibly the most common mistake in ETL design. Whenever we conduct design reviews of existing ETL processes, one of the first things we look for is the placement of filters. A filter is a component that exists in most ETL products that applies constraints to the data after it’s been retrieved. Filters are extremely useful because in many cases you need to constrain on fields from the source system that are not indexed. If you were to apply the constraint in the extraction SQL on a nonindexed field, the source database would need to perform a full table scan, a horrendously slow process. Conversely, if the source system indexes the field you want to constrain, this would be the preferred place for filtering because you eliminate extracting unwanted records.
We often notice that filters are placed downstream of very complex calculations or process-intensive and I/O-intensive data look-ups. Granted, at times you must perform certain calculations before filters are applied. For example, when you have to figure the dwell time of a Web page, you must calculate the difference between the current page hit and the next before you dispose of the unwanted pages.
As a general rule, you should keep and apply ETL filters as far upstream in the process as requirements permit. Typically, filtering advantages are best achieved when they are placed immediately following the initial SQL statement that extracts data from the source system and before any calculations or look-ups occur. Precious processing is wasted if you transform data and then throw it away.
Apply ETL filters to reduce the number of rows to process instead of applying constraints to the extraction SQL only if the source system database does not have the appropriate indexes to support your constraints. Because other factors such as table size, SQL complexity, network configuration, and so on play a role in data-retrieval performance, it makes sense to test both strategies before deciding on the optimal solution.
Partitioning and Parallelizing
Partitioning and parallelizing your ETL process is more than a design issue; it requires specific hardware and software and software solutions as well. One can partition data without executing its ETL in parallel and visa versa. But if you attempt to parallelize without partitioning, you can incur bottlenecking. An effective partition and parallelization strategy for unpredictable source data is to create hash partitions on your target tables and apply that same partition logic to the ETL process. Be careful though—hash partitions may not be an optimal solution for the data warehouse ad-hoc queries. Work closely with the data warehouse architect to implement the most appropriate partition strategy. Refer to earlier in this chapter for techniques and advantages concerning parallel processing.
Updating Aggregates Incrementally
Aggregates are summary tables that exist in the data warehouse specifically designed to reduce query time. Aggregates make a dramatic performance gain in the data warehouse because queries that had to scan through hundreds of millions of rows now can achieve the same results by scanning a few hundred rows. This drastic reduction in rows is attributable to the ETL process combining additive facts in a mechanical rollup process. More complex summaries that depend on complex business rules are not what we call aggregates in the dimensional world. Remember that an aggregate is used in conjunction with a query rewrite capability that applies a fairly simple rule to judge whether the aggregate can be used rather than a dynamic aggregation of atomic data at query time.
Aggregates are computed in several different ways in a mature data warehouse environment:
Calculating aggregate records that depend only on the most recent data load. Product rollups and geographic rollups (for instance) generated entirely from the most recent data load should be calculated by sorting and summarizing the data outside the DBMS. In other words, don’t use the DBMS’s sort routines when native OS sorts are much faster. Remember that the computation of aggregates is merely a process of sorting and summarizing (creating break rows).
Modifying an existing aggregate in place by adding or subtracting data. This option is called tweaking the aggregate. An existing aggregate spanning an extended period of time may be modified when that period of time includes the current load. Or an existing aggregate may be modified when the criteria for the aggregate are changed. This can happen, for example, if the definition of a product category is modified and an aggregate exists at the category level, or a rollup above the category level. If the tweak to the category is sufficiently complicated, a quality-assurance check needs to be run, explicitly checking the aggregate against the underlying atomic data.
Calculating an aggregate entirely from atomic data. This option, called a full rollup, is used when a new aggregate has been defined or when the first two options are too complex to administer.
Taking Only What You Need
It doesn’t make much sense to retrieve hundreds of thousands or millions (or even billions) of rows if only a few hundred of the records are new or have been modified since the last incremental ETL process. You must select a mechanism for retrieving deltas from the source system only. There are several ways to approach change-data capture depending on what’s available in the source transaction system. Refer to Chapter 6 for a display of the many different techniques for capturing changed data in the source system and techniques for determining the most appropriate for your particular situation.
Once you have the rows trimmed down to a manageable size for your incremental loads, you must next ensure that you don’t return more columns than necessary. Returning excessive columns is commonly encountered in look-ups in ETL tools. Some ETL tools automatically select all of the columns in a table whether they are needed or not when it is used for a look-up. Pay special attention to explicitly unselect columns that are not vital to the process. When you are looking up surrogate keys, you typically need only the natural and surrogate keys from a dimension. Any other column in a dimension is superfluous during a surrogate key look-up process.
Bulk Loading/Eliminating Logging
Bulk loading is the alternative to inserting data into the data warehouse one row at a time, as if it were a transaction system. The biggest advantage of utilizing a bulk loader is that you can disable the database logging and load in parallel. Writing to the rollback log consumes overhead as well as I/O and is unnecessary in the data warehouse. Specific bulk-load techniques and advantages to bulk loading are offered throughout this book.
Dropping Databases Constraints and Indexes
Another certain way to have a positive impact on loading your data warehouse is to drop all of the constraints and indexes from the target of the ETL process. Remember, the data warehouse is not transactional. All data is entered via a controlled, managed mechanism—ETL. All RI should be enforced by the ETL process, making RI at the database level redundant and unnecessary. After a table is loaded, the ETL must run a post-process to rebuild any dropped indexes.
Eliminating Network Traffic
Whenever you have to move data across wires, the process is vulnerable to bottlenecking and performance degradation.
Depending on your infrastructure, it sometimes makes sense to run the ETL engine on the data warehouse server to eliminate network traffic. Furthermore, benefits can also be achieved by storing all of the staging data on internal disk drives, rather than by having the data travel over the network just to touch down during the ETL process.
This recommendation can be a Catch-22, meaning that in your situation, putting the ETL engine on your data warehouse database server might actually make your performance worse, not better. Work with your ETL, database, and hardware vendor to achieve the best solution for your specific requirements.
Letting the ETL Engine Do the Work
ETL products are specifically designed to extract, transform, and load massive amounts like no other nondata warehousing solution. With minimal exception, most databases are designed to support transactional and operational applications. Database-procedural programming is good for supporting data-entry applications but is not optimal for processing large data sets at once. The use of cursors—where each record is analyzed individually before moving on to the next—is notoriously slow and usually results in unacceptable performance while processing very large data sets. Instead of using procedures stored within the database, it’s beneficial to utilize the ETL engine for manipulating and managing the data.
Summary
This chapter has provided an overview and some examples of the technologies you need to choose to develop your ETL system. You must start by choosing a development environment: either a dedicated ETL tool suite from one of the vendors we have listed or a development environment based on operating system commands driven by scripting languages, with occasional escapes into a low-level programming language.
In the second half of this chapter, we have given you some guidance on DBMS-specific techniques for performing high-speed bulk loads, enforcingRI, taking advantage of parallelization, calculating dimensional aggregates, and troubleshooting performance problems.
Now we’re ready to get operational in Chapter 8 and manage this wonderful technology suite we have built.