You cannot build the house without foundations
A long time ago (in 1998, to be precise), I went to the SAS European User Group conference in Hague, where Bill Inmon was a keynote speaker, and that was the first time I have seen him present. He said in this lecture something very important that I have remembered: “You cannot build the house without foundations. I have never seen beautiful foundations – they are always a lot of mud, steel, and concrete. But only on good foundations can you build a good and beautiful house.”
The same logic applies for Data Warehouse – if you want to build a good Data Warehouse, you need to have good foundations. In the Data Warehouse context, foundations are the master data that business needs, that needs to be modeled right and cleansed. Master data include all the data about time periods, locations, product and service catalogs, involved parties, and types of events and statuses.
After that, you will build the ground floor of your house where you spend most of your time doing daily business or your Data Warehouse’s base layer. That is the detailed data that you will load from your source systems or external sources, integrate and cleanse to fit in your data model. That will include detailed data about your customers, their accounts and transactions, invoices and payments, and financial data.
Finally, you will build your top floor where you will have your private area, and that is derived data layer in your Data Warehouse. In this area, you are creating derived data marts or data sets that combine data from various parts of foundation and base layers, enabling you to do advanced analytics like profitability and risk calculations or predictive models about customer behavior or calculate KPI’s.
You can see a sample organization of the Data Warehouse model for a bank in the following image:
When creating the model, you will use entities and relationships represented in your Data Warehouse as tables and primary-foreign key relationships. You can think about entities as rooms where you can store your things, and relationships are like doors connecting those rooms. Every room has one or more doors connecting the room with other rooms, and you will use those doors to move and communicate between rooms. Each room has its own functionality – in the same ways entities are representing different business subjects.
Within the big house, rooms are used to support different functionalities, such as dining, living room, guest rooms, or bedrooms. In the same way, entities and relationships are grouped in Subject Areas that cover the specific business area requirements – Sales, Inventory or Contact Center. In the model image above, subject areas are represented as small boxes.
In the next blog, we will discuss more about model building blocks and modeling patterns used for model design. At some point later, we will also discuss the benefits of implementing a standard industry Data Warehouse model.
