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.
The slow refresh happens because Power Query re-queries Salesforce each time you extract distinct values using Reference and Distinct. Salesforce isn’t built for repeated row-level queries, so this quickly becomes inefficient.
To fix this without sacrificing model quality, move the transformation into a Dataflow Gen2. It connects to Salesforce once, extracts and cleans the data and stores it in OneLake. Your dataset then pulls from that clean, cached table, avoiding repeated queries and improving refresh time.
When I said Dataflows Gen2 with scheduled or incremental refresh, I meant setting the dataflow to refresh on a schedule, so it updates independently of your dataset. Your model always connects to the pre-refreshed table. Incremental refresh is only useful for large, changing data, scheduled refresh is enough for small dimensions.
Please refer:
Understand star schema and the importance for Power BI - Power BI | Microsoft Learn
Differences between Dataflow Gen1 and Dataflow Gen2 - Microsoft Fabric | Microsoft Learn
Create your first Microsoft Fabric dataflow - Microsoft Fabric | Microsoft Learn
Ingest Data with Dataflows in Microsoft Fabric - Training | Microsoft Learn
Helper VThank for your response. Unfortunately, I work for a government organization with strict IT policies and I do not have access to Dataflow Gen 2 & OneLake. 😑 We really struggle with the limited technology we have. I have Excel, Power Bi or MS Access. Most of our current reporting is done via MS Access with vba coding to automate it (don't ask me how much I hate vba...😣)
Maybe one day they will expand our resources. Because I do not have access I am very ignorant about & have absolutely no experience with Fabric, Dataflow Gen2, data lakes, warehouses, etc. I struggle to understand the concept of what Fabric really is, quite honestly. But, this forum, and experts such as yourself, have certainly helped me. And as I find the time, I will continue to read what I can although I find hands-on learning a much better experience.
Again, thank you so much. My plan going forward with my first Power Bi report is to leave it as-is. The report is very limited with respect to who will view it, and certainly no one else will be developing reports from the model. It works really well at the moment, and when I did attempt the changes, it really messed up the existing measures. Measures that took me months to figure out with the help of the kind contributors on this forum, without whom I never would have suceed. I am not professionally trained and this was my first time attempt at DAX. (I am currently trying to modify the main measure used & may start a separate post. It is the same issue I always struggle with - trying to figure out how to filter a table using another table...context!)
- v-hashadapu
Hi Txtcher , sorry to know your struggle with limited resources, really hope it gets better for you. Fabric and power BI documentation is available online to understand them better, Please refer them for better understanding and feel free to open new posts in community whenever you feel like you didn't understand something. We are always happy to help.
Microsoft Fabric documentation - Microsoft Fabric | Microsoft Learn
Thank you.