615644 Data Warehousing

1
615-644 Data Warehousing
Week 4 Data Warehouse Design Kimball and
Breslin
2
KimballER Models

• End users cannot understand or remember an ER
  model
• Software cannot usefully query a general ER
  model
• Use of an ER model is not intuitive and does not
  support high-performance retrieval of data

3
KimballDimensional Models

• Data is represented in a standard, intuitive
  framework that allows for high-performance access
• Highly recognisable to end users in the business

4
KimballDimensional Model Strengths

• Predictable, standard format
• Withstands unexpected changes in user behavior
• Gracefully extensible to accommodate new design
  decisions
• Standard approaches for handing common modelling
  situations
• Slowly changing dimensions etc.
• Growing body of software tools that use aggregates

5
KimballDimensional Modelling Myths

• Dimensional models lead to stove-pipe systems
• No one understands dimensional models
• Dimensional models only work with retail
  databases
• Snowflaking is an alternative to dimensional
  modelling
• Dimensional models only work for certain kinds of
  single-subject data marts

6
KimballER Models

• ER models data rather than a business
• ER models are wildly variable in structure
• The variability leads to the need for custom,
  hand-written and tuned SQL
• ER models should be used for OLTP systems and
  back-room data cleaning and combining

7
BreslinKimball and Inmon Approaches

• Bill Inmon
• Top-down approach that adapts traditional ER
  andrelational database tools
• Data warehouse and operational database are
  integrated into a single data resource
• Evolutionary - extends existing approaches
• Primary audience is IT professionals

8
BreslinKimball and Inmon Approaches

• Ralph Kimball
• Bottom-up approach using dimensional modelling
• Data warehouse has unique requirements that are
  different from operational databases
• Revolutionary - replaces existing approaches
• Primary audience is end user

9
BreslinComparing Kimball and Inmon

• Kimball and Inmon similarities
• Similar time-stamped data
• Similar extract, transform and load (ETL)
• Kimball and Inmon differences
• Development methodologies and architectures
• Data modelling
• Philosophical - IT and end user focus

10
BreslinChoosing the Best Approach
Characteristic
Favours Kimball
Favours Inmon
Org DSS requirements
Tactical
Strategic
Data integration requirements
Individual business areas
Enterprise-wide integration
Structure of data
Non-metric data and data for multiple and varied
information needs
Business metrics, performance metrics and
scorecards
Need to adapt to highly volatile needs within a
limited scope
Growing scope and changing requirements are
critical
Scalability
Source systems are relatively stable
High rate of change from source systems
Persistency of data
Staffing and skills requirements
Small teams of generalists
Larger team(s) of specialists
Time to delivery
Organisations requirements allow for longer
start-up time
Need for the first data warehouse application is
urgent
Lower start-up costs, with each subsequent
project costing about the same
Higher start-up costs, with lower subsequent
project development costs
Cost to deploy
11
Breslin Conclusion

• Each approach has its strengths and weaknesses
• Organisational characteristics may help determine
  the most appropriate approach
• More important issue may be the soft skills of
  the data warehouse team
• Leadership, communication, planning and
  inter-personal relationships