Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Improving formula engine performance

I have a query that's taking 55 seconds for the FE and 1 for the SE   DAX Studio obviously gives me the SQL and SE timings, but I can't see how to either   a) see what's taking 55 seconds in the ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Greg_Deckler - thanks again for your help

 

I've used the DAX PA to generate performance data for export to DAX Studio, and have been running queries there - following the approach of commenting out fields to see what's causing issues.

 

Haven't used PQ diagnostic tracing - will need to learn more about it. Does it help query optimisation?

 

 

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

Query diagnostics will help you troubleshoot your power query refresh, but not your DAX expressions.  Can you post your DAX expression so we can propose optimization changes?

Regards,

Pat

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks mahoneypat - here's the DAX that's taking 56 seconds to run

     

    DEFINE
  VAR __DS0FilterTable = 
    TREATAS(
      {"2020 Q1",
        "2020 Q2",
        "2020 Q3",
        "2020 Q4"},
      'bonus_region_sales_target'[remuneration_period]
    )

  VAR __DS0FilterTable2 = 
    FILTER(
      KEEPFILTERS(VALUES('bonus_applications_details'[is_eligible_for_bonus])),
      NOT('bonus_applications_details'[is_eligible_for_bonus] IN {0,
        BLANK()})
    )

EVALUATE
  TOPN(
    502,
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP(
          'Application Received Date'[Application Recieved Date],
          'bonus_applications_details'[application_code],
          'bonus_applications_details'[fundraise_name],
          'bonus_applications_details'[investor_name],
          'adviser_contacts'[adviser_code],
          'adviser_contacts'[adviser_name],
          'current_intermediaries'[intermediary_code],
          'current_intermediaries'[company_name],
          'bonus_applications_details'[amount_on_application],
          'bonus_applications_details'[pending_payment_amount],
          'bonus_applications_details'[imputed_percentage],
          'dbt_marts oisales__bonus_accelerators'[activation_type],
          'bonus_applications_details'[sales_region],
          'bonus_applications_details'[remuneration_period],
          'bonus_applications_details'[investor_code],
          'bonus_applications_details'[is_cleared],
          'bonus_bdm_sales_target'[employee_name]
        ), "IsGrandTotalRowTotal"
      ),
      __DS0FilterTable,
      __DS0FilterTable2,
      "Amount", 'bonus_applications_details'[Amount]
    ),
    [IsGrandTotalRowTotal],
    0,
    'Application Received Date'[Application Recieved Date],
    1,
    'bonus_applications_details'[application_code],
    1,
    'bonus_applications_details'[fundraise_name],
    1,
    'bonus_applications_details'[investor_name],
    1,
    'adviser_contacts'[adviser_code],
    1,
    'adviser_contacts'[adviser_name],
    1,
    'current_intermediaries'[intermediary_code],
    1,
    'current_intermediaries'[company_name],
    1,
    'bonus_applications_details'[amount_on_application],
    1,
    'bonus_applications_details'[pending_payment_amount],
    1,
    'bonus_applications_details'[imputed_percentage],
    1,
    'dbt_marts oisales__bonus_accelerators'[activation_type],
    1,
    'bonus_applications_details'[sales_region],
    1,
    'bonus_applications_details'[remuneration_period],
    1,
    'bonus_applications_details'[investor_code],
    1,
    'bonus_applications_details'[is_cleared],
    1,
    'bonus_bdm_sales_target'[employee_name],
    1
  )

ORDER BY
  [IsGrandTotalRowTotal] DESC,
  'Application Received Date'[Application Recieved Date],
  'bonus_applications_details'[application_code]/*,
  'bonus_applications_details'[fundraise_name],
  'bonus_applications_details'[investor_name],
  'adviser_contacts'[adviser_code],
  'adviser_contacts'[adviser_name],
  'current_intermediaries'[intermediary_code],
  'current_intermediaries'[company_name],
  'bonus_applications_details'[amount_on_application],
  'bonus_applications_details'[pending_payment_amount],
  'bonus_applications_details'[imputed_percentage],
  'dbt_marts oisales__bonus_accelerators'[activation_type],
  'bonus_applications_details'[sales_region],
  'bonus_applications_details'[remuneration_period],
  'bonus_applications_details'[investor_code],
  'bonus_applications_details'[is_cleared],
  'bonus_bdm_sales_target'[employee_name]*/

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I'm stripping back the DAX to see how it's translated into SQL and can see that even if I'm only querying two columns - date and amouunt - the SQL generated is querying every single field in the fact table

       

      I can't see a reason to query fields that aren't required. Does this indicate something wrongly configured in Power BI or Synapse?

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        Anonymous Perhaps we should back up. What does your source data look like and what are you trying to accomplish? I feel like we are trying to bail water when we could just plug the hole...

         

        Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

        The most important parts are:
        1. Sample data as text, use the table tool in the editing bar
        2. Expected output from sample data
        3. Explanation in words of how to get from 1. to 2.