Best Practice Building Data Model Using SalesForce source

[Txtcher]

Hello,

I have been developing my first Power Bi report over the past several months and am still learning. I am currently reviewing the model built and am wondering if I should build dimension tables.

I have 3 fact tables:  Complaints, Allegations & Events (events are the investigation of the complaints). There is a 1-to-many relationship from Complaints to Allegations & a 1-to-many relationship between Complaints and Events.

I created a calendar table. The report measures are 99% count measures: (How many complaints received, how many open complaints, how many closed complaints (closed because they were investigated), how many complaints containing abuse allegations, etc. These counts are all by region, program and priority.)

Within the Complaints fact table, the 3 descriptive columns  (region, program, priority) are used to slice the data throughout the report. I have been studying model development and am wondering if we should create dim tables from these columns. But creating them in Power Query with SF as the datasource, using duplicate queries and merges would most definitely increase the refresh time. So, is it still worth it create them? We could take a hybird approach as 2 of the 3 dimension tables could be created statically as they are small and would not change (region & program). 

And one more question, With respect to the status column, there is a status change date. Should status become a dimension table if there is a date associated with it?

I am happy to post a snapshot of the model if needed. Thank you in advance for your reponse. 

[Txtcher]

@v_hashadapu

Unfortunately, when I created the dimension tables in power query using reference queries and list distinct it has increased the query refresh time by about 10 fold.  I am better off without them.

[v-hashadapu]

Hi @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

[Txtcher]

@v-hashadapu 

Thank 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.

[v-hashadapu · Accepted solution]

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.

[Txtcher]

Thank 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.

[lbendlin]

Salesforce meta data is case sensitive.  Power Query is case sensitive.  Power BI is NOT case sensitive.

Always use Case Safe IDs. Always!!!

Take advantage of Salesforce's Object Field History objects. They will help you to reduce the amount of snapshots you have to carry.