Power BI Data Model: Core Fundamentals

Are your reports feeling sluggish?

Are your visuals taking forever to load?

Brace yourself – it’s time to optimize and inject some flavor with Power BI data models.

Now, I know what you’re thinking. Data models? Optimization? Isn’t that stuff for the nerds in the IT department?

Well, my friend, you couldn’t be more wrong. Power BI data models are the secret sauce that can turn your run-of-the-mill reports into powerhouses of information. They allow you to efficiently organize and structure your data, making it easier to analyze and visualize.

Forget about spending hours waiting for your data to load. With a suitable data model, your insights will be delivered in the blink of an eye. It’s like having a Ferrari for your reports while others are stuck with a rusty old bicycle.

And here’s the best part: you don’t have to be a tech genius to make it happen. We’ll walk you through the steps so you can become a data modeling maestro in no time.

Table of Contents:

1. What is a Power BI Data Model?
2. Why use a Data Model in Power BI?
3. Common Challenges in Data Modeling in Power BI
4. Choose The Best Data Modeling Tool
5. Power BI Data Model Concept Examples
6. Power BI Data Modeling Best Practices
7. Steps For Data Modeling in Power BI
8. Wrap Up

First”¦

What is a Power BI Data Model?

Definition: The Power BI data model is the foundation of a Power BI report or dashboard. It organizes and relates data from various sources, enabling efficient visualization. It encompasses tables, relationships, and calculations, providing a foundation for creating insightful reports and dashboards. This model enhances data exploration, helping you make informed decisions based on the interconnected data components.

Why use a Data Model in Power BI?

Using a data model in Power BI is crucial for integrating diverse data sources, establishing relationships, and optimizing performance. It enables efficient data aggregation, supports flexible analysis, and provides tools for validation and cleansing. The hierarchical data visualization, support for DAX formulas, and integration with Power Query enhance the depth and specificity of data analysis, ensuring meaningful insights for informed decision-making.

Common Challenges in Data Modeling in Power BI

1. Complex Relationships:  Laying out and overseeing connections between tables can be intricate, particularly with huge datasets and many-sided business rationale.
2. Data Quality Issues:  Ensuring data accuracy and consistency across assorted sources presents difficulties, requiring careful validation and purging to keep up with unwavering quality.
3. Performance Optimization: Large datasets may encounter performance issues. Balancing data granularity and optimizing queries is crucial for responsive dashboards.
4. DAX Complexity:  Composing and understanding Data Analysis Expressions (DAX) formulas, particularly for complex calculations, may represent a test for clients with limited DAX proficiency.
5. Data Source Variety: Integrating data from various sources with distinct structures and formats requires a thoughtful approach to maintain coherence in the data model.
6. Version Control: Managing changes and versions in the data model can be challenging, especially in collaborative environments where multiple users contribute to the development.
7. Security Concerns:  Implementing robust security measures to confine access to given jobs and consents while maintaining data integrity can be complex.
8. Scalability: Ensuring the scalability of the data model as data volumes grow over time requires ongoing monitoring and optimization.

Choose The Best Data Modeling Tool

Selecting the best data modeling tool depends on various factors such as your specific requirements, the complexity of your data, and your team’s expertise. For instance, you might consider the differences between Looker and Power BI when evaluating your options. Here are some popular data modeling tools, each with its strengths:

Power BI

• Strengths: Integrated with Microsoft ecosystem, user-friendly interface, suitable for small to mid-sized businesses.
• Considerations: May not be as scalable for extremely large datasets.

Tableau

• Strengths: Powerful visualization capabilities, suitable for large datasets, strong community support.
• Considerations: Higher learning curve for complex analyses.

ER/Studio

• Strengths: Robust data modeling features, supports complex data structures, suitable for large enterprises.
• Considerations: May be too advanced for smaller projects.

IBM InfoSphere Data Architect

• Strengths: Comprehensive data modeling and design, integrates well with IBM’s data management ecosystem.
• Considerations: May have a steeper learning curve.

Oracle SQL Developer Data Modeler

• Strengths: Specific to Oracle databases, robust data modeling features, integrates well with Oracle products.
• Considerations: Primarily designed for Oracle environments.

Data Modeling Concept Examples

• Sales Data Set

A sales data set in Power BI collects data related to sales activities. It includes information such as sales revenue, quantity sold, customer details, product details, and other relevant metrics. This data set helps to analyze and visualize sales performance, identify trends, and track key performance indicators (KPIs). Consequently, helps to make data-driven decisions to improve sales strategies and outcomes.

Example

Let’s say you have the company sales data table below.

Store	Category	Items	Brand	Unit Sold
Online Store	Electronics	Mobile	Samsung	39
Online Store	Electronics	Tablet	Samsung	73
Online Store	Electronics	Laptop	Dell	156
Online Store	Garments	Jeans	Levi’s	46
Online Store	Garments	T-Shirt	H&M	104
Online Store	Garments	Jackets	Puma	41
Online Store	Furniture	Sofa	IKEA	73
Online Store	furniture	Chair	Kartell	46
Online Store	furniture	Desk	Stickley	43

