INFS3603 Week 3: Data Modelling in Power BI

• INFS3603 is an introduction to Business Analytics, with Week 3 covering the topic of Data Modelling.

Agenda

• Week 2 topics will be recapped.
• The importance of data modelling in Power BI is revealed.
• Effective data model construction involves:
  • Utilizing a start schema
  • Identifying fact versus dimension tables
  • Using primary and foreign keys
  • Defining cardinality
  • Setting cross-filter direction
  • Creating active and inactive relationships
• A demonstration is to show how to create a data model in Power BI.

Power BI Ecosystem

• Consists of three components: Power BI Desktop, Power BI Service, and Power BI Mobile.
• Each component has a purpose, working together to make a smooth experience for users.
• The typical workflow:
• Bring data into Power BI Desktop to create a report.
• Publish to the Power BI service to create visualizations or build dashboards.
• Share dashboards with others.
• View and interact with shared dashboards and reports on Power BI Mobile apps.
• Power BI Desktop requires two engines: Power Query Editor and Data Modelling.

Power Query

• Tool for transforming data and performing ETL, which cleans and shapes data for analysis.
• ETL framework:
  • Extract: Gets data from different systems like databases, spreadsheets, logs, and cloud services.
  • Transform: Cleans, structures, and enriches the data to meet the requirements of the target databse.
  • Load: Moves transformed data into a database or data warehouse.

Data Analyst Tasks

• Tasks involve preparing, modelling, analysing, visualizing, and managing data within the Power BI environment.

Data Modelling in Power BI

• Process to define the structure, properties, and relationships in a data model.
• A data model represents how data is structured and related in Power BI, composing tables and relationships for creating reports and visualizations.
• Data modeling ensures data exploration, good performance, accurate reports, and easier maintainability.

Data Modelling

• Affects the speed and efficiency of data retrieval.
• When designed correctly, can guarantee the accuracy, consistency, and dependability of reports.
• Well-designed data models ensure reports are scalable.

Building Effective Data Models

• Involves using star schemas, identifying fact and dimension tables, primary and foreign keys, cardinality, cross-filter direction, and relationships.

Star Schema

• The star schema is used to categorize tables as dimensions or facts.
• Denormalized tables keeps data in a single table and is not a considered a best practice for Power BI.

Why Star Schema

• The typical transactional database structure is not friendly for a BI system
• The model is hard to understand for a report user.
• Too many tables can severely impact query performance.
• Makes data reporting simple and fast.

Snowflake Schema

• A variation of the star schema.
• Employs a set of normalized tables for single business entity.

Fact vs Dimension Tables

• Normalization focuses on reducing redundancy and inconsistency by storing data across related tables
• Fact Table stores data that can be measured, providing information about events, transactions, or measurements.
• Dimension Tables contain key attributes/characteristics linked to fact tables that enable data analysis through filtering, grouping, and aggregating.

Comparing Fact and Dimension Tables

• Fact tables store events or observations with dimension keys and measure columns.
• Include numerous rows and are used for summarization.
• Dimension tables store business entity attributes with key and descriptive columns for filtering and grouping.
• Typically contains fewer rows than fact tables.

Primary and Foreign Keys

• Primary Keys uniquely identify each row of a table, with distinct counts corresponding to the total number of rows, often linked to foreign keys in fact tables.
• Foreign Keys are columns in tables that correspond to primary keys in other tables, these values enable the fact tables to reference the related dimension tables.

Cardinality

• Cardinality illustrates the unique values in an association with another.
• One-to-One Cardinality (1:1): This occurs when one record in the first table is related to one and only one record in the second table
• One-to-Many Cardinality (1:N): Where one record is on the 'one' side of the relationship and it is related to many records on the 'many' side. This is most common in Power BI modelling.
• Many-to-Many Cardinality (N:N): Data relationships can only be related through direct support.

Cross Filter Direction

• Single refers to when filtering data affects the data shown, also the filtering of data does not affect the data shown.
• The second type of Bi-directional filters data and affects performance when creating ambiguous paths.
• The possible options of cross-filter are dependent on the cardinality type.
  • Relationships that have 1:1 cardinality will only have a Bi-Directional filter direction.
  • Many:Many will have single or Bi-Directional directions.

Active vs Inactive Relationships

• Active relationships are already created by default.
• You can only have one active relationship to avoid conflicting and incorrect results.
• Relationships between dimensions are needed when the fact table can connect from different tables so each active relationship can connect, in turn causing simultaneous filtering.
• However, model sizes may potentially be increased when it is duplicated.
• Define a DAX with the expression with the USERELATIONSHIP function.

Power BI Relationships

• Power BI relationships are used to have multiple fields, filtering ability also lies across multiple tables.
• Relationships are only based on one filed, which creates connections for its tables, and filters depending on the direction.
• You can create new relationships to edit, delete, or allow Power BI to automatically generate these relationships.

Attributes of a good Data Model

• Be easily understood and consumed
• Have large data changes that are Scalable
• It provides predictable performance
• Ensure it is Flexible and Adaptable

Building an Effective Data Model

• Build a quick and answered business question using an easy-to-understand data model.
• The data must be user friendly
• Good-quality relationships between tables that make sense
• Having these qualities will ensure a good report and that the data set will be greatly enjoyed by the customers.

Model Types

• Conceptual Model
• Logical Model
• Physical Model