POWER BI
It is a tool that is mainly aimed to help organizations and individuals to visualize and organize data
Data modelling
Data modelling is a foundational concept in Power BI that determines how data is structured, related, and analyzed within a report. A well-designed data model improves performance, ensures accurate calculations, and makes reports easier to understand and maintain.
In Power BI, the way you structure your data known as Data Modeling is the single most important factor in determining whether your reports are fast, accurate, and easy to use. While Power Query handles the cleaning, the Data Model (Model View) is where you define how different tables "talk" to each other to create a cohesive story.
1. The Building Blocks: Fact vs. Dimension Tables
Before choosing a schema, you must distinguish between your two types of tables:
- Fact Tables (The "What"): These contain the quantitative data or "metrics" you want to analyze. They are usually long, containing thousands or millions of rows of transactions.
Example: In the Kenya crops dataset, the Yield or Sales table is the fact table.
- Dimension Tables (The "Who, Where, When"): these provide context to your facts. They contain unique lists of attributes used for filtering and grouping.
Example: the County table (Kenya Crop dataset).
2. Choosing a Schema: Star vs. Snowflake
A "schema" is the visual layout of your tables and their relationships.
*The Star Schema *
-In a Star Schema, the central fact table is connected directly to several dimension tables. Visually, it looks like a star.
Power BI is "engine-optimized" for this structure. It simplifies your DAX calculations and provides the fastest query performance because there is only one "hop" from a filter (Dimension) to a result (Fact).
Always aim for a Star Schema. If your data is "flat" (one big table), use Power Query to break it into one Fact and multiple Dimensions.
The Snowflake Schema
A Snowflake Schema is an extension where dimension tables are "normalized" into further sub-dimension tables.
Example: Instead of one Product table, you have a Product table that links to a Category table, which then links to a Department table.
The Downside: While it saves storage by reducing duplicate text, it creates a "chain" of relationships. This makes your DAX more complex and can slow down your reports as Power BI has to "jump" through multiple tables to get a result.
3. Relationships and Cardinality
Relationships are the paths through which filters flow. In Power BI, we primarily deal with One-to-Many relationships.
One to Many: One patient in the Patients (Dimension) table can have many visits in the Treatments (Fact) table.
Directionality: Always try to keep your cross-filter direction as Single. This ensures that filters flow from the Dimension to the Fact, preventing "circular dependency" errors and keeping performance high.
Top comments (0)