Data Modelling, Relationships & Joins in Power BI

Data modelling is the process of analyzing and defining different data types, as well as the relationships between those bits of data. In Power BI, data modelling is the process of organizing multiple tables and defining the relationships between them so that power BI can analyze and visualize business data.

A good data model improves report performance, makes DAX calculations easier, supports accurate business insights, simplifies maintenance and provides better scalability as the amount of data and reporting requirements grow.

Data Modelling Approaches.

1.Flat Table.

A flat table / flat data model organizes data into a single table where each row represents a record and each column represents an attribute. Flat tables approach consolidates all the necessary data into a single denormalized table before it gets to the BI tool which sometimes means one table with all the columns (Facts and Attributes) already joined and ready for use.

Advantages
- It isn't tied to a specific tool since all transformations happen at a database level and so moving across tools becomes easier.
- It minimized the risk of discrepancies between reports and KPI's by ensuring a single source of data
- It makes it easier to track changes, roll back updates and maintain data integrity by allowing for version control
- It makes querying easier without needing to understand complex relationships between tables.

Disadvantages.
- Can become large and occasionally slower to query when dealing with massive datasets or frequent refresh cycles.
- May struggle in high volume environments, when powering multiple dashboards or when using databases that aren't optimized for analytical workloads.
- Risk of increasing storage costs and complicating updates due to data duplication.
- Very costly to optimize especially because they become inefficient as datasets grow.

Situations where appropriate.
-When your dataset has only a few thousand rows, making performance slowdowns unnoticeable.
- When individual users or small teams manage data in simple tools like Excel or CSV files.
- When you need to test an idea rapidly without spending time building complex table relationships.
- When denormalized data speeds up read operations in specific reporting layers or data pipelines by avoiding table joins.
- When you must easily share raw data across different apps, platforms, or serverless functions without a database engine.

Implications for Power BI performance and model complexity.
- Using a flat table data model in Power BI severely undermines performance and increases model complexity because it conflicts directly with the tool's underlying VertiPaq columnar storage engine. By forcing data into one massive, denormalized table, you suffer from inefficient column compression and a high memory footprint due to repeated text values, which triggers sluggish slicers and slow DAX scan times as Power BI is forced to query millions of rows rather than tiny dimension tables. Furthermore, while a single table eliminates relationships, it introduces development complexity through cluttered field lists, rigid models that cannot scale to new business processes, and high-risk DAX bugs like Auto-Exist, which can silently cause incorrect report totals when multiple columns are filtered together.

2. Star Schema

The star schema features a central fact table that contains measurable quantitative data surrounded by dimension tables that contain descriptive attributes related to the fact data. The structure looks like a star, with a single dense table sitting at the center, and connected to surrounding lookup tables using simple relationships.

This schema contains two distinct table types.
- Fact Tables.
These are at the center of the model. They record actual observations, metrics or transactional events (e.g., Fact_Sales) and consist of continuous numerical values and a collection of Foreign Keys which act as pointers linking back to the surrounding descriptive tables.
- Dimension Tables.
These are tables surrounding the fact table that contain descriptive context or attributes related to the events recorded in the fact table (e.g., Dim_Product, Dim_Date, Dim_Customer). Each Dimension table contains a unique Primary Key that anchors a one to many relationship flowing directly into the fact table. They also contain highly denormalized, flattened, repeated attributes to avoid deep branching relationships.

Advantages.

• Yield rapid query responses because analytical databases scan short, wide tables to filter rows in long narrow tables.
• The layout is easy for business analysts, report developers and end users to understand and navigate.
• Aggregations run quickly because calculations are grouped by highly indexed dimensional attributes.

Disadvantages.

• Because the dimensional tables are denormalized and repeat text fields, they increase the physical storage size of those dimensions.
• This schema does not naturally handle complex hierarchy variations natively without forcing them into flat structures.
• Because a single star schema focuses around one core process, combining multiple distinct processes requires creating independent fact tables that share dimensions.

 Situations where appropriate.

• When stakeholders use tools like Tableau or PowerBI to build their own reports without needing to write complex SQL queries.
• When tracking performance metrics across consistent structural or Temporal intervals is required such as daily sales or machinery sensor output.
• When the data warehouses or data lakes process millions or billions of rows of historical data.

 Implications for Power BI performance and model complexity.

