Predicting BW Database Volume
Revisiting the Technical Content in BW Administration Cockpit with SAP Predictive Analysis
The following blog post demonstrates how to use the technical content of SAP BW as a forecast data basis for a prognosis model in SAP Predictive Analysis. The aim is to show a smooth and straight-forward process avoiding additional modelling outside of BW as much as possible. In the described use case the Database Volume Statistics have been chosen as an example.
The official SAP Help summarizes the Technical Content in BW Administration Cockpit as follows: “The technical BI Content contains objects for evaluating the runtime data and status data of BW objects and BW activities. This content is the basis for the BW Administration Cockpit, which supports BW administrators in monitoring statuses and optimizing performance.”
The Technical Content with its pre-delivered Web Reporting might look a bit old-fashioned nevertheless the variety, quality, and quantity of data which is “generated” at any time in the system is very useful and important for further analysis. The type of data has a strong focus on performance-related data (e.g. query runtimes, loading times) but also other system-related data like volume statistics are available.
Technically there are simply the following 3 steps to follow:
- Expose cube information model to Hana (SAP BW)
- Adjust data types to PA-specific format (Hana Studio)
- Create forecast model (SAP PA Studio)
The Database Volume statistics in the technical content are designed with a simple data model consisting of just one cube with some characteristics (day, week, month, DB object, object type, DB table etc.) and key figures (DB size in MB, number of records etc.). Following the above steps with this set of data, choosing a certain type of algorithm, results in a bar chart shown below integrated with forecast figures for the past and some months into the future.
The blue bars represent the actual database size by month. The green line represents the calculated figures of the forecast model (in this case a Double Exponential Smooth regression) for the past 20 months and 10 months into the future.
Below are some technical details for each of the mentioned steps:
(1) Expose information model of Infocube 0TCT_C25 to Hana Studio
- Edit the Infocube in BW and set the flag for “External SAP HANA view”:
Immediately the information model is generated as an Analytic View and can be viewed in Hana Studio:
- Content -> system-local -> bw -> bw2hana -> 0 -> Analytic Views -> TCT_C25
(2) Adjust data types to PA-specific format (Hana Studio)
- The generated Analytic View of Infocube 0TCT_C25 looks like below:
SAP Predictive Analysis needs (currently) a specific time-ID column and the key figures must be of data type DOUBLE. The new Calculation View CV_TCT_C25_1 is created based on the generated Analytic View TCT_C25:
- Column [Month] (PA_TIME_ID_MONTH) = <unique sequential number for each month>
- Column [Database Size] (PA_TCTDBSIZE) = DOUBLE(0TCTDBSIZE)
(3) Create forecast model (SAP PA Studio)
Creating a forecast model in SPA Predictive Analysis follows the standard tasks as for any other data source.
- Select data source i.e. select prepared calculation view including (time) key id column and relevant key figures
- Select and configure components for the model:
- Use [Filter] component (if necessary restrict columns and rows like filtering the relevant database object types, time range etc.)
- Choose adequate [Algorithm] component, in the following case a Double-smoothing algorithm (PAL) has been chosen for forecasting several months into the future
And finally the resulting trend diagram is shown (see above).
 Infocube 0TCT_C25
 This post deals with SAP BW on Hana 7.40/SP6 and SAP Predictive Analysis 1.19
 The blog post is focusing on the technical aspects to get a forecast model successfully executed. The chosen algorithm might not be statistically appropriate.
 Assuming the technical content has been activated in SAP BW
 Unfortunately it’s not yet possible to expose the information model of a Multiprovider
 Data used is from April 2013 to November 2014. To get a unique ID the following calculation is used (in order to get a sequence starting from 1):
(int(“0CALYEAR”) – 2013)*12 + int(rightstr(“0CALMONTH”,2)) – 3