PowerBI provides a handful of features for building robust data models. Here are a few concepts to begin modeling data in PowerBI :
Fact tables & Dimensions tables:
In its simplest form, a data model design will consist of the following:
Fact table:
Also known as primary table. This table contains numeric data which we want to aggregate and analyze. It’s the primary table in a schema and has foreign keys to link it with dimension tables/
Dimension tables:
Also known as a lookup table. This table contains descriptive data. This descriptive data is primarily text data used to slice and dice the data available in primary tables.
Measures in PowerBI :
Measure In Power BI is an expression which outputs a scalar value. There are two classifications of measures in PowerBI
Implicit Measures:
Any column value can be summarized by a report visualization. This is referred to as an implicit measure. In other words, it’s the default summarization available for which you do not have to write a DAX query.
Explicit Measures:
Explicit measures on the other hand are those which require DAX calculation to query the underlying data model.
Generally, dimension tables contain a relatively small number of rows. Fact tables on the other hand can contain very large numbers of rows and continue to grow over time.
Relationships:
Once you have imported some data, the next step is to build a relationship between the tables. Relationship can be defined by either
1. Going to the Model view from Left Side Ribbon
2. Or, from Modeling -> Manage relationships
Purpose of Relationship:
Relationships decide how the filters applied on one column of the table will propagate to the other model tables. If a table is disconnected (does not have any relation to other tables), any filter applied on other tables will not propagate to the disconnected table. There are certain attributes of relations that determine the propagation of filters.
Relationship Keys:
The column on the basis of which relationship is established determines the link for propagating filters. To build a relationship, you need to determine which column will be the primary key and which one will be foreign key. Once the columns are determined, you can drag and drop the columns from either of the tables. PowerBI will prompt a dialog pop-up to confirm the keys.
In this example, we are creating a relationship between Date & Week Start Date.
Relationship Cardinality:
Cardinality defines what type of relationship exists between the tables; it can be:
1 to 1:
The column of the table on both sides has only one instance of the value.
1 to Many:
The column of the table on one side of the relationship is usually dimension table. It has only one instance of a value. This usually is the primary key. The table on many sides is usually a fact table and can have many instances of value.
Many to 1:
Many to one is inverse of the above with same logic. Just the direction is reversed.
Many to Many:
Many to Many relationships remove the need for unique values in tables. It removes the need to create bridging tables for establishing relationships.
In powerBi , it appears like this:
Relationship Direction:
Under the heading cross-filter direction, powerBI allows you to configure two types of directions
Single Cross Filter Direction:
Single cross filter direction would mean that relationship will only propagate in single direction. E.g., if a single cross filter direction is chosen in a 1 to Many relationships, filter will only execute when we filter from 1 side of the table.
Double Cross Filter Direction:
A double cross filter, as the name suggests, propagates filters from both directions. E.g., if a double cross filter direction is selected in a 1 to Many relationships, filter will execute from 1 side of the table as well as from the many sides of the table.
Cross filter options vary by cardinality. The following combinations are possible in PowerBI
| Cardinality type | Cross filter options |
| One-to-many (or Many-to-one) | Single Both |
| One-to-one | Both |
| Many-to-many | Single (Table1 to Table2) Single (Table2 to Table1) Both |
PowerBI can support any schema arrangement. Here I cover the two most used ones
Commonly Used Schemas
Commonly used schemas in in PowerBI are:
Star Schema:
A star schema has a single FACT table which connects to multiple DIMENSION tables.
Cardinality: The cardinality between DIMENSION and FACT table in a star schema is 1 to Many.
Snowflake Schema:
Snowflake schema is a variant of STAR schema in which you have dimension tables that are related to other dimension tables in a chain. When possible, you should flatten these dimension tables to create a single table.
Cardinality: The cardinality between DIMENSION and FACT table in a snowflake schema is 1 to Many.
Comparison between Star & Snowflake Schema
| Star Schema | Snowflake Schema |
| Simplest data model | Relative Complex model |
| Hierarchies for the dimensions are stored in the dimensional table. | Hierarchies are divided into separate tables. |
| It contains a fact table surrounded by dimension tables. | It also contains one fact table surrounded by dimension tables. However, these dimension tables are in turn surrounded by other dimension tables. |
| Only a single join creates the relationship between the fact table and any dimension tables. | A snowflake schema requires joins to fetch the data. |
| Denormalized Data structure | Normalized Data Structure. |
| Data redundancy is high | Data redundancy is low |