none
Salesforce Object Queries and Large Cache Size Issue RRS feed

  • Question

  • I'm experiencing an issue with querying Salesforce objects when doing multiple refreshes.  The cache size keeps growing with every refresh and then when the cache size reaches the size limit, the queries will time out with Salesforce.  Clearing the cache before every refresh solves the problem but that seems like an unnecessary step.

    I'm only pulling down a few thousand rows in total from all queries and on each refresh the cache increases approximately 50mb.  I've experienced the cache size increasing over 1GB with one query only returning less than 100 records.  I may not be query folding properly so if anyone has any tips there, I would certainly appreciate it.

    Thursday, October 1, 2015 1:35 PM

Answers

All replies

  • Can you share the formula text of your query?

    Ehren

    Tuesday, October 6, 2015 10:58 PM
    Owner
  • Here's the main query:

       

    let
        Source = Salesforce.Data(),

        Deals = Source{[Name="Opportunity"]}[Data],
        #"Removed Other Columns" = Table.SelectColumns(Deals,{"Dealer_Number__c", "OwnerId", "Name", "Application_Number__c", "Application_Init_Date__c", "Bureau_Score__c", "Custom_Score__c",
        "StageName", "Portal_User_Id__c", "REQST_STRCTR_LTV__c", "APRVD_STRCTR_Loan_Term__c", "APRVD_STRCTR_Buy_Rt__c", "APRVD_STRCTR_Part_Amt__c", "AboveCutoff1__c",
        "APRVD_STRCTR_Acq_Fee__c", "REQST_STRCTR_TOTAL_AMT_FINC__c","Req_Ev_Requested__c","BR_Decision_Comments__c", "FUP_Sts_Comment__c", "FUP_Sts__c", "Loan_Admin_Status__c", "Loan_Admin_Date__c", 
       "Loan_Admin_Duration__c"}),
        #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [OwnerId] = GetOwnerId("OWNERID")),
        #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Date.IsInPreviousNDays([Application_Init_Date__c], 90) or Date.IsInCurrentDay([Application_Init_Date__c])),
        #"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"Application_Init_Date__c", Order.Descending}}),
        #"Filtered Rows2" = Table.SelectRows(#"Sorted Rows", each ([Loan_Admin_Status__c] <> "FUNDED")),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows2",{"OwnerId"}),
        #"Added Custom" = Table.AddColumn(#"Removed Columns", "Approval_Ind", each if[StageName] = "APPSCOR" then 1 else if [StageName] = "APPROVE" then 1 else if [StageName] = "APPCOND" then 1 else if [StageName] = "PENAPPR" then 1 else 0),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Captured_Committed_Ind", each if [Loan_Admin_Status__c] = "FUNDED" then 0 else if [Loan_Admin_Status__c]= "PENBKD" then 0 else if [Loan_Admin_Status__c] = "BOOKED" then 0 else if [Loan_Admin_Status__c] = "RETURNED" then 0 else if [FUP_Sts__c] = "CAPTURED" then 1 else if [FUP_Sts__c] = "COMMITTED" then 1 else 0)
    in
        #"Added Custom1"

    The function in the query is pulling in a value from a value in the workbook that the user inputs.

    This is one of about 6 queries I have set up in the workbook and all but this one returns a few hundred rows of data.  The query above returns around 7,000 rows.

    Appreciate your help!

    Chris


    Wednesday, October 7, 2015 12:17 AM
  • Hi Chris,

    Can you send this to us as a frown?

    Thanks,

    Oguz

    Friday, October 16, 2015 9:45 PM
    Moderator
  • Frown sent.

    I've tested this same workbook using the 64-bit version of the Power Query addin and I haven't experienced any issues as of yet.  It just appears to be the 32-bit version.

    Appreciate your help with this.


    ChrisM26

    Tuesday, October 20, 2015 7:50 PM
  • I'd encourage you to take this question to the Power BI community @ community.powerbi.com

    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Wednesday, October 28, 2015 12:38 AM
    Owner