Design Approach

Our approach has evolved over years of architecting, designing and implementing business and organizational databases to support Business Intelligence and Analytics usage.

We've led the design and implementation of over 75 data warehouses over the past 26 years and have taught over 1,000 people our design approach. The design efforts we have led have held up over the years and we've had very positive feedback from the students we have trained. Some saying that it is the only approach in their experience that has been truly successful.

We architect a hybrid design between 3nf and dimensional. We keep master tables / dimensions normalized in the staging area but do create flattened dimensions at the different levels of the hierarchies to support better performance. We try to keep the schemas as clean as possible for the database optimizer to make optimal access path decisions. We enhance dimension tables as required to better support business users and to provide single dimensions that support different departmental or business unit requirements without requiring separate data structures or marts.

Transaction and event tables are highly normalized and stored once. They often have some slight de-normalizations to support ease of use and performance while preserving maintainability. We also create additional structures as needed for analytical requirements that are a level up from the detail and use materialized views when available in the DBMS to support summarized data transparent to BI tools or SQL users.

Essentially we model based on a combination of source data realities and business user requirements.

We organize the Analytic Data Store by business process area and create global (conformed) dimensions that are used across business areas. We prioritize the implementation of the process areas by business value and complexity. We like to start with a process area of high business value yet reasonable complexity in order to fine tune and standardize the development process and the overall approach. ETL (Data acquisition, preparation and integration) has to be designed to fit the model and support it. Achieving the simplicity of presentation necessary for a design to be successful with the business users takes extra effort but pays off in dividends in the end.