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[1] 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.[2]

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.


BW on Hana and SAP Predictive Analysis[3] together are extending the possibilities how to see the data and what to do (potentially more) with it.[4]

Technically there are simply the following 3 steps to follow[5]:

  1. Expose cube information model to Hana (SAP BW)
  2. Adjust data types to PA-specific format (Hana Studio)
  3. 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.

/wp-content/uploads/2014/11/1_587136.png


Below are some technical details for each of the mentioned steps:

(1) Expose information model of Infocube 0TCT_C25 to Hana Studio[6]

  • Edit the Infocube in BW and set the flag for “External SAP HANA view”:

/wp-content/uploads/2014/11/2_587094.png

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

/wp-content/uploads/2014/11/3_587095.png


(2) Adjust data types to PA-specific format (Hana Studio)

  • The generated Analytic View of Infocube 0TCT_C25 looks like below:

/wp-content/uploads/2014/11/4_587108.png

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>[7]
  • Column [Database Size] (PA_TCTDBSIZE) = DOUBLE(0TCTDBSIZE)

/wp-content/uploads/2014/11/5_587109.png


(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

/wp-content/uploads/2014/11/6_587137.png

And finally the resulting trend diagram is shown (see above).


[1] Infocube 0TCT_C25

[2] SAP Help Portal -> Technology -> SAP NetWeaver Platform

[3] This post deals with SAP BW on Hana 7.40/SP6 and SAP Predictive Analysis 1.19

[4] The blog post is focusing on the technical aspects to get a forecast model successfully executed. The chosen algorithm might not be statistically appropriate.

[5] Assuming the technical content has been activated in SAP BW

[6] Unfortunately it’s not yet possible to expose the information model of a Multiprovider

[7] 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

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Kurt Holst

    Hi Oliver,

    Great – thanks for putting this together & sharing.

    This answers a question that many are asking, and I suppose you could also extend this to number of cubes, size of all/individual cubes, cube runtime etc.

    BR, Kurt

    (0) 
    1. Oliver Baer Post author

      Hi Kurt,

      thanks for the positive feedback! Main purpose of the exercise was to get an understanding how easy or difficult it is to “reshape” the front-end of the technical content with up-to-date design and predictive functionality using Hana and Lumira or PA. And this is really easy!

      I am using heavily the technical content for assessing status and dynamics of our SAP BW, especially which queries are executed most in which area and at which time and its growth. I intend to elaborate much more on it (esp. the really big cubes like 0TCT_C01 and 0TCT_C02). Hopefully I get some more insights to share 😉

      Best regards,

      Oliver.

      (0) 

Leave a Reply