Tools Advisor | Other Tools

Investigating Referential Integrity Violations in DAX Studio

This guide explores how to investigate Referential Integrity (RI) violations in DAX Studio, identifying discrepancies between alerts in the Overview and Relationships tabs, validating data relationships, and using DAX queries for deeper

Prompt

When I run Dax Studio I get a handful of RI violation alerts which I’m trying to investigate.The vPaq analyzer overview displays the alerts as expected, but when I go to the Relationships tab all alerts are gone.

For this particular case I’m focusing on [Fact] Survey Data which has two keys participating in a 1*M relationship.

When I run the two EXCEPT() queries I get a blank result.
When I run the SUMX() query I get 1 count of blank rows as a result.
When I check with backend they can’t find any RI violations between the tables involved.

Am I conducting my test in a wrong way?
Why is Dax Studio telling my that there are RI violations on the Overview page but that everything is fine on the Relationships tab?

Answer

Investigating RI Violations in DAX Studio

Understanding the Issue

You are experiencing Referential Integrity (RI) violation alerts in DAX Studio while analyzing the [Fact] Survey Data table, which has two keys involved in a 1:M relationship. However, you are noticing discrepancies between the Alerts on the Overview page and the Relationships tab where no issues are reported.

Key Points

RI Violations shown in Overview: Alerts indicate potential issues between fact and dimension tables.
No Alerts in Relationships Tab: May suggest the issues do not manifest in current data but might still exist logically.
EXCEPT() and SUMX() Queries: Running EXCEPT() led to a blank result, and SUMX() returned a count of 1 blank row.

Breakdown of the Investigation Steps

1. Analyzing DAX Queries

The behavior of your queries suggests that there may indeed be some data integrity issues. Let’s analyze the use of your DAX functions.

EXCEPT() Queries

The EXCEPT() function should help identify values in one table that do not exist in another. If both queries yield blank results, it might indicate that every value in the first table finds a corresponding record in the second table.

SUMX() Query

A result of “1 count of blank rows” indicates the presence of at least one row in your [Fact] Survey Data that either has a missing foreign key or exists with a blank value.

2. Validate Data Relationships

Confirm that the foreign key relationships in your model are accurately reflecting the actual data values. This confirms that issues may lie in the data itself:

Review the data types of the keys in the involved tables.
Ensure there are no null values or incorrectly formatted entries in the foreign key fields.

Recommended DAX Queries for Deeper Analysis

Here are some DAX queries you can run to further investigate the relationship integrity:

a. Identify Blank Values

To identify potential issues with blank or null values in your foreign key columns, you can use the following DAX:

BlankCount = COUNTROWS(FILTER('Fact Survey Data', ISBLANK('Fact Survey Data'[YourForeignKeyColumn])))

b. Checking Existing Relationships

To get a clearer image of which keys are present or missing, run these queries individually for both keys:

MissingKeys = 
EXCEPT(
    VALUES('Fact Survey Data'[YourForeignKeyColumn]),
    VALUES('Dimension Table'[PrimaryKeyColumn])
)

c. Generating Summary Information

You might also want to summarize data entries with respect to key presence:

Summary = 
SUMMARIZE(
    'Fact Survey Data',
    'Fact Survey Data'[YourForeignKeyColumn],
    "Count", COUNT('Fact Survey Data'[SomeMeasureColumn])
)

Conclusion and Next Steps

Data Integrity Checks

Review your keys for blank or null values in the [Fact] Survey Data table.
Ensure foreign key relationships are correctly set up and devoid of formatting issues.

Utilize DAX Studio for Verification

Use the above DAX queries to uncover hidden data issues.
Compare with the backend data to ensure alignment.

Recommendations for Further Learning

Consider exploring courses on the Enterprise DNA Platform focusing on data modeling and DAX deep dives, which can enhance your understanding of these concepts and enhance your troubleshooting skills.

By employing these strategies and tools, you should be able to diagnose the underlying issues effectively and understand the discrepancies between DAX Studio’s alerts and actual relationship integrity.