Staging tables structure?

[jcoira]

Hi,

My OLTP systems logs every change so when transferring daily information from OLTP to my staging area I only transfer those records which suffered any changes.

But I have a concern about staging area tables structure. Should tables in SA have a normalize or a denormalize structure? In the first approach I can have a set of tables with the same structure as my OLTP system and this could spped up my cleansing and surrogate key processes, while with the second one my SA tables will look more like the dimensional tables.

Which are the pros and cons of each approach?

Thanks,

// jcoira

 

[kishleo]

yeah its better you have the same structure as your source in staging area, actually in staging area you there is no need to normolize or denormalize the structures, just maintain it as source structure later you can have and ODS area where you can cleanse data from staging area and store in ODS area here you can have in normolized format or denormalized depending on your usage and how you want to implement your data warehouse. better to have data in ods in normalized format ô¿ô Kishore

 

[jcoira]

Hi,

But the drawback I see with the approach of having so many "set of tables" is that I have guarantee the quality of data in all those tables and therefore the audit process could be more difficult to implement.

What do you think?

// jcoira

 

[kishleo]

yes you are right, is your data warehouse large? is it an enterprise data warehouse? or just a couple of data marts?
do you have ODS layer? or just directly moving data into fact tables and dimensions from the staging area
can you tell me clearly ô¿ô Kishore

 

[jcoira]

Well actually Im involved in the enterprise data warehouse. I would like to use surrogate keys, SCD and some other techniques. I don't know whether I should use an ODS layer or not. Also I need to know the structure of such an ODS layer in case I use it. Could you tell me a bit about that?

Thanks,

// jcoira

 

[kishleo]

yes, actually when it comes to EDW everyone prefers to have an ODS, it is also a Data warehouse but which is much related to your source system, and in normalized form and cleaned data, if your data is comming from different sources you can combine data into similar tables from different source, once you build the ODS it will feed your Fact tables and dimensions, and also in some cases you can drill down to ODS level to get specific information.
if you plan to use ODS layer, you can just dump your source data into staging area first without any transformations, then while you tranfer data from staging area to ODS area you can cleanse and do your ETL process. in this case even you do not need any keys on your staging area, this helps you in incremental loading of your data warehouse. ô¿ô Kishore