DimModel.pptx - Dimension Modeling MSBA 6320 Prof De Liu 1...

Info icon This preview shows pages 1–12. Sign up to view the full content.

View Full Document Right Arrow Icon
Dimension Modeling MSBA 6320, Prof De Liu 1
Image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Agenda The Need for Different Designs Dimensional Model Components Star Schema vs Snowflake Schema Additional Modeling Concepts Data Warehouse Design Processes 2
Image of page 2
The Need for Different Designs Transactional systems Designed to allow for quick transactional processing and efficient storing of data. To accomplish this, designers typically use some type of normalization. Most strive for “Third Normal Form”. Analytical systems Designed to extract and query data quickly Access speed is the main concern Hence, normalization which is widely used for transactional databases, is generally not appropriate for data warehouse design Design should reflect multidimensional view 3
Image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
The Problem Transactional models, while efficient for transaction processing, are not good for analytics 4 How do we determine the average grade in biology in a given semester?
Image of page 4
The Solution It would be nice to organize the data to be pulled out efficiently. 5
Image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Agenda The Need for Different Designs Dimensional Model Components Fact Grains Dimensions Fact table styles Star Schema vs Snowflake Schema Additional Modeling Concepts Data Warehouse Design Processes 6
Image of page 6
Dimensional Modeling – Fact Table Fact Table Primary table which stores the performance measurements of the business The term “fact” refers to measurements Each row corresponds to a specific measurement incidence Each measurement is taken at the intersection of all the relevant dimensions (e.g., day, product, and store). Most facts are numeric (including binary) Fact table tends to be long and narrow A few dimensions and facts columns But fine grained, so many rows 7 Dimensio ns facts
Image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Grains Grain conveys the level of detail associated with the fact table measurements. It provides the answer to the question, “how do you describe a single row in the fact table?” Examples of grain declaration One row per scan of an individual product on a customer’s sales transaction One row per line item on a bill from a doctor One row per individual boarding pass scanned at an airport gate One row per daily snapshot of the inventory levels for each item in a warehouse One row per bank account each month 8
Image of page 8
Determine the Grains of the following fact tables What is the grain of the fact_enrollment? Grain may involve a subset of dimensions 9 Dimensio ns Grain
Image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
All Facts Must Be At the Same Grain All facts in a fact table must be at the same grain. Can course rating be added as a fact for this fact table? 10 Dimensio ns Grain
Image of page 10
Dimension Tables Dimension Tables Contain descriptors of different grain dimensions.
Image of page 11

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 12
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern