+++> 2.SAP BW Statistics

BW Statistics

Purpose:

We can use BW Statistics to evaluate the fundamental functional areas of the Business information warehouse and also BW Statistics provides us the options that allow us to evaluate data from both the OLPA processor and Warehouse management.

We can able to find the below things with the statistical information

• Which Info Cubes, Info Objects, Info Sources, source systems, queries, and aggregates are currently being used in the system?

How often are these objects being used? Which datasets are in use?

• Which users are currently working in the system?
• Which aggregates could you use to reduce the runtime of the queries? At what rate does the time taken to load the data, including the time it takes to roll-up the aggregates, increase?
• Which aggregates, Info Cubes, Info Objects, or Info Sources are no longer used and can therefore be deleted?
• Performance: To what extent do queries put pressure on the database, the OLAP server, and the frontend? Can this pressure be reduced by changing the definition of the queries?

2 Major Focused Areas in this Chapter:

• Data Load Statistics – Monitoring the performance of BI Data Loads and Process Chains.
• Query Runtime Statistics.

• Maintaining query runtime statistics data recording
• How to analyze query runtime
• How to interpret query runtime statistics data

Integration of BW Statistics:

When a query is executed, data is specified for the OLAP Server and for access to the data base is saved temporarily in underlying data base tables, once the navigation step has been completed. This is also the case when the ODBO is used. Additional data is collected into the other data base tables when the aggregates are filled out and rolled up after loading data into warehouse management.

It does not take long to calculate and save BW statistics data. However, the dataset can be considerable with larger installations. For this reason, the data input for each Info Provider in each area of OLAP and warehouse management can be activated and deactivated individually. You are able to delete stored data.

Pre-requisite:

Activating Data Transfers for BW Statistics

Query Run-time Statistics

In order to know the query runtime statistics information, We need to Install relevant Multi Provider from Technical Content and need to load the data into the underlying cubes, so that all the query run times statistics information will be stamped in those info cubes, so that the standard reports available on that info providers can display the detail statistical information for those queries.

 

See the below diagram shows Data flow for query runtime statistic cubes

0TCT_MC01:

In brief, the main Info Provider for query runtime monitoring is 0TCT_MC01 which Contains aggregated information on query runtimes. The data volume of that Info Provider is about 2 – 10 records per Navigation Step.

Example: 1000 users with 5 navigations each / day. Assumed average n° of records in0TCT_C01 per navigation step = 5.

In that Example: Records per day in 0TCT_C01: 1000 x 5 x 5 = 25000.

0TCT_MC03:

For query performance tuning at Data Manager / Database / BI Accelerator level,

the Info Provider 0TCT_MC03 is essential! It provides important information on

Multi Provider processing as well as details on individual reads from Info Providers and its related persistence (fact tables, aggregates, BI Accelerator indices…).

0TCT_MC02:

The Info Provider 0TCT_MC02 is only needed for troubleshooting or “drill down“on exceptional runtimes. In practice, you can alternatively achieve this „drill down“by using the DB View RSDDSTAT_OLAP not using that Info Provider. Also, usually a long history is not needed for that detail data as it is mostly used for spontaneous troubleshooting. This is especially important as that Info Provider will contain mass data! One navigation step might create between 30 – 80 records in RSDDSTAT_OLAP and 0TCT_C02.

Example: 1000 users with 5 navigations each / day. Assumed average n° of records in 0TCT_C02 per navigation step = 50.

Data Integration among the Query statistical info cubes (0TCT_C01, C02 & C03) from underlying data base tables (RSDDSTAT_OLAP, RSDDSTAT_DM).

Data Load Statistics

These Statistical Cubes will maintain all the process chain’s process types runtime statistic details.

In order to know the Data load statistics information, We need to Install relevant Multi Provider from Technical Content and need to load the data into the underlying cubes, so that all the data load statistics information will be stamped in those info cubes, so that the standard reports available on that info providers can display the detail statistical information for all the process types in the process chain.

Implementation/Installation of BW Statistics:

BW Statistics are delivered as part of Technical Content; this technical content contains all the objects that are needed to implement BW Statistics.

Process:

(BW Side)

1. Go to RSA5 and activate the data source.
2. Go to RSA3 and check the no of records.
3. Go to RSA1 and Select Application Component “Business Information Warehouse” →”BI Statistics” replicate the data sources.
4. Go to BI Content and Install the Info Cube.
5. Create Transformations.
6. Create DTP.

Note: The first 2 activities (RSA5 & RSA3) should be performed in BW side itself. Because,all the technical content for BW statistics would available in BW system itself.