Tag Archives: ETL

Data warehouse – Loading data into a dimensional model – High Level – simplified.

Data warehouse – Loading data into a dimensional model – High Level – simplified.

This is a big topic and I know that there are several ways of doing this.
I am going to discuss how I like to do it.

We split the environment into Stage and (DWA/ODS) tables on the actual database platform. This can be any databasae ie Oracle,SQL server even Teradata.
Two separate databases – I will tell you later why.
Also make it a standard that you create the tables with schemas ie USA. Customer_Flat and USA.Customer_STG so that if the business grows then you support multiple countries this can even be regions depending on your business. – will list positives when splitting it by schema later.

Stage contains two sets of tables for each file(Source test file etc) coming in
Let’s say the file name is Customer then you will have Customer_Flat and Customer_STG where the Flat table only consists of string fields no type definition and on the STG table we have type definitions.
We apply type check rules on the Flat table and update the record to the correct type or mark it as error In some cases we even do MDM (Master Data Management Data) lookups and update the columns. MDM is important when you work across countries, business and operational systems etc.work with this from day one in your project – later it will more complex and it will cost you more.
We will discuss MDM definition later on the Blog for now see it as lookup values.

So the next step after this is to insert the correct values onto the STG table this is a select into from the flat table because all types are now fixed (Create one auto script on db level for this so that you never do this again (use table defs etc) I will blog how to do this later).
Now finally run other rules/check data changes on the STG table preparing it for the next insert into the fact or dimension ODS/DWA.

Note :
All data is tagged with a BatchID so that we can track it back to the source files.
I will describe the BatchID methods in a later blog and how that works for me.

Dim and Fax load Method

Dim and Fax load Method

Now we have the data in a structured database with type format for insertion to the DWA/ODS.
Firstly we populate all the dimension(s) tables – this is with type/1/2/3 etc
then the fact table be selecting the relevant key from the dimension and inserting it into the fact table. It’s a simple formula always starts outwards inwards meaning the dimensions first then the fact table. See picture.

Inner_Outer Tables

Inner_Outer Tables

In some cases if you are unable to get the actual Dim Key then use a default (ie -1) and insert the fact record we do not want to leave records out of the fact due to missing dimension values you can always update the fact later with the correct key. This happens often when you are missing a MDM lookup value due to business process waiting to define it for IT. Do not lose the data, set a default and inform business of the missing values asap. Create BI alert reports for this.

Also remember to use you own key generator when you create the dim surrogate keys when populating data.

Why two (2) separate databases for the staging and the actual ODS/DWA.

  • Data can be loaded and processed (Load steps) on stage without impacting the ODS database.
  • Data can be left on stage then later processed to ODS when ODS loads are minimal with minimum impact to users- Remember that the ODS.DWA belongs to business not IT.
  • Database backups can be done separately. Again avoiding impact.
  • You can store the databases on different Server/Drives for speed and redundancy etc. Technical consideration.
  • In some cases you have multiple countries (Businesses) and data missing for one then loads must be stopped and not loaded to ODS until all data is in stage (Per business requirement) this helps with that and well you can process separately depending on the requirements.
  • ETL standard can be implemented on both the stage and a deferent set on the ODS/DWA.
  • List a few more but I can think of more – your turn.

Splitting your Staging into Schemas for each table why?

  • Some databases allows you to place each partition on separate drive (gaining speed on disk read)
  • Easy identify the country/region for which you load data simply by looking at the table name.
  • More control over your data loads and technical architecture.
  • In some DBs you can even backup a schema.
  • You may have your own list – so share with us..

I know you have negative on both the splitting of the stage and the schema this can be another discussion later. Write them in the comment section then I will list them in this blog article and we can start that blog post..

{Views and opinions on this Blog does not reflect current/past employers view(s).}

Data Warehouse – One – Enterprise ETL Yes or No

So the question here is should you have one enterprise ETL team and one ETL infrastructure for your entire enterprise/company/corporation

In my opinion Yes as soon as possible, but as always there is a but.

You can only have this is if you have standards in place (Or you are creating the standards with your first project) and when you have maturity in the team /enterprise to support and maintain this.
You need to clearly define the tools that you use for ETL – decide on your etl partner this partner is there to stay.

Create a process that can be re-used for each following etl job.

OK so what am I saying “re-usable etl job” – basically all ets job should for example support

  1. Batch_Run_id – in order to trace when data was inserted it should
  2. Batch_Date
  3. County Code – enterprises solution must scale across countries/States.
  4. Using the same Error table for logging
  5. Using the same metadata layer
  6. Using the same audit function
  7. Be re-executable.
  8. Be traceable – ie when where did it fail.
  9. If you use surrogate keys then all must use the same method to derive the key.
  10. Create components small components that can be re-used.

We can go on and on but you get the picture.

So what is your opinion?