Forum Discussion
Helper VBest Practice Building Data Model Using SalesForce source
Hi Txtcher , Thank you for reaching out to the Microsoft Community Forum.
Yes, you should create separate dimension tables for Region, Program and Priority. This aligns with proper star schema modelling. It improves performance when slicing count based measures and keeps your model maintainable, especially with your multiple fact tables.
To avoid impacting Salesforce refresh times, extract distinct values using reference queries in Power Query or better, load them via dataflows gen2 with scheduled or incremental refresh. For Region and Program, static tables work fine if they rarely change.
For Status, if you're only reporting current status, keep it in the fact table. But if you're analyzing changes over time, create a status history fact table with Complaint ID, Status and StatusChangeDate, sourced from Salesforce's field history if available.
Community SupportHi Txtcher , Thank you for reaching out to the Microsoft Community Forum.
Yes, you should create separate dimension tables for Region, Program and Priority. This aligns with proper star schema modelling. It improves performance when slicing count based measures and keeps your model maintainable, especially with your multiple fact tables.
To avoid impacting Salesforce refresh times, extract distinct values using reference queries in Power Query or better, load them via dataflows gen2 with scheduled or incremental refresh. For Region and Program, static tables work fine if they rarely change.
For Status, if you're only reporting current status, keep it in the fact table. But if you're analyzing changes over time, create a status history fact table with Complaint ID, Status and StatusChangeDate, sourced from Salesforce's field history if available.
Helper VThank you so much for taking the time to respond.
I will implement your recommendations. I especially like the status history table idea.
However, I do not know what you mean when you say to load them "via dataflows gen2 with scheduled or incremental refresh." My apologies for my ignorance but I am not formally trained and would consider myself an advanced beginner/intermediate user with respect to power query, and definitely just a beginner with respect to Power Bi. If you could point me to any helpful articles that would be greatly appreciated.
Thank you again for your response.