Back in 2007 when I first started working with ETL processes and building Data warehouses, it was a steep learning path. Setting up a Data Warehouse (DWH) required plenty of planning and project management before the actual development could start. There were various steps that lead to a functional DWH capable of servicing the business needs.
To name a few
- Create Source to Target Mappings including business rules and logic.
- Create a naming convention to be used throughout the DWH.
- Document the business logic as you go on building the DWH.
- Generate and store Surrogate Keys.
- Decide on what goes in SCD 1 and SCD 2.
- Design the architecture, whether it would be Star or Snowflake or just some form of denormalized data.
- Deciding on the load frequency.
- Finally loading it into target data model.
It is estimated that in a normal Business Intelligence (BI) project, close to 80% of the time and budget is spent on setting up the DWH. It is important to understand here that a solid DWH architecture and design sets up your entire BI project for success. Any critical failures or misunderstandings in the design and architecture of DWH can have serious business consequences. Considering these factors automating your DWH implementation is a step that every company would like to invest in.