You can appreciate how the report has presented this information, making the gleaning of insights effortless.

• Spending Data Set

A spending dataset consists of information about an organization’s planned and actual costs.

It helps companies analyze and compare their budgeted and actual expenses. The data set includes details such as cost categories, budget amounts, actual expenditures, and variances. It helps to identify budget deviations, track spending trends, and make informed financial decisions. Additionally, it can support cost-of-living comparison by city, allowing organizations to evaluate expenses across different locations and plan budgets accordingly.

Example

Suppose you have a company spending data set below.

Total Spend	Department	Category	Spend Amount ($)
Total Spend	Marketing	Advertising	20,000
Total Spend	Marketing	Events	15,000
Total Spend	Marketing	Collateral	30,000
Total Spend	Marketing	Salaries	50,000
Total Spend	Operations	Rent	10,000
Total Spend	Operations	Utilities	8,000
Total Spend	Operations	Supplies	15,000
Total Spend	Operations	Salaries	40,000
Total Spend	Sales	Salaries	30,000
Total Spend	Sales	Commissions	6,000
Total Spend	R&D	Salaries	40,000
Total Spend	R&D	Contractors	20,000
Total Spend	Admin	Salaries	30,000
Total Spend	Admin	Legal	15,000
Total Spend	Admin	IT	10,000

You can present it in a Power BI report as below to make analysis easy.

Power BI Data Modeling Best Practices

Effective data modeling is crucial for creating insightful and performant Power BI reports and dashboards. Here are some of the key data modeling practices:

1. Plan: Allocate sufficient time to strategize the arrangement of your data structure, establish relationships, and outline measures like Days Between Two Dates. Understand the business requirements and the questions the report needs to answer. This will help you craft a data model that seamlessly provides the intended insights and visual representations.
2. Simplify: Avoid unnecessary complexity by only including the tables, columns, and essential relationships for your analysis. Overly complex models can lead to confusion and performance issues.
3. Data transformation: Use Power Query for data transformation tasks. Cleanse, shape, and aggregate your data within c before loading it into the data model. This ensures your data is in the right format and reduces the need for extensive transformations in DAX.
4. Data types: Use appropriate data types for each column in your tables. Power BI provides various data types such as text, whole numbers, decimals, date/time, and days between two dates, etc. Employing the appropriate data types not only boosts performance but also guarantees precise calculations and visualizations, especially when supported by an optimized Power BI deployment pipeline.
5. Relationships: Establish correct and meaningful relationships between your tables. Relationships are the foundation of your data model and impact how data is combined and analyzed. Here’s how to do it effectively:

• Create relationships based on primary keys and foreign keys.
• Use a “Single” or “Both” direction for relationships based on your analysis needs.
• Avoid creating unnecessary relationships, as they can clutter your model.
• Manage relationship cardinality (one-to-one, one-to-many, many-to-many) accurately.

Steps For Data Modeling in Power BI

We’ll break down the process into five distinct stages.

Stage 1: Logging in to Power BI

• Log in to Power BI.
• Enter your email. Click the “Submit” button.

• You are redirected to your Microsoft account.
• Enter your password and click “Sign in”.

• Choose whether to stay signed in.

• Once done, the Power BI home screen will open.

Stage 2: Creating a Data Set and Selecting the Data Set to Use in Your Sankey Chart

• Click on the “Create” option on the left-side menu.
• Select ”Paste or manually enter data“.

• We’ll use the following cash flow data for this example.

Total Cost	Company Type	Company Name	Expertise Categories	Expertise	Cost
Total Cost	Subcontractor	Skyline Contractors	Mechanical Installation	Plumbing & Heating	15456
Total Cost	Subcontractor	Skyline Contractors	Mechanical Installation	Mechanical Work	10159
Total Cost	Subcontractor	Onyx General Contractors	Mechanical Installation	Plumbing & Heating	18045
Total Cost	Subcontractor	Onyx General Contractors	Mechanical Installation	Mechanical Work	12695
Total Cost	Subcontractor	Living Well Remodeling	Mechanical Installation	Plumbing & Heating	14589
Total Cost	Subcontractor	Living Well Remodeling	Mechanical Installation	Welding	11456
Total Cost	Supplier	Power-up Builders	Raw Material	Cement	20561
Total Cost	Supplier	Power-up Builders	Raw Material	Steel	32456
Total Cost	Supplier	Five-star Construction	Raw Material	Bricks	10253
Total Cost	Supplier	Five-star Construction	Raw Material	Timber	9000

• Paste the above data table into the “Power Query” window.
• Select the “Create a dataset only” option.

• Click on the “Data Hub” option on the left-side menu.
• Power BI populates the data set list. (If you have not created a data set, refer to the Error! Reference source not found section.)

• Choose a data set for the Sankey chart.
• PBI populates the screen as shown below:

• Click on the “Create a report” dropdown.
• Select “Start from scratch“.

Stage 3: Adding the Power BI Sankey Diagram Extension by ChartExpo

