# Power BI 데이터 모델링: 훌륭한 분석을 위한 훌륭한 길

Data Modeling in Power BI

Introduction

At a time when data is rapidly generated by different activities ranging from experiments to business, more often do we get data in different formats and structures. The data may or may not be related depending on the source. Related data in different formats and structure need to be re-organized to establish existing relationships for accurate analysis and reporting. To establish data model relationships and properties we need advanced tools that can connect to these sources. Then load the different datasets from source, and perform modelling to connect the links.

Data Modeling therefore, becomes very important. Data Modeling is the process of defining the data structure, properties, and relationships within a Data Model. Through organizing tables and columns, defining relationships between tables in the model, setting cardinality and cross-filter direction we achieve the best model which supports accurate calculations and reporting.

A Data Model is the logical representation of how data is structured and related within the data model.

Before modeling ensure that your data is clean and up to date, ready for modeling.

Why Data Modeling

1. Improved Performance and Scalability – join optimization, filters, and aggregations lead to a good model that is efficient and reliable. However, poorly designed models with complex relationships and redundant records cause delays, making queries slow.

2. Accuracy and Consistency – Guarantees the accuracy and consistency of the data used, making it reliable in reporting and drawing precise insights and improved decision making.

3. Supports Data Exploration – Users can use hierarchies and drill-down paths to support efficient data exploration.

4. Ease of Maintenance – Any changes in one model or report are reflected everywhere in real time enabling reusability and scalability, reducing the effort required to maintain reports and models as the business grows.

Components of Power BI Data Model

a. Tables and Sources

Tables are the raw building blocks of data models. They contain fields and records of particular data from different sources.
Sources, where we get our data from can vary depending on the context. We can get our data from SQL Database servers, Excel files, Data warehouse, SharePoint, and Power BI Dataflows.

Designing Source Tables

i. Fact Tables

Fact tables contain quantitative data, numerical data that can be aggregated and analyzed. Examples the Sales, Orders, or Invoices. This information is about specific events, business transaction, or measurements at the core of the data model.

Fact tables keeps one record for each transaction or event, includes only keys and metrics, and they avoid storing descriptive attributes.

Fact tables contain:

• Foreign Key(FK) – a link to the Dimension table i.e. DateKey, ProductKey, e.t.c.

• Measures(Numeric values) – Quantitative data you can aggregate. i.e. Quantity, UnitPrice, TotalAmount etc.

• Degenerate dimensions – Descriptive data that does not warrant its dimension table. i.e. OrderNumber, InvoiceNumber.

ii. Dimension Tables

Dimension tables provide context or additional details to the data in Fact Tables. They contains fewer rows while fact tables can contain a large number of rows and they are on growing.

Relationship are used to establish links and connections between the fact and dimension tables in Power BI.

Dimension table contain:

• Primary Key(PK) – Unique identifier e.g. ProductID

• Descriptive attributes – Text, categories, hierarchies e.g. ProductName

• Hierarchy Columns – Columns that form drill-down paths e.g. Country -> City -> Town.

• Natural key – original identifier from the source system e.g. ProductCode

iii. Reference and Lookup Tables

They are small but critical tables in supporting the integrity and interpretation of data. They support additional mappings or categorizations. Example: DiscountTypes or StatusCodes.

Loading Data

• Open Power BI
• On the Ribbon Home tab, Click on Get Data from the Data group option.

• From the drop down list, choose the source of your data. i.e. Text/CSV if your data is text or comma separated file.

• Choose the path to your file i.e. C:/Users/User/Downloads/file.csv and click on Open.
• Click on Connect on the Power Query editor window.
• Your data is extracted to Power Query for cleaning. Click on Transform - Data to clean explore and clean it on the Power Query editor.

• Finally, on the Power Query Editor Window Ribbon, Click on Close & Apply from the Home tab.

Changes are loaded and applied to Power BI. We will load all our Toy Store E-Commerce Database data set for modeling using the procedure above. The data is already clean, we are going to use it for demonstrating relationships in data modeling.

b. Relationships

Relationships are an important aspect of data modeling in Power BI. Relationships are for establishing connections and links between tables of a data model. They allow related tables to communicate with each other. They are the link between Fact Tables and Dimension Tables.

