Data Modeling in Power BI

Hello! Welcome back.

In our last lesson, we spent time in the Power Query Editor, transforming and cleaning individual tables to ensure our data quality is high. This is a crucial step, but data rarely lives in isolation. A financial analyst might have one table with daily stock prices, another with company fundamentals, and a third with economic indicators. The real power comes from connecting them.

This lesson is about building the "brain" of your Power BI report: the data model. Our goal is to describe the relationship model in Power BI and create simple relationships between data tables. You'll learn how to teach Power BI how your different tables relate to one another, which unlocks the ability to create powerful, integrated visualizations.

1. From Tables to a Model: The "Why"

Before we jump into Power BI, it's important to understand a few key concepts. When you have multiple tables, you need a logical structure to connect them. This structure is called a data model. A well-designed model is the foundation of any good report.

The most common and effective way to structure a data model in Power BI is by using a star schema. This involves two types of tables:

• Fact Tables: These tables record business events or transactions. They typically contain numerical data you want to aggregate (like sales amount, shares traded, profit). For your interest in financial markets, a fact table could be a list of every stock trade you've executed, containing columns like Date, Ticker, Shares, and Price. Fact tables are usually very long.
• Dimension Tables: These tables describe the "who, what, where, when, why" of your facts. They contain descriptive attributes. For our stock trade example, you might have a Companies dimension table with columns like Ticker, CompanyName, and Sector, and a Calendar dimension table with columns like Date, Month, and Year. Dimension tables are usually much shorter than fact tables.

In a star schema, a central fact table is connected to several surrounding dimension tables, looking like a star.

To get a clear, practical explanation of these concepts, the following video is an excellent resource.

2. Building Relationships in Power BI

Now that you understand the concepts, let's apply them in Power BI. The relationships between your tables are managed in the Model view.

When you load data, Power BI often tries to autodetect relationships for you by looking for columns with the same name in different tables. This is often helpful, but it's essential that you know how to check, edit, and create these relationships manually to ensure your model is correct.

Creating and Managing Relationships

There are two main ways to create a relationship:

1. Drag and Drop: In the Model view, you can click on the key column in one table (e.g., Ticker in your Companies table) and drag it onto the corresponding column in the other table (e.g., Ticker in your Trades table).
2. Manage Relationships Dialog: You can use the "Manage relationships" button in the ribbon to get a more detailed dialog box for creating and editing connections.

Let's see this in action.

3. Understanding Relationship Properties

When you create a relationship, you're creating a line between two tables. But this line has important properties that define how it works. When you double-click a relationship line, the "Edit relationship" dialog box opens. The two most important settings are Cardinality and Cross-filter direction.

Cardinality

Cardinality describes the nature of the relationship between the two tables. The most common type in a star schema is Many-to-One (*:1).

• In our example, the Trades table (the fact table) has many entries for a single company.
• The Companies table (the dimension table) has only one entry for that same company.
• Therefore, the relationship is Many-to-One from Trades to Companies. The 1 side is on the dimension table, and the * (many) side is on the fact table.

Cross-Filter Direction

This setting determines how filters flow between tables. A small arrow on the relationship line indicates the direction.

• Single: This is the default and most common setting. It means that filters flow from the "one" side to the "many" side. For example, if you filter your report to show only the "Technology" sector from your Companies table, that filter will flow "downhill" to the Trades table, showing you only the trades for tech stocks.
• Both: This allows filters to travel in both directions. This can be useful in some complex models, but for a simple star schema, Single is almost always the correct and safer choice. Using "Both" incorrectly can lead to ambiguity and unexpected results.

The true power of data modeling comes alive when you see how filters work.

For further reading and to have as a reference, Microsoft's official documentation is an excellent resource.

Conclusion

You have now learned the fundamentals of data modeling in Power BI, which is a massive step forward. Moving beyond flat files and single tables into a relational model is what separates basic reporting from true business intelligence.

Key Takeaways:

• A data model connects multiple tables so they can work together.
• The star schema, with a central fact table and surrounding dimension tables, is the ideal structure.
• Relationships are created in the Model view by connecting common columns (keys).
• Cardinality defines the relationship type, with Many-to-One (*:1) being the most common.
• Cross-filter direction controls how filters flow, typically from the "one" side (dimension) to the "many" side (fact).

In our previous lessons, we imported and cleaned data. Today, we connected it. Now, our data model is ready. In the next lesson, we will finally dive into the Report view and start creating our first basic visualizations—bar charts, line charts, and tables—to bring your financial data to life.