Presenting, Linking and Presenting Data in Power BI

As a data analyst, you are presented with data that can span from a single sheet with a few thousand rows, to a full worksheet with sheets, that have thousands and thousands of rows. Microsoft Excel will properly handle the single sheet effortlessly, allowing you to clean this data, and even present it using the available charts provided for you. However, when you have the complex data, Microsoft Excel might not be the best tool to analyze this data. First, you will need something that is efficient in terms of processing this complex data, present your data in a more, efficient manner, possibly have it in a few separate but linked tables, and provide a better manner to scale your data. This is where Power BI comes in.

What is Power BI?

Power BI is a business analytics and data visualization platform provided by Microsoft, that turns raw, scattered data into interactive, easy-to-understand charts and dashboards. Power BI, can be used to connect data from over 100 data sources, including Excel spreadshets, SQL databases, cloud services, etc. In this article, we look at three aspects of Power BI that are central to how data is connected: data modelling, relationships, and joins.

Data Modelling in Power BI

Data modelling is the process of structuring and organizing, and connecting data so that it can be analyzed correctly and efficiently. It involves creating relationships between tables, knowing which table holds what information, and deciding how the tables will connect to one another. A well designed data model matters more than tidiness: it directly affects how fast reports run, how simple of complex the DAX calculations become, how well the model scales as data grows, and how easy it is for someone or you as the author to maintain.

Data Modelling comes in different approaches, each with its own place.

• Flat Table: Flat table in data modelling, also called one big table (OBT), is a data modelling approach where all transactional data and descriptive attributes are combined into a single, denormalized table. Instead of having data separated into multiple connected tables, every column will reside in one place.
  At a glance, this is simple to read. However, it comes with real costs:there is massive data redundancy (the same customer details repeat on every row), update anomalies (if a customer changes their email, every one of their historical rows must be updates), and an inability to filter and aggregate cleanly, since the tabes mixes several different grains at once - a single row is simultaneously describing and order, a customer, and a product.
  A flat table is appropriate only for small, one-off, or throwaway analyses - a quick ad hoc check, or data that already arrives denormalized and will not be reused. For any report that will grow, refresh regularly, or need ore than one grain of analysis, a flat table works against Power BI development best practices, which favor a star schema layout.

• Star Schema Layout: A star schema, the recommended data modelling technique in Power BI, is a technique, where a central fact table connects to multiple dimensions table in a star-like structure. It's core components include:

  • Fact Tables: Fact tables sit at the centre and store quantitative or numerical transactionsl data (e.g sales amounts, quantitis, revenue) alongside foreign keys that point out to each dimension.
  • Dimension Tables: They surround the fact table and hold descriptive attributes e.g. product names, customer details, geography, and date, which provide context for filtering and grouping. Each dimension table contains a key column (or columns) that uniquely identifies each row, plus supporting descriptive columns.

Dimension tables generally hold a relatively small number of rows. Fact tables, on the other hand, can contain a large number of rows and continue to grow over time.

The Star Schema is preferred in Power BI for several reasons:

1. Faster Performance - There are fewer joins for the engine to resolve, since every dimension is a single hop from the fact table.
2. Simpler DAX - Star schema makes Data Analytics Expressions (DAX) calculations cleaner, more predictable, and less prone to error.
3. Less Redundancy - The star schema stores descriptive text once in dimension tables rather than repeating it across rows in transaction logs.
4. One-to-Many Relationships - The relationships connect the "one" side (dimension table) to the "many" side (fact table) with filters flowing strictly from dimensions to facts.

Even so, a star schema is not entirely free of trade-offs. Each dimension carries a small amount of denormalization of its own - for instance, a product's category name is stored directly on the product row rather than in a separate table - which is a deliberate compromise made in exchange for speed and simplicity, and is quite different from the redundancy problem a flat table has. A star schema also assumes the business process is reasonably well understood upfront, since the fact table's grain has to be decided early and is expensive to change later.
A star schema is appropriate for the large majority of Power BI reporting models. It is the default recommendation for new projects, unless there is a specific reason - usually a very large, shared, hierarchical dimension - to normalize further.

