When loading data from source system into data warehouse, we can "dump"
the data as quickly as we can into stage as is, for later processing; or we
can transform the data on the fly and populate the data warehouse directly from
the source system. Both approaches have their own advantages and disadvantages, so let us look
First of all, if we transform and process the data on the fly, the data extraction
process would take longer. Especially if we are doing upsert, e.g. update or
insert depending whether the record already exists or not in the data warehouse.
The time window on the source system may not allow us the leisure that we desire.
For example, the source system could be doing a nightly batch from 8 pm to midnight,
followed by backup process from midnight to 3 am, and the time slot allocated
for the data warehouse to extract data from this source system is 4-5 am in
the morning. Hence we want to connect, extract as quickly as possible then disconnect.
In some data warehouse implementations, we extract data from the source systems
at certain intervals continuously throughout the day, for example: every hour.
An example of this implementation is a order processing warehouse system for
a multinational company to enable corporate customers to analyse their orders
using a web application. The source systems are 8 different ERP systems throughout
the globe and we want to minimise the burden to the source systems. Therefore
the same principles apply: we want to connect, extract as quickly as possible,
then disconnect from the source systems.
So that is the first reason why we want to stage the data: time. The second
reason, which could be more important, is the robustness, or reliability of
the data warehouse system. If the download process failed, we would like to
be able to recover and start again. Consider these 2 scenarios. Scenario 1,
using SSIS we download from a DB2 on a mainframe, transform the data (key maintenance,
etc) and upsert into a star-schema data warehouse. Scenario 2, an RPG program
extracts the DB2 data into flat files, then we use SSIS to upload the data into
our data warehouse. Which of these 2 scenarios are more reliable, e.g. if in
the middle of the process a) we lost network connection to the mainframe, or
b) there was a power cut. Which scenario has higher chance of recovering and
continue the process again when the network or electricity is up and running
The source data is web of inter-related files or tables. For example, we may
have order file, product file, customer file, etc. In scenario 1, we may be
download+upsert orders when network connection to the mainframe breaks. At this
time, there could be orphanage in the data warehouse, e.g. order records without
corresponding customer or product records. In scenario 2, we may be downloading
orders from source system into flat files when network connection to the mainframe
breaks. And the data integrity of our data warehouse is intact because those
new orders are not in the data warehouse yet. Suppose in scenario 2 the power
cut happens when all new order, customer and product records have been extracted
into stage. When we upload those 3 entity into our data warehouse, the power
cut happens. If we wrap the upsert process in a transaction, no problem! The
data integrity of our data warehouse is still intact. Because it is a transaction,
it's either all or nothing.
The third reason of staging the data is to decouple the source data extraction
process and the data warehouse update process. For example, the data extraction
process could be happening every hour and it takes 2-3 minutes every time. Whilst
the data warehouse update (including the cube update) could be taking place
4 times a day: at 7 am, at 12 noon, at 4 pm and at 8 pm, taking 1 hour each.
(The data warehouse downtime is not 1 hour, but minimised to 1 minute because
we update is a copy of the warehouse DB, then when it's finished we rename
the database). As we can see in this example, the advantage of decoupling
the 2 processes is that they can be set to execute at different intervals.
The source systems data extraction process itself could be happening at different
intervals. Some data entities such as orders could be happening every hour,
some entities such as delivery vehicles and currency rates could be happening
only once a day and some entities such as performance targets could be happening
only once a month. It all depends on the nature of the data: some data changes
every hour of the day including week ends but some data is only updated once
a month! If we don't stage the data, how could we cope with this nature, different
data coming at different intervals? Yes there is a work around, but it would
be easier if we stage the data.
The fourth reason - easier development. If you would like to outsource the
development of your data warehouse (to India for example), you could either:
a) allow the outsource company to connect to your source system (and it could
your main ERP system we are talking about here), or b) give them DVDs (or LTO
tapes) containing stage files (or database). In scenario a, you will want to
give them access to your development ERP system. Believe me, you wouldn't
want to give them access to your production system. There are a long list of
reasons for this, among other things are: 1) risk of main ERP system downtime
because of 'slightly incorrect query' being put by them, resulting in Cartesian
explosion, 2) at 11:05 am 30 ERP users could be phoning helpdesk because of
'slow response time' - you found out later that they were testing an SSIS package
which uploads last 3 months orders into data warehouse - this package finishes
running at 11:17 and those users say that the system is 'quick and responsive'
In scenario a, your network colleague may be saying "err - it is against
our company security policy to allow a 3rd party to VPN into our network",
or similar. Or even worse: "Allow TCP port 3389 in the firewall? Are you
joking? Haven't you heard about denial of service because of remote desktop protocol?
I'm sorry this request is absolutely out of question (in the name of security)".
All I'm trying to say here is: if you don't stage your data, you are in scenario
a. You need to allow the outsource company to connect to your source system,
with all the issues. Usually you will end up with scenario c: bring the outsource
company into your building. In other words, find a local data warehousing company.
If, on the other hand you stage your data, then you are in scenario b (the DVD
or LTO tapes scenario), have them to sign confidentiality agreement, and ...
your data warehouse project is up and running!
So far it's all about advantages of having a stage. What are the advantages
of not having a stage? Well, one of them is simplicity. Within a single SSIS
package you move the data from source straight to warehouse, with the key maintenance
and all. Secondly, the overall processing time is shorter.
Just a very brief note on what and where a stage is: the stage could be a file
system, or it could be a database. It could be on the source system RDBMS database
format, or it could be on the data warehouse RDBMS database format. For example,
if your source system is running on Informix on Unix platform and your data
warehouse is on Microsoft SQL Server, you have 4 choices about where to create
the stage, each with their own advantages: 1) On Informix as a database, 2)
as flat file system on Unix, 3) on Windows server as text files, or 4) as a
database on SQL Server. Generally speaking, the closer the stage to the source,
the shorter the extraction time. For a full discussion on this topic (advantages
and disadvantages of each of the 4 options), I will have to write a separate
article. It's not the kind of thing you can explain in a paragraph, unfortunately.
There is a 5th choice for the above scenario (it's uncommon, and has its own
disadvantages, but it is possible): the stage could be in memory (RAM).
6th March 2006