• Adopting a star-schema architecture is the single most effective way to maximize Power BI performance and streamline model complexity because it aligns perfectly with the underlying VertiPaq in-memory columnar storage engine. By separating transactional metrics into a central fact table and descriptive attributes into dedicated, highly compressed dimension tables, Power BI achieves superior data compression, smaller file sizes, and accelerated DAX query scans. When users interact with report elements, visual rendering is nearly instant because slicers only need to query small, indexed dimension tables to dynamically pass filters down a clean one-to-many relationship path to the fact table. From a usability standpoint, this structural layout reduces model complexity by eliminating confusing relationship loops and replacing multi-table snowflake structures with an intuitive field list. Consequently, DAX formulas become remarkably simple, clean, and elegant, freeing report developers from writing defensive code to handle mismatched data granularities or fighting calculation defects like the Auto-Exist bug, resulting in highly accurate calculations and faster report development.

3. Snowflake Schema

This is a multi-dimensional data model where centralized, quantitative fact tables connect to dimension tables that are broken down into further lookup tables. The complex branching relationships extend outward from the center, resembling a snowflake.

Built using three distinct structural layers.

• Central Fact Tables. Contains transaction-level keys, numbers and facts - (e.g., Fact_Sales containing metrics like Quantity and Revenue).
• Primary Dimension Tables. These are tables that map directly to the fact table via a foreign key. They store a secondary foreign key pointing further down the line (e.g., Dim_Product contains a key for categories rather than the category text name).
• Sub-Dimension Tables. These are normalized lookup tables connecting strictly to primary dimensions tables rather than the core fact table (e.g., a standalone Dim_Category table or a separate Dim_Geography table containing city/country data).

Advantages.
- because of data normalization rules, text labels are never repeated across rows which minimizes storage in relational databases.
- Changing a group name or an attribute hierarchy only requires updating a singe record in a sub-dimension table instead of bulk-row modifications.
- Eliminates the risk of typing or recording anomalies since attributes are isolated in single, authoritative lookup sources.

Disadvantages.
- Query engines must evaluate complex multi-table joins to trace a filter from a sub-dimension down to a transactional fact table.
- The visual layout is much more crowded and difficult to parse at a glance compared to a clean star structure.
- Keeping track of sequential, multi-layered keys increases development friction when writing ETL or data pipeline logic.

 Situations where appropriate.

• Environments where reducing physical storage footprints and ensuring total database normalization are the absolute priorities.
• When a single branch of dimensions (like a complex, multi-tiered geography master list) is shared globally across multiple entirely different business lines or separate fact tables.
• Systems where data is strictly read directly via a traditional SQL engine optimized to parse multi-table relational joins natively.

 Implications for Power BI performance and model complexity.

• In Power BI, a snowflake schema should generally be avoided or flattened into a Star Schema during the ingestion phase due to several architectural friction points:

• A snowflake schema significantly degrades Power BI performance and adds immense development overhead because it introduces multi-hop relationship chains that force the VertiPaq columnar engine to calculate complex cross-table joins during runtime. While it saves minimal physical disk storage, this design undermines Power BI’s native column compression algorithms, resulting in sluggish slicer response times as filters must cascade through multiple intermediate lookup tables before arriving at the core fact table. From a usability standpoint, the model complexity spikes dramatically, loading the user interface with a cluttered, multi-table field list that confuses report creators. This intricate web of relationships forces developers to write highly complex, defensive DAX measures to accurately manage filter context across varying table granularities, drastically increasing the likelihood of development bottlenecks, unoptimized query paths, and frustrating calculation bugs.

Fact Tables & Dimension Tables.

Fact Tables.

Fact tables are quantifiable events of businesses that are recorded at a certain degree of detail. It exclusively records measurable, quantitative business events at the exact moment they occur. The information stored inside consists entirely of numeric data points known as measures and structural foreign keys (FK) used to identify when, where, and how the event happened.

Measures (Fact Tables) are numeric values meant to be aggregated using mathematical operations such as SUM, AVERAGE, MIN, or MAX. Examples include physical units sold, gross profit margins, account balances, or sensor temperature readings.

Dimension Tables.

Facts are given a descriptive context by dimension tables. They specify the way users slice, filter, group, and drill into measures. They contain the textual, categorical, or qualitative information that answers the "who, what, where, and when" of a transaction. Dimension rows are relatively stable and slow-moving compared to the rapidly expanding rows of a fact table.

Descriptive Attributes (Dimension Tables) are textual descriptors, IDs, or dates used to filter, group, slice, and slice-and-dice measures in a report. Examples include an employee’s job title, a product’s color, a customer's country, or a calendar month name.

Granularity.

