In the realm of business analytics, creating a visually stunning dashboard is often the final step of a much deeper process. The true backbone of every successful Power BI solution is data modelling.
Data modelling is the process of identifying, organising, and defining the data a business collects and the relationships between them. It involves creating visual representations of data structures to ensure that reports are not only accurate but also performant and scalable. As data volumes grow, the difference between a sluggish, confusing report and a high-speed analytical tool often comes down to the quality of the underlying model. The Building Blocks: Fact and Dimension Tables
To understand how to build a model, one must first distinguish between the two types of tables that inhabit it: Fact tables and Dimension tables.
1. Fact Tables
A fact table is the "main table" in your model, typically containing events such as sales transactions, hospital visits, or machine readings.
• Characteristics: They contain quantitative attributes (numbers) meant to be aggregated, such as "Revenue," "Yield," or "Quantity Sold".
• Structure: These tables are usually long and narrow. They often contain duplicate values because an event (like a specific product sale) can occur multiple times. They utilise keys (like Product ID) to link out to other tables.
2. Dimension Tables
Dimension tables contain the descriptive attributes used to slice, group, and filter the data found in fact tables.
• Characteristics: These tables hold information such as "Customer Name," "Product Category," or "Geographic Region".
• Structure: Unlike fact tables, dimension tables should contain unique values for the entity they describe (no duplicates). They are generally wider but contain fewer rows than fact tables.
Schema Design: Star vs. Snowflake
The arrangement of these tables is known as the schema. While different designs exist, the Star Schema is universally recognised as the gold standard for Power BI.
The Star Schema
In a star schema, a central fact table is surrounded by multiple dimension tables, resembling a star.
• Why it is preferred: The Power BI engine is optimised to work best with this structure. It reduces the number of joins required to filter data, creating a cleaner, more organised model.
• Benefits: It ensures DAX measures calculate faster, reports refresh more quickly, and the solution remains scalable even as data volume increases into the millions of rows.
The Snowflake Schema
The snowflake schema is a variant of the star schema where dimension tables are further normalised. In this design, dimensions branch off into other dimensions. For example, a "Product" table might link to a separate "Product Category" table, which in turn links to "Product Subcategory".
• The Trade-off: While this can be useful when fact tables exist at different levels of granularity (e.g., sales by product vs. targets by region), it generally adds unnecessary complexity. Extra relationships force filters to propagate through longer chains, which can negatively impact performance.
One Big Table (OBT)
Beginners often attempt to flatten all data into a single table. While this may work for quick prototyping or ad-hoc analysis, it is considered a transitory state. It limits functionality—such as time intelligence and handling multiple data grains—and often leads to performance challenges due to large file sizes and repetitive data storage.
The Glue: Relationships
A data model is only functional if the tables effectively talk to one another. This is achieved through relationships, which are defined by cardinality and cross-filter direction.
Cardinality
Cardinality defines how rows in one table relate to rows in another.
• One-to-Many: This is the ideal relationship for linking a Dimension table (one unique ID) to a Fact table (many transactions).
• Many-to-Many: This relationship type is problematic and should be avoided whenever possible. It typically arises when connecting two fact tables directly or when dimensions are not unique. Misusing this can lead to "ambiguous" results, duplicated totals, and incorrect reporting.
Directionality
• Single Direction: Filters flow from the "one" side (Dimension) to the "many" side (Fact). This is the recommended setting for most scenarios.
• Bi-directional (Both): This allows filters to flow in both directions. While it can solve specific problems (like filtering a slicer based on available data), it is computationally expensive and can produce unpredictable results by introducing ambiguity into the model path.
Why Good Modelling is Critical
The structure of your data determines the performance, flexibility, and accuracy of your reports.
- Performance: Poor modelling choices—such as relying on "One Big Table" or using complex snowflake chains—can slow down data refreshes and visual rendering. Conversely, a star schema minimises the work the engine must do, allowing reports to scale to very large datasets without lagging.
- Accuracy: Bad relationships jeopardise data integrity. For instance, analysing monthly sales targets against daily sales data using a many-to-many relationship can cause targets to be duplicated across every day, leading to vastly inflated and incorrect totals. A proper model ensures aggregations (sums, averages) are calculated correctly across different contexts.
- Usability: A well-designed star schema groups attributes logically (e.g., all customer details in one Customer table). This makes the "Fields" pane cleaner and easier for end-users to navigate compared to searching through a massive, flat table.
Conclusion
While it is tempting to drag and drop raw data directly into visualisations, investing time in data modelling is non-negotiable for professional analysis. By adhering to the star schema, ensuring one-to-many relationships, and clearly distinguishing between facts and dimensions, developers can build Power BI solutions that are robust, accurate, and lightning-fast.
Top comments (0)