Data modelling is the process of connecting data structures, creating relationships between them and organizing the data for analysis. In Power BI data models are created when the data sources are loaded on the platform in the form of tables.
Table Types
We may categorize tables containing data in quantitative and descriptive terms. The tables with data that can be aggregated are referred to as Fact tables. Dimension tables are those which describe the data in terms of where, when, and why attributes of the data.
For instance a fact table may include the sales, quantity, amount etc while a dimension table may include the location, city and date attributes.
Schema
The logical representation of the relationships between tables is what is referred to as a schema. Data is correlated using relationships between fact tables and dimension tables on the model view of Power BI.
A relationship is created between tables when the primary and foreign keys are connected together in a schema.
Sample Schema:
Schemas can be structured in two ways:
Star Schema.This is the arrangement that connects dimension tables with fact tables using a direct relationship. Example:
Snowflake Schema.This arrangement connects fact tables with dimension tables with dimension tables having other sub-dimension relationships. Example:
The difference between the two structures is that dimension tables in star schemas have a direct relationship with the fact tables while all dimension tables in snowflake structures don’t have a direct relationship.
This affects query performance in that, star schemas have only one hop through the relationships while snowflake have 2 or more hops when querying the data.
Top comments (0)