Grain defines exactly what one row represents in a fact table. All foreign keys and measures inside the fact table have to match this chosen grain to prevent double counting or data corruption during reporting.

Fact Table Example.

Dimension Table Example.

Table plain Text.

Relationships in Power BI

A relationship in Power BI defines a logical link between two tables based on a shared column. In a relational data model, data is split into specialized tables to eliminate redundancy and improve performance (normalization). Relationships allow Power BI to filter and slice metrics in transaction tables using descriptive attributes from dimension tables without combining everything into a single massive, flat table.

Core Data Modeling Concepts

• Primary Key (PK): A column (or set of columns) in a dimension table where every value is completely unique. It serves as the master identifier for a single entity (e.g., CustomerKey in Dim_Customer).

• Foreign Key (FK): A column in a table that references a Primary Key in another table. It allows multiple records to map back to a single primary entity (e.g., CustomerKey in Fact_Sales).

• Unique Values: Ensures that each key appears exactly once on the "One" side of a relationship. If duplicates exist where Power BI expects unique values, data integrity is lost, leading to calculation errors.

• Cardinality: Refers to the numerical mapping between rows of two related tables (e.g., 1:*, 1:1, :).

• Referential Integrity: The rule ensuring that every Foreign Key value in a fact table exists as a matching Primary Key in the corresponding dimension table. If a sale records CustomerKey = 999, but key 999 is missing from Dim_Customer, referential integrity is broken, resulting in blank/unmatched categories in reports.

• Active vs. Inactive Relationships:

  • Active Relationship: The default link between two tables used to propagate filter context automatically across visuals. Only one active relationship can exist between any two tables at a time.
  • Inactive Relationship: A dormant connection created when multiple paths exist between two tables (e.g., Dim_Date linked to both OrderDate and ShipDate in Fact_Sales). It remains unused until explicitly activated in DAX measures using functions like USERELATIONSHIP().

Example from Snowflake-Schema-Main

• Dim_Customer Table: CustomerKey is the Primary Key. It contains strictly unique values because each customer is registered only once in master records.

• Fact_Sales Table: CustomerKey acts as a Foreign Key. A single customer can make multiple purchases over time, so their CustomerKey appears multiple times (many rows) across transactional records.

Relationship Cardinalities

1. One-to-Many (1:*)

• How It Works: One row in the dimension table connects to zero, one, or multiple rows in the fact table. Filters automatically flow from the "One" side (dimension) to refine the "Many" side (fact).

• Practical Example: Dim_Product (1) connects to Fact_Sales (*) via ProductKey. Filtering for "Laptops" filters all transaction records for laptops.

• When to Use: This is the standard best practice for dimensional data modeling (Star Schema / Snowflake Schema).

• When NOT to Use: Never use if both tables contain non-unique, repeating key values.
  

[ Dim_Product ] (1)  <--------->  (*) [ Fact_Sales ]
(ProductKey: PK)                      (ProductKey: FK)

2. One-to-One (1:1)

• How It Works: One row in Table A links directly to exactly one matching row in Table B.

• Practical Example: Linking a Dim_Customer table to a separate Dim_CustomerSecurity table where sensitive user details are stored on a 1-to-1 basis.

• When to Use: Useful when splitting large tables for security purposes, performance optimization, or separating frequently accessed columns from rarely used ones.

• When NOT to Use: Avoid when unnecessary, as two 1:1 tables can almost always be merged into a single table, reducing model complexity.
  

[ Dim_Customer ] (1)  <--------->  (1) [ Dim_CustomerSecurity ]
(CustomerKey)                           (CustomerKey)

3. Many-to-Many (:)

• How It Works: Neither table has unique values in the key column. Rows on both sides connect to multiple rows in the opposite table, requiring bi-directional filtering by default or bridge handling.

• Practical Example: Linking a Fact_Sales table directly to a Fact_Targets table on DateKey when both tables contain multiple rows for each date.

• When to Use: Use sparingly when modeling direct relationship scenarios where unique keys cannot be established.

• When NOT to Use: Avoid using as a quick fix for duplicate key issues. : Relationships create complex DAX calculations, introduce ambiguity, and can produce unpredictable visual aggregations. Instead, build a intermediate bridge/dimension table with unique values to split it into two 1: relationships.
  

[ Fact_Sales ] (*)  <--- (Bridge / Intermediate) --->  (*) [ Fact_Targets ]
(DateKey)                                                   (DateKey)

Filter Direction.

