Understanding Dynamic Rolling Graphs and its BEx-BO implementation

[Former Member]

Understanding Dynamic Rolling Graphs and its BEx-BO implementation

Applies to:

SAP BI BO

Summary

This document helps understand the dynamic Rolling graphs used for business analyses by many organizations today. It explains the challenges accompanied in implementing these in SAP Business Objects WEbi on top of BEx query and how these can be overcome.

Author(s):     Amit Jain, Aditi Sharma

Company:     Infosys Ltd.

Created on: 19 Dec 2012

Related search terms

Dynamic graphs, Rolling graphs, Moving graphs, Rolling Sales, Graphs in SAP BO Webi, Webi Graphs using structure in Bex.

Author Bio

Amit Jain is a Technology Analyst, working with Infosys for three and a half years. Amit has worked on various SAP BW-BO – implementation projects and currently working on SAP BW projects.

Aditi Sharma is a Senior Systems Engineer, working with Infosys for three years. Aditi has worked on various SAP BW-BO – implementation projects and currently working on SAP BW projects.

Table of Contents

• Introduction
• Purpose of Document

Problem Statement

• Proposed Solution
• Implementing Rolling graphs in Webi
• Handling Complex Graphs using Boolean Logic in Bex Query

Introduction

A business might want to track the movement of Sales and target together to keep an eye on their forecasting. Rolling Graphs provide that long-term view to understand where the business is heading in the overall direction. These graphs are dynamic in nature i.e. as the coming months roll in; these are updated and each shifting period generates the latest information.

Let us take an example of ‘XYZ’ Company. The higher management of the organization comprising of the CEOs, CFOs, Finance team etc, are interested in the overall picture of the business and not just YTD sales. They are more interested in the larger period of 12 or 24 month Rolling sales for the current year. They also wish to see the Planned Rolling sales for that year. A typical graph with such information would look like:

*Assuming September 2012 as the current month

Here the Rolling Sales for each month is a sum of last 12 month sales, for example:

And the Planned Rolling Sales are sum of the Actual Sales and Planned Sales, for example:

In order to check and compare the progress of organization on yearly basis, generally the Planned Rolling Sales are shown against December of current year.

Such graphs provide greater insight of true performance of the business. Organizations may keep a track of 2 to 3 years of annual turnover and benchmark forecasted turnover.

Purpose of Document

This document helps understand the Rolling graphs, what are the challenges accompanied in implementing these and the ways & means of achieving it in Business Objects WEbi on top of BEx query.

Problem Statement

While implementing Rolling graphs in SAP Business Objects WEbi, we need to define structures in BEx queries. These structures are static whereas the value it fetches from the database is dynamic. Due to the Rolling selections defined in the structure, the intricacies increases and the graph is represented incorrectly.

Case 1 :

Let’s take an example:

The user wants to analyze 12 month Rolling sales till the current month i.e. September 2012, as shown below:

                      Figure 1

However, while actually implementing this scenario, the graph doesn’t stop at the current calendar year/month (see Figure 2). Here the sales are increasing consistently till Sept, but a decline from October. This decline in sales is not due to the decline in actual sales that has happened, instead due to the structure defined in BEx.

                                                                                              Figure 2

Since, rolling sales are a sum of last 12 month sales, hence for the months Oct, Nov and Dec, even if the Actual sales have not happened, the structure selections would bring in some Rolling sales value. Hence there is a decline from Oct-12 to Dec -12 (where there have been no sales yet)

Case 2:

The user also wants to analyze the forecasted Sales along with the Actual Rolling Sales. These planned sales need to be shown only for December of current year.

                 Figure 3

However, again due to the structure defined, the planned sales are generated for full year (see Figure 4).

                                                                                             Figure 4

In such scenarios, the correct representation of the graphs needs to be achieved in WEBi without eliminating the use of structures in BEx.

Proposed Solution

For achieving the aforesaid scenarios, we can have a solution in which we somehow restrict the key figures along the structure elements in BEx, so that these are populated only for the required structure elements. In this way, it is possible to represent the restricted KFs along with the structure in WEBi and achieve the required results.

Overall approach for the solution :

Step 1) Create Structure in Bex Query designer to implement X-Axis in graph :-

The months on X Axis are represented by a structure in BEx. This structure holds the period buckets for Rolling months. You can name the structure elements as per your understanding.

For example:

Each selection is restricted to a 12 months starting from December current year. Here CY represents Current year and PY represents Prior Year.

Here, each structure element is restricted to a variable on 0CALMONTH along with an offset.

This is done using the value ranges, and applying an offset for the last 12 months.     

                  

Step 2) Stopping the structure elements using a Dummy KF :-

In order to stop the structure getting populated for all the elements (1 CY, 2 CY etc) except Dec, a Boolean logic in BEx can be implemented using an extra dummy KF called “Counter”.

This Counter KF is added as an extra KF in the InfoCube and gets filled by a constant for each record in Cube.

                   

                                                                                           Figure 5

• The addition of a KF is a feasible option as it does not require any existing data deletion. This is just an enhancement to the cube.
• This would be populated for all the dataset, hence making it flexible and easier to use it for other Boolean logics as well.
• The Counter KF comes handy when the data needs to be restricted for Prior year and next year.

Now, let’s take an example of a Cube having the following data:-

Actual Sales for 2012, 2011, 2010

Planned Sales of 2012, 2011, 2010

The user wants to see the Actual Rolling Sales and the Planned Rolling sales for Sept 2012. When the underlying BEx query is executed, the counter KF is populated for each structure element.

              

Note: The Counter KFs holds ‘1’ for every record present in the cube. The values for Counter KFs above represent aggregation at Calendar Year/Month used in structure. These can vary with the number of records fetched from the cube.

Case 1 : Restricting the Planned Rolling Sales KFs :-

When this counter KF is restricted only for December current year, we get a value only against 12 CY (see figure 6)

                                                                                              Figure 6

Now to restrict the Planned Rolling sales for only for 12 CY, we use a Boolean logic where only the intersection of “Planned Rolling Sales” and “Counter” takes place. Thus, we get a KF restricted to only 12 CY i.e. ‘Planned Rolling Sales with Counter”.

Case 2 : Restricting the Rolling Sales KFs :-

Similarly, by creating new restricted counter KFs; the Rolling sales can be restricted till current calendar year/month.

                                                                                                                      Figure 8

Implementing Rolling graphs in Webi

Once the Bex query is designed, the graph is easier to implement in Webi.

The structure in BEx query is represented by a dimension and the KFs are represented by Measures in Universe. The dimensions and the measures form the WEbi Query.

         

Use the Line or bar graph with structure as the dimension and measures as the KFs.

         

Graph Results:

         

Handling Complex Graphs using Boolean Logic in Bex Query

More complex graphs, such as the one shown below, with 2-3 years of Rolling figures can be implemented using boolean logic in  Bex queries. This requires multiple Counter KFs with different Boolean logics for current year, Prior year.

         

The above graph can be implemented using a structure of 36 elements, to bring in 3 years of Rolling data. Separate KFs for Prior to Prior year, Prior Year and Current Year are required to be build restricting using boolean logic (see figure 9).

With the help of BO WEBi Slice and dice property, the data can be split for multiple years using structure dimension.

          Figure 9