SCHEMAS AND DATA MODELLING IN POWER BI

What is a schema?
A schema represents a logical grouping of tables that are related to each other.
It acts as a blueprint defining how fact tables (metrics) and dimension tables (attributes) connect to enable efficient reporting, analysis, and data modeling.

What is data modelling in power BI?
Data modeling in Power BI is the process of defining how data tables relate to one another to ensure accurate calculations and high-performance reports.
It acts as a "semantic layer" between your raw data and your final visuals.

Core concepts of data modelling
-Star Schema -it is the gold standard for Power BI. It consists of a central fact table (containing quantitative data) surrounded by Dimension Tables (descriptive data).
Star schema looks like a star when drawn.
It has one main table in the center and other tables connected around it.

• Snowflake Schema is a data warehouse modeling technique where dimension tables are normalized into multiple related sub-tables. Features of snowflake schema include: -Normalization: Snowflake schema uses normalized tables to reduce redundancy and improve consistency. Hierarchical Structure: Built around a central fact table with connected dimension tables. -Multiple Levels: Dimensions can be split into multiple levels, allowing detailed drill-down analysis. -Joins: Requires more joins, which can slow performance on large datasets. -Scalability: Scales well for large data, but its complexity makes it harder to manage.

Relationships in power BI
Relationships in Power BI data modeling connect tables via common columns, enabling cross-table analysis, filtering, and accurate visualizations.
Types of table relationships include:
-One-to-One(1:1) Relationship: Each row in the first table is related to only one row in the second table.
-Many-to-One Relationship(*:1): Many rows in the first table are related to one row in the second table.
-One-to-Many Relationship(1:*): One row in the first table is related to one or more rows in the second table.
-Many-to-Many Relationship(:): Each row in the first table can be related to multiple rows in the second table. This type requires an intermediate table to link the two tables.

Fact and dimension tables
-Fact tables contain the data that we want to analyze. The data is usually transactional in nature. A fact table also needs to include the keys to the related dimensions.
-Dimension tables provide the information to help us describe, categorize, group, or filter the data in the fact tables.

Why good data modelling is critical for performance and accurate reporting

1. Performance Optimization -Optimized Compression -Reduced Query Time -Faster Data Refresh Efficient Memory Usage -Avoidance of "Many-to-Many" Chaos.
2. Accurate & Consistent Reporting -Single Source of truth. -Correct Filter Propagation. -Time Intelligence capabilities. -Handling ambiguity. -Separation of logic.