When you select a value in a report visual or slicer (such as choosing a specific year or product category), Power BI creates a filter context. This filter context flows automatically across active relationships between tables to isolate the relevant transaction rows in your fact table.

How Filter Context Flows

Filters flow along the relationship line between tables, moving from the table where the selection was made to the target table. The direction in which this filter context is allowed to travel depends on the Filter Direction setting of the relationship.

[ Dimension Table ]  ─── (Filter Direction Arrow) ───>  [ Fact Table ]

1. Single-Direction Filtering (Default & Standard)

• How It Works: Filters travel in one direction only—from the "One" side (Dimension) to the "Many" side (Fact).

• Behavior: Selecting an entity in a dimension table filters the records in the fact table. However, filtering a fact table does not filter or restrict the items shown in the dimension table.

• Model View Indicator: A single arrowhead pointing from the dimension table toward the fact table (1 ──> *).

2. Bidirectional / Both Filtering

• How It Works: Filters travel in both directions across the relationship.

• Behavior: Selecting a value in a dimension filters the fact table, and filtering the fact table simultaneously filters the connected dimension table (as well as any other downstream dimension tables).

• Model View Indicator: A double arrowhead pointing toward both tables (1 <──> * or * <──> *).

Step-by-Step Example: Filtering Fact_Sales from Dim_Product

Using our dataset model (Dim_Category ──> Dim_Product ──> Fact_Sales):

1. User Action: A user clicks on the "Electronics" category in a report slicer.

2. First Propagation: The filter context targets Dim_Category where CategoryName = "Electronics". It passes down the 1 ──> * relationship on CategoryKey to Dim_Product, instantly filtering Dim_Product to only include products belonging to Electronics (e.g., ProductKey = 1 for "Laptop Pro" and ProductKey = 2 for "Smartphone X").

3. Second Propagation: The filter context continues down the next 1 ──> * relationship on ProductKey to Fact_Sales.

4. Final Result: Power BI filters Fact_Sales to keep only rows where ProductKey is 1 or 2, dynamically recalculating metrics like SUM(Revenue) or SUM(Quantity) strictly for Electronics transactions.

Why Bidirectional Filtering Should Be Used Carefully

While bidirectional filtering allows tables to filter each other, enabling it permanently in relationship properties introduces major data modeling risks:

• Ambiguous Filter Paths: When multiple bidirectional relationships exist in a model, Power BI creates closed loops (circular paths). The engine cannot determine which path to take to apply a filter, leading to unpredictable measure results or DAX calculation errors.
• Unnecessary Model Complexity & Slow Performance: Bidirectional paths force Power BI's VertiPaq engine to evaluate dynamic cross-filtering on large tables at report runtime, increasing memory usage and slowing down report visuals.
• Unexpected Slicer Masking: Filtering a transaction table can unexpectedly mask or hide valid items in dimension drop-downs, confusing users who expect to see all available lookup options.

Best Practice: Keep physical relationship filter directions set to Single. If bidirectional filtering is required for a specific business metric, activate it dynamically inside individual DAX measures using CROSSFILTER() rather than changing the physical model properties.

Joins in Power Query.

A join is a relational operation that combines columns from two tables based on matching values in one or more common key columns.

In Power Query, tables are combined using Merge Queries (Home $\rightarrow$ Merge Queries). You select a primary table (Left Table), a secondary table (Right Table), and highlight the matching key columns in both preview panels. Power Query evaluates the matching key values and appends a nested Table column containing the matched rows from the right table, which can then be expanded.

Source Sample Tables from Our Model

To illustrate each join type clearly, we use two sample tables from our dataset:

• Left Table: Dim_Customer

  • Contains records for CustomerKey 1 through 5.

• Right Table: Dim_CustomerSecurity

  • Contains records for CustomerKey 1 through 4, plus an unassigned security record with CustomerKey 99.

1. Left Outer Join (All from First, Matching from Second)

1. How It Works: Evaluates every row in the left table and retrieves matching rows from the right table. If no match exists in the right table, null values are filled in.

2. Records Retained: All rows from the Left Table + matched rows from the Right Table.

3. Practical Example: Merging master customer details (Dim_Customer) with optional security audit records (Dim_CustomerSecurity).

2. Right Outer Join (All from Second, Matching from First)

1. How It Works: Evaluates every row in the right table and retrieves matching rows from the left table. Unmatched left attributes are filled with null.

2. Records Retained: All rows from the Right Table + matched rows from the Left Table.

3. Practical Example: Ensuring all compliance records in Dim_CustomerSecurity are evaluated against customer master records.

