RI Violations due to dimension only being relevant for some records - fix or leave?

[aJayMitchell]

I'm currently reviewing the metrics of a data model through DAX Studio, and noticed that a number of the dimension tables are flagging RI (referential integrity) violations.

These are being flagged due to not all rows in the fact table having related dimension values, and hence include null/blank. An example below where the two 'ZZ' rows don't have a 'Code' as it's not relevant for these types of transactions.

My question is, is it best practice to add a row into the dimension tables to account for the null/blank values, and resolve the RI violation, or is it such a minor issue that it is safe to leave it as is?

Dimension Table

Code	Name
A	Alpha
B	Bravo
C	Charlie
D	Delta

Fact Table

Row #	Account	Code
1	A123	A
2	A123	B
3	ZZ312
4	ZZ145
5	B234	C
6	C412	C

[otravers · Accepted solution]

It is best practice to ensure there's referential integrity in star schemas, Kimball makes a big deal out of it in the Data Warehouse Lifecycle. You'll want to add a "No Code" row in your dimension table as well as replace nulls in the fact table with the corresponding AccountCode key (you don't want nulls in your foreign key).

This best practice does translate to Power BI as assuming RI is in place generates simpler and faster SQL queries (INNER JOIN rather than LEFT OUTER JOIN).

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-assume-referential-integrity