Forum Discussion
Best practice advice data model
Hi all,
I'm looking for some advice on how to design my data model. The performance of the report is too slow and I have been looking into the tabular editor best practice rules. I have quite some bi-directional or many - to - many relationships which could be the issue of the bad performance. I pasted a screenshot of a small part of the model (the model consists of multiple cases like this). And I was wondering what best practice (in terms of performance) would be.
Tabel D is the fact table, for each scenario, technology, year, mineral there is a value. This value is used in the chart.
The user wants to be able to filter the chart on specific minerals using two slicers.
Table C consits of a mineral mapping table: In "Minerals - Tier 1" some minerals are aggregated together to make filtering easier for the user.
Table B is the full list of options (unpivot), of which column "Options(legend)" is added to slicer 1.
Table A is the list of "tiers"/"splits" which is added to slicer 2. If the user chooses "Tier 2" in slicer 2 it can choose from the full list of minerals in slicer 1 to filter on. If the user chooses "Tier 1" then it can choose an aggregated group of minerals at once.
Currently I have a bidirectional relationship between table B and C (on ID). However I actually only need it in the direction of B to C, but since it is then many-to-one, this is not possible. Another option could be to have a direct relationship between table B and D (on ID) but this would then be a many-to-many relationship. What do you think is best, or is there an alternative solution? By changing the tables and or data model?
Thank you!
Martine
Hi Anonymous
I think the one-to-many bidirectional relationship will be better than a many-to-many relationship between table B and D (on ID). Sometimes we cannot avoid bidirectional relationships completely. As the current bidirectional relationship is one-to-many, its filtering result is still accurate.
Best Regards,
Jing
3 Replies
Hi Anonymous
I think the current relationships are good for this small part model. The data model is not the only factor that influences the report performance. You can also consider optimizing other aspects like removing unnecessary data, removing unnecessary interactions, reduce the number of visuals on a page, avoiding too many complex DAX expressions, .etc.
The following articles provide some suggestions on how to improve the performance. You may find them helpful.
Best practice rules to improve your model's performance | Microsoft Power BI Blog
Tips to increase the performance of your Power BI Reports | by Elias Nordlinder
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!Hi Jing,
Thanks a lot for your response. I will have a look at the links you provided.
So you do not think the bidirectional relationship between B and C is a problem? Or do you know a way to get rid of it? Indeed this is only a small part of the model but I have about 8 or 9 of these similar setups with other tables & slicers.
Thank you!
Martine
Hi Anonymous
I think the one-to-many bidirectional relationship will be better than a many-to-many relationship between table B and D (on ID). Sometimes we cannot avoid bidirectional relationships completely. As the current bidirectional relationship is one-to-many, its filtering result is still accurate.
Best Regards,
Jing