5. Where was my data read from?
In the preceding parts of this blog series, I described:
- how the general architecture to make use of the Active/Active (read enabled) configuration looks lik...
- how the client decides if it is allowed to read from the secondary SAP HANA database,
- how the client’s intent is converted into SAP HANA database hints.
Once you are implementing SAP S/4HANA and want to make use of the Active/Active (read enabled) configuration, you probably will ask yourself if there is an option to verify where the data was read from, the primary SAP HANA database or the secondary SAP HANA database.
Well, there are options to find this out. But it is a bit tricky, requiring more than one step:
- Find out how to relate the calls made by your app to the resulting SQL select statements in SAP HANA.
- Find the SQL select statements in SAP HANA.
This part of the blog series is a bit longer and more technical than the other parts. I try to keep it as simple as possible and concentrate on the important aspects. Like always, questions and feedback are welcome.
5.1 Prerequisites
You require:
| What | Mandatory or optional | Remark |
| Access to your app in the end user UI | Mandatory | |
| Access to the view browser app | Optional | I also will show how to get the related information directly from SAP HANA or in the ABAP development tools. However, it is more convenient to display the ABAP CDS views in the view browser app. |
| Access to the SAP S/4 HANA backend ABAP server for displaying ABAP CDS view in the ABAP development tools | Optional | I also will show how to get the related information directly from SAP HANA. However, it is more convenient to display the ABAP CDS views in the view browser app |
| Access to SAP HANA primary database | Mandatory | You need to execute selects in the SQL console, either from SAP HANA studio or SAP HANA Web IDE. |
| Access to SAP HANA secondary database | Mandatory | You need to execute selects in the SQL console, either from SAP HANA studio or SAP HANA Web IDE. |
5.2 Relate the calls made by your app to the resulting SQL select statements in SAP HANA
Well, SAP has enabled a first set of applications for reading from the secondary SAP HANA database. These are listed in SAP note 2405182 .
You may simply select SQL statements in SAP HANA by one of the methods described in chapter 5.3, restricting your select by the execution time. But in a busy system this will give you a long list of select statements, making it hard to decide which is the call you are interested in.
Your end user actions in the analytical app results in SQL select calls against a so called CDS view in ABAP, which causes data being read from the CDS view representation in SAP HANA. CDS stands for the Core Data Services, SAP’s approach allowing to define a semantical rich business data models and services built on top. So a pragmatic way is to find the statements caused by your app via the name of the CDS view.
Take into account that there a two names for the CDS view: The name of the CDS view as ABAP entity, and the name of its representation on the HANA DB (SQL view or HANA table function).
5.2.1 Find the ABAP CDS view name from the ODATA call in the browser
For SAP Fiori apps, I can use the F12 browser tools to inspect the http calls which are sent by the UI to the backend. In the http calls I find the ODATA service name, from which I often can deduce the ABAP CDS view name. The following description is for the Google Chrome browser, it is similar in other browsers.
- Launch the SAP FIORI Launchpad containing the app in the browser.
- Press F12. The developer tools of the browser opens.
- Make sure that in the developer tools of the browser, you are in the section for the network traffic.
- Execute a step in your app (e.g. navigate to a drilldown or do a selection.
- In the list of calls, I scroll down to the $batch call (there may be more than one).
- Have a look at the header section.
- Find the ODATA service name after the string ‘/sap/opu/odata/sap’.
- In the example, the ODATA service name is C_SUPPLIEREVALOVERALLQRY_CDS. The corresponding CDS view name is the same w/o the sufiix ‘_CDS’: C_SUPPLIEREVALOVERALLQRY.
Note that there are exceptions, where the ODATA service is named differently, and hence the CDS view cannot be easily deduced. However, in a plenty of cases the described deduction of the CDS view should work.
5.2.2 ABAP CDS view name and SAP HANA entity name
Having now found the name of the ABAP CDS view, I should take into account that the corresponding entity in SAP HANA has a different name.
I find the SAP HANA entity name by either inspecting the view definition in the view browser app, or by inspecting it directly in the ABAP development tools, or by a select in SAP HANA.
In the view browser app, I can display the annotations and find the header annotation : AbapCatalog.sqlViewName, telling me the name of the HANA entity:
In the ABAP development tools, I will find the header annotation : @AbapCatalog.sqlViewName:, telling me the name of the HANA entity:
In the example above, the HANA entity name is CMMSUPLREVALOVRQ.
If I do not want to display the view in the ABAP development tools, I can get the same information in SAP HANA by a select against the table where the header annotations of CDS views are stored:
select distinct strucobjn as viewName, value as HANA_NAME from DDHEADANNO
where name = 'ABAPCATALOG.SQLVIEWNAME'
and strucobjn = 'C_SUPPLIEREVALOVERALLQRY';
or from this table:
select ddlname as ABAP_NAME, objectname as HANA_name from DDLDEPENDENCY
where DDLNAME = 'C_SUPPLIEREVALOVERALLQRY' and objecttype = 'VIEW';
5.2.3 Find the name of the view that is selected from
Now there is one more tricky thing:
Often, the ODATA service for an analytical app is a so called analytical query view. Inside the analytical query view, another view, a cube view is called. And the cube view is the one from which finally data is selected in SAP HANA.
So how to find out if the CDS view in the ODATA service is an analytical query?
I find out if it is an analytical query by either inspecting the view definition in the view browser app, or by inspecting the view definition in the ABAP development tools, or by a select in SAP HANA.
In the view browser app, I will find the header annotation : Analytics.query: true:
In the ABAP development tools, I will find the header annotation : @analytics.query: true:
If I do not want to display the view in the ABAP development tools, I can get the same information in SAP HANA:
select name, value from DDHEADANNO
where name = 'ANALYTICS.QUERY'
and strucobjn = 'C_SUPPLIEREVALOVERALLQRY'
This statement will return value = true if the view is an analytical query, or no result if it is not an analytical query.
5.2.4 Finding the cube view name for a given analytical query view name
Again, I have three options to find this out:
- I find the cube view name by either inspecting the view definition in the view browser app,
- I find the cube view name by either inspecting the view definition in the ABAP development tools,
- I find the cube view by a select in SAP HANA.
In the view browser app, I can read the ABAP name of the cube view on the Cross Reference section of the query view. It is the view name with the ‘from’ relation:
In the ABAP development tools, I will find the cube view name after the ‘from’ in the view definition:
In the example above, the cube view name is C_SupplierEvalOverall. Note that this is the ABAP CDS view name. See above how to deduce the HANA entity name for this!
If I do not want to display the view in the ABAP development tools, I can get the same information in SAP HANA.: I read create statement of the SAP HANA representation which is either a SQL view or a table function:
select a.definition from functions as a where a.function_name = 'CMMSUPLREVALOVRQ'
union all
select b.definition from views as b where b.view_name = 'CMMSUPLREVALOVRQ'
I copy the obtained definition into an editor, search for ‘from’ and read the cube view name after ‘from’. Note that by this method I get the HANA entity name of the cube view.
5.3 Find the SQL select statements in SAP HANA
I now have the name of the view that your application reads from. This is:
- Either the view addressed by the ODATA call (if it is not an analytical query view)
- Or the cube view called by the analytical query view (if the ODATA service addresses an analytical query view)
With this, I now read the statements executed in SAP HANA. I may use the SQL console of SAP HANA studio or SAP Web IDE. I just try to read the statements separately on both SAP HANA databases, the primary and the secondary HANA database. The one where I find the statements, is the one where the select was executed.
Basically, there are multiple options to find statements which were executed in SAP HANA:
- Option 1: Use the SAP HANA SQL Plan Cache
- Option 2: SAP HANA expensive statement trace
5.3.1 Reading statements from the SAP HANA SQL plan cache
My preferred way to find executed SQL statements in SAP HANA is the evaluation of the SQL plan cache. But what is the SAP HANA SQL plan cache?
Well, the HANA SQL optimizer creates (compiles) optimized execution plans for each select statement. SAP HANA stores the execution plans for a while and reuses them for the next call. This storage of compiled plans is the SAP HANA SQL plan cache.
As an advantage, the SQL plan cache is switched on by default and no special configuration is required. As a disadvantage, I get the SQL statements w/o parameter values. For my current goal (to find out where the statement was executed), this is not really an issue.
Since the lifetime of the SQL plan cache records is limited, I should read the plan cache soon after having used your app.
I can read the SQL plan cache from its monitoring view in SAP HANA :
select statement_string, user_name ,last_execution_timestamp, execution_count from m_sql_plan_cache where statement_string like '%CMMSUPLREVALOVR%' and statement_string not like '%m_sql_plan_cache%' order by last_execution_timestamp desc;
I get a result like this:
So I clearly see the executed statements executed by my app, and the execution_count increases when the statement is executed on the same SAP HANA database again. Note that the user in the USER_NAME column is not my business user, but the user finally calling from the ABAP server into the SAP HANA database.
5.3.2 Reading statements from the SAP HANA expensive statement trace
As an alternative, I may activate the SAP HANA expensive statement trace on both SAP HANA databases, the primary and the secondary database.
After having activated the SAP HANA expensive statement trace, I run my app from the end user UI and execute typical steps. Then I should deactivate the SAP HANA expensive statement trace again.
See here how to activate the expensive statement trace.
Then I read the recorded expensive statements:
select start_time, statement_string, parameters, operation, db_user, app_user, records, duration_microsec, cpu_time, statement_hash from m_expensive_statements where statement_string like '% CMMSUPLREVALOVR %' and statement_string not like '%m_expensive_statements%' order by start_time desc
Also with this, I simply have to select on both, the primary SAP HANA database and the secondary SAP HANA database, and clearly see where the statements were executed.
All parts of this blog series- SAP Managed Tags:
7 Comments
