Power BI Data Modeling: Star Schema, Normalization & KPIs
Flat Table
A Flat Table stores all data in a single table. It is simple and quick to build, making it suitable for very small or basic analyses. However, it leads to duplicated data, which increases the risk of errors and inconsistencies. As the dataset grows, performance and scalability worsen. For this reason, flat tables are not recommended for advanced or professional Power BI reports.
Relational Model
A Relational Model organizes data into multiple tables connected through keys. This structure minimizes redundancy and ensures strong data integrity. While it is excellent for transactional systems, it is not ideal for Power BI reporting. Many relationships increase model complexity, slow down visuals, and make DAX harder to write. Therefore, relational models are often used only as an intermediate step before building a star schema.
Star Schema
A Star Schema is based on a central fact table connected to several dimension tables. Facts contain numerical values, while dimensions provide descriptive context for slicing and filtering. This design improves performance, simplifies relationships, and makes reports easier to interpret. It is the preferred modeling approach for professional business intelligence solutions in Power BI.
Key elements:
- Fact table: numerical measurements used in analysis (sales, transactions, events).
- Dimension tables: descriptive attributes for filtering and grouping (product, customer, date).
Attributes in Power BI
In Power BI, attributes are descriptive fields that define entities such as customers, products, or dates. They are mainly used for filtering, grouping, and categorization rather than aggregation. Attributes are typically stored in dimension tables within a star schema. Well-designed attributes improve report usability, while poorly structured ones limit analytical value.
Comparison of Models
Flat tables
Flat tables are user-friendly but inefficient for complex or large-scale reporting. Relational models provide accuracy and normalization but are not optimized for Power BI performance. The star schema offers the most effective balance, combining simple relationships with high query speed and clearer DAX logic. As a result, it is the recommended model for business intelligence reporting.
Normalization
Normalization organizes data to reduce duplication and maintain consistency by applying normal forms. Although normalization improves data quality, excessive normalization can negatively impact Power BI performance due to multiple joins. Therefore, normalized data is usually reshaped into a star schema for reporting.
Common normal forms:
- 1NF: all values are atomic.
- 2NF: every non-key column depends on the full primary key.
- 3NF: attributes depend only on the key and not on other non-key fields.
Semantic Model
A Semantic Model in Power BI represents the business layer built on top of prepared data. It includes correctly related tables, optimized measures, and well-defined DAX calculations. Technical fields are hidden, and hierarchies are created to simplify analysis. Proper relationships and filter directions enhance both performance and usability, allowing users to analyze data without needing to understand the underlying data structure.
Granularity
Granularity defines the level of detail represented by each row in a table, such as one transaction or one date. Consistent granularity is essential for accurate calculations and meaningful insights. Combining different levels of detail leads to incorrect measures and unreliable visuals. Using detailed data provides greater flexibility, as aggregation can always be applied later based on the analysis needs.
Third Normal Form (3NF)
A table is in Third Normal Form (3NF) when it meets the requirements of 2NF and all non-key attributes depend exclusively on the primary key. This eliminates transitive dependencies and ensures that each piece of information is stored only once. While 3NF is excellent for database design, it is not optimal for analytical tools. For this reason, 3NF databases are commonly transformed into star schemas for Power BI reporting.
KPIs in Power BI
Key Performance Indicators (KPIs) measure how effectively a business is achieving its objectives. They are implemented as DAX measures that respond dynamically to filters and context. KPIs are more efficient than calculated columns and are commonly used to track metrics such as sales, profitability, growth, or customer retention. Well-defined KPIs help guide decision-making and structure reports effectively.