• Creating the Sankey Diagram requires us to use an add-in or Power BI visual from AppSource.
• Navigate to the right side of the Power BI dashboard.
• Open the Power BI Visualizations panel.
• Click the ellipsis symbol (…) as highlighted in the diagram below. This will import the Power BI Sankey Diagram extension by ChartExpo.

• The following menu opens:
• Select the “Get more visuals” option.

• The following window opens.
• Enter “Sankey Diagram for Power BI by ChartExpo” in the highlighted search box.
• You should see the “Sankey Diagram for Power BI by ChartExpo”, as shown in the image below.

• Click the highlighted “Add” button.

• Power BI will add the “Sankey Diagram for Power BI by ChartExpo” icon in the visualization panel.

Stage 4: Drawing a Sankey Diagram with ChartExpo’s Power BI extension

• Select the “Sankey Diagram for Power BI by ChartExpo” icon in the visualization panel.
• The following window opens in the report section of your dashboard:

• You can resize the visual as needed.
• Navigate to the right side of your Power BI dashboard.
• You should see “Fields” next to “Visualizations.”

• You’ll select the fields to use in your Sankey chart here.
• The ChartExpo visual needs to be selected, though. Select the fields in the following sequence:
  • Total Cost
  • Company Type
  • Company Name
  • Expertise Categories
  • Expertise
  • Cost

• You’ll be asked for a ChartExpo license key or email address.

Stage 5: Activate your ChartExpo Trial or Apply a Subscription Key

• Select the ChartExpo visual.
• You should see three icons below “Build Visual” in the Visualizations panel.

• Select the middle icon, “Format visual.”
• The visual properties will be populated as shown below.

• To begin using ChartExpo as a new user;
  • Enter your email address in the textbox under the “Trial Mode” section. ChartExpo will send the License key to this email upon subscribing to the add-in.
  • Ensure you provide an accurate and up-to-date email address.
  • Toggle “Enable Trial” to activate your 7-day trial.

• You should receive a welcome email from ChartExpo.
• If you do not find the email in your inbox, kindly check your spam folder.
• The Sankey Diagram you create under the 7-day trial contains the ChartExpo watermark.

• If you have obtained a license key:
  • Enter your license key in the “ChartExpo License Key” textbox in the “License Settings” section (see below).
  • Slide the toggle switch next to “Enable License” to “On“.

• Your Sankey Diagram will then appear without a watermark.

• Let’s add a Prefix (such as a $ sign) with the numeric values in the chart.
• Expand the “Stats” properties and include the Prefix value.

• Let’s add colors to each node. Expand the “Level Colors” properties and select the colors.

• Automatically all changes will be saved.

Insights

• The procurement cost at Level 1 amounts to $155K.
• At Level 2, $72.3K (46.7%) of the total cost was allocated to the supplier, while $82.4K (53.3%) was spent on subcontractors.
• At Level 3, the supplier cost of $72.3K was divided between Power-up Builder ($53.0K) and Five-star Construction ($19.3K).
• The subcontractor cost of $82.4K was distributed among Skyline Contractors ($25.6K), Onyx General Contractors ($30.7K), and Living Well Remodeling ($26.0K).
• At Level 4, the supplier companies supplied raw materials worth $72.3K, while Mechanical Installations accounted for approximately $82.4K.
• Within the raw material cost, cement, steel, bricks, and timber accounted for $20.6K, $32.5K, $10.3K, and $9K, respectively.
• From the mechanical installation cost, Plumbing Heating, Mechanical Work, and Welding accounted for $48.1K, $22.9K, and $11.5K, respectively.

FAQs

What is data modeling in Power BI?

Data modeling in Power BI involves designing the structure of your data, establishing relationships, and creating calculated measures. It forms the foundation for insightful visualizations and effective analysis within Power BI reports and dashboards.

How do I create a Power BI data model?

To create a Power BI data model;

• Import data from various sources using Power Query.
• Design tables, define relationships between them, and optimize data types.
• Create calculated columns and measures using DAX to enhance analysis and visualization capabilities.

How do I open the Power BI data model file?

Launch Power BI, navigate to “File,” and select “Open” to locate and load your .pbix file. The data model, visuals, and settings will be accessible for further editing and analysis.

Wrap Up

The Power BI data model is the cornerstone of building insightful and high-performing reports and dashboards. Designing tables, establishing meaningful relationships, and employing calculated measures, lays the foundation for extracting profound insights from data. This optimization ensures your reports deliver accurate and relevant information, empowering you to make informed decisions.

A robust data model not only enhances data integrity but also facilitates smoother data transformation processes. Through thoughtful planning, you align your model with business requirements, unraveling intricate patterns and correlations within your data. The result? Clearer visualizations that succinctly convey the story hidden in the numbers.

ChartExpo’s prowess helps you create appealing, interactive visualizations that effectively communicate data patterns and variances. This makes it easier to understand the data and enables you to present your findings clearly and impactfully.

Therefore, leveraging the power of Power BI data models and ChartExpo facilitates data-driven decision-making. Consequently, helps you achieve financial success and gain a competitive edge in today’s fast-paced business landscape.

Start optimizing your reports today and unlock the full potential of your spending data set with Power BI and ChartExpo.