Understanding schemas and data modelling in Power BI

Power BI is a vital tool when it comes to connecting various data sets from different sources, transforming that data, and creating interactive, visual reports and dashboards.

Many beginners wonder about the difference between schemas, and data models; schemas are specific designs that conceptualize how to structure one's data for analysis while data models are implementations of the selected schemas. This indicates that data modelling is dependent on the schema that is created on Power BI.

Common terminologies when dealing with schemas and data modelling:

• Fact table - stores key business data such sales or transactional data which changes regularly based on adjustments and projections

A fact table will normally contain dimension key columns that relate to dimension tables, and numeric measure columns, these allows summarization

• Dimension table stores the additional information related to the transactional data (lookup/descriptive data) and is mostly constant

A dimension table contains a key column (or columns) that acts as a unique identifier and other columns which support filtering and grouping your data.

• Normalized data describes data that's stored in a way that reduces repetitious data
  

• Denormalized data describes data that is stored in a way that has repetitious data in rows

1. Schema Design (The Planning Stage)

Before bringing data into Power BI, you must determine how to structure it for analysis. A star schema is the most preferred approach, where you organize data into a central fact table surrounded by dimension tables.

A well-planned schema ensures better performance, easier DAX, and more accurate reports.

Actions to execute: Deciding which tables are facts and which are dimensions and identifying relationships between the two.

Types of schemas:

1. Star Schema

In a star schema, a fact table is surrounded by multiple dimension tables. Power BI engine works best with star schema, where the fact table will always be in the middle, whilst the rest of the dimensional tables will be surrounding the fact table

2. Snowflake schema
A snowflake schema has the same layout as the star schema however, it extends further than it with some or all of the dimension tables are further divided into sub-dimension tables

2. Data Modeling (The Implementation Stage)

Data modeling is the actual, hands-on process in Power BI where you apply the schema design.

It involves importing, cleansing in Power Query, creating tables, and establishing relationships (e.g., one-to-many) in the Modeling view.
What you are doing: Implementing the star schema using Power Query and the Relationship View.

Semantic models:
A semantic model consists of all connected data, transformations, relationships, and calculations. To follow the flow of Power BI, you first connect to data, transform data, and create relationships and calculations to create a semantic model.

Relationships in PowerBI

Relations in PowerBI are connections between tables based on common columns. These connections enable data from multiple sources to be used in a single, accurate report. The relationships also ensure that slicers and other visualization tools correctly reflect on other data tables.

Common types of relationships within Power BI include:

1. One-to-Many (1:M) / Many-to-One (M:1): The most common, ideal relationship where one dimension table links to multiple rows in a fact table (e.g., Product table to Sales table).

2. One-to-One (1:1): Each record in table A matches exactly one record in table B; rare, often suggesting tables should be merged.

3. Many-to-Many (M:M): Multiple rows in one table match multiple rows in another; used when direct relationships are not possible, though often requiring bridge tables. 

Creating relationships on PowerBI:

1. Using the auto-detect feature on PowerBI
   When you load data containing multiple tables on PowerBI, it automatically attempts to find and create relationships for you. These relationships are mainly determined by the names of the columns within your data tables. To automatically detect this on PowerBI, on the Modeling tab, select Manage relationships > Autodetect

2. Manually creating a relationship
   This is feasible in instances where PowerBI is unable to automatically detect relationships between tables especially when the table columns have different names.

To manually create a connection;

• On the Modeling tab, select Manage relationships > New.
• In the Create relationship dialog box, in the first table drop-down list, select a table. Select the column you want to use in the relationship.
• In the second table drop-down list, select the other table you want in the relationship. Select the other column you want to use, and then select OK.

Key Aspects of Power BI Relationships:

1. Cardinality: Defines the nature of the relationship, i.e how many rows are related between tables. This would be One-to-many (most common, e.g., one customer to many orders), One-to-one, or Many-to-many.

2. Cross-filter Direction: Determines how filters flow between tables. Single (default, one side filters many side) or Both (bidirectional, filters flow both ways), though 'both' should be used cautiously.

3. Active vs. Inactive: Only one active relationship can exist between two tables for direct filtering, but multiple inactive relationships can be defined for use in DAX calculations.

4. Autodetect: Power BI can automatically find and create relationships based on matching column names during data load, though manual configuration is often needed.

Resources used:
Data Model Guidelines and Best Practice
Understand star schema and the importance for Power BI