BOBJ & HANA for Basis (2) …

[former_member182307]

Data sources... Where to find the data?

Blog series starts here

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	M_SQL_PLAN_CACHE M_SQL_PLAN_CACHE_RESET 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	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	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	M_TABLE_VIRTUAL_FILES M_RS_TABLES M_TABLE_LOB_FILES	Tables usage on disk
M_SERVICE_REPLICATION HOST_SERVICE_REPLICATION	M_SERVICE_REPLICATION HOST_SERVICE_REPLICATION	Dataset for system repllication
SYS_STATISTICS.global_disks M_DISKS M_DISK_USAGE	global_disks M_DISKS M_DISK_USAGE	Dataset for disks usage
M_BACKUP_CATALOG M_BACKUP_CATALOG_FILES	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.

BOBJ & HANA for Basis (3) …