Relationships determine how data in the model is filtered, aggregated, combined, and visualized. They’re critical to the accuracy and performance of queries in Power BI.

Cardinality

Cardinality type defines each relationship in a model. Cardinality refers to the number of unique values in one table related to the number of unique values in another.

Types of Cardinality in Relationships

• One-to-One(1:1) – Every single record in one table is related to another single record in another table. Commonly used when splitting a large table for performance and security reasons.

• One-to-Many(1:N) – Refers to when a single record in one table is related to many records in another table(s). Very common type of relationship in Power BI models. The Fact table is on the Many side while the Dimension table is on the One side when linking Fact tables and Dimension tables.

• Many-to-Many(N:N) – Refers to when many records in the first table are related to many other records in the second table.

Cross Filter Directions

Used to determine the direction which filters propagate.

Single – When applied, only one of the tables in the relationship can filter data across the other table. Filtering data on the other table does not affect the data shown on the first table.

Bi-Directional – When applied, data can be filtered on either direction between the tables. Filtering data in one table affects data shown on the other table and vice versa. It is less often used and can impact performance negatively and create ambiguous filters.

Cross-filter options are largely dependent on the cardinality type.

Cardinality Cross-Filter Options 1:1 Bi-Directional 1:* Single or Bi-Directional : Single or Bi-Directional

Table 1: A table showing cardinality and their cross-filter options.

Creating a Relationship

To create relationships, first you need to:

• Identify the matching columns(keys).
• Choose the right cardinality.
• Decide on the Filter direction.

The Model View Interface

To manage relationships in Power BI Desktop:

1. Click the Model icon in the left sidebar (the icon with three connected boxes)

1. You’ll see all your tables displayed as boxes with their columns listed

1. Lines between tables represent relationships
2. The line style indicates cardinality: 1 on one end and * on the many end

Auto-Detect Relationships

When you load tables, Power BI automatically recognizes columns with same data type and name and creates relationships. However, you shoul always create relationships manually or review those created automatically.

Just like in our case, Power BI automatically creates relationships between tables in our model. We will review them.

When auto-detect works well:

• Column names match exactly (e.g., ProductID in both tables)
• Data types are compatible
• Values actually correspond between the tables

Creating Relationships Manually

Using the Managing Relationship Dialog

• On the Home tab → Manage Relationships

• Click New

• Select the first table and column, From Table
• Select the second table and column, To Table
• Configure cardinality and cross-filter direction

• Click Save

Step by Step for Creating Relationships for our Toy Store E-Commerce Database

1. Load all tables:
   

   ```
       products
       orders
       order_items
       order_item_refunds
       website_sessions
       website_pageviews
   ```
   

   
   `

2. Open Model view and delete any autodetected relationship.

To delete a relationship:

• In Model view, right-click on the line joining the products and order_items tables
• From the options displayed, Click on Delete

• Save your work

1. Create the following relationships:

From Fact Table To Dimension Table Cardinality Cross-Filter products[product_id] order_items[product_id] One-to-Many Single orders[order_id] order_items[order_id] One-to-Many Single orders[order_id] order_item_refunds[order_id] One-to-Many Single website_sessions[website_session_id] orders[website_session_id] One-to-One Bi-Direction website_sessions[website_session_id] website_pageviews[website_session_id] One-to-Many Single order_items[order_item_id] order_item_refunds[order_item_id] One-to-one Bi-Direction

Table 2: Table showing relationships to be created.

Creating a relationship between the products and order_items tables

• On the Home tab → Manage Relationships
• Click New
• Select the products table and specify the column product_id
• Select the order_items table and specify the column product_id
• Set cardinality to One-to-Many and the cross-filter as Single

• Click Save

Complete creating all the other relationships following the procedure. You will get something similar to:

1. Verify by checking that filters from any dimension table correctly filter the fact table.

Conclusion

Data modeling is very crucial to building powerful reports and visualizations. It is key to great analysis because it establishes relationships, links, and connections existing in a data model making it wholesome and complete. Further, it improves query performance increasing efficiency and reliability of queries. Correct data modeling can lead to accurate reporting and result reliability, thus informed decision making. To view the complete model and data for your practice on GitHub visit Toy Store E-Commerce.