Loading data into the Datawarehouse

This article describes the various stages of loading data into the data warehouse

There are several stages in the process:

Preparation for Extraction

Data can come in various formats. It can be extracted from the source database directly or it may be loaded from the files. When we extract data directly all we need to is to check if connection is working.
This usually done automatically by ETL automation tool. In case of files, we need to obtain the files first. They might be stored on the remote ftp server or somewhere in the web. Those files have to be copied to the location where the can be accessed by the ETL tool.

Some ETL Tools offer complete automation of business processes including full support for file operations

Extraction.

SQL can be used to extract the data from the database or text parser to extract the data from fixed width or delimited files.
Extracting data often involves the transfer of large amounts of data from source operational systems. Such operations can impose significant processing loads on the databases involved and should be performed during a period of relatively low system load or overnight.

Cleansing and Transforming data.

Data cleansing is a process of checking data against predefined set of rules

For example:

• Checking date formats
• Checking field length
• Pattern validation
• Data type checks

A lot of data transformations can be performed during the process of extracting data from the source systems. However, there are often some additional tasks to execute before loading the data into the data warehouse. For example, reconciling inconsistent data from heterogeneous data sources after extraction and completing other formatting and cleansing tasks and generating surrogate keys.

Some typical data transformations include:

• Joining multiple fields into one field (Address 1 + Address 2 + Address 3).
• Generating calendar tables.
• Mapping data from one representation to another, such as Female to 1 and Male to 0
• Transforming data from multiple representations to a single representation, such as a common format for telephone numbers
• Creating surrogate keys

Loading the Data

Once all the data has been cleansed and transformed into a structure consistent with the data warehouse requirements, data is ready for loading into the data warehouse.

The initial load of the data warehouse consists of populating the tables in the data warehouse schema and then checking that the data is ready for use.

After the data has been loaded into the data warehouse database, verify the referential integrity between dimension and fact tables to ensure that all records relate to appropriate records in other tables. DBA should verify that every record in a fact table relates to a record in each dimension table that will be used with that fact table.

Data integrity in the reverse order is not necessary, however in some cases it might be necessary to remove unrelated data. For example when a dimension table has several times more records than the fact table

Dealing with null and empty strings.

Most queries that retrieve data from the data warehouse use inner joins between the fact and dimension tables. If you use a dimension table containing data that does not apply to all facts, you must include a record in the dimension table that can be used to relate to the remaining fact table values. For example, for null value 0 can be used as surrogate key of dimension table and for empty string 1.

To verify referential integrity in a star schema simple SQL query can be used to count the rows returned when all appropriate dimension tables are joined to the fact table using inner joins. The number of rows returned by this query should match the number of rows in the fact table. With large data warehouses it might have some performance implications and should be executed outside of normal working hours

More Information:

• Advanced ETL Processor
• Visual Importer

Star Schema Optimisation

This article describes the ways to optimize star schemas and making your data warehouse run faster

This article is not database specific

Basic Star Schema of a retail shop

In the previous article we have described the ways of calculating SQL query execution time.

Let's think about what can be done about Query performance.

We have only two options

• Improve hardware
• Make the database server do less work

Improving Datawarehouse hardware

There are a lot of ways of improving hardware. You can add memory processors increase your hardrive size etc. But quite often it does not help

We have seen some configurations with 64 gigs of memory and 32 processors

With all 32 processors doing nothing but the database is running very slow.

All this because the DBA decided to use raid 5 for better protection.
The performance of you disks this the most important part of the data warehouse.

It defines it. It tells how long do you have before the system becomes unusable due to pour performance

HDtach is very useful utility for checking disk performance, you would be very surprised when compare the laptop with the servers. Modern laptops quite often much faster.

The bottom line is:

• HDD performance is the key 
• Do not use RAID 5 use RAID 10

Making the database server do less.

Let's go back to the Star Schema Diagram

STATE CODE field is 5 bytes long
Everybody knows that the US state abbreviation is always two characters long

if we change our definition from nvarchar(2) to nchar(2) field size changes from 5 bytes to 4

Next well know thing, that there are no international characters in US state abbreviation. There is no need to use Unicode and we can change field type from nchar(2) to char(2)

The field length is 2 bytes now.

Everybody knows that there are only 52 states in US

What we can do next, is to move STATE CODE out of the fact table in to the dimension and use STATE_ID BYTE in fact table as state identifier.

By using common sense we were able to reduce the field size five times. The same technic can be applied to the rest of fields.

Conclusion

Make sure your disk fast and fact table record size as small as possible

Datawarehouse Performance

This article describes the ways of estimating data warehouse performance

Ever wonder why it takes so long to execute the Query? It works fine now but how long will it take to run in 3 years?

This article is not database specific

Below is the basic Star schema of a retail shop
It has one fact table and 3 dimensions

Let's have a close look at the Fact table structure and particularly fields types: 

The STATE_CODE field: The type is nvarchar most likely it it uses 2 bytes to store one character, plus it has nil as terminator so in total it is 5 bytes

Some of the databases store the length of string, which could take up to 2-4 bytes

Lets do the same for the rest of fields

Field Name	Field Length
STATE_CODE	5
PRODUCT_CODE	21
PERIOD_CODE	13
AMOUNT	6
Total row size	45

So how long it will take to run the following query?

Select STATE_CODE, PRODUCT_CODE, PERIOD_CODE, Sum(AMOUNT) as  AMOUNT
From FACT_TABLE
GROUP BY STATE_CODE, PRODUCT_CODE, PERIOD_CODE

This sql will use the full scan of the table: EG it wiill read entire table and calculate summary  

To answer this question we need to know our HDD performance and number of rows in the table

HDtach can easily measure the hard drive performance

Let assume that we have 10 million rows and our RAID can do 50mb per sec sequential read

450 000 000 / (50 *1048576) = 8.58 sec

Those people who have actually run the queries against the table with 10 million records would say that this number is far too optimistic.

• The actual row size is much bigger
• It also holds some additional data such as row CRC or rowid's
• Plus data is stored in blocks and they do have some additional data as well
• Plus the table itself stores some additional information.
• HDD performance in not realistic as well in multi-user environment

So what is the best way to estimate the time taken to execute the query?

We believe that practical aproach gives the best results:

• Create table
• Populate table with some dummy data
• Count number of rows
• Get actual table size using database specific functions

SQL Server provides a built-in stored procedure that you can run to easily show the size of a table, including the size of the indexes. Which might surprise you.

Syntax:

sp_spaceused ‘Tablename’

Than use the formula table size/hdd performance to calculate the time
and the last step ran the sql during peak times and low times
and compare the results.

If you have historical data this approach would also help you estimate how long will it take to run the SQL in 3 years.