Hirerachal dimensions

Question

I have two dimensions "Job Family" and "Job Subfamily" linked to a fact table. I didn't make a relationship between them even tho they sound related. I didn't make a relationship to make my model optimal since if I did, the JobSubfamily would be the one linked to the fact table.

I kept it separate like that, but then when I moved to power BI , these will be my slicer filters. When I filter "Job Family" , I should be getting the JobSubfamilies of only that Job Family. So since the two dimensions don't see each other , what did is to enable bidirectional relationship instead of unidirectional. It perfectly works but I don't know if it makes my reports later slow.

I'm asking if my modeling approach is correct or not since I want to study the measures of the fact table by both of these dimensions.

Answer 1

Your approach of enabling a bidirectional relationship between the "Job Family" and "Job Subfamily" dimensions in Power BI to achieve the desired filtering behavior in your reports is a valid solution. It allows you to filter the "Job Subfamily" based on the selected "Job Family" and analyze the measures in your fact table using both dimensions.

Enabling a bidirectional relationship between dimensions can be an effective way to handle certain reporting requirements, especially when you need to filter one dimension based on another. Power BI handles bidirectional filtering efficiently, and it should not inherently result in slow reports.

However, it's important to consider the potential impact on performance when working with bidirectional relationships, especially if your data model is large or complex. Bidirectional filtering requires additional processing and can introduce more complexity into the model. Here are a few considerations to ensure optimal performance:

1. Selective Enablement: Only enable bidirectional filtering when necessary. Evaluate if bidirectional filtering is required for all scenarios or if it can be limited to specific report pages or visuals. This can help minimize the impact on performance.

2. Cardinality and Data Volume: Consider the cardinality and data volume of your dimensions. If the dimensions have a large number of distinct values or if the data volume is significant, it can impact the query performance. Monitor and optimize query execution times if necessary.

3. Indexing and Data Structure: Ensure that the underlying data structure and indexing of your dimensions are optimized for query performance. Proper indexing can improve the speed of filtering operations.

4. Testing and Monitoring: Test the performance of your reports with the bidirectional relationship enabled and monitor the query response times. If you observe any performance degradation, you may need to reevaluate your modeling approach or consider alternative techniques, such as creating a bridge table to establish a direct relationship between the dimensions.