Multidimensional schema of data warehouse

The subject is Database management and business intelligence DMBI. Types of multidimensional schemas - 3.

1. Multidimensional Schema of Data Warehouse Kunjan Shah 170 410 107 103 Batch C DMBI

2. What is Multidimensional schema? Schema is a logical description of the entire database. Designed to model data warehouse systems It includes the name and description of records of all record types including all associated data-items and aggregates. A database uses relational model, while a data warehouse uses Star, Snowflake, and Fact Constellation schema.

3. Types of Multidimensional Schemas Star Schema Snowflake Schema Fact Constellation Schema or Galaxy Schema

4. What is a Star Schema? The simplest type of Data Warehouse schema Structure resembles a star. Each dimension in a star schema is represented with only one-dimension table. This dimension table contains the set of attributes. There is a fact table at the center. It contains the keys to each of four dimensions.

5. Structure of Star Schema

6. Advantage of Star Schema Easy for Users to Understand Optimizes Navigation Most Suitable for Query Processing(against an OLTP system)

7. Disadvantages of Star Schema Data integrity is not enforced. Not as flexible in terms of analytical needs. Star schemas don’t reinforce many-to-many relationships within business entities – at least not frequently. Uses large disk space

8. What is Snowflake Schema? Represented by centralized fact tables which are connected to multiple dimensions tables. The principle behind snowflaking is normalization of the dimension tables by removing low cardinality attributes and forming separate tables. It normalizes dimensions to eliminate redundancy

9. Snowflake Schema

10. Advantages of Snowflake Schema Reduces the the problem of data integrity Uses small disk space Improvement in query performance Easy to understand.

11. Disadvantages of Snowflakes Schema Adds complexity to source query joins Additional maintenance efforts needed due to the increase number of lookup tables.

12. What is Galaxy or Fact constellation Schema? Combination other two schemas Constitutes of multiple fact tables sharing dimension tables.

13. Structure of Fact Constellation Schema

14. When to choose ? while introducing hierarchies in dimension. In order to deals with bigger dimension tables. helpful for aggregating fact tables.

15. Advantages of Fact Constellation Schema Provides a flexible schema. Improved data retrival Simplified business logic Better understanding Fast aggregation Extensibility

16. Disadvantages of Fact Constellation Schema difficult to maintain more complex than star and snowflake schemas

17. Real World Scenario Consider a database for a retailer that has many stores , with each store selling many products in many product categories and of various brands . A data warehouse or data mart for such a retailer would need to provide analysts the ability to run sales reports grouped by store, date (or month, quarter or year), or product category or brand.

18. When Using Star Schema

19. Sample Query SQL query to get number of products sold by country and brand, when the database uses a snowflake schema.

20. When Modeling with SnowFlake Schema

21. Query To Get Same Report

22. Thank You & Happy World Sleep Day !!