SCHEMA
A schema refers to the structure and organization of data within a data model. Schemas define how data is connected and related within the model, influencing the efficiency and performance of data queries and reports.
DATA MODELLING
Data modeling is process of structuring and relating your data tables to create a logical, efficient semantic model, enabling accurate analysis and powerful reports by defining relationships, creating DAX calculations (measures), and organizing data, often using star or snowflake schemas within Power Query and Model View in Power BI Desktop.
TYPES OF SCHEMAS IN POWER BI
Star Schema
The star schema is a simple and commonly used schema in data warehousing. It consists of a central fact table surrounded by dimension tables, forming a star-like pattern.
Structure
The central fact table contains quantitative data (e.g., sales), while the dimension tables hold descriptive attributes related to the facts (e.g. Employee, Date, Territory).
Use Cases
Star schemas are ideal for straightforward reporting and querying. They are efficient for read-heavy operations, making them suitable for dashboards and summary reports.
Benefits
Faster query performance, simplified relationships (one-to-many), and better usability for DAX calculations.
Snowflake Schema
This is a normalized version of the star schema. In this design, dimension tables are further divided into related tables, resulting in a more complex structure.
Use Cases
Snowflake schemas are used in scenarios requiring detailed data models and efficient storage. They are beneficial when dealing with large datasets where data redundancy needs to be minimized
Benefits
Reduced data redundancy and improved data integrity.
Drawbacks: Slower query performance and more complex relationships.
Why Good Modeling is Critical for Performance
- Optimized Query Speed
A well-structured model (e.g., using star schemas with fact and dimension tables) organizes data for faster retrieval. It reduces unnecessary complexity, allowing BI tools to run queries faster.
Reduced Resource Usage
Good modeling minimizes redundant data and streamlines relationships, which keeps the model lean, reduces memory usage, and ensures smoother, faster user experiences.Scalability
A robust model is designed to grow with the business. It accommodates increasing data volumes and new data sources without requiring a full redesign or significant performance degradation.Efficient Indexing
Proper physical modeling allows for the implementation of effective indexing and partitioning strategies, allowing the database to scan only necessary data rather than the entire dataset.
Top comments (1)
very insightful