Data Modelling and Schemas in Power BI

Source: Dev.to

Understanding Data Modelling in Power BI

Types of Data Models

• Conceptual data modelling – Defines what data a business needs and how concepts relate (e.g., customers, product).
• Logical data modelling – Adds columns, attributes, facts, and dimensions; identifies entities and their relationships.
• Physical data modelling – Describes how data is stored, organized, and accessed at the physical level (e.g., product key). This model works with a DBMS and focuses on performance‑related properties.

Key Terms

• Dimensional model – Organizes data for easy retrieval in reporting.
• Fact table – Represents an event; may include measures.
• Dimension table – A category of information (a noun) that provides descriptive context.
• Attribute – A column in a dimension table that describes the object.

Attributes of a Good Data Model

• Easily understood and consumable.
• Provides predictable performance.
• Scalable for large datasets.
• Flexible and adaptable.

Fact and Dimension Tables

Fact Table

• Examples: cost, discount, quantity sold.
• Characteristics:
  • Many rows (high granularity).
  • Stores numeric values used in aggregations (SUM, AVERAGE, COUNT).

Dimension Table

• Answers “who, what, where, when, how, and why” of an event.
• Fewer rows than fact tables, but can be wide with many text columns.
• Each dimension is defined by a single primary key.
• Contains descriptive characteristics of business‑process nouns.
• Dimension attributes serve as the primary source of query constraints, grouping, and report labels.
• Examples: date, region, customer details.

Schema Types

Star Schema

• Named for its star‑like structure, with a central fact table surrounded by dimension tables.

Snowflake Schema

• A normalized version of the star schema where dimension tables are further broken down into related tables.

Relationships in Power BI

• Cardinality – One‑to‑many, one‑to‑one, many‑to‑many.
• Cross‑filter direction – Single or both.
• Active vs. inactive relationships – Determines which relationships are used by default in calculations.

Importance of Good Data Modelling

• Accuracy in reporting.
• Improved data quality and integrity.
• Enhanced performance.
• Scalability and flexibility.

Conclusion

A well‑designed data model is critical for Power BI’s performance, accuracy, and scalability. Proper modeling ensures fast queries, correct calculations, and intuitive reporting, whereas poor modeling leads to slow dashboards, incorrect insights, and wasted memory.