3. Full Outer Join (All Rows from Both Tables)

1. How It Works: Combines all records from both tables regardless of matches. Missing pairs on either side are filled with null.

2. Records Retained: All rows from both tables (complete union of matched and unmatched keys).

3. Practical Example: Reconciling two lists to see all active customers and all security profiles.

4. Inner Join (Only Matching Rows)

1. How It Works: Keeps only rows where the key value exists in both the left and right tables. Unmatched records from both sides are dropped.

2. Records Retained: Strictly matching pairs present in both tables.

3. Practical Example: Returning only customers who have completed security verification.

5. Left Anti Join (Rows Only in First Table)

1. How It Works: Filters the left table to isolate rows that have no matching key in the right table.

2. Records Retained: Left-only unmatched rows.

3. Practical Example: Identifying customers in Dim_Customer who are missing a security profile in Dim_CustomerSecurity.

6. Right Anti Join (Rows Only in Second Table)

1. How It Works: Filters the right table to isolate rows that have no matching key in the left table.

2. Records Retained: Right-only unmatched rows.

3. Practical Example: Finding orphan security profiles in Dim_CustomerSecurity that are not assigned to any customer in Dim_Customer.

Power Query Joins vs Power BI Relationships

Merging tables in Power Query and establishing model relationships in Power BI serve fundamentally different functions within your analytical data pipeline.

Key Structural Differences

• Physical Data Combination in Power Query Merge: Yes, a Power Query Merge physically combines attributes into a single query structure during data transformation. When expanding columns from Dim_CustomerSecurity into Dim_Customer, Power Query reshapes the underlying M-code query and loads a single combined table into memory.

• Separation via Model Relationships: No, creating a relationship between tables does not combine them physically. Both tables remain distinct physical storage objects in the VertiPaq engine. The relationship acts as a logical bridge allowing filter context to pass dynamically between them at visual rendering time.

• Workflow Stage Execution:

  • Power Query Merge: Occurs at the ETL (Extract, Transform, Load) stage before data is compressed and loaded into the tabular data model.
  • Model Relationships: Defined in the Model View stage after data has been loaded into the VertiPaq engine, governing DAX calculation context and reporting visuals.

Core Comparison Matrix

Feature / Aspect	Power Query Merge Queries	Power BI Model Relationships
Primary Purpose	Denormalize, consolidate, or clean datasets	Build Star/Snowflake schemas for dynamic analytics
Storage Impact	Permanently increases table width (more columns/rows)	Keeps tables lean and distinct in VertiPaq memory
Execution Engine	Power Query Formula Engine (M)	VertiPaq Analytics Engine (DAX)
Dynamic Interactivity	Static (fixed during data refresh)	Dynamic (responds instantly to slicers and visual filters)

When to Choose a Merge vs. a Relationship

1. When to Use a Merge (Power Query)

• Consolidating 1-to-1 Extensions: Merging extended attribute lookup tables (e.g., merging Dim_CustomerSecurity into Dim_Customer to avoid holding unnecessary table entities in your model).

• Flattening Snowflake Dimensions: Collapsing multi-level dimension hierarchies (e.g., merging Dim_Category directly into Dim_Product to create a clean, single-level Product dimension table).

• Pre-filtering Fact Rows: Joining reference keys to eliminate unwanted rows before loading data into the model.

2. When to Use a Relationship (Model View)

• Connecting Fact and Dimension Tables: Linking Fact_Sales to Dim_Customer or Dim_Product.

• Preserving Multi-Fact Models: Connecting shared dimensions (e.g., Dim_Category or Dim_Date) to multiple fact tables like Fact_Sales and Fact_Targets.

Impact of Excessive Merging on Data Model Performance

• Severe Storage Bloat: Merging dimension attributes directly into large fact tables (e.g., merging Dim_Customer attributes into 1,000,000 rows of Fact_Sales) forces repetitive string text (such as customer names and email addresses) into every fact row. This ruins VertiPaq run-length encoding (RLE) dictionary compression and balloons your file size.

• Granularity Errors and Measure Corruption: Merging a table with a higher row count (1-to-Many) into a fact table duplicates transaction metrics, causing measures like SUM(SalesAmount) or COUNTROWS() to return inaccurate, double-counted figures.

Why Keeping Fact and Dimension Tables Separate is Preferable

Keeping fact tables (containing numerical events/metrics) separated from dimension tables (containing descriptive attributes) inside a Star Schema provides critical modeling advantages:

