Introduction
This blog post is intended to provide a guide on how to measure the amount of resources that your system uses for seamless planning.
With seamless planning you are able to store the data of planning models from SAP Analytics Cloud in SAP Datasphere. Consequently, resources of SAP Datasphere are used for data storage, planning activities and analytic queries. The purpose of this blog is to guide you through the options of monitoring those resource consumption. Starting from your first planning model to the overall tenant resources you will be shown easy and straight forward methods to get the information you need.
The target personas are the central SAP Datasphere Administrator for a tenant wide perspective and key users of an implementation project, who wish to monitor the resource consumption of their project.
How to find your seamless planning model in SAP Datasphere
Let’s start at the beginning: Once you create a planning model in SAP Analytics Cloud you now have the option to store your data in SAP Datasphere. You are then asked to provide a space in SAP Datasphere as a storage location. In the background SAC Analytics Cloud is now creating a hidden HANA schema in said space.
In order to find out where your model resides in SAP Datasphere and how much storage it consumes, please go to the space monitoring (Space Management -> Select your space -> Monitor). As this monitoring view covers the entire space, it is helpful to filter for the seamless planning schema which is called <SPACE_NAME>#SAC_<some ID>.
Filtering the table list for the model ID which you find in the URL of your SAP Analytics Cloud planning model shows you exactly how the components of your model are stored: The first table in the screen shot shows the fact table which is followed by master data tables for the dimensions and text tables for member description translation. Please note that the fact table contains only published versions. The data used in private versions are stored in separate tables starting with “sap.fpa.services.epm.versions”. As the model ID is the last element of the table name, it may be truncated. Noticeably, there are no tables containing comments that are set for the planning model, as those are stored in SAP Analytics Cloud and not accessible from SAP Datasphere.
Finding your seamless planning objects in Datasphere
You now have an overview of the disk and memory used for storage of your seamless planning model and its private versions.
How to find the storage used for seamless planning in a space…
… via the Space Management
Now that we have the information we need for the first model, let’s aggregate that up to the storage per space. Luckily, the monitoring dashboard in the screenshot provides a donut and a bar chart depicting the storage used per schema in that space. As seamless planning creates one SAP HANA schema per space that uses seamless planning, all we need to do is filter for the schema containing ‘#SAC_” after the space name.
Storage used per schema in a space
… via the Database Explorer
The same information is also present in the SAP HANA monitoring views. To access those create a data base user in the space management and open the Database Explorer.
How to navigate to the Database Explorer
The views currently used to provide the record count and storage size in disk and memory are M_CS_TABLES for memory information and M_TABLE_PERSISTENCE_STATISTICS for disk information. Please note, that these monitoring views contain only information about column tables that are loaded into memory and thus the calculation might change in the future. To incorporate more scenarios, we will encapsule those calculations in a temporary view. To follow the subsequent instructions, please run the following SQL to create the temporary view used in this blog as a baseline:
create view TEMP_DSP_SAC_STORAGE as select
SUBSTRING(m.schema_name, 1, LOCATE(m.schema_name, ‘#SAC_’)-1) as SPACE_NAME,
m.schema_name,m.table_name,
m.record_count,
d.disk_size as Disk_Size,
(CASE
WHEN m.”LOAD_UNIT” = ‘PAGE’ THEN 0
ELSE CASE
WHEN m.”LOADED” = ‘FULL’ THEN m.”MEMORY_SIZE_IN_TOTAL”
WHEN m.”LOADED” != ‘FULL’ AND m.”ESTIMATED_MAX_MEMORY_SIZE_IN_TOTAL” = -1 AND RECORD_COUNT = 0 THEN 0
WHEN m.”LOADED” != ‘FULL’ AND m.”ESTIMATED_MAX_MEMORY_SIZE_IN_TOTAL” = -1 AND m.”LAST_ESTIMATED_MEMORY_SIZE” >= 0 THEN m.”LAST_ESTIMATED_MEMORY_SIZE”
ELSE m.”ESTIMATED_MAX_MEMORY_SIZE_IN_TOTAL”
END
END) AS “USED_MEMORY”
FROM M_CS_TABLES m left JOIN M_TABLE_PERSISTENCE_STATISTICS d on m.schema_name=d.schema_name and m.table_name=d.table_name
WHERE m.schema_name like ‘%#SAC_%’ ;
To calculate the storage used for seamless planning within the space use the following SQL-Statement:
SELECT SPACE_NAME,SCHEMA_NAME, count(TABLE_NAME) as NO_OF_TABLES, sum(DISK_SIZE) as DISK_SIZE, sum(USED_MEMORY) as USED_MEMORY
FROM TEMP_DSP_SAC_STORAGE
WHERE SCHEMA_NAME LIKE ‘<YOUR_SPACE>#SAC_%’
group by SPACE_NAME,SCHEMA_NAME;
The result provides an aggregate of storage used for the seamless planning content within <YOUR_SPACE>.
How to find the storage used for seamless planning within the tenant
General information on memory consumed by the tenant is found in the system monitoring. Here, the seamless planning data is included in the “Data in Spaces” sections.
Storage used tenant wide is part of “Data in Spaces”
As the monitoring entails all the data stored in the spaces, we need to use SQL to filter out the seamless planning specific part. Therefore, we use the previously introduced temporary monitoring view to derive a list of spaces that are used for seamless planning and list their memory use:
SELECT SPACE_NAME,SCHEMA_NAME, count(TABLE_NAME) as NO_OF_TABLES, sum(DISK_SIZE) as DISK_SIZE, sum(USED_MEMORY) as USED_MEMORY
FROM TEMP_DSP_SAC_STORAGE
group by SPACE_NAME,SCHEMA_NAME
order by USED_MEMORY desc;
To aggregate the total storage consumed by all schemas used for seamless planning on the tenant, execute the following SQL statement:
SELECT sum(DISK_SIZE) as DISK_SIZE, sum(USED_MEMORY) as USED_MEMORY
FROM TEMP_DSP_SAC_STORAGE;
This concludes the resources consumed for the data seamless planning stores on Datasphere. Next, we will have a look at the compute resources required for calculation during planning activities.
How to find the memory and CPU used for seamless planning activities…
With seamless planning it is not only the data storage that is used in SAP Datasphere but also the computation required for planning activities like creating a new planning version, executing a data action and publishing a planning version. Therefore, this section focuses on estimating the memory and CPU usage of those planning activities. Please note that analytical requests for querying the data can also be captured in a similar way but it is not the focus of this blog.
As the seamless planning activities are created and started in SAP Analytics Cloud, they are not captured in the monitoring of Task Logs in SAP Datasphere. Therefore, we will trace them with the expensive statement tracing to estimate their resource consumption. The results can be seen in the System Monitor UI, the HANA Cockpit and the Database Explorer. We will cover all three methods.
First, enable expensive statement tracing in the system configuration.
How to enable expensive statement tracing
Set a threshold that is high enough to filter the ‘background noise’, and low enough that the statement you expect to be expensive is still traced. For instance, you could start with 500 milliseconds. Now all statements that run for a longer time than the set threshold will be logged and can be viewed in the System Monitor’s Statement Logs.
With the tracing active in SAP Datasphere, go to SAP Analytics Cloud and trigger the planning activity you want to evaluate.
… via the System Monitor
Next, go back to SAP Datasphere: In the System Monitor the tab “Statement Logs” is now available and your planning activity can be analyzed by the first method.
How to find seamless planning activities in the expensive statement logs
At the moment, planning activities are not clearly marked as such, so here are some indicators to identify them:
Check “Start Time” for the time frame when the planning activity was runningcheck “Schema Name” for your space name,the “Activity” is most likely a “CALL” action.
While all those statements together make up the entire load of your activity, the main bulk of the activity is probably handled by a single statement with the highest memory usage and most probably has Statement Details that execute an MDS call.
… via the HANA Cockpit
For the second method, click on the Database Overview (HANA Cockpit) button and navigate to Performance Monitor -> Expensive Statements. There, you can see the spikes in resource consumption corresponding to expensive planning activities.
Finding planning activities in the HANA Cockpit
… via the Database Explorer
Lastly, this information and more details can also be found in the Database Explorer with the help of the monitoring view M_EXPENSIVE_STATEMENTS:
SELECT SCHEMA_NAME, START_TIME, OBJECT_NAME,STATEMENT_STRING, DURATION_MICROSEC, MEMORY_SIZE, CPU_TIME
FROM M_EXPENSIVE_STATEMENTS
WHERE SCHEMA_NAME LIKE ‘%#SAC_%’ AND OPERATION = ‘CALL’
ORDER BY START_TIME DESC;
Finding planning activities via the Database Explorer
Please be aware that the timestamps of the START_TIME column may not match as the database output is stored in UTC time zone and the SAP Datasphere UI displays the time zone configuration of your device.
This concludes the analysis of memory and CPU usage for planning activities such as creating a planning version (and copying data), executing a data action and publishing a planning version. While analytical queries can also be traced and found in the expensive statements sections, they do contain different statement details.
Conclusion
Following this blog post supports you in monitoring storage and computation in your system that is used for seamless planning and to judge the resources needed for it to run smoothly. For further information about sizing information for seamless planning, visit the SAP Note 3564858 – SAP Datasphere Tenant Configuration for Planning Use Cases.
IntroductionThis blog post is intended to provide a guide on how to measure the amount of resources that your system uses for seamless planning.With seamless planning you are able to store the data of planning models from SAP Analytics Cloud in SAP Datasphere. Consequently, resources of SAP Datasphere are used for data storage, planning activities and analytic queries. The purpose of this blog is to guide you through the options of monitoring those resource consumption. Starting from your first planning model to the overall tenant resources you will be shown easy and straight forward methods to get the information you need.The target personas are the central SAP Datasphere Administrator for a tenant wide perspective and key users of an implementation project, who wish to monitor the resource consumption of their project.How to find your seamless planning model in SAP DatasphereLet’s start at the beginning: Once you create a planning model in SAP Analytics Cloud you now have the option to store your data in SAP Datasphere. You are then asked to provide a space in SAP Datasphere as a storage location. In the background SAC Analytics Cloud is now creating a hidden HANA schema in said space.In order to find out where your model resides in SAP Datasphere and how much storage it consumes, please go to the space monitoring (Space Management -> Select your space -> Monitor). As this monitoring view covers the entire space, it is helpful to filter for the seamless planning schema which is called <SPACE_NAME>#SAC_<some ID>.Filtering the table list for the model ID which you find in the URL of your SAP Analytics Cloud planning model shows you exactly how the components of your model are stored: The first table in the screen shot shows the fact table which is followed by master data tables for the dimensions and text tables for member description translation. Please note that the fact table contains only published versions. The data used in private versions are stored in separate tables starting with “sap.fpa.services.epm.versions”. As the model ID is the last element of the table name, it may be truncated. Noticeably, there are no tables containing comments that are set for the planning model, as those are stored in SAP Analytics Cloud and not accessible from SAP Datasphere.Finding your seamless planning objects in DatasphereYou now have an overview of the disk and memory used for storage of your seamless planning model and its private versions.How to find the storage used for seamless planning in a space…… via the Space ManagementNow that we have the information we need for the first model, let’s aggregate that up to the storage per space. Luckily, the monitoring dashboard in the screenshot provides a donut and a bar chart depicting the storage used per schema in that space. As seamless planning creates one SAP HANA schema per space that uses seamless planning, all we need to do is filter for the schema containing ‘#SAC_” after the space name.Storage used per schema in a space… via the Database ExplorerThe same information is also present in the SAP HANA monitoring views. To access those create a data base user in the space management and open the Database Explorer.How to navigate to the Database ExplorerThe views currently used to provide the record count and storage size in disk and memory are M_CS_TABLES for memory information and M_TABLE_PERSISTENCE_STATISTICS for disk information. Please note, that these monitoring views contain only information about column tables that are loaded into memory and thus the calculation might change in the future. To incorporate more scenarios, we will encapsule those calculations in a temporary view. To follow the subsequent instructions, please run the following SQL to create the temporary view used in this blog as a baseline:create view TEMP_DSP_SAC_STORAGE as select
SUBSTRING(m.schema_name, 1, LOCATE(m.schema_name, ‘#SAC_’)-1) as SPACE_NAME,
m.schema_name,m.table_name,
m.record_count,
d.disk_size as Disk_Size,
(CASE
WHEN m.”LOAD_UNIT” = ‘PAGE’ THEN 0
ELSE CASE
WHEN m.”LOADED” = ‘FULL’ THEN m.”MEMORY_SIZE_IN_TOTAL”
WHEN m.”LOADED” != ‘FULL’ AND m.”ESTIMATED_MAX_MEMORY_SIZE_IN_TOTAL” = -1 AND RECORD_COUNT = 0 THEN 0
WHEN m.”LOADED” != ‘FULL’ AND m.”ESTIMATED_MAX_MEMORY_SIZE_IN_TOTAL” = -1 AND m.”LAST_ESTIMATED_MEMORY_SIZE” >= 0 THEN m.”LAST_ESTIMATED_MEMORY_SIZE”
ELSE m.”ESTIMATED_MAX_MEMORY_SIZE_IN_TOTAL”
END
END) AS “USED_MEMORY”
FROM M_CS_TABLES m left JOIN M_TABLE_PERSISTENCE_STATISTICS d on m.schema_name=d.schema_name and m.table_name=d.table_name
WHERE m.schema_name like ‘%#SAC_%’ ;To calculate the storage used for seamless planning within the space use the following SQL-Statement:SELECT SPACE_NAME,SCHEMA_NAME, count(TABLE_NAME) as NO_OF_TABLES, sum(DISK_SIZE) as DISK_SIZE, sum(USED_MEMORY) as USED_MEMORY
FROM TEMP_DSP_SAC_STORAGE
WHERE SCHEMA_NAME LIKE ‘<YOUR_SPACE>#SAC_%’
group by SPACE_NAME,SCHEMA_NAME;The result provides an aggregate of storage used for the seamless planning content within <YOUR_SPACE>.How to find the storage used for seamless planning within the tenantGeneral information on memory consumed by the tenant is found in the system monitoring. Here, the seamless planning data is included in the “Data in Spaces” sections.Storage used tenant wide is part of “Data in Spaces”As the monitoring entails all the data stored in the spaces, we need to use SQL to filter out the seamless planning specific part. Therefore, we use the previously introduced temporary monitoring view to derive a list of spaces that are used for seamless planning and list their memory use:SELECT SPACE_NAME,SCHEMA_NAME, count(TABLE_NAME) as NO_OF_TABLES, sum(DISK_SIZE) as DISK_SIZE, sum(USED_MEMORY) as USED_MEMORY
FROM TEMP_DSP_SAC_STORAGE
group by SPACE_NAME,SCHEMA_NAME
order by USED_MEMORY desc;To aggregate the total storage consumed by all schemas used for seamless planning on the tenant, execute the following SQL statement:SELECT sum(DISK_SIZE) as DISK_SIZE, sum(USED_MEMORY) as USED_MEMORY
FROM TEMP_DSP_SAC_STORAGE;This concludes the resources consumed for the data seamless planning stores on Datasphere. Next, we will have a look at the compute resources required for calculation during planning activities. How to find the memory and CPU used for seamless planning activities…With seamless planning it is not only the data storage that is used in SAP Datasphere but also the computation required for planning activities like creating a new planning version, executing a data action and publishing a planning version. Therefore, this section focuses on estimating the memory and CPU usage of those planning activities. Please note that analytical requests for querying the data can also be captured in a similar way but it is not the focus of this blog.As the seamless planning activities are created and started in SAP Analytics Cloud, they are not captured in the monitoring of Task Logs in SAP Datasphere. Therefore, we will trace them with the expensive statement tracing to estimate their resource consumption. The results can be seen in the System Monitor UI, the HANA Cockpit and the Database Explorer. We will cover all three methods.First, enable expensive statement tracing in the system configuration.How to enable expensive statement tracingSet a threshold that is high enough to filter the ‘background noise’, and low enough that the statement you expect to be expensive is still traced. For instance, you could start with 500 milliseconds. Now all statements that run for a longer time than the set threshold will be logged and can be viewed in the System Monitor’s Statement Logs.With the tracing active in SAP Datasphere, go to SAP Analytics Cloud and trigger the planning activity you want to evaluate.… via the System MonitorNext, go back to SAP Datasphere: In the System Monitor the tab “Statement Logs” is now available and your planning activity can be analyzed by the first method.How to find seamless planning activities in the expensive statement logsAt the moment, planning activities are not clearly marked as such, so here are some indicators to identify them:Check “Start Time” for the time frame when the planning activity was runningcheck “Schema Name” for your space name,the “Activity” is most likely a “CALL” action.While all those statements together make up the entire load of your activity, the main bulk of the activity is probably handled by a single statement with the highest memory usage and most probably has Statement Details that execute an MDS call.… via the HANA CockpitFor the second method, click on the Database Overview (HANA Cockpit) button and navigate to Performance Monitor -> Expensive Statements. There, you can see the spikes in resource consumption corresponding to expensive planning activities.Finding planning activities in the HANA Cockpit… via the Database ExplorerLastly, this information and more details can also be found in the Database Explorer with the help of the monitoring view M_EXPENSIVE_STATEMENTS:SELECT SCHEMA_NAME, START_TIME, OBJECT_NAME,STATEMENT_STRING, DURATION_MICROSEC, MEMORY_SIZE, CPU_TIME
FROM M_EXPENSIVE_STATEMENTS
WHERE SCHEMA_NAME LIKE ‘%#SAC_%’ AND OPERATION = ‘CALL’
ORDER BY START_TIME DESC;Finding planning activities via the Database ExplorerPlease be aware that the timestamps of the START_TIME column may not match as the database output is stored in UTC time zone and the SAP Datasphere UI displays the time zone configuration of your device.This concludes the analysis of memory and CPU usage for planning activities such as creating a planning version (and copying data), executing a data action and publishing a planning version. While analytical queries can also be traced and found in the expensive statements sections, they do contain different statement details.ConclusionFollowing this blog post supports you in monitoring storage and computation in your system that is used for seamless planning and to judge the resources needed for it to run smoothly. For further information about sizing information for seamless planning, visit the SAP Note 3564858 – SAP Datasphere Tenant Configuration for Planning Use Cases. Read More Technology Blog Posts by SAP articles
#SAP
#SAPTechnologyblog
