The main data source for the reports that I build from is SQL Server. To build the report I need to first extract it from the relevant databases and then build the data model in Power BI.
Most of the transformations are done in SQL, transforming the data before it is imported. The Where clauses in these queries represent the business rules for the report, unless applied as page filters or slicer defaults that can then be changed.
As per recommended practices this generally results in a simple star schema that looks something like this.
So, what’s the problem? If the data in your dimensions hasn’t got the same criteria applied in SQL as the Fact table, then you can potentially say hello to blank rows.
Power BI creates a blank row that is used to join everything between the tables that don’t have a match to ensure the relationships still work. This can affect your visuals as some results may be lumped together and shown against a blank row.
To prevent this happening, in the past I would ensure that the same criteria used for the main fact table was then applied to all the dimensions in SQL. This made sure there was no “bad” data impacting on the relationships.
This works but it’s painful to maintain. If a business rule changes, then every fact and dimension query may need to be updated and some of the query conditions can be complex.
A different approach that I’ve been trying now to simplify maintenance is:
- Apply the business rules only to the fact table (where clauses in SQL).
- Create dimensions that are not limited based on business rules for the report.
- Then in Power Query merge the dimension and fact table using the inner join option to ensure that only rows that match between the fact and dimension table are returned in the Dimension table.
Note: I don’t add any columns from the merged FACT table, just leave as the join.
Result
Changing the business rules for a report means I only have to change the criteria (where clause) for the FACT table SQL query. The dimensions are updated via the Power Query inner join to only include records relevant to the updated business rules.
Still need to click on the power query steps to ensure they are updated when the main query is changed. However, I think this is an easier task that having to review and update the criteria for each SQL statement that is bringing in a dimension.