• Snow Flake Schema: A snowflake scheme takes a star schema one step further, where some dimension tables are normalized into sub-dimensions, E.g instead of a DimProduct holding the category name directly, that column is split out into a separate DimCategory table, and DimProduct links to it instead.

While normalization in a snowflake model is high, it comes with disadvantages:

• Power BI loads more tables, which is less efficient from a storage and performance perspective.
• Longer relationship filter propagation chains need to be traveresed, and this might be less efficient that filters applied to a single table.
• The Data pane presents more model tables to report authors, which can result in a less intuitive experience, especially when snowflake dimension tables contain only one or two columns.

A snowflake schema is appropriate when a dimension is large, genuinely hierarchical, and reused by more than one fact table - enough that the storage saved by normalizing it outweighs the cost of the extra relationship hop. Outside of that specific case, it is usually better to flatten the dimension back into a star using Power Query before loading.

Fact Tables and Dimenstion Tables

Fact and dimension tables were introduced briefly above, but the distinction deserves a closer look on its own, since almost every modelling decision in Power BI comes back to it.

Fact table - Stores the measurable business events - things that happened, with numbers attached. Its rows are typically numerous, and its numeric columns are measures: values meant to be summed, averaged, or counted, such as Quantity, UnitPrice, Discount, or Amount. Alongside these measures, a fact table holds foreign keys pointing out to each relevant dimension. Common examples include FactSales, FactOrders, and FactTransactions.

Dimension table - Stores the descriptive attributes used to slice, filter, and label the facts - the “who, what, where, and when” of a business event. Its rows are comparatively few, and it is identified by a primary key. Typical examples are DimCustomer, DimProduct, DimDate, and DimLocation.

The difference between a measure and an attribute matters here: a measure produces a meaningful result when aggregated - SUM(Quantity) makes sense - while an attribute is a label used to group or filter (summing a CustomerID, or averaging a City name, is meaningless). This is why fact tables tend to be "tall and narrow" - few columns, many rows - while dimension tables are "wide and short" - many descriptive columns, fewer rows.

Relationships in Power BI

When discussing data models, we slightly mentioned relationships.

A Relationship connect two or more tables using a shared column or key so that reports can filter and aggregate data correctly across them. Without a relationship, tables sit in the model completely isolated from one another.
Some of the key terms used in relationships include:

• Primary key(PK): This is a column in a table that uniquely identifies each row, with no duplicates.
• Foreign key(FK): This is a column in another table that refers to the primary key described above. They can also repeat in a table.
• Cardinality: Whether "one" side and "many" side columns contain unique values.
• Referential Integrity: Ensures that every foreign key value in the fact table should exist in the dimension's Primary key column. Orphaned keys referencing a key not existing in the dimension table shows up as a blank/unknown member in visuals.
• Active and Inactive Relationships: An active relationship in Power BI is the default path used automatically for filtering and calculations, while an inactive relationship is ignored until explicitly activated using DAX.

This is exactly why, for example, CustomerID is unique in DimCustomer. Each customer appears exactly once, but the same CustomerID repeats many times in FactSales, once for every order that customer has places. The dimenstion describes the customer once, while the fact table will record every even that customer was involved in.

Let's now look at the types of relationships that apply in PowerBI:

Types of relationships

1. One-to-many(1:*) - In this relationship, one row in the dimension relates to many rows in the fact. It is used whenever a dimension describes many fact events.
2. One-to-one(1:1) - Each row in one table matches exactly one row in the other, both sides being unique. A proper example is with an employee table and and employe badge table, one badge per employee. It is however rare, and indicates that the two tables should simply be merged into one, since they carry no extra "many" value.
3. Many-to-many(* : *) - In this relationship, neither side is unique, e.g. a student table and a course table where each student takes many courses and each course has many students. This relationship is supported natively by Power BI via a shared column with duplicates on both sides, but it should be used cautiously as it can produce ambiguous or unexpected duplicate results. The remedy is usually to introduce proper bridge/junction tables that turns it into two clean one-to-many relationships instead.

As much as relationships have been established, we need to understand that they are not just connections. They also define which way filters travel. In power BI, filters will travel in two ways, which we will discuss below.

Filter Direction

