INFO 340 Lecture Notes - Lecture 8: Data Warehouse
INFO 340 Lecture 8
- 3 types of slowly changing dimensions
o Type 1 – changed dimension attribute is overwritten
▪ Gain clarity but lose history
o Type 2 – changed dimension attribute causes new dimension record to be
created
▪ Old row + new row
▪ Maintain history by running reports on
▪ Drawback – need to add new row for everything; double database size
o Type 3 – changed dimension attribute causes alternate attribute to be created
▪ Both old and new values of attribute are simultaneously accessible in
same dimension record
- Snowflake model
Review
- What is a data warehouse?
o Long-term, strategic approach
▪ What are our best products/suppliers
▪ What might we want to change
- Why?
o Combine external data with internal data to gain insight into sales, products,
customers, etc.
o Greater understanding of business space
- How?
o Find patterns with how people behave
o Make accurate predictions for the future
Database maintenance – routine work to keep system running
Know the Database Environment
- Transactions
o How does data come into system, how often, what objects are affected
- Dataflow
o How is data modified once in the system?
o When is data leveraged, and when do modifications occur?
o Who depends on these updates?
Document Summary
What is a data warehouse: long-term, strategic approach, what are our best products/suppliers, what might we want to change. Why: combine external data with internal data to gain insight into sales, products, customers, etc, greater understanding of business space. How: find patterns with how people behave, make accurate predictions for the future. Database maintenance routine work to keep system running. Transactions: how does data come into system, how often, what objects are affected. Customers: who are they, what are their needs, what are the most urgent data/processes. Includes: logins, stored procedures, data types, constraints, functions, triggers. Skill of dba staff: who"s (cid:271)est suited for ea(cid:272)h task, opportunities for strengthening, aspirations, vulnerabilities. What needs to be done to keep running: define events that will impact database environment in absence of maintenance, e. g. , hard drive fills up, job dependencies. Better we understand database environment allows us to define risks and take action.
