## 3.10 Troubleshooting Multi-Dimensional Services Queries
This section describes how to execute, capture and analyze InA queries for SAP Analytics Cloud (SAC) HANA Multi-Dimensional Service.
## Introduction
SAP Analytics Cloud's query language is called InA (Information Access). InA queries are executed using HANA Multi-Dimensional Service and the result set is returned to SAC front-end for rendering.
InA queries are expressed in JSON which is not designed for human consumption but it is a text format which can at least be read by humans. To analyze an InA query for troubleshooting purposes you can record the query execution using the browser's development tools and save the query as a file. You can then execute the query in isolation using the EXECUTE_MDS procedure call and analyze the execution plan using visualization tools.
A similar approach can be used for other applications such as Analysis Office and Lumira Designer, but in these cases the third-party tool Fiddler is required.
This section describes how to execute, capture and analyze InA queries in this way and includes an overview of using Fiddler for Analysis Office:
- How to capture InA queries
- How to execute InA queries and visualize the execution plan
- How to execute multiple InA queries in parallel
- Debugging Analysis Office InA queries
## More Information
For a general introduction to MDS and frequently-asked questions which may also help with troubleshooting, refer to the SAP Note 2670064 FAQ: SAP HANA Multi-Dimensional Services (MDS) . For details of diagnosis data required by SAP Support refer to SAP Note 2691501 SAP HANA MDS: Information needed by Development Support .
Other SAP Notes related to MDS include:
- 2550833 - HANA built-in procedure: EXECUTE_MDS
- 2773755 - Guideline for Controlling Server Load with InA/MDS Analytic Requests
## How to Capture InA Queries
You can capture InA queries issued by SAC using the browser's development tools, the following steps and examples are illustrated using Google Chrome.
1. Start the Chrome debugging tool by one of the following methods:
- From the Customize and Control Google menu, choose More tools Developer tools (shortcut key Ctrl+Shift+I).
- From the menu bar select View Developer Developer tools (shortcut key F12).
2. From the panel and console which is opened select the Network tab.
3. Navigate to the problematic page in SAC and when the debug screen has loaded filter for GetResponse as shown here:
To save the network capture:
1. Look for the Record button on the toolbar of the Network tab. It should be red. If it is gray, click it once to start recording.
2. Check the Preserve log checkbox on the toolbar.
3. Click the Clear button to clear out any existing logs from the Network tab.
4. Now try to reproduce the issue that you want to analyze while the network requests are being recorded.
5. Once you have reproduced the issue, right click on a grid line with gray background on the grid of network requests and select Save as HAR with Content , and save the file to your computer. This HAR file can be reopened to analyze and replay the InA queries within.
## How to Execute and Visualize InA Queries
InA queries can be individually executed as described here. To execute an InA query the SAC user must have the role INA_ROLE. If the customer is using a technical user to execute InA queries, then the role must also be assigned to the user who wants to troubleshoot the issue.
1. Open the SQL Console in Web IDE (or any other console such as SAP HANA studio).
2. Call built-in stored procedure SYS.EXECUTE_MDS with the InA query that was previously captured as the REQUEST parameter. Both REQUEST and RESPONSE are in JSON format (data type NCLOB):
```
CALL SYS.EXECUTE_MDS('Analytics' ,'' ,'' ,'' ,'' ,REQUEST=>'{"Analytics"…' ,RESPONSE=>?);
```
It is possible to render the RESPONSE into a tabular format, but it is not needed for performance analysis.
1. You can now visualize the execution plan of an InA query by invoking the menu actions shown below:
The result of visualization depends on the query, but it resembles a SQL query visualization plan as shown below:
The execution of each step of the plan is identified in this graph. If Execute Model is expanded, it will eventually lead to column store operations that are used to fetch the data from source tables. This method can be used to evaluate the performance of model with respect to the InA queries issued by SAC.
If most of the time is spent in Process Cube and/or Prepare Query, then it is possible that the bottleneck is in MDS engine (see related topics in the SAP HANA Performance Guide for Developers ). But if most of the execution time is spent in Execute Model, then the bottleneck is most probably in the way the model is designed.
The use of workload classes to apply workload settings for your application may be one way of improving performance. If, for example, a single execution of a query in isolation does not take a lot of resources, but there is a degradation in performance in a production environment where many queries are executed simultaneously, a workload class and mapping could be used to manage the allocation of resources. This could, for example, apply a higher priority to the query or apply resource limitations on other queries.
## How to Execute Multiple InA Queries in Parallel
SAC stories contain visualizations and each visualization requires at least one InA query. The InA queries in a story are executed in parallel which will cause more load on a customer's HANA machine and which may lead to performance degradation.
Customers can test their HANA machine and the model's performance by opening multiple connections and executing multiple InA queries at once using the EXECUTE_MDS procedure explained above.
## Analysis Office InA Queries
A similar approach to that described above for SAC can be used to collect InA queries for Analysis Office and Lumira Designer using Fiddler; Fiddler is a freely-available third-party troubleshooting tool that can trace web traffic activity. Once Fiddler is installed you can open the application where the issue occurs and reproduce the problem. The query and response are then visible and, if necessary, you can save these and send them to SAP Support for analysis. Refer to the following SAP Notes for more details:
- How to download, install and run Fiddler is described in SAP Note 1766704 How to use Fiddler to collect HTTP and HTTPS traces .
- Details of the procedure to extract the query can be found in SAP Note 2839059 How To Extract Ina Json Query and Response from Fiddler Traces .
## Related Information
SAP Note 2691501
SAP Note 2550833
SAP Note 1766704
SAP Note 2839059
SAP Note 2773755
Query Execution Engine Overview
Using "Execute in SQL Engine" in Calculation Views