Dimensional Modeling
Krzysztof Dembczy´nski
Intelligent Decision Support Systems Laboratory (IDSS)
Pozna´n University of Technology, Poland
Bachelor studies, seventh semester
Academic year 2018/19 (winter semester)
1 / 48
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Guidelines and tips
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
1 / 163
This page cannot be seen from the preview
Don't miss anything!
Related documents
Partial preview of the text
Download Dimensional Modeling and more Schemes and Mind Maps Business in PDF only on Docsity!
Dimensional Modeling
Krzysztof Dembczy´nski
Intelligent Decision Support Systems Laboratory (IDSS) Pozna´n University of Technology, Poland
Bachelor studies, seventh semester Academic year 2018/19 (winter semester)
Review of the Previous Lecture
- Processing of massive datasets.
- Evolution of database systems: I (^) Operational (OLTP) vs. analytical (OLAP) systems. I (^) Analytical database systems. I (^) Design of data warehouses. I (^) Relational model vs. multidimensional model. I (^) NoSQL. I (^) Processing of massive datasets.
Outline
1 Motivation
2 Conceptual Schemes of Data Warehouses
3 Dimensional Modeling
4 Summary
Motivation
- (^) University authorities decided to analyze teaching performance by using the data collected in databases owned by the university containing information about students, instructors, lectures, faculties, etc.
Motivation
- (^) University authorities decided to analyze teaching performance by using the data collected in databases owned by the university containing information about students, instructors, lectures, faculties, etc.
- They would like to get answers for the following queries:
Motivation
- (^) University authorities decided to analyze teaching performance by using the data collected in databases owned by the university containing information about students, instructors, lectures, faculties, etc.
- They would like to get answers for the following queries: I (^) What is the average score of students over academic years?
Motivation
- (^) University authorities decided to analyze teaching performance by using the data collected in databases owned by the university containing information about students, instructors, lectures, faculties, etc.
- They would like to get answers for the following queries: I (^) What is the average score of students over academic years? I (^) What is the number of students over academic years? I (^) What is the average score by faculties, instructors, etc.?
Motivation
- (^) University authorities decided to analyze teaching performance by using the data collected in databases owned by the university containing information about students, instructors, lectures, faculties, etc.
- They would like to get answers for the following queries: I (^) What is the average score of students over academic years? I (^) What is the number of students over academic years? I (^) What is the average score by faculties, instructors, etc.? I (^) What is the distribution of students over faculties, semesters, etc.?
Example
- An exemplary query could be the following:
SELECT Instructor, Academic_year, AVG(Grade) FROM Data_Warehouse GROUP BY Instructor, Academic_year
- And the result:
Academic year Name AVG(Grade) 2010/11 Stefanowski 4. 2011/12 Stefanowski 4. 2010/12 Slowi´nski 4. 2011/12 Slowi´nski 4. 2011/12 Dembczy´nski :)
Motivation
- (^) Problem to solve: How to design a database for analytical queries?
Conceptual schemes of data warehouses
- (^) Three main goals for logical design: I (^) Simplicity: - (^) Users should understand the design, - (^) Data model should match users’ conceptual model, - (^) Queries should be easy and intuitive to write. I (^) Expressiveness: - (^) Include enough information to answer all important queries, - Include all relevant data (without irrelevant data). I (^) Performance: - An efficient physical design should be possible to apply.
Three basic conceptual schemes
- Star schema,
- (^) Snowflake schema,
- (^) Fact constellations.
Star schema
- (^) Measures, e.g. grades, price, quantity.
Star schema
- (^) Measures, e.g. grades, price, quantity. I (^) Measures should be aggregative.