Using the Query Diagnostics Tool in SAS® Visual Analytics on SAS® Viya®


Overview

SAS Visual Analytics on SAS Viya provides a Query Diagnostics Tool. This tool can be used to investigate issues concerning the queries that SAS Visual Analytics generates for SAS® Cloud Analytic Services (CAS). For example, it can be used to investigate performance problems.

Launch the Query Diagnostic Tool

To access this tool, you must do the following:

  1. Open your report in SAS Visual Analytics.
  2. Select the object that you want to examine.
  3. Press Ctrl+Alt+Q on your keyboard.

A window similar to the following should appear (notations added), but might vary slightly depending on your SAS Visual Analytics release:

  1. This drop-down list contains queries generated for all objects that have been loaded in the report.
  2. The options menu includes the following items:
    • Retrieve from cache - Indicates whether the query should pull results from cache (enabled) or run against the CAS server directly (disabled).
    • Homogenize - An experimental option that helps determine whether results should be pulled from cache. For example, rather than use unique ID names (bi3), this option tries to use actual column names (AccountID) to see whether the same query was already cached from another report (where the IDs might be different). 
    • CAS log - Used to display the log from the CAS server.
    • BIData log - Used to display the log from the reportdata microservice.
    • String Table - Breaks out categorical values into an index file.
  3. Execute - Runs the query.
  4. Delete - Administrators only. This clears the report-data result cache for all users.
  5. XML/JSON toggle - Displays output, where applicable, in XML format or JSON format. The default format is XML.
  6. Body - The body of the query.


How to Use the Query Diagnostic Tool

To use this tool, complete these steps:

  1. Select which query from the drop-down list that you would like to run. If you had an object selected when you pressed CTRL+ALT+Q, then the query for that object is selected in the list. The list contains only queries that have already been generated by the report. If your report contains several pages, but you have loaded only the first page, then the drop-down list contains queries from only the first page.
  2. Optional. Set any options as needed. When troubleshooting CAS performance, disable Retrieve from cache and enable both CAS log and BIData log. In addition, always leave Homogenize as the default enabled.

  3. Press the execute button to run the query. There is no progress indicator to let you know the query is running.
     

After the query completes, you see output similar to the following:

  1. URL tab - This tab appears as soon as you press the execute button, and contains the full URI that was sent to server. (Not available after release 8.5.)
  2. Results tab - This tab appears after the query completes. It displays the results from the query in XML or JSON format. If the String Table option is also enabled, then those results appear here as well.
  3. Results (data) tab - Contains the raw data returned from the query in CSV format. If the String Table option is enabled, then those results appear here as well.
  4. Export button and run time in milliseconds - The run time represents the time that it took to run the query and return results. The Export button exports the Results (data), the CAS Action Log, and the Server Log (if those options are enabled).
  5. CAS Action Log tab - Appears only if CAS log option is enabled. Contains logging output from the CAS server.
  6. Server Log tab - Appears only if the BIData log option is enabled. Contains logging output from the reportdata microservice.