I am in process of designing a Data Warehouse Architecture. While exploring various options to Extract data from Production and putting into Data Warehouse, I came across many articles which mainly suggested following two approaches -
- Production DB ----> Data Warehouse (Star Schema) ----> OLAP Cube
- Production DB ----> Staging Database ----> Data Warehouse (Star Schema) ----> OLAP Cube
I am still not sure which one is the better approach in terms of Performance and reducing processing load on Production database.
Which approach you find better while designing Data Warehouse ?
Below points are taken from, DWBI Organization's article
Staging area may be required if you have any of the following scenarios:
Performance and reduced processing may not be only considerations. Adding a staging may sometimes increase latency
(i.e. time delay between occurrence of a business incidence and it's reporting). But I hope above points will help you to make a better judgement.
ETL = Extract, Transform and Load. Staging database's help with the Transform bit. Personally I always include a staging DB and ETL step.
A Staging database assists in getting your source data into structures equivalent with your data warehouse FACT and DIMENSION destinations. It also decouples your warehouse and warehouse ETL process from your source data.
If your data warehouse destination tables pretty much map your production DB tables with only some additional dimension fields then you could get away with ignoring the Staging Database. This will save you a little development time. I don't recommend this as you:
Most likely though you will be performing some sort of data manipulation (converting dates to DATE_DIM keys, aggregating values) in which case a staging DB will help you separate your transformation logic and calculations from your data warehouse operations (dimensioning data).
You may have also come across this sort of pattern:
[PROD DB] -(ETL)-> [RAW DB] -(ETL)-> [STAGING DB] -(ETL)-> [DW DB] -(ETL)-> [DM DB]
which if performance considerations are important you may want to look at. In your case the RAW_DB could be an exact 1:1 copy of your production database and the ETL step that creates it might just be a recreate the DB from the most recent nightly backup. (Traditionally RAW_DB was used for getting data from various external sources with each field as pure text, these fields where then converted to their expected data type with exceptions handled as encountered. This is not so much of a problem when you have one source and its a nice strongly typed normalized database)
From this RAW_DB the next ETL process would truncate and populate staging such that the STAGING DB contains all the new/updated records that are going into the warehouse.
Another added benefit of all these steps is that it really assists in debugging weird data as for any given run you can see record values inside each of the difference databases and identify which ETL process is introducing the sadness.
There are a few potential advantages of using an intermediary staging database, which may or may not apply to your situation. There is no perfect, one-size fits all solution. Some of the potential advantages include:
There are possible disadvantages too, which may or may not matter to you. Chief among these is having to have another database server. A lot of the advantages could be meaningless if you are using the same server to host the production and/or data warehouse databases.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With