POWER BI- Schema and Data Modelling.

Schema and Data Modelling in Power BI

We will explore schema and data modeling in Power BI, and why good modeling is critical for performance and accurate reporting. Key terms covered include Star Schema, Snowflake Schema, Relationship Table, Fact Table, and Dimension Table.

What is Data Modelling?

Data modelling is the process of creating structured visual representation of a given data to support analysis and eventually make informed decisions.Its simply focuses on how your data should make sense.

Star Schema

A star schema is a multi-dimensional data model used to design data warehouse and relational database so that it is easy to understand and analyze.It mainly denormalizes data into dimentions and fact. Fact being the centre of star schema with foreign keys to other tables joining it.

Benefits of Star Schema

• Usability: Clean, organized data makes reports intuitive
• Query Performance: Fewer joins enable faster queries on large datasets vs. normalized models
• Simple Queries: Minimal join dependency compared to snowflake schemas

Snowflake Schema

A snowflake schema extends the star schema by normalizing dimension tables into sub-tables, creating branching structures. It's like multiple star schemas interconnected.

Benefits of Snowflake Schema

• Fast data retrieval
• Enforces data quality
• Common data warehousing model
• Storage efficient

Fact Table

A Fact Table is the central table in a data warehouse schema e.g. in star and snowflake schemas. It stores quantitative data (measures/metrics) or facts about a business process and links to dimension tables through foreign keys. Example of data stored in a fact table is unit sold, amount etc.

Dimension Table

Dimentional table is almost same as fact table, only that it stores descriptive data like time, product etc. They use a unique surrogate key (often an integer) that acts as a primary key, which is referenced by a corresponding foreign key in the fact table.

Conclusion

Good Power BI data modeling optimizes memory usage, speeds up DAX query processing, reduces memory footprint, and ensures accurate aggregations. Poor models cause slow performance and scalability issues as datasets grow.