Salesforce Object with many records causing long load times

[wfrohwein]

I have a salesforce account object 4+ million records and soon to be 8 million. When i first created my dashboard, the account object only had about 200,000 records in it. The dashboard was nice and snappy. I had not worked on the dashboard since it had about 200,000 records and now it has about 4+ million. Now when i hit refresh, i am stuck on this screen and have been on this screen for about 8 hours. According to the task manager, there is stuff happening. I see memory going up and down. But i dont know what else to look for.

CC Order is another object that has about 24,000 records. It has a lookup to the Account object within salesforce. I used this same lookup id to merge queries within power bi. Here is the advanced editor output from the queries below. Also here is the current number of record counts per object.

ccrz__E_Order__c 24,347
ccrz__E_Cart__c 30,062
Account 4,843,116
User 186,657

// Account Object Query

let
Source = Salesforce.Data("https://login.salesforce.com/", [ApiVersion=48]),
Account1 = Source{[Name="Account"]}[Data],
#"Removed Columns" = Table.RemoveColumns(Account1,{"A ton of columns"})
in
#"Removed Columns"

// CC Order Object Query

let
Source = Salesforce.Data("https://login.salesforce.com/", [ApiVersion=48]),
ccrz__E_Order__c = Source{[Name="ccrz__E_Order__c"]}[Data],
#"Removed Columns" = Table.RemoveColumns(ccrz__E_Order__c,{"A ton of columns"}),
#"Add CreatedDateAZ" = Table.AddColumn(#"Removed Columns", "CreatedDateAZ", each Date.From([CreatedDate]+#duration(0,-7,0,0))),
#"Add CreatedDateTimeAZ" = Table.AddColumn(#"Add CreatedDateAZ", "CreatedDateTimeAZ", each [CreatedDate]+#duration(0,-7,0,0)),
#"Add Created Today" = Table.AddColumn(#"Add CreatedDateTimeAZ", "Created Today", each if Date.From([CreatedDateTimeAZ])=Date.From(if TimeZone.Current="UTC" then (DateTime.LocalNow()+#duration(0,-7,0,0)) else DateTime.LocalNow()) then true else false),
#"Add Created L7" = Table.AddColumn(#"Add Created Today", "Created L7", each if [CreatedDateTimeAZ] >= DateTime.From(Date.From(if TimeZone.Current="UTC" then (DateTime.LocalNow()+#duration(0,-7,0,0)) else DateTime.LocalNow()))+#duration(-6,0,0,0) then true else false),
#"Add Created L30" = Table.AddColumn(#"Add Created L7", "Created L30", each if [CreatedDateTimeAZ] >= (DateTime.From(Date.From(if TimeZone.Current="UTC" then (DateTime.LocalNow()+#duration(0,-7,0,0)) else DateTime.LocalNow()))+#duration(-29,0,0,0)) then true else false),
#"Add CreatedDateAZFormatted" = Table.AddColumn(#"Add Created L30", "CreatedDateAZFormatted", each [CreatedDateAZ]),
#"Filtered Rows" = Table.SelectRows(#"Add CreatedDateAZFormatted", each [ccrz__OriginatedCart__c] <> ""),
#"Merge Account" = Table.NestedJoin(#"Filtered Rows", {"ccrz__Account__c"}, Account, {"Id"}, "Account", JoinKind.LeftOuter),
#"Expanded Account" = Table.ExpandTableColumn(#"Merge Account", "Account", {"Name"}, {"Account.Name"}),
#"Merge Cart" = Table.NestedJoin(#"Expanded Account", {"ccrz__OriginatedCart__c"}, #"CC Cart", {"Id"}, "Cart", JoinKind.LeftOuter),
#"Expanded Cart" = Table.ExpandTableColumn(#"Merge Cart", "Cart", {"ccrz__TotalAmount__c"}, {"Cart.ccrz__TotalAmount__c"}),
#"Merge User" = Table.NestedJoin(#"Expanded Cart", {"CreatedById"}, User, {"Id"}, "CreatedBy", JoinKind.LeftOuter),
#"Expanded CreatedBy" = Table.ExpandTableColumn(#"Merge User", "CreatedBy", {"Name"}, {"CreatedBy.Name"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded CreatedBy",{{"Created L30", type logical}, {"Created L7", type logical}, {"Created Today", type logical}})
in
#"Changed Type"

// CC Cart Object Query

let
Source = Salesforce.Data("https://login.salesforce.com/", [ApiVersion=48]),
ccrz__E_Cart__c = Source{[Name="ccrz__E_Cart__c"]}[Data],
#"Removed Columns" = Table.RemoveColumns(ccrz__E_Cart__c,{"Some Columns Removed"})
in
#"Removed Columns"

// User Object Query

let
Source = Salesforce.Data("https://login.salesforce.com/", [ApiVersion=48]),
User1 = Source{[Name="User"]}[Data],
#"Removed Columns" = Table.RemoveColumns(User1,{"Tons of columns removed"})
in
#"Removed Columns"

My guess is the CC Order table where i merge queries with the account object to get the account name is whats causing the slowdown for the refresh.

I also created a new pbix where i just imported the base queries mentioned above. It was stuck on loading account data forever. I left my computer open all night and it was done in the morning.

If there is anything else i can upload tp help diagnose this let me know. From the Model View, i have no relationships setup. Also the CC Cart, Account and User queries i have set to disable load. So in the Report View, you only see the CC Order query and a table i made in dax which is here below:

Top 3 Orders = TOPN(3, FILTER('CC Order', 'CC Order'[Created L30] = TRUE), 'CC Order'[ccrz__TotalAmount__c], DESC)

Thank you for any help ahead of time.

[v-jialluo-msft]

Hi  @wfrohwein ,

According to your description, your problem is that large data sets refresh too slowly.

You can try the incremental refresh feature to configure incremental refresh for tables with large data volume to optimize refresh efficiency, please refer to the following links:

Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Learn

Best Regards,
Gallen Luo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wfrohwein]

I have another question for you. In the documentation i read this "Query folding - Incremental refresh is designed for data sources that support query folding". As i understood it, The salesforce objects connector does not support query folding. So in theory that means that incremental refresh would not work correct? Not without maybe putting all the data in azure sql server database or something similar that supports query folding?

[wfrohwein]

Thank you. I am going to look into this and see if the client has the needed licenses. The solution sounds like it should work, but if they dont have the proper license, i will still mark this as the solution as its not your fault that they do not have the proper license.