1. Single Direction - This is the default filter direction for 1:many relationships. Filterig the "one" side (dimension) filters the "many" side(fact), but not the reverse.
2. Both/bidirectional: Filters flow both ways. Selecting a product in a dimension table filters the fact table, and filering the fact table can filter back and affect the visible rows in the dimension table.

Joins in Power Query

A join physically combined rows from tables in Power Query, based on matching key columns, before the data ever gets loaded into the model. Joins are performed using the Merge Queries feature to combine two tables. The following are the joins we have in Power Query:

1. Left Outer Join: This join keeps all rows from the first (left) table and matching rows from the second (right) table. Unmatched rows show as null.
2. Right Outer Join: Keeps all rows from the second (right) table, and matching rows from the first (left) table.
3. Full Outer Join: This join returns all rows from both tables, matching or not.
4. Inner Join: This join returns only rows that have matching values in both tables.
5. Left Anti-Join: Returns only the rows from the first(left) table that have no match in the second table.
6. Right Anti-Join: Returns only the rows from the second (right) table that have no match in the first table.

Source(Excel Unplugged)

Power Query Joins vs Power BI Relationships

It is worth nothing the difference between merging tables in Power Query and creating relationships in the Power BI data model.
Does a Power Query merge physically combine data? Yes. A merge copies matching columns from the second table into the first, producing one wider, denormalied table.
Does creating a relationship combine the tables? No. The tables remain fully separate in teh model, each with its won rows and columns. A relationship is basically a live link used at query time to propagate filters, not a physical copy of data.
At what stage does each happen? Merges happen in the Power Query Editor, during the transform/ETL stage, before data is loaded. Relationships are created afterward, in Model view, nce the tables already exist in the model.
When would you choose a merge instead of a relationship? When a single flattened table is needed for a specific purpose e.g. exporting a denormalized extract, or resolving a snowflaked dimension back into its parent so the model does not need the extra relationship hop.
How can excessive merging affect a model's structure? Merging fact-level detail into dimenstions, or merging multiple fact tables together, recreated the flat-table problem discussed earlier: redundancy returns, file size grows, refreshes slow down, and the odel loses the single clean grain per table that made the star schema fast and prdicatable.
Why keep the fact and dimension tables separate? Separation preserves a single source of truth for every attribute, keeps the fact table narrow and fast to scan, and lets Power BI's relationship enginde do the aggregation work it is build for.

Recommended Power BI Model

Weighing everything discussed above, the recommended approach for a typical business intelligence project is a star schema, built with single-direction, one-to-many relationships flowing from each dimension into the fact table, with bidirectional filtering reserved only for specific, justified cases such as a many-to-many bridge table.

This recommendation holds up against each of the factors that matter in practice:

• Performance - a star schema aligns with how Power BI's VertiPaq engine compresses and scans data; narrow, single-hop relationships resolve fastest.
• DAX simplicity - SUM, CALCULATE, and time-intelligence functions behave predictably when every dimension sits one hop from the fact table; both flat tables and snowflakes complicate this in different ways.
• Model readability - a star schema is visually obvious in Model view; the fact table and its surrounding dimensions are immediately clear to anyone opening the file.
• Scalability - adding a new dimension, such as DimPromotion, is a single new relationship, not a redesign of the whole model.
• Data redundancy - dimensions carry a small, deliberate amount of denormalization (like a flattened category name), a reasonable trade for speed, and nowhere near the repetition problem of a flat table.
• Maintainability - a clear separation between facts and dimensions makes future changes, such as renaming a column or adding an attribute, low-risk and localized to one table.
• Ease of reporting - report authors work with a small, well-labelled set of dimension fields to slice by, rather than searching through one enormous table.
• Filter propagation - single-direction relationships give unambiguous, predictable filtering, which becomes essential once a report has more than one or two slicers.
• Model complexity - a star schema keeps the number of relationship hops to one, which keeps the whole model easy to hold in your head.

A snowflake schema is only worth its added complexity when a dimension is genuinely large, hierarchical, and shared across multiple fact tables - enough that the storage and maintenance savings outweigh the extra relationship hop and DAX complexity it introduces. A flat table should be reserved for small, one-off analyses that will never need to scale. For everything else - which, in practice, is most Power BI projects - the star schema, with single-direction, one-to-many relationships, is the right default.