Introduction
This blog post series describes a concept on how to monitor the system impact of SAC reporting requests (Ina/MDS) on SAP Datasphere. Starting from an analysis of the activity patterns it will enable you to group the requests by:
- runtime
- peak memory
- story- and widget name
- target view
The target audience is the central SAP Datasphere Administrator (global perspective) or a key user of an implementation project, who likes to monitor his resource consumption (project perspective).
Source of Monitoring and Initial Setup
Every reporting request of SAC is captured in a monitoring table called M_MULTIDIMENSIONAL_STATEMENT_STATISTICS.
Per default, the statistic aggregates identical requests and provides figures like max, min or average runtimes. For our analysis, we need to change the default to enforce a per-call capturing. This creates a detailed set of information allowing us to show the distribution of calls over the day or measure the fluctuations in runtime of the same call and more.
Enable Per-Call Capturing
Log-on to SAP Datasphere and navigate to the monitoring configuration: System => Configuration => Monitoring.
Here you find a section for MDS Information Tracing.
 |
To my experience so far, we didn't observe any relevant impact of system performance. Hence the warning could be ignored and the tracing could stay active as standard.
The size of the monitoring table is limited to 100.000 when tracing is enabled. Additional records will push out the oldest entries. Depending on your reporting activities, you can expect to have data from the past two days up to past two weeks.
For information can be found in the SAP Datasphere Help-Page.
Note: This statistic captures all requests - independent from e.g. duration. If a request is rated as expensive in terms of M_EXPENSIVE_STATEMENTS, you can find addition information in the system monitor => logs => statements - with filter Object Type = MDS.
Activity Overview
For a first overview, let us have look at the distribution of calls.
Report Design: Add hour to the rows and date to the columns will create the following report:
How to Read:
On the 31st of July, 130 SAC requests reached SAP Datasphere during the time interval from 8:00 to 9:00 CET.
Please note, that a single SAC Story creates several requests, e.g a story with three tables will create at minimum three requests. As a consequence we can't derive the concrete user activity, but only measure the impact in terms of requests.
If you are interessted in the user activities, please check out the SAC performance stories. Here you find the user activities and the related remote calls grouped by these activities (SAC help page).
How to Interpret:
Assuming that a story startup triggers around 5-10 requests (depending for sure on size and complexity of the story definition) we see just a low level of activity. For larger projects a typical threshold would be 50.000 requests per day.
The report above combines all types of Ina/MDS requests. Typical types are:
- request for metadata
(e.g. getting information about a datasource, read hierarchy information etc.) - analytical requests
(e.g. fetching a result set) - control requests (e.g. closing a connection, sending events or hints etc.)
Recommendation: Apply a filter on column Execution-Type and exclude the types hints and analytics-actions.
Runtime Analysis
Now let us put our focus on the runtime. The time is measured on the server itself.
Note: The runtime of a request in a client side trace (aka "har-file" or the SAC performance story) is always longer than measured in the server side trace. This is due to the time a request needs to travel from the browser via the network to the server and back.
Note: The runtime represents the elapsed time of the request and not the CPU time. Due to parallel executions or wait times, the elapsed time could be above or below the CPU time.
Runtime Distribution Overview
This report provides a system wide overview about the runtime distribution per day. The runtimes are segmented in a logarithmic scale.
Report Design: Add Runtime Seq. to the rows and date to the columns. The measure is still count.
How to Read:
On August, 1st, we had no reporting request with a runtime exceeding 5 secs. On August, 2nd, we had 22 requests with a runtime between 10 and 30 secs.
The last row shows a runtime of zero. This is an artifact of the statistic where records show a runtime of zero seconds. These artifact typically correlate with the execution type Analytics-Action. The recommendation is to add a filter on execution type excluding the value Analytics-Action.
Still some artifact remain:
How to Interpret:
As on on/going activity the response times of reporting requests should be monitored. Expensive requests should to be brought to the attention of the responsible project or developer asking for improvement activities.
Start with a definition of a threshold for "expensive" and set the filter for runtime accordingly. As a default value, I would propose larger than 10 secs. With this limit we can describe the performance status of the reporting requests:
This ratio could be made more specific by filtering by space/schema or stories etc.
How to Hand-Over - View Optimization:
An optimization on SAP Datasphere side typically requires the definition of the reporting request. This request can be executed in e.g. HANA DB Explorer and further research e.g using plan-viz could start.
The definition of the reporting request can be found in column statement_string. As this a pretty long and can't be displayed in a report, use the statement_hash uniquely identify the expensive request that should be improved.
Here is sample report with statement hash, Schema and DataSource in the rows, date in the colum and the runtime as measure. In addition, a top 10 has been applied. Using the statement hash the full request can be selected, using the schema and data source/view name can be used to find the person/project responsible.
How to Hand-Over - Story Optimization:
Here you add the story name/id and the widget id to the rows.
With this information you can inform the developer/designer about expensive calls send out by his story and widget. Typically, you would trigger both channels to optimize the request in optimizing the story definition and optimizing the model to cope best with the request.
Hint: Besides the typical checks like view persistency, SQL optimization, pre-calculate measure when filling the persistency instead of run-time calculation ... you also could consider the ina/MDS cube caching. This is enabled per default.
Use the following statement to list the cache entries:
SELECT * FROM "SYS"."M_CACHE_ENTRIES"
WHERE "CACHE_ID" = 'MDS' AND "COMPONENT" = 'CUBE'If you find just a small list, consider using an Analytical Model instead of an Analytical Dataset and in addition, create a view persistency. This will change the view type from row to column.
Peak Memory
You can follow the same steps as outlined for the runtime. Just use the measure PEAK_MEMORY_KB instead of the runtime measure and the column Peak Memory [Kb/log] for the segmentation of memory usage.
As a threshold for an expensive call I would propose 500MB as starting value.
How to Implement:
The views require a monitoring space - either the SAP_ADMIN space or the one selected in System -> Configuration -> Monitoring.
Copy the content into a *.json file and import it into your monitoring space of choice.
Reporting on SAC Requests (Ina/MDS)
The CSN File containing the table and view definitions.
Select and copy the whole line, e.g. via shift-cursor down or triple click.
{ "definitions": { "SAP_TCT_INA_V_R_01": { "kind": "entity", "elements": { "APP_USER": { "@EndUserText.label": "Application User", "type": "cds.String", "length": 256 }, "DB_USER": { "@EndUserText.label": "DB User", "type": "cds.String", "length": 256 }, "START_TIME": { "@EndUserText.label": "Time", "type": "cds.Timestamp", "key": true, "notNull": true }, "DATE": { "isCalculated": true, "@EndUserText.label": "Date", "type": "cds.Date" }, "WEEKDAY": { "isCalculated": true, "@EndUserText.label": "Weekday", "type": "cds.Integer" }, "WEEK": { "isCalculated": true, "@EndUserText.label": "Week", "type": "cds.Integer" }, "HOUR": { "isCalculated": true, "@EndUserText.label": "Hour", "type": "cds.Integer" }, "DATE_TIME": { "isCalculated": true, "@EndUserText.label": "Date-Time", "type": "cds.DateTime" }, "DATE_HOUR": { "isCalculated": true, "@EndUserText.label": "Date-Hour", "type": "cds.DateTime" }, "EXECUTION_COUNT": { "isCalculated": true, "@EndUserText.label": "Execution Count", "type": "cds.Integer", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "EXECUTION_TYPE": { "isCalculated": true, "@EndUserText.label": "Execution-Type", "type": "cds.String", "length": 256 }, "STORY_NAME": { "isCalculated": true, "@EndUserText.label": "Story Name", "type": "cds.String", "length": 256 }, "STORY_ID": { "isCalculated": true, "@EndUserText.label": "Story Id", "type": "cds.String", "length": 256 }, "WIDGET_ID": { "isCalculated": true, "@EndUserText.label": "Widget Id", "type": "cds.String", "length": 256 }, "DATA_SOURCE_NAME": { "isCalculated": true, "@EndUserText.label": "Data Source", "type": "cds.String", "length": 256 }, "DATA_SOURCE_SCHEMA": { "isCalculated": true, "@EndUserText.label": "Data Source Schema", "type": "cds.String", "length": 256 }, "DATA_SOURCE_INSTANCE_ID": { "isCalculated": true, "@EndUserText.label": "Data Source Instance Id", "type": "cds.String", "length": 256 }, "RUNTIME": { "@EndUserText.label": "Runtime [ms]", "type": "cds.Integer64", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "RUNTIME_SEG_SECS": { "@EndUserText.label": "Runtime Seg.[secs]", "type": "cds.String", "length": 100 }, "RUNTIME_SEG_LOG": { "@EndUserText.label": "Runtime Seq.[ms/log]", "type": "cds.String", "length": 100, "@Analytics.dimension": true }, "PEAK_MEMORY_KB": { "isCalculated": true, "@EndUserText.label": "Peak Memory [Kb]", "type": "cds.Integer64", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "PEAK_MEM_SEG_KB_LOG": { "@EndUserText.label": "Peak Memory [Kb/log]", "type": "cds.String", "length": 100 }, "ROW_COUNT": { "isCalculated": true, "@EndUserText.label": "Count", "type": "cds.Integer", "@AnalyticsDetails.measureType": { "#": "BASE" }, "@Aggregation.default": { "#": "SUM" } }, "STATEMENT_HASH": { "@EndUserText.label": "Statement Hash", "type": "cds.String", "length": 32 }, "STATEMENT_STRING": { "@EndUserText.label": "Statement String", "type": "cds.LargeString" } }, "query": { "SELECT": { "from": { "SELECT": { "from": { "ref": [ "M_MULTIDIMENSIONAL_STATEMENT_S" ], "as": "base" }, "columns": [ { "ref": [ "APPLICATION_USER_NAME" ], "as": "APP_USER" }, { "ref": [ "USER_NAME" ], "as": "DB_USER" }, { "ref": [ "LAST_EXECUTION_TIMESTAMP" ], "as": "START_TIME" }, { "func": "TO_DATE", "args": [ { "ref": [ "LAST_EXECUTION_TIMESTAMP" ] } ], "as": "DATE" }, { "func": "WEEKDAY", "args": [ { "ref": [ "LAST_EXECUTION_TIMESTAMP" ] } ], "as": "WEEKDAY" }, { "func": "WEEK", "args": [ { "ref": [ "LAST_EXECUTION_TIMESTAMP" ] } ], "as": "WEEK" }, { "func": "HOUR", "args": [ { "ref": [ "LAST_EXECUTION_TIMESTAMP" ] } ], "as": "HOUR" }, { "func": "TO_SECONDDATE", "args": [ { "func": "SUBSTRING", "args": [ { "ref": [ "LAST_EXECUTION_TIMESTAMP" ] }, { "val": 1 }, { "val": 19 } ] }, { "val": "YYYY-MM-DD HH:MI:SS" } ], "as": "DATE_TIME" }, { "func": "TO_SECONDDATE", "args": [ { "func": "SUBSTRING", "args": [ { "ref": [ "LAST_EXECUTION_TIMESTAMP" ] }, { "val": 1 }, { "val": 13 } ] }, { "val": "YYYY-MM-DD HH" } ], "as": "DATE_HOUR" }, { "func": "to_int", "args": [ { "ref": [ "EXECUTION_COUNT" ] } ], "as": "EXECUTION_COUNT" }, { "xpr": [ "case", "when", { "val": "{\"Analytics\":{\"Actions\":" }, "=", { "func": "left", "args": [ { "ref": [ "STATEMENT_STRING" ] }, { "val": 24 } ] }, "then", { "val": "Analytics-Actions" }, "else", { "func": "substr_after", "args": [ { "func": "substr_before", "args": [ { "ref": [ "STATEMENT_STRING" ] }, { "val": "\":" } ] }, { "val": "\"" } ] }, "end" ], "as": "EXECUTION_TYPE" }, { "func": "substr_before", "args": [ { "func": "substr_after", "args": [ { "func": "substr_after", "args": [ { "ref": [ "STATEMENT_STRING" ] }, { "val": "\"ClientInfo\":{\"Context\":" } ] }, { "val": "\"StoryName\":\"" } ] }, { "val": "\"" } ], "as": "STORY_NAME" }, { "func": "substr_before", "args": [ { "func": "substr_after", "args": [ { "func": "substr_after", "args": [ { "ref": [ "STATEMENT_STRING" ] }, { "val": "\"ClientInfo\":{\"Context\":" } ] }, { "val": "\"StoryId\":\"" } ] }, { "val": "\"" } ], "as": "STORY_ID" }, { "func": "substr_before", "args": [ { "func": "substr_after", "args": [ { "func": "substr_after", "args": [ { "ref": [ "STATEMENT_STRING" ] }, { "val": "\"ClientInfo\":{\"Context\":" } ] }, { "val": "\"WidgetId\":[" } ] }, { "val": "]" } ], "as": "WIDGET_ID" }, { "func": "concat", "args": [ { "func": "substr_before", "args": [ { "func": "substr_after", "args": [ { "func": "substr_after", "args": [ { "ref": [ "STATEMENT_STRING" ] }, { "val": "\"DataSource\":{\"InstanceId\":" } ] }, { "val": "\"ObjectName\":\"" } ] }, { "val": "\"" } ] }, { "func": "substr_before", "args": [ { "func": "substr_after", "args": [ { "func": "substr_after", "args": [ { "ref": [ "STATEMENT_STRING" ] }, { "val": "\"DataSource\":{\"Context\":" } ] }, { "val": "\"ObjectName\":\"" } ] }, { "val": "\"" } ] } ], "as": "DATA_SOURCE_NAME" }, { "func": "concat", "args": [ { "func": "substr_before", "args": [ { "func": "substr_after", "args": [ { "func": "substr_after", "args": [ { "ref": [ "STATEMENT_STRING" ] }, { "val": "\"DataSource\":{\"InstanceId\":" } ] }, { "val": "\"SchemaName\":\"" } ] }, { "val": "\"" } ] }, { "func": "substr_before", "args": [ { "func": "substr_after", "args": [ { "func": "substr_after", "args": [ { "ref": [ "STATEMENT_STRING" ] }, { "val": "\"DataSource\":{\"Context\":" } ] }, { "val": "\"SchemaName\":\"" } ] }, { "val": "\"" } ] } ], "as": "DATA_SOURCE_SCHEMA" }, { "func": "concat", "args": [ { "func": "substr_before", "args": [ { "func": "substr_after", "args": [ { "ref": [ "STATEMENT_STRING" ] }, { "val": "\"DataSource\":{\"InstanceId\":\"" } ] }, { "val": "\"" } ] }, { "func": "substr_before", "args": [ { "func": "substr_after", "args": [ { "func": "substr_after", "args": [ { "ref": [ "STATEMENT_STRING" ] }, { "val": "\"DataSource\":{\"Context\":" } ] }, { "val": "\"InstanceId\":\"" } ] }, { "val": "\"" } ] } ], "as": "DATA_SOURCE_INSTANCE_ID" }, { "ref": [ "TOTAL_EXECUTION_TIME" ], "as": "RUNTIME" }, { "func": "to_int", "args": [ { "xpr": [ { "ref": [ "TOTAL_EXECUTION_TIME" ] }, "/", { "val": 1000 } ] } ], "as": "RUNTIME_SEG_SECS" }, { "xpr": [ "case", "when", { "ref": [ "TOTAL_EXECUTION_TIME" ] }, "=", { "val": 0 }, "then", { "val": 0 }, "when", { "func": "log", "args": [ { "val": 10 }, { "ref": [ "TOTAL_EXECUTION_TIME" ] } ] }, "-", { "func": "to_int", "args": [ { "func": "log", "args": [ { "val": 10 }, { "ref": [ "TOTAL_EXECUTION_TIME" ] } ] } ] }, "<", { "val": 0.477 }, "then", { "val": 3 }, "when", { "func": "log", "args": [ { "val": 10 }, { "ref": [ "TOTAL_EXECUTION_TIME" ] } ] }, "-", { "func": "to_int", "args": [ { "func": "log", "args": [ { "val": 10 }, { "ref": [ "TOTAL_EXECUTION_TIME" ] } ] } ] }, "<", { "val": 0.6989 }, "then", { "val": 5 }, "else", { "val": 10 }, "end" ], "as": "RUNTIME_SEG_LOG_DELTA" }, { "xpr": [ "case", "when", { "ref": [ "TOTAL_EXECUTION_TIME" ] }, "=", { "val": 0 }, "then", { "val": 0 }, "else", { "func": "to_int", "args": [ { "func": "power", "args": [ { "val": 10 }, { "func": "to_int", "args": [ { "func": "log", "args": [ { "val": 10 }, { "ref": [ "TOTAL_EXECUTION_TIME" ] } ] } ] } ] } ] }, "end" ], "as": "RUNTIME_SEG_LOG" }, { "func": "to_bigint", "args": [ { "xpr": [ { "ref": [ "MAX_EXECUTION_MEMORY_SIZE" ] }, "/", { "val": 1024 } ] } ], "as": "PEAK_MEMORY_KB" }, { "xpr": [ "case", "when", { "ref": [ "TOTAL_EXECUTION_TIME" ] }, "=", { "val": 0 }, "then", { "val": 0 }, "when", { "func": "log", "args": [ { "val": 10 }, { "xpr": [ { "ref": [ "MAX_EXECUTION_MEMORY_SIZE" ] }, "/", { "val": 1024 } ] } ] }, "-", { "func": "to_int", "args": [ { "func": "log", "args": [ { "val": 10 }, { "xpr": [ { "ref": [ "MAX_EXECUTION_MEMORY_SIZE" ] }, "/", { "val": 1024 } ] } ] } ] }, "<", { "val": 0.477 }, "then", { "val": 3 }, "when", { "func": "log", "args": [ { "val": 10 }, { "xpr": [ { "ref": [ "MAX_EXECUTION_MEMORY_SIZE" ] }, "/", { "val": 1024 } ] } ] }, "-", { "func": "to_int", "args": [ { "func": "log", "args": [ { "val": 10 }, { "xpr": [ { "ref": [ "MAX_EXECUTION_MEMORY_SIZE" ] }, "/", { "val": 1024 } ] } ] } ] }, "<", { "val": 0.6989 }, "then", { "val": 5 }, "else", { "val": 10 }, "end" ], "as": "PEAK_MEM_SEG_KB_LOG_DELTA" }, { "xpr": [ "case", "when", { "ref": [ "MAX_EXECUTION_MEMORY_SIZE" ] }, "=", { "val": 0 }, "then", { "val": 0 }, "else", { "func": "to_int", "args": [ { "func": "power", "args": [ { "val": 10 }, { "func": "to_int", "args": [ { "func": "log", "args": [ { "val": 10 }, { "xpr": [ { "ref": [ "MAX_EXECUTION_MEMORY_SIZE" ] }, "/", { "val": 1024 } ] } ] } ] } ] } ] }, "end" ], "as": "PEAK_MEM_SEG_KB_LOG" }, { "val": 1, "as": "ROW_COUNT" }, { "ref": [ "STATEMENT_HASH" ] }, { "ref": [ "STATEMENT_STRING" ] } ] }, "as": "Subselect1" }, "columns": [ { "ref": [ "APP_USER" ] }, { "ref": [ "DB_USER" ] }, { "ref": [ "START_TIME" ] }, { "ref": [ "DATE" ] }, { "ref": [ "WEEKDAY" ] }, { "ref": [ "WEEK" ] }, { "ref": [ "HOUR" ] }, { "ref": [ "DATE_TIME" ] }, { "ref": [ "DATE_HOUR" ] }, { "ref": [ "EXECUTION_COUNT" ] }, { "ref": [ "EXECUTION_TYPE" ] }, { "ref": [ "STORY_NAME" ] }, { "ref": [ "STORY_ID" ] }, { "ref": [ "WIDGET_ID" ] }, { "ref": [ "DATA_SOURCE_NAME" ] }, { "ref": [ "DATA_SOURCE_SCHEMA" ] }, { "ref": [ "DATA_SOURCE_INSTANCE_ID" ] }, { "ref": [ "RUNTIME" ] }, { "xpr": [ { "ref": [ "RUNTIME_SEG_SECS" ] }, "||", { "val": " < " }, "||", { "ref": [ "RUNTIME_SEG_SECS" ] }, "+", { "val": 1 } ], "as": "RUNTIME_SEG_SECS" }, { "xpr": [ "case", "when", { "ref": [ "RUNTIME_SEG_LOG_DELTA" ] }, "=", { "val": 3 }, "then", { "val": "(" }, "||", { "func": "round", "args": [ { "func": "log", "args": [ { "val": 10 }, { "ref": [ "RUNTIME_SEG_LOG" ] } ] } ] }, "||", { "val": ") " }, "||", { "ref": [ "RUNTIME_SEG_LOG" ] }, "||", { "val": " < " }, "||", { "ref": [ "RUNTIME_SEG_LOG" ] }, "*", { "val": 3 }, "when", { "ref": [ "RUNTIME_SEG_LOG_DELTA" ] }, "=", { "val": 5 }, "then", { "val": "(" }, "||", { "func": "round", "args": [ { "func": "log", "args": [ { "val": 10 }, { "ref": [ "RUNTIME_SEG_LOG" ] } ] } ] }, "||", { "val": ") " }, "||", { "ref": [ "RUNTIME_SEG_LOG" ] }, "*", { "val": 3 }, "||", { "val": " < " }, "||", { "ref": [ "RUNTIME_SEG_LOG" ] }, "*", { "val": 5 }, "when", { "ref": [ "RUNTIME_SEG_LOG_DELTA" ] }, "=", { "val": 10 }, "then", { "val": "(" }, "||", { "func": "round", "args": [ { "func": "log", "args": [ { "val": 10 }, { "ref": [ "RUNTIME_SEG_LOG" ] } ] } ] }, "||", { "val": ") " }, "||", { "ref": [ "RUNTIME_SEG_LOG" ] }, "*", { "val": 5 }, "||", { "val": " < " }, "||", { "ref": [ "RUNTIME_SEG_LOG" ] }, "*", { "val": 10 }, "else", { "val": "0" }, "end" ], "as": "RUNTIME_SEG_LOG" }, { "ref": [ "PEAK_MEMORY_KB" ] }, { "xpr": [ "case", "when", { "ref": [ "PEAK_MEM_SEG_KB_LOG_DELTA" ] }, "=", { "val": 3 }, "then", { "val": "(" }, "||", { "func": "round", "args": [ { "func": "log", "args": [ { "val": 10 }, { "ref": [ "PEAK_MEM_SEG_KB_LOG" ] } ] } ] }, "||", { "val": ") " }, "||", { "ref": [ "PEAK_MEM_SEG_KB_LOG" ] }, "||", { "val": " < " }, "||", { "ref": [ "PEAK_MEM_SEG_KB_LOG" ] }, "*", { "val": 3 }, "when", { "ref": [ "PEAK_MEM_SEG_KB_LOG_DELTA" ] }, "=", { "val": 5 }, "then", { "val": "(" }, "||", { "func": "round", "args": [ { "func": "log", "args": [ { "val": 10 }, { "ref": [ "PEAK_MEM_SEG_KB_LOG" ] } ] } ] }, "||", { "val": ") " }, "||", { "ref": [ "PEAK_MEM_SEG_KB_LOG" ] }, "*", { "val": 3 }, "||", { "val": " < " }, "||", { "ref": [ "PEAK_MEM_SEG_KB_LOG" ] }, "*", { "val": 5 }, "when", { "ref": [ "PEAK_MEM_SEG_KB_LOG_DELTA" ] }, "=", { "val": 10 }, "then", { "val": "(" }, "||", { "func": "round", "args": [ { "func": "log", "args": [ { "val": 10 }, { "ref": [ "PEAK_MEM_SEG_KB_LOG" ] } ] } ] }, "||", { "val": ") " }, "||", { "ref": [ "PEAK_MEM_SEG_KB_LOG" ] }, "*", { "val": 5 }, "||", { "val": " < " }, "||", { "ref": [ "PEAK_MEM_SEG_KB_LOG" ] }, "*", { "val": 10 }, "else", { "val": "0" }, "end" ], "as": "PEAK_MEM_SEG_KB_LOG" }, { "ref": [ "ROW_COUNT" ] }, { "ref": [ "STATEMENT_HASH" ] }, { "ref": [ "STATEMENT_STRING" ] } ] } }, "@EndUserText.label": "SAP_TCT_INA_V_R_01", "@ObjectModel.modelingPattern": { "#": "ANALYTICAL_FACT" }, "@ObjectModel.supportedCapabilities": [ { "#": "ANALYTICAL_FACT" }, { "#": "ANALYTICAL_PROVIDER" } ], "@DataWarehouse.consumption.external": true, "@DataWarehouse.sqlEditor.query": "/*\n\n View Name: SAP_TCT_INA_INA_V_R_01\n\n Docu : Source View M_MULTIDIMENSIONAL_STATEMENT_STATISTICS\n https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.05/en-US/5b04f05f501f4f91aea202f139... This view parses the ina request and extracts the following properties: \n - Ina/MDS Requests Grouped per Date and Hour \n - Ina/MDS Requests Grouped by Type \n - Runtime Segmentation \n - Name of the Story (currently only provided for Analytics and Batch Calls)\n - Name of the first data source found (for batch, several data sources are possible)\n \n For performance reasons we used the string parsing instead of hana json statements.\n \n This works best with the MDS setting to write unique records ( MDS key name is \"statistics_unique_entry\" ) and a \n max rowcount increased to a limit of 100.000 (MDS key name is \"max_statistics_entry_count\")\n\n Example for setting the parameters\n ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'System' ) SET\n ('mds', '<mds key name>') = 'value' WITH RECONFIGURE;\n \n This configuration can be set in SAP Datasphere as \"Trace\" for MDS: navigate to system => configuration => monitoring. \n\n*/\n\nSELECT \n \"APP_USER\",\n \"DB_USER\",\n \"START_TIME\",\n \"DATE\",\n \"WEEKDAY\",\n \"WEEK\",\n \"HOUR\",\n \"DATE_TIME\",\n \"DATE_HOUR\",\n \n \"EXECUTION_COUNT\",\n \"EXECUTION_TYPE\",\n \n \"STORY_NAME\",\n \"STORY_ID\",\n \"WIDGET_ID\",\n \n \"DATA_SOURCE_NAME\",\n \"DATA_SOURCE_SCHEMA\",\n \"DATA_SOURCE_INSTANCE_ID\",\n \n -- Runtime: raw value in ms, and segmentation in full seconds and log-scale (ms)\n \"RUNTIME\",\n \"RUNTIME_SEG_SECS\" || ' < ' || \"RUNTIME_SEG_SECS\" + 1 as \"RUNTIME_SEG_SECS\",\n case \n when \"RUNTIME_SEG_LOG_DELTA\" = 3 then '(' || round(log(10,\"RUNTIME_SEG_LOG\")) || ') ' || \"RUNTIME_SEG_LOG\" || ' < ' || \"RUNTIME_SEG_LOG\" * 3 \n when \"RUNTIME_SEG_LOG_DELTA\" = 5 then '(' || round(log(10,\"RUNTIME_SEG_LOG\")) || ') ' || \"RUNTIME_SEG_LOG\" * 3 || ' < ' || \"RUNTIME_SEG_LOG\" * 5 \n when \"RUNTIME_SEG_LOG_DELTA\" = 10 then '(' || round(log(10,\"RUNTIME_SEG_LOG\")) || ') ' || \"RUNTIME_SEG_LOG\" * 5 || ' < ' || \"RUNTIME_SEG_LOG\" * 10\n else '0' end as \"RUNTIME_SEG_LOG\",\n \n -- Memory: raw value and logarithmic segmentation\n \"PEAK_MEMORY_KB\",\n case \n when \"PEAK_MEM_SEG_KB_LOG_DELTA\" = 3 then '(' || round(log(10,\"PEAK_MEM_SEG_KB_LOG\")) || ') ' || \"PEAK_MEM_SEG_KB_LOG\" || ' < ' || \"PEAK_MEM_SEG_KB_LOG\" * 3 \n when \"PEAK_MEM_SEG_KB_LOG_DELTA\" = 5 then '(' || round(log(10,\"PEAK_MEM_SEG_KB_LOG\")) || ') ' || \"PEAK_MEM_SEG_KB_LOG\" * 3 || ' < ' || \"PEAK_MEM_SEG_KB_LOG\" * 5 \n when \"PEAK_MEM_SEG_KB_LOG_DELTA\" = 10 then '(' || round(log(10,\"PEAK_MEM_SEG_KB_LOG\")) || ') ' || \"PEAK_MEM_SEG_KB_LOG\" * 5 || ' < ' || \"PEAK_MEM_SEG_KB_LOG\" * 10\n else '0' end as \"PEAK_MEM_SEG_KB_LOG\",\n\n \"ROW_COUNT\",\n \"STATEMENT_HASH\",\n \"STATEMENT_STRING\"\n\nFROM (\n\n SELECT \n \n \"APPLICATION_USER_NAME\" as \"APP_USER\",\n \"USER_NAME\" as \"DB_USER\",\n \n \"LAST_EXECUTION_TIMESTAMP\" as \"START_TIME\",\n \n \tTO_DATE(\"LAST_EXECUTION_TIMESTAMP\") as \"DATE\",\n \tWEEKDAY(\"LAST_EXECUTION_TIMESTAMP\") as \"WEEKDAY\",\n \tWEEK(\"LAST_EXECUTION_TIMESTAMP\") as \"WEEK\",\n \tHOUR(\"LAST_EXECUTION_TIMESTAMP\") as \"HOUR\", \t\n \n \tTO_SECONDDATE(SUBSTRING(\"LAST_EXECUTION_TIMESTAMP\", 1, 19), 'YYYY-MM-DD HH:MI:SS') as \"DATE_TIME\",\n \tTO_SECONDDATE(SUBSTRING(\"LAST_EXECUTION_TIMESTAMP\", 1, 13), 'YYYY-MM-DD HH') as \"DATE_HOUR\",\n \n -- per default, the statistic figures are aggregated per identical mds statement\n -- If mds tracing is enabled, execution_count for a record is always 1 (no aggregation takes place)\n \tto_int(\"EXECUTION_COUNT\") as \"EXECUTION_COUNT\",\n \t\n -- request type: e.g. METADATA, ANALYTICS\n case\n when '{\"Analytics\":{\"Actions\":' = left(\"STATEMENT_STRING\",24) then 'Analytics-Actions'\n else substr_after(substr_before(\"STATEMENT_STRING\", '\":'), '\"') \n end as EXECUTION_TYPE,\n \n -- storyName/Id/Widget\n substr_before(substr_after(substr_after(\"STATEMENT_STRING\",'\"ClientInfo\":{\"Context\":'),'\"StoryName\":\"'),'\"') STORY_NAME,\n substr_before(substr_after(substr_after(\"STATEMENT_STRING\",'\"ClientInfo\":{\"Context\":'),'\"StoryId\":\"'),'\"') STORY_ID , \n substr_before(substr_after(substr_after(\"STATEMENT_STRING\",'\"ClientInfo\":{\"Context\":'),'\"WidgetId\":['),']') as WIDGET_ID , \n \n -- use native string operations to save performance over json_value/json_query:\n concat( \n substr_before(substr_after(substr_after(\"STATEMENT_STRING\",'\"DataSource\":{\"InstanceId\":'),'\"ObjectName\":\"'),'\"')\n , substr_before(substr_after(substr_after(\"STATEMENT_STRING\",'\"DataSource\":{\"Context\":'),'\"ObjectName\":\"'),'\"')\n ) as DATA_SOURCE_NAME,\n \n concat( \n substr_before(substr_after(substr_after(\"STATEMENT_STRING\",'\"DataSource\":{\"InstanceId\":'),'\"SchemaName\":\"'),'\"')\n , substr_before(substr_after(substr_after(\"STATEMENT_STRING\",'\"DataSource\":{\"Context\":'),'\"SchemaName\":\"'),'\"')\n ) as DATA_SOURCE_SCHEMA,\n \n concat( \n substr_before(substr_after(\"STATEMENT_STRING\",'\"DataSource\":{\"InstanceId\":\"'),'\"')\n , substr_before(substr_after(substr_after(\"STATEMENT_STRING\",'\"DataSource\":{\"Context\":'),'\"InstanceId\":\"'),'\"')\n ) as DATA_SOURCE_INSTANCE_ID,\n \n -- runtime and segments for reporting\n \"TOTAL_EXECUTION_TIME\" as \"RUNTIME\",\n \n \tto_int(\"TOTAL_EXECUTION_TIME\" / 1000) as \"RUNTIME_SEG_SECS\",\n \tcase \n \t when \"TOTAL_EXECUTION_TIME\" = 0 then 0\n \t when log(10,\"TOTAL_EXECUTION_TIME\") - to_int(log(10,\"TOTAL_EXECUTION_TIME\")) < 0.477 then 3 \n \t when log(10,\"TOTAL_EXECUTION_TIME\") - to_int(log(10,\"TOTAL_EXECUTION_TIME\")) < 0.6989 then 5\n \t else 10 end as \"RUNTIME_SEG_LOG_DELTA\",\n \n case \n \t when \"TOTAL_EXECUTION_TIME\" = 0 then 0\n \t else to_int(power(10,to_int(log(10,\"TOTAL_EXECUTION_TIME\")))) end as \"RUNTIME_SEG_LOG\",\n \n to_bigint(\"MAX_EXECUTION_MEMORY_SIZE\" / 1024 ) as \"PEAK_MEMORY_KB\",\n \n case \n \t when \"TOTAL_EXECUTION_TIME\" = 0 then 0\n \t when log(10,\"MAX_EXECUTION_MEMORY_SIZE\" / 1024) - to_int(log(10,\"MAX_EXECUTION_MEMORY_SIZE\" / 1024)) < 0.477 then 3 \n \t when log(10,\"MAX_EXECUTION_MEMORY_SIZE\" / 1024) - to_int(log(10,\"MAX_EXECUTION_MEMORY_SIZE\" / 1024)) < 0.6989 then 5\n \t else 10 end as \"PEAK_MEM_SEG_KB_LOG_DELTA\",\n \n case \n \t when \"MAX_EXECUTION_MEMORY_SIZE\" = 0 then 0\n \t else to_int(power(10,to_int(log(10,\"MAX_EXECUTION_MEMORY_SIZE\"/1024)))) end as \"PEAK_MEM_SEG_KB_LOG\",\n \n 1 as \"ROW_COUNT\",\n \"STATEMENT_HASH\",\n \"STATEMENT_STRING\"\n \n FROM \"M_MULTIDIMENSIONAL_STATEMENT_S\" as base )" }, "M_MULTIDIMENSIONAL_STATEMENT_S": { "kind": "entity", "elements": { "HOST": { "@EndUserText.label": "Host name", "type": "cds.String", "length": 64 }, "PORT": { "@EndUserText.label": "Internal port", "type": "cds.Integer" }, "STATEMENT_STRING": { "@EndUserText.label": "Statement string", "type": "cds.LargeString" }, "STATEMENT_HASH": { "@EndUserText.label": "Statement hash", "type": "cds.String", "length": 32 }, "USER_NAME": { "@EndUserText.label": "User name", "type": "cds.String", "length": 256 }, "APPLICATION_USER_NAME": { "@EndUserText.label": "Application user name", "type": "cds.String", "length": 256 }, "APPLICATION_NAME": { "@EndUserText.label": "Application name", "type": "cds.String", "length": 256 }, "STATEMENT_TYPE": { "@EndUserText.label": "Statement type which can be either INA or MDX", "type": "cds.String", "length": 4 }, "LAST_CONNECTION_ID": { "@EndUserText.label": "Last connection ID that executed the statement", "type": "cds.Integer" }, "LAST_EXECUTION_TIMESTAMP": { "@EndUserText.label": "Last execution timestamp", "type": "cds.Timestamp" }, "LAST_METADATA_READ_DURATION": { "@EndUserText.label": "Last time spent in metadata read", "type": "cds.Integer64" }, "LAST_QUERY_PREPARATION_DURATION": { "@EndUserText.label": "Last time spent in query preparation", "type": "cds.Integer64" }, "LAST_PLAN_EXECUTION_DURATION": { "@EndUserText.label": "Last time spent in plan execution", "type": "cds.Integer64" }, "LAST_POST_PROCESSING_DURATION": { "@EndUserText.label": "Last time spent in post processing", "type": "cds.Integer64" }, "LAST_CUBE_PROCESSING_DURATION": { "@EndUserText.label": "Last time spent in cube processing", "type": "cds.Integer64" }, "LAST_PLAN_EXECUTION_RESULTSET_SIZE": { "@EndUserText.label": "Last size of the intermediate result returned by plan execution in bytes", "type": "cds.Integer64" }, "LAST_RESULTSET_SIZE": { "@EndUserText.label": "Last size of the resultset in bytes", "type": "cds.Integer64" }, "LAST_RESULTSET_CELL_COUNT": { "@EndUserText.label": "Last number of cells in the resultset", "type": "cds.Integer64" }, "LAST_HIERARCHY_COUNT": { "@EndUserText.label": "Last number of hierarchies", "type": "cds.Integer" }, "LAST_CACHED_HIERARCHY_COUNT": { "@EndUserText.label": "Last number of hierarchy cache hit", "type": "cds.Integer" }, "LAST_CALCULATION_ENTITY_COUNT": { "@EndUserText.label": "Last number of calculation entities", "type": "cds.Integer" }, "LAST_CALCULATION_ENTITY_GROUP_COUNT": { "@EndUserText.label": "Last number of calculation entity groups", "type": "cds.Integer" }, "LAST_DRILL_DIMENSION_COUNT": { "@EndUserText.label": "Last number of dimensions in drill", "type": "cds.Integer" }, "LAST_AGGREGATION_DIMENSION_COUNT": { "@EndUserText.label": "Last number of dimensions in aggregation", "type": "cds.Integer" }, "LAST_SERIALIZED_CUBE_SIZE": { "@EndUserText.label": "Last size of the serialized cube in bytes", "type": "cds.Integer64" }, "LAST_REQUEST_QUEUE_COUNT": { "@EndUserText.label": "Last number of jobs waiting in the request queue", "type": "cds.Integer64" }, "LAST_EXECUTION_STATUS": { "@EndUserText.label": "Last execution status of the statement", "type": "cds.String", "length": 16 }, "LAST_PERFORMANCE_DATA": { "@EndUserText.label": "Last performance metrics", "type": "cds.LargeString" }, "EXECUTION_COUNT": { "@EndUserText.label": "Execution count", "type": "cds.Integer" }, "TOTAL_METADATA_CACHE_HIT_COUNT": { "@EndUserText.label": "Total number of metadata cache hit", "type": "cds.Integer" }, "TOTAL_DATA_CACHE_HIT_COUNT": { "@EndUserText.label": "Total number of data cache hit", "type": "cds.Integer" }, "TOTAL_EXECUTION_TIME": { "@EndUserText.label": "Total time of execution", "type": "cds.Integer64" }, "AVG_EXECUTION_TIME": { "@EndUserText.label": "Average time of execution", "type": "cds.Integer64" }, "MIN_EXECUTION_TIME": { "@EndUserText.label": "Minimum time of execution", "type": "cds.Integer64" }, "MAX_EXECUTION_TIME": { "@EndUserText.label": "Maximum time of execution", "type": "cds.Integer64" }, "MAX_CALLED_THREAD_COUNT": { "@EndUserText.label": "Maximum thread count", "type": "cds.Integer" }, "MAX_EXECUTION_MEMORY_SIZE": { "@EndUserText.label": "Maximum memory size", "type": "cds.Integer64" } }, "@EndUserText.label": "M_MULTIDIMENSIONAL_STATEMENT_STATISTICS", "@ObjectModel.modelingPattern": { "#": "DATA_STRUCTURE" }, "@ObjectModel.supportedCapabilities": [ { "#": "DATA_STRUCTURE" } ], "@DataWarehouse.external.schema": "SYS", "@DataWarehouse.external.entity": "M_MULTIDIMENSIONAL_STATEMENT_STATISTICS" } }, "version": { "csn": "1.0" }, "meta": { "creator": "CDS Compiler v1.19.2" }, "$version": "1.0"}The views can be directly used in the preview of SAP Datasphere or e.g. via SAP Analytics Cloud.
Conclusion
Following this blog post supports you in monitoring your HANA system and identify the resource impact of SAC reporting requests. In addition, it enables you to identify expensive calls (runtime, peak memory).
Outlook: Stability of Response Times
Once the reporting requests are in good range with respect to performance and peak memory, the next topics to check will be the stability of response times. Here are two aspects to measure the stability:
- Median of Requests
- Compare identical requests
Another blog post covering this aspect is planned - stay tuned.
Building History
With an increasing size of the monitoring table, the reporting performance of the proposed view will decrease. Hence I recommend to introduce a persistency. One approach that worked well at customer projects is the following configuration:
- Create a copy of SAP_TCT_INA_V_R_01
- Create a dataflow to fill a local table HIST_TCT_INA_V_R_01 - mode: appending data
- Add a filter for START_TIME to the view. Set the filter value to the max value of HIST_TCT_INA_V_R_01. This can be done dynamically.
- Schedule the dataflow on an hourly or daily basis
Here is the snipped to fetch the must current start_time from the persistence:
WHERE START_TIME >
( SELECT
CASE WHEN MAX(START_TIME) IS NULL
THEN TO_DATE(0)
ELSE MAX(START_TIME) END
FROM HIST_TCT_INA_V_R_01 )Looking forward to hear your feedback and proposals for improvement.
I’d be happy to hear your thoughts, ideas and comments on this blog post. Let me know in the comments!
- SAP Managed Tags:
3 Comments