1. Optimal Compression: High-cardinality text fields are stored once in lightweight dimension lookup tables. Fact tables retain only narrow integer keys (CustomerKey, ProductKey), allowing the VertiPaq engine to compress millions of rows efficiently.

2. Flexible Analytics Across Shared Dimensions: A single standalone dimension table (e.g., Dim_Category) can simultaneously filter multiple fact tables (Fact_Sales and Fact_Targets), enabling side-by-side performance tracking against target KPIs.

3. Clean, Readable DAX: Keeping entities separate simplifies DAX measures, avoiding complex filtering logic required when working with oversized single-flat-table models.

Recommended Power BI Model

Merging tables in Power Query and establishing model relationships in Power BI serve fundamentally different functions within your analytical data pipeline.

Architecture Overview: Merge vs. Relationship

Key Structural Differences

• Physical Data Combination in Power Query Merge: Yes, a Power Query Merge physically combines attributes into a single query structure during data transformation. When expanding columns from Dim_CustomerSecurity into Dim_Customer, Power Query reshapes the underlying M-code query and loads a single combined table into memory.

• Separation via Model Relationships: No, creating a relationship between tables does not combine them physically. Both tables remain distinct physical storage objects in the VertiPaq engine. The relationship acts as a logical bridge allowing filter context to pass dynamically between them at visual rendering time.

• Workflow Stage Execution:

  • Power Query Merge: Occurs at the ETL (Extract, Transform, Load) stage before data is compressed and loaded into the tabular data model.
  • Model Relationships: Defined in the Model View stage after data has been loaded into the VertiPaq engine, governing DAX calculation context and reporting visuals.

Core Comparison Matrix

Feature / Aspect	Power Query Merge Queries	Power BI Model Relationships
Primary Purpose	Denormalize, consolidate, or clean datasets	Build Star/Snowflake schemas for dynamic analytics
Storage Impact	Permanently increases table width (more columns/rows)	Keeps tables lean and distinct in VertiPaq memory
Execution Engine	Power Query Formula Engine (M)	VertiPaq Analytics Engine (DAX)
Dynamic Interactivity	Static (fixed during data refresh)	Dynamic (responds instantly to slicers and visual filters)

When to Choose a Merge vs. a Relationship

1. When to Use a Merge (Power Query)

• Consolidating 1-to-1 Extensions: Merging extended attribute lookup tables (e.g., merging Dim_CustomerSecurity into Dim_Customer to avoid holding unnecessary table entities in your model).

• Flattening Snowflake Dimensions: Collapsing multi-level dimension hierarchies (e.g., merging Dim_Category directly into Dim_Product to create a clean, single-level Product dimension table).

• Pre-filtering Fact Rows: Joining reference keys to eliminate unwanted rows before loading data into the model.

2. When to Use a Relationship (Model View)

• Connecting Fact and Dimension Tables: Linking Fact_Sales to Dim_Customer or Dim_Product.

• Preserving Multi-Fact Models: Connecting shared dimensions (e.g., Dim_Category or Dim_Date) to multiple fact tables like Fact_Sales and Fact_Targets.

Impact of Excessive Merging on Data Model Performance

• Severe Storage Bloat: Merging dimension attributes directly into large fact tables (e.g., merging Dim_Customer attributes into 1,000,000 rows of Fact_Sales) forces repetitive string text (such as customer names and email addresses) into every fact row. This ruins VertiPaq run-length encoding (RLE) dictionary compression and balloons your file size.

• Granularity Errors and Measure Corruption: Merging a table with a higher row count (1-to-Many) into a fact table duplicates transaction metrics, causing measures like SUM(SalesAmount) or COUNTROWS() to return inaccurate, double-counted figures.

Why Keeping Fact and Dimension Tables Separate is Preferable

Keeping fact tables (containing numerical events/metrics) separated from dimension tables (containing descriptive attributes) inside a Star Schema provides critical modeling advantages:

1. Optimal Compression: High-cardinality text fields are stored once in lightweight dimension lookup tables. Fact tables retain only narrow integer keys (CustomerKey, ProductKey), allowing the VertiPaq engine to compress millions of rows efficiently.

2. Flexible Analytics Across Shared Dimensions: A single standalone dimension table (e.g., Dim_Category) can simultaneously filter multiple fact tables (Fact_Sales and Fact_Targets), enabling side-by-side performance tracking against target KPIs.

3. Clean, Readable DAX: Keeping entities separate simplifies DAX measures, avoiding complex filtering logic required when working with oversized single-flat-table models.