Hi,
1)
When setting up the relationships for a direct lake semantic model, there is the option to select "Assume referential integrity". What does this option mean in the context of direct lake?
I have been trying to read up a bit on "Assume referential integrity", and here is my understanding so far:
"Assume referential integrity" is available as an option only in the context of DirectQuery in Power BI Desktop, and this setting enables DirectQuery to run more efficient queries against the data source by using inner joins instead of outer joins, according to the documentation.
Let's say a relationship is a one-to-many relationship:
By checking the "Assume referential integrity" box, the developer of the semantic model is telling Power BI to assume that
- for each value in the many-column, there will be a corresponding value in the one-column (the inner join will exclude records from the many-table which don't have a corresponding value in the one-table).
- the data in the many-column is never null or blank (the inner join will exclude records from the many-table where the key column is null or blank).
The developer of the semantic model bears the risk of getting inconsistent results if these assumptions about the data source proves to be wrong. For example, exclusion of records from the many-table would impact visual totals.
In Microsoft Fabric, when setting up the relationships in a Direct Lake semantic model, there is the option of checking "Assume referential integrity".
Does "Assume referential integrity" apply to the Direct Lake mode, or does it only apply if the visual queries fall back to DirectQuery?
Does Direct Lake behave like Import mode or like DirectQuery, with regards to "Assume referential integrity"?
(Sorry for using FactOrder and FactOrderLine in this example of a relationship 😅 I know relationships between fact tables are not optimal from a performance perspective. Imagine instead that this is a relationship between a regular dimension table and a fact table.)
2)
Primary, foreign and unique keys in Fabric Data Warehouse and SQL Analytics Endpoint are not enforced, meaning that the keys in Data Warehouse and SQL Analytics Endpoint aren't a guarantee for avoiding duplicate values in the key columns.
The documentation says: "Primary key columns must contain unique values. DAX queries will fail if duplicate primary key values are detected."
My interpretation of this is that the DAX queries, which are triggered when we browse or refresh a Power BI report page, validates whether or not the relationship key columns have unique values. This is also my experience from reports built on top of the direct lake default semantic model. If I have duplicate values (or null values) in the column on the one-side of a relationship, then the visuals in the Power BI report which are depending on this relationship will display error messages (see below).
I'm curious if the DAX queries (in Direct Lake mode) always will detect if there are duplicate (or null) values in the primary key?
("DAX queries will fail if duplicate primary key values are detected.")
I hope they always will detect such cases, so we get consistent results (i.e. an error message if there are duplicates or null values in the primary key column) 😃
I hope someone can shed some light on this. Thank you! 😀
Ps. The Direct Lake documentation mentions that
- "Validation is limited for Direct Lake models. User selections are assumed correct and no queries will validate cardinality and cross filter selections for relationships, or for the selected date column in a date table."
Does the limited validation which is mentioned here, refer to the edit relationship dialog we use when we are defining the relationships in the semantic model? (and also the "mark as date table" dialog)
Or does the limited validation also refer to the DAX queries in the Power BI report visuals (as mentioned above, i.e. checking for duplicate or null values on the one-side of relationships before rendering a visual)?
