Guidelines for moving repeated BI workloads

Review the guidelines for moving Repeated BI workloads.

• Avoid selecting workloads that download data.
• Ensure that the datasets are compressed Parquet tables and not in text or ORC format.
• Select workloads that are an output of BI queries submitted from any BI tools and repeated multiple times.

  You can find the repeated queries under simple query profile analysis.

• A small to medium-sized VW is recommended for BI workloads.
• Redirect the BI tool to point to CDW for the workloads.
• Check that the performance of the queries in CDW is faster.
• Set proper values for run-time filters, EXEC_TIME_LIMIT_S property, and so on as recommended for TPC-DS queries.
• Set query timeouts and session timeouts to avoid non-responsive queries.
• Look at the peak spilled metrics in the query profiles and depending on the value increase the data cache size and lower the scratch space (SCRATCH_LIMIT) to cache more data for better performance.
• Increase the value of the EXEC_SINGLE_NODE_ROWS_THRESHOLD property to at least 5000 for small query optimization.
• Schedule weekly statistic data collection for better query performance.