Modern SAP BI tools provide a range of capabilities for complex business scenarios involving reporting, dashboard design, and predictive analytics, together with data-entry and business-planning capabilities. I provide a detailed overview of these competencies using an example of a business application and related data modeling where transactional numbers from SAP ERP have to be combined in a dashboard with a set of key figures maintained by users via a web portal page.

In terms of tools, I am limiting the scope of this article to SAP Business Warehouse (BW) Integrated Planning (IP) on the back end, and BusinessObjects Design Studio on the front end. Obviously, SAP Business Planning and Consolidation (BPC) and other planning tools can be used in case of complex planning scenarios. However, in this article I show how SAP BW IP itself, together with BusinessObjects, meets the needs of this particular business requirement.

In the case of a highly decentralized retail organization with multiple entities maintaining their transactional data in various ERP systems and Microsoft Excel spreadsheets, a common data entry approach is needed to deliver a range of benchmark key performance indicators (KPIs). Here, I am talking about thousands of sites. To set up data consolidation from such a variety of different (in nature) data sources may take years, whereas a portal entry page for major KPIs may require only few months’ work.

Let’s imagine that you’re getting financial data for these entities via a data extraction from local ERP systems while non-financial numbers are stored elsewhere or not stored anywhere at all. The benchmark KPIs require combining financial and non-financial numbers in a common set of dashboards. Some examples of non-financial numbers by site are number of working hours per month, number of employees, number of safety incidents, number of special equipment units used, retail store area, and number of full-time employees (FTEs).

Some of the non-finance indicators can be considered as master data for retail sites, and some others can be maintained on a weekly or monthly basis. The challenge here is, if there is no source system containing these numbers in a consistent way, how do you collect them from hundreds or thousands of retail locations?

One option for doing this is to use a BusinessObjects Design Studio Portal application connected to an SAP BW IP model. When published on the SAP BI platform, the application can be accessed via a URL for site users so that they can see and maintain those indicators in a consistent, standardized way. The data-entry system accepts only numeric KPIs; characters are not allowed. A consolidated dashboard with drill-down capabilities is designed for data-quality monitoring purposes. To give access to appropriate data slices within the application, a proper SAP BW authorization model can be applied that grants users rights to maintain indicators depending on their profile and location.

First I start with a high-level model description of this process, followed by detailed, step-by-step instructions on how to set up a proper solution using the latest back-end and front-end tools from SAP.

Proposed Data Model

A model in SAP BW on SAP HANA (Figure 1) can be made up of a transactional data flow for finance indicators (e.g., finance general ledger [FIGL]-line items) and a data flow for non-finance indicators that is maintained by site users via a portal page in SAP BW IP. For the non-finance indicators you need to set up appropriate aggregation levels in SAP BW IP on top of direct update DataStore Objects (DSOs) and input-ready Business Explorer (BEx) queries. The queries have to have the proper level of detail needed for data entry, and appropriate authorization-relevant InfoObjects for maintaining authorizations and security by site and user group. This kind of setup allows users to enter non-finance indicators via a portal web page.

Figure 1

A data flow example for a solution that leverages BusinessObjects Design Studio on top of BW IP

SAP BW IP Design-Relevant Model

The next step is to define the proper key figures to be used in the direct-update DSO for data-entry purposes.

You need to set up a generic number Key Figure type with no aggregation to be used in the direct-update DSOs and the relevant BEx Queries—in this case, ZKFNO2. In the Administrator Workbench (not shown) select InfoObjects and click the Create New Key Figures button to create a new key figure. A screen like the one in Figure 2 opens, where you make the required entries under the Aggregation tab.

Figure 2

Define the relevant key figures

Next, you need to set the InfoObject ZPLANT (a copy of a standard 0Plant) as authorization-relevant to enable analysis authorizations by site. Go to the Business Explorer tab (shown in Figure 3), and tick the Authorization Relevant check box to enable analysis authorizations. Then activate the InfoObject by clicking the activate icon (not shown).

Figure 3

Make the ZPLANT InfoObject authorization-relevant so it can be used in analysis authorizations

Design Finance Documents’ Advanced DSO

The next step is to design the finance documents’ advanced DSO to store FIGL-item transactions. This advanced DSO can be based on a standard business content DSO for FIGL items (e.g., 0FIGL_O14); otherwise, it has to be created from scratch. The advanced DSO accumulates FIGL-item data from appropriate extractors by source system. In the described scenario, the FIGL items are supplied by source systems from multiple countries independently. In cases in which there are large data volumes you have to leverage the extended-table property, available in Dynamic Tiering. This is done by checking the Extended table property check box in the advanced DSO.

For more information about the Dynamic Tiering capabilities available in SAP HANA Support Package 9 and up, follow this link: https://help.sap.com/saphelp_nw74/helpdata/en/c0/fbf3f7bef240bfb3e93d3d5e005619/content.htm.

Design Direct-Update DSOs for Non-Finance KPIs

Direct-update DSOs are used for modeling data-entry capabilities with the help of SAP BW IP (e.g., one DSO for master data and another one for transactional data with the 0CALMONTH object in the key fields). In this section, I show how to create a new direct-update DSO.

To design a data entry model in SAP BW IP, you first have to set up appropriate filters and aggregation levels by executing transaction code RSPLAN. This opens the screen in Figure 4 with an aggregation-level example for master data KPIs by site and cost center. Here you can create aggregation levels by dragging and dropping InfoObjects to the Aggregation Level area. Once you’ve made your desired changes, click the activate icon (not shown) to save and activate your changes.

Figure 4

The aggregation levels for master data KPIs by cost center and site

Connect All DSOs in a CompositeProvider

The CompositeProvider has to connect the underlying DSOs (e.g., the direct-update DSOs together with the FIGL transactional ADSO) in a union. Some of the key fields that may be used for connectivity are Site, Cost Center, and Period.

Design a Set of BEx Queries

To consume SAP BW on SAP HANA data models in the front-end tools, you first have to design BEx Queries. Input-ready queries are used for designing portal pages for data-entry capabilities (non-finance KPIs). KPI-defining queries are based on combined datasets of finance and non-finance indicators in order to design end-user dashboards and reports.

Design BEx Input-Ready Queries

To design data-entry capabilities for end users, first you have to set up a BEx Query with input-ready key figures for each KPI type from the list of non-finance indicators maintained via the portal entry (e.g., the master data site or the cost center transactional data). These BEx Queries are designed on top of the appropriate aggregation levels. Navigate to the BEx Query Designer to create a new query, and select the appropriate aggregation level with non-finance indicators (Figure 5).

Figure 5

Set the Key Figures as input-ready and the disaggregation value rule in the input-ready BEx Query

Select the disaggregation value by ticking the Disaggregation Copy radio button. Disaggregation (or top-down distribution) is used when you want to set a rule for distributing values top down. For example, in the scenario described in this article you want users to enter master data values by site, and the system has to copy (distribute) these values down to all cost centers belonging to the site. Therefore, you set the rule for Disaggregation Copy to achieve this. Once you’ve made your desired changes, click the save icon to save the Query.

Useful links
For more details about input-ready queries, please refer to this SAP Help portal link:
https://help.sap.com/saphelp_nw70/helpdata/en/43/1d023a41130bd5e10000000a422035/frameset.htm.

To see some examples of different types of top-down distributions, follow this link:
https://help.sap.com/saphelp_nw70/helpdata/en/47/27759655ea6560e10000000a11466f/content.htm.

Design BEx Queries for KPI Analysis

Next, you need to create a set of BEx Queries that deliver end-user KPIs to be analyzed in the dashboards and reports. You can set up a number of BEx Queries on top of the client report CompositeProvider to deliver needed KPIs. The queries should use appropriate filters or structures to calculate and combine KPIs based on finance and non-finance InfoProviders. One of the filters can be a selection of KPIs that the query is supposed to deliver in a particular dashboard. Another filter can be based on Cost Centers, for example, and exclude all overhead related Cost Centers. These queries are consumed in the analytical dashboards.

Consume Data Models in BusinessObjects Design Studio

The next step requires designing an application in BusinessObjects Design Studio. The application consumes the BEx Queries that were developed in the previous steps. Once published on the BI platform, the application is available to end users as a web portal page.

You can use a Planning Template as your starting point (Figure 6). Launch BusinessObjects Design Studio, select Planning Template to create a new application, and then click the Finish button. This opens a screen like the one in Figure 7 where you can add BEx Queries (both input ready and Analysis KPIs) as data sources.

Figure 6

Create a new application and planning template in BusinessObjects Design Studio

Figure 7

Examples of new data sources added in the new application

In Figure 7, the new data sources have already been added. To add new ones, you simply mouse over the data sources you want to add and right-click to add them.

The next step is to add a Tabstrip component in the application so that you can combine and arrange objects by tab. In the Container Components section on the left, select Tabstrip (Figure 8) and drag it down to the Layout folder below to add it. This changes the screen on the right, which now shows two new tabs: Tab 1 and Tab 2.

Figure 8

Add new Tabstrip components

Next, for a more user-friendly interface, re-arrange data-entry panels by copying and pasting them inside the Data Entry Tab. Select the crosstab object in the Outline section (in this case, CROSSTAB_6), and click it to see its Properties (Figure 9). Then assign the data source from the Data Source list to associate it with the crosstab.

Figure 9

Link the data source to a crosstab

Next, you want to adjust the on_startup code for data sources to enable input readiness. Scroll down to the Edit Script Function area of the BusinessObjects Design Studio application (Figure 10). Select the on_startup function and update the code for each input-ready data source in the Script box below it. Then click the Save button (not shown) to save your changes.

Figure 10

Make modifications to the on_startup script to enable input readiness

The next step is to add dimension filters and link them to queries. In the Site Data Entry area of BusinessObjects Design Studio (Figure 11), drag and drop your required dimension filters to the application screen, and bind them to the appropriate data sources.

Figure 11

Bind the dimension filters to the data sources

These modifications are extremely useful for proper user navigation experience. Once these changes are implemented, users are able to select country/site combinations from drop-down lists and all the queries in the portal page display only the relevant information.

Portal Entry Page Example

After all these customizations have been made, the web application can be launched via a URL. It then authenticates users with SAP BW authorizations and only displays information that is relevant to the user. Figure 11 shows an example of the look and feel of the new data entry application.

Figure 12

An example of a portal Site Data Entry Page based on the BusinessObjects Design Studio application

This is a sample data entry page available to site users via a browser. It is designed in BusinessObjects Design Studio 1.5 connecting to BEx Queries on top of BW IP—Integrated Planning—in SAP BW 7.4 on SAP HANA. When users update KPIs and click the save icon, the numbers are automatically updated in the direct-update DSO. Further, these non-finance KPIs are used for analytical reporting in combination with the FIGL-item data from the traditional SAP BW data flows. Please note, in BO Design Studio 1.6 users can easily copy paste budget numbers from Excel spreadsheets instead of entering them one by one.

The set of BEx Queries developed for KPIs can be consumed in various BusinessObjects front-end tools for reporting and analysis: from BusinessObjects Design Studio to SAP Lumira, BusinessObjects Analysis, and Crystal Reports. I provide an overview of the various BusinessObjects tools in my “Quick Guide to Help You Select the Right Presentation-Layer SAP BI Tool,” BI Expert article.

Sergei Peleshuk

Sergei Peleshuk has more than 15 years of experience implementing BI technologies for global clients in retail, distribution, fast-moving consumer goods (FMCG), oil, and gas industries. He has helped clients to design robust BI reporting and planning capabilities, leading them through all project phases: from analysis of requirements to building BI roadmaps, technical architecture, and efficient BI teams. Sergei is an expert in modern BI tools and technologies available on the market, including SAP Business Warehouse (SAP BW), SAP HANA, BusinessObjects, and SAP Lumira. Sergei maintains a business intelligence portal at www.biportal.org.

Sergei will be presenting at the upcoming SAPinsider HANA 2017 conference, June 14-16, 2017, in Amsterdam. For information on the event, click here.

You may contact the author at peleshuk@biportal.org.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.