How to view Historical Data in SAP DB Control Center

The Historical Data feature in SAP DB Control Center (DCC) collects and stores system health status information. This feature, however, is not automatically enabled. This document will guide you through setting up data capture and different options for data visualization.

Note: This document was originally created by a former colleague of mine, Yuki Ji, in support of a customer engagement initiative regarding the SAP DB Control Center (DCC) product.  To preserve this knowledge, the document was migrated to this space.

The information in this document applies to DCC SP10 or higher.

The purge period of historical data collection needs to be configured according to the capacity of the system hosting DCC and the number of systems being monitored. Relation between systems monitored and space needed can be found in the DCC documentation. Otherwise to enable historical data and configure purging requires only two SQL statements to be run.
Enabling Historical Data Capture

In your DCC system execute the following statements with a user who has the DBCCAdmin role, such as DCC_ADM. The statements are to enable/disable historical data collection, and to set the data purge period in minutes (21600 minutes = 15 days, 4320 = 3 days, 1440 = 1 day).

To enable historical data collection:

01. upsert "SAP_HANA_DBCC"."sap.hana.dbcc.data::Site.PreferenceValues" ("name", "v_int")
02. values('apca.historical.enabled', 1) with primary key;
03. upsert "SAP_HANA_DBCC"."sap.hana.dbcc.data::Site.PreferenceValues" ("name", "v_int")
04. values('apca.historical.purge.max_age', 21600) with primary key;

To disable historical data collection:
01. upsert "SAP_HANA_DBCC"."sap.hana.dbcc.data::Site.PreferenceValues" ("name", "v_int")
02. values('apca.historical.enabled', 0) with primary key;

For more information on configuration or on the schema of the stored data, refer to section 3.9.4 of the DB Control Center 4 Guide documentation or Configure Storage of Historical Data.

Data Visualization

The data that is collected is stored in the table named sap.hana.dbcc.data::APCA.Historical under the schema SAP_HANA_DBCC and can be viewed by running this SQL statement:

01. SELECT TOP 1000 * FROM "SAP_HANA_DBCC"."sap.hana.dbcc.data::APCA.Historical" [order by timestamp desc];  

The column resourceId tracks which registered system the information is for. The correlation of resourceId and system name is mapped can be viewed by running this SQL statement:

01. SELECT [TOP 1000] "RES_ID","RES_NAME" FROM SAP_HANA_DBCC"."sap.hana.dbcc.data::RESOURCES"  

Reminder: historical data is collected about every 2 minutes, so over the course of a day a single triggered alert can be checked and noted up to about of 720 times.

Now that data is collected, there are several options on how to manipulate and visualize the data. In this blog post we will cover some available options through SAP tools: a custom XS application, SAP Lumira, SAP Crystal Reports.

Custom XS Application

To create a custom XS application, you can use SAP HANA Studio with the SAP HANA Development perspective, or the SAP HANA WebIDE.

One way of taking the data from the table and displaying it is to:

1. Create a view to simplify the desired data.

Note: To update a view for HANA you are required to drop it, then recreate that view. Filtering by resourceId returns only the results for a specific system (see table sap.hana.dbcc.data::RESOURCES to find a system's resourceId).

01. --drop view SAP_HANA_DBCC.apca_view  

02. create view SAP_HANA_DBCC.apca_view(  

03. "day",  

04. "alertHigh",  

05. "alertMedium",  

06. "alertLow"  

07. )  

08. as select  

09. TO_VARCHAR("timestamp", 'YYMMDD') as "day",  

10. sum("alertHigh"),  

11. sum("alertMedium"),  

12. sum("alertLow")  

13. from "SAP_HANA_DBCC"."sap.hana.dbcc.data::APCA.Historical"  

14. where "timestamp" > ADD_DAYS(CURRENT_TIMESTAMP, -31) --and "resourceId" = 101  

15. group by TO_VARCHAR("timestamp", 'YYMMDD')  

16. order by "day" desc;  

2. In the XS application use OData calls to access data from the system by creating an .xsodata file.

01 service namespace "data_visualization.services" {  

02. "SAP_HANA_DBCC"."APCA_VIEW" as "View" key generate local "key";  

03. }

  

3. Use existing development frameworks for UI and for graphing to display the data.

The above graph displays the SUM of alerts noted by DCC per day for the past 31 days.

SAP Lumira

In SAP Lumira, generating a report starts with defining a dataset then manipulating the measures and dimensions to display the information desired about the registered system(s).

The following is an example query to create a dataset, including the system name, that can be used to create High/Medium/Low priority Alert related graphs or reports:

01. select "resourceId","RES_NAME", "timestamp", "alertHigh", "alertMedium","alertLow" from  

02.     "SAP_HANA_DBCC"."sap.hana.dbcc.data::APCA.Historical",  

03.     "SAP_HANA_DBCC"."sap.hana.dbcc.data::RESOURCES"  

04. where "SAP_HANA_DBCC"."sap.hana.dbcc.data::APCA.Historical"."resourceId"= "SAP_HANA_DBCC"."sap.hana.dbcc.data::RESOURCES"."RES_ID"  

The same example query without the system name:

01. select "resourceId”, "timestamp", "alertHigh", "alertMedium","alertLow" from  

02.     "SAP_HANA_DBCC"."sap.hana.dbcc.data::APCA.Historical”  

Creating the dataset by running a SQL query on a database system:

You can further manipulate and prepare your data, or begin creating your graphs under Visualize. In Lumira simply drag and drop you Measures and Dimensions from the far left column and begin defining your graph.

Some examples of graphs you could create:

The SUM of all High, Medium, or Low priority Alerts triggered and noted over the course of a week. Recall, DCC collects data on a system about every two minutes.

In this graph we can see which days of the week had the most alerts per system and per alert.

Once the graphs have been created SAP Lumira can be used to generate infographics or other reports.

SAP Crystal Reports

As with SAP Lumira, for SAP Crystal Reports you will need to define a data source before manipulate the data as you wish.

To define your HANA system as a data source you will need to have the HANA Client installed on the computer that is running Crystal Reports. Once it is installed you can then use ODBC or JDBC to create a connection to the DCC enabled system and pull the data from the tables sap.hana.dbcc.data::APCA.Historical and sap.hana.dbcc.data::Resources.

With Crystal Reports you can then create graphs and generate detailed reports and report templates of your system landscape health.

Below, both graphs are created using a dataset for resource 101, or system YJI over the course of 31 days.

Follow-up

The use and visualization of collected Historical Data can be performed using various tools - XS applications, SAP Lumira, and SAP Crystal Reports to name a few. As you begin looking at the historical data and how you will be using it, please consider the following:

• Is there other system information you would like to see captured?
• What reports would you create with the currently available information?
• Ideally, what reports would you create with additional information?

Source: scn.sap.com