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|
|The data extracted from these views will be used to build the dataset for HANA workload and runtimes|
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.
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.|
|Tables usage on disk|
|Dataset for system repllication|
|Dataset for disks usage|
|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.
Create the required objects in HANA to build our data mart.