Technical Articles
BOBJ & HANA for Basis (2) …
Data sources… Where to find the data?
Well … It all started with the need for me to pull some performance/capacity data out of HANA. I needed to build up some HANA performance review report.
In Hana, you’ll find several performance views/tables available for you to evaluate the performance of the Hana system.
All the data gets collected automatically by the HDB system ( statistics server whether embedded or not ) which is a great thing.
The main ones I’m pulling the data from are :
| TABLE / VIEW | RELATED INFO FROM SAP HELP | REPORTING USAGE |
|
M_SQL_PLAN_CACHE M_SQL_PLAN_CACHE_RESET SYS_STATISTICS.HOST_SQL_PLAN_CACHE |
The data extracted from these views will be used to build the dataset for HANA workload and runtimes | |
|
M_EXPENSIVE_STATEMENTS M_SQL_CLIENT_NETWORK_IO |
Provided that the expensive statements trace is activated on your HDB, you’ll be able to use these to build some Application usage tops based on runtimes. If the expensive statement trace is not activated then the M_EXPENSIVE_STATEMENTS view does not get populated. |
|
|
HOST_HEAP_ALLOCATORS HOST_COLUMN_TABLES_PART_SIZE GLOBAL_ROWSTORE_TABLES_SIZE HOST_RS_INDEXES |
Memory areas usage will be pulled out from these.
|
|
| M_CS_ALL_COLUMNS | M_CS_ALL_COLUMNS | Dataset for Tables tops |
| GLOBAL_TABLE_PERSISTENCE_STATISTICS | GLOBAL_TABLE_PERSISTENCE_STATISTICS | Tables Growth and shrinks ( disk usage ) can be reviewed based on these views. |
|
M_TABLE_VIRTUAL_FILES M_RS_TABLES M_TABLE_LOB_FILES |
Tables usage on disk | |
|
M_SERVICE_REPLICATION HOST_SERVICE_REPLICATION |
Dataset for system repllication | |
|
SYS_STATISTICS.global_disks M_DISKS M_DISK_USAGE |
Dataset for disks usage | |
|
M_BACKUP_CATALOG M_BACKUP_CATALOG_FILES |
Dataset for backup perrformance review |
These are only a subset of the overall available system views of course. A list of all monitoring views is available in SAP help here , and SYS_STATISTICS views can be found there. I’ll use some others depending on the reporting needs.
Most of the views persist the data for a defined time length. For example, views/tables managed by the statistics server are persisted for 42days as a default.
But in my case, I’ll need to keep this information for a longer period of time ( like 2 years for example ) and also, I won’t use the raw data extracted from these system views.
So I will create my own specific schema and set of tables in order to persist the data as long as I want and have it in the format I like.
The benefit I see here is I’ll have the hand on this dataset without modifying anything in terms of system configuration, and also, I avoid the risk of deleting data from system views. Last thing: I hope it gives me room to play around with data aging!
The drawback is that I’ll kind of have the data stored twice ( for the first 42 days let’s say ).
Last but not least, this will allow me to learn how tables/objects get created in hana by the way :).
Additional objects required
As I’ll have my own schema, I’ll need objects in order to manage that additional metrics datamart :
- Tables of course, in which the processed data metrics will be persisted,
- Stored procedure to format, load the data and also perform the housekeeping tasks,
- Jobs to perform the regular update and maintenance tasks for the data.
Next step
Create the required objects in HANA to build our data mart.