Getting reacquainted with the BW Administration Cockpit.
This document is meant to reintroduce the importance of SAP BW’s Technical Content and specifically the BW Administration Cockpit. In the recent years, SAP HANA has stolen much of the spotlight from everything else that is equally important to our existing customers who are not ready to move onto the SAP HANA platform.
While everyone has been busy acquiring knowledge and getting acquitted with the latest SAP HANA capabilities and functions, little did we notice that SAP has sneaked in significant changes to the technical content that we have been so familiar with. For example, an Xcelsius dashboard has been included to provide a management style reporting while installation has become much more straightforward. This article is not meant to discuss the importance of using Technical Content in a BW environment but to raise the awareness of how easy it is to implement, what the new functionality can address and learnings that we have gathered while enabling this feature.
This document should be use as a guide to setup the BW Administration Cockpit in an environment where this has not been enabled. The effort is relatively minimal with no significant impact on existing objects but please address the warning messages in the installation log. The estimated effort require to complete this installation from an end to end process should not require anywhere greater than 10 hours on a single resource.
You may skip this section if you have prior experience with SAP BW’s Technical Content. This section is aim to provide a high level understanding on the importance of using and having visibility of the system’s health through use of the generated numerical logs within the BW application.
Aside from the obvious benefits of being of being able to contextualise error and perform analysis, enabling the BW Administration Cockpit is surprising simple. There are no additional licensing costs associated with it and this feature comes as part of the NetWeaver platform. So in essence, you have a free, powerful and insightful tool that if not leverage, will be such a waste.
The advantage of empowering your clients to monitor the health of the system will allows them a greater knowledge to take proactive measure in ensuring the system stays at its optimal level. Having tangible numbers to indicate who are their active users can be a useful communication tool to drive the adoption of BW to the wider community within an organisation. For example, an organisation would have heavily investment in an enterprise warehouse solution and would like to see it being productively used. What better way to feed these information back to the management team on the number of active reporting users, the type of reports that are frequently used and how it is being used. It can also be used as impact assessments mechanism in the event where an underlying BW object needs to be modified and the need to understand what and importantly who it will be affected can save the team a lot of Monday morning hate mail.
In my opinion, the most beneficial of all in enabling the BW Administration Cockpit is that the information is provided in an Xcelsius dashboard, it is easy to understanding and the information is not overly sensitive. Because of these reasons, I do not see a valid justification for not sharing this information with the larger community. If an organisation uses SAP portal, it can be included as part of the corporate view where it can help to create a culture where information drives decision making and an open and honest view of how the reporting system is performing is a feature everyone can learn to appreciate. Some of the newly provided content such as data consumption by InfoArea is not included into the Xcelsius dashboard but is part of the delivered content. Using this report allows the business to make informed decision on cost e.g. this report will allow the business to allocate the usage cost across different departments and the below sample data indicates that the Finance department is the largest memory consumer therefore cross departmental charges can take place with the appropriate groups. Another sample report, the BW DB Usage report can give you an insight into the trend of the data growth and this can help with hardware sizing by avoiding preventable upgrades by channelling funds to other areas of improvement.
If you have been using the technical content from the days of the 3.x version right throughout version 7.x, you will be pleased to know that tons of improvements have been added with plenty of reports and tools to provide you with the insight to assist with performance tuning and improvement. This section of the document will provide coverage on the content update from version BW 7.3 onwards.
Overview of the Xcelsius based BW Administration Cockpit
A worthy mention improvement that has been added is the BW Administration Cockpit to assist with monitoring jobs and the health of the BW system. The Administration Cockpit comes in two flavours, which is, the traditional integration with SAP Portal using BEx queries or the new Xcelsius integration. The Xcelsius Dashboard is a representation of the old queries in a new format but it does bring a breath of fresh air to the way we view information and the Xcelsius Dashboard is accessable through transaction RSTC_XCLS from the BW system. With the use of the Xcelsius based BW Administration Cockpit, you are provided with a high level view of the important information to keep your business users happy and with its intuitive layout you are able to locate the information and drill down to the detail error message to understand the next course of action. The monitoring options that are available to you through the Xcelsius Dashboard are:
- Alerts: BW InfoProviders with erroneous requests
- Performance Monitoring: Daily Query Performance and Weekly Process Chain Performance
- BW Usage Statistics: DB Usage, Query Usage, Top Users, BWA / DB / OLAP usage
OLAP cache and SAP NetWeaver BW Accelerator usage
If you are in an environment that uses BW Accelerator to improve query performance, the two new MultiProvider’s (0TCT_MC31 and 0TCT_MC32) below can offer you an insight into memory usage that allows you to better utilise your hardware and identify areas of improvements which you can perform to spread the load across the server. Some useful queries that are available from the technical content are reports such as CPU usage trends across periods, hourly CPU trends, Available memory, % of memory used and a breakdown of memory usages by InfoProvider.
If BWA is not present, you can still benefit from the Technical Content in the area of report performance if you have enable the OLAP cache consumption. By default and in many instances, the OLAP cache usage will be enabled and tweaks can be made to provide better efficiency or memory
handling method when the cache memory has been exhausted, e.g. reading the cache locally or from the database.
With the new BW7.3 release, characteristic 0TCTOLAP_DM has been added into InfoCube’s 0TCT_C01 and 0TCT_CA1 and this acts as a flag to distinguish between the pure OLAP Cache access and the Data Manager access. It is a useful method to identify the objects with high memory usage so that appropriate actions can be taken to switch these objects to use BWA. In another example, when trends on frequently use reporting objects starts to increase, you can create an exception query on an acceptable OLAP Time to flag for appropriate action. Be caution not to overload the BWA server but I guess this is where such report will make sense and allow you to draw the fine line.
Report availability and InfoProvider data status
While the previous version of the technical Content was only able to provide you with information on the status of the data load from a Process Chain, InfoPackage and DTP, the BW7.3x content has added queries that allows you to know if a report is available for reporting and relevant information on inconsistent and incomplete data load error surrounding the underlying InfoProvider.
A use case for this type of information is that it can be useful in furnishing the end user with the status of their reports. To achieve this, a specific customer enhancement query can be built against a department and expose only the applicable InfoProvider’s to them. The objective of this feature is to eliminate report inefficiency, rework and the possibilities of reporting on outdated numbers. The other benefit of this new feature is that it can also list out possible impacted BW objects as a result of data load failures. This type of report can assist with any troubleshooting exercise and also allow you the confidence to identify dependant objects in the event a huge data reloading excise is required to resolve your issue.
Database Volume Statistics
As briefly discussed under the Benefits section, the new improvement in the Database Volume Statistics content allows greater visibility on data distribution by hierarchies, BW objects e.g. InfoProvider’s and Table type. The goal of this data model is to provide the BW Administrator another useful tool to analyse the database growth in respective to the BW objects. The new Technical Content that provides this information sits under the 0TCT_C25 InfoProvider. You now have the insight into data consumption by functional area and the sample screen shoot below can easily help indefinite the entity within a business which consumes the most database memory.
In the past, it was almost impossible to break down the type of database consumption by BW object type without a lot of effort and man days but with this new content, database questions such as “What is the database size of an ODS Change Log or how many tables has the E and F table used?” can now be easily answered without going into ST03N and compiling the required information.
The BI Administration Cockpit is a recommended reporting feature that has been provided by SAP through the use of Technical Contents and this document is meant to cover the topic of installation and useful features within the BI Administration Cockpit and Technical BI Content layer.
Browse through the details found in the standard documentation because it is an excellent way to familiarised yourself with the installation procedure and the instructions provided by SAP are clear and concise.
We discovered that an active SAP portal is a crucial component in having a working cockpit to allow reporting through Xcelsius. Others might argue that having established a BICS connection, it is sufficient to execute any dashboard reporting from BW, however, this was not the case for this exercise.
This installation procedure is meant to act as a guide under BW version 7.4 SP09 (SAPKW74009). Some installation procedure might have changed over the course of time due to product improvement and thus necessary precaution is required to successfully implement this Administration Cockpit in a landscape which might be on a different release.
While the installation of the BW Administration cockpit is simple and straight forward, the documented installation procedure can help to clarify any doubt or questions that might arise in your effort to provide this solution to your client.
When the installation is complete and you have verified that all the necessary process chains are in place, you can begin by loading the master data using Process Chain 0TCT_MD_C_FULL_P01 and subsequently followed by the 0TCT_C* Process Chains.
To use the Xcelsius dashboard, in the BW system, enter RSTC_XCLS and this will launch a web browser session extending to a preconfigured portal address and you should see a similar dashboard below provided that you have setup SAP portal and successfully activated the BW Administration Cockpit.
This dashboard will allow you an overview of three basic monitoring which is the Alerts, Performance and Usage of the system.
This section is a collection of additional information that has been useful in providing core information to strengthen the understanding, concept and troubleshooting guide towards the usage of the Technical Content. Please make full use of the attached links and SAP Service Market Place to find updated information on technical areas which might have changed during the course of multiple system improvements.
1. Discovered errors after the Technical Content installation.
To avoid having to reinstall the entire Technical Content, use transaction RSTCO_ADMIN to restart the failed installation. A yellow status can also be an indicator that a newer version has been released and attention is required to handle this warning message. RSTCO_ADMIN can also be used to fix an installation that was executed by a user without the proper authorisation for Business Content installation. For supporting information, please refer to OSS 1069134 – Improved monitoring RSTCO_ADMIN
2. The background (SM37) job name is BI_TCO_ACTIVATION.
Use this to understand the installation procedure, the potential warning or error messages that might occur as a result of your installation.
3. Assigning an importance criterion to SAP’s Technical Content.
This feature will allow you to sort or filter BW technical objects and it needs to be maintained by assigning an importance value against the customer query that you wish to create or maintain. E.g., by assigning an importance value to a Technical Content Process Chain or InfoCube, you are able to sort that information to give it prioritisation amongst the other objects that is being monitored. The default importance value for all BW technical objects is set at 50 and to change this, use transaction RSTCIMP to assign any value between 0 and 100. The underlying table that stores this information is RSTCIMPDIR.
With the customising complete, transfer the value to InfoObject 0TCTBWOBJCT via DataSource 0TCTBWOBJCT_ATTR and verify attribute 0TCTIMPRTNC.
4. Collection of Statistical Information.
All newly created BW Query, InfoProvider, Web Template and Workbook have been defaulted to collect statistical information and this setting can be change to disable it, turn it back on and determine the level of aggregation to report on. This setting is maintained using transaction RSDDSTAT and as a rule of thumb, it is advisable to leave all objects turn on while maintaining a sense on the aggregation data that is required. Once you have evidence of where performance monitoring is not required, e.g. on InfoProvider’s with low data volume, this setting can be turn off.
If an InfoProvider has this setting disabled, e.g. InfoProvider ZKUST01, all newly created queries will inherit this property and no statistical information will be collected for it. However, you can overwrite this setting in the Query tab to explicitly only collect information for that desired Query.
The amount of data or level of detail to be collected can also be adjusted based on the setting of 1, 2, 9 and 0. Below is an extracted text from SAP and further detail can be found here, http://help.sap.com/saphelp_nw70/helpdata/en/43/e37f8a6df402d3e10000000a1553f7/content.htm
Statistics Detail Level for the Query Object Type
For queries, you also have the option of selecting a detail level for the statistics data. You can choose from the following:
- 0 – Aggregated Data: The system writes only one OLAP event (event 99999) for the query. This contains the cumulative times within the OLAP processing of the query. The system does not record data from the aggregation layer of the analytic engine or aggregation information.
- 1 – Only Front End/Calculation Layer Data: The system records all OLAP events, but not separate data from the aggregation layer of the analytic engine. The system writes only the general data manager event 9000 in the OLAP context as well as the aggregation information.
- 2 – All: The system records all data from the area for the front end and calculation layer as well as data from the area for the aggregation layer and aggregation information.
- 9 – No Data: The system does not record any data from the front end and calculation layer or from the aggregated event 99999. However, it does record data for the BEx Web templates and workbooks, depending on the setting.
5. Deleting Statistical Data.
Statistical data can grow at an exponentially rate depending on factors such as the number of users in the system, the frequency of query activities and the type of aggregation setting that has been enabled under transaction RSDDSTAT. SAP’s data retention period for table RSDDSTAT_* is 14 days but you can overwrite the standard setting of 14 days by maintaining a numeric value in the RSADMIN table for entry TCT_KEEP_OLAP_DM_DATA_N_DAYS.
To do this, use the SAP_RSADMIN_MAINTAIN program to add or modify this entry. The example below holds a value of 7 days.
Alternatively, to manually delete the statistical data use the standard Delete Statistical Data function under transaction RSDDSTAT or execute program (SE38) RSDDSTAT_DATA_DELETE.
It will come as no surprise that the BW Administration Cockpit will need to be owned and managed by the IT department to ensure continuous improvement is performed productively. By having these statistical data turned into readable information, it allows an easier way to keep track of what is going on within the IT landscape regardless of the size of your enterprise.
There is no need to reiterate that the function of IT is to support the core business function but put on your green hat to find a business use case for it e.g. in an environment where SLA for BW plays an important KPI e.g. the BW server is hosted by an application provider, these information can be prove to be useful.
It is not enough to just activate the Technical Content and start running the Process Chain to collect the information that has been generated by the system but having a good understanding of the data and the standard reports is especially crucial to perform actionable task to safeguard the health of the BW server. Use the standard reports as building blocks to further enhance and drive specific monitoring and runtime statistics requirements once your team has a better understanding on the other areas to improve on.
In terms of the new features provided by SAP, it is worthwhile to recognise that new contents might be available and be mindful that continuous improvement is certain with every release and upgrade.
Great Document. Thanks for sharing. I really appreciate the effort.