Blog: William McKnight

« Management Phobia in Business Intelligence | Main | Focus: Disaster Recovery »

Source System impact on DW/BI data model

Today, I'm thinking about building the DW/BI data model and the various approaches builders will take on the relationship to the source system(s). This is one of about 35 different aspects of a DW/BI methodology that are often effected in a shop in de-facto fashion, and often suboptimally. With this entry, I'd like to point out the importance, not only of this decision, but of making all these decisions in a heads-up fashion.

Theoretically, the source system(s) should have little impact on the DW/BI data model. However, let’s take a look at real world data warehouse model building strategies.

Data warehouse data models are built with one of 3 strategies in mind...

1. Mimic the operational data model(s) completely. This saves time in the modeling process and you know that you are building something for which you will have the data for and you know where you’ll get the data. With this approach, you solve one problem you are probably having and that has to do with limited query access to operational systems.
2. Model the data warehouse based entirely on user access requirements irrespective of the source systems and the availability of data. This will create a data model that is fit-for-purpose and possibly technically elegant, but the data acquisition piece will need to then be tied in with the modeling effort. Since the source data has not been considered, the mapping effort may be long and there may be unrealistic constructs in the model.
3. Model the data warehouse based on user access requirements but with knowledge of and an eye on the source systems. Data acquisition design is a parallel activity in this strategy with source systems identification and analysis having previously been done so the modeler understands what is available and doesn’t stray beyond the bounds of what is available during the modeling effort.

I recommend the third approach for DW/BI modeling efforts. This strategy strikes the best balance between designing for user access and building that which you can reasonably populate. This illustrates the need for a close working relationship between the data modeler(s) and the data acquisition programmer(s), whose work eventually needs to be brought together.

Share: