BeyeBLOGS | BeyeBLOGS Home | Get Your Own Blog

« How to design if many to many relationship exists between dimension and fact table | Main | How to use Oracle's Metadata package for impact analysis »

February 17, 2007

Data Staging Strategy considerations

By: Milind Zodge

Whenever we start designing a Data Mart/ Data Warehouse environment first thing comes is staging area. In this article I am focusing on what different types of Data Staging Techniques are there and how to define a Data Staging Strategy.

While defining the strategy you will have to focus on
What technique you will be using?
What kind of data load it will be, full data or incremental?
Where the staging data will reside?
Where should aggregation be performed?

There are following well-known techniques are available:
1. Store and Forward: In this technique, a data is stored in staging area and then used for transformation and loading into Data Warehouse environment
2. Direct Database insert/update: In this technique, a data is directly read from ODS and directly will be inserted or updated in the Data Warehouse environment

There are following Data Load types:
1. Full Data: Here you use all the rows and update Data Warehouse environment with the data. This is time consuming process and the processing time will gradually increase because of data growth rate
2. Delta or Incremental: Here you only get the changed/new records and you process these records so that information is passed to Data Warehouse environment

Types Staging Data Stores:
1. File: Data can be placed in File. If more sorting operation needs to be performed then storing data in this format is beneficial
2. Table: Staging data can be stored in the table either permanently or for some time till it gets published to the Data Warehouse area

Where to perform aggregation: If the aggregation is required by the Data Warehouse process. It can be either performed while loading the stage area or loading Data Warehouse area. Decide where you want to perform this.

Thus considering all the sides you can prepare a good Data Staging Strategy.

Please refer to "Data Strategy" book by Sid, Larissa and Majid for more details.

Posted by Milind Zodge at February 17, 2007 8:45 PM


Post a comment

Remember Me?