Using SAP BEx and SAP BO as an alternative to report SAP BPC data for Financial Consolidation

[former_member198951]

Main goal

This article will explain how to ETL (Extract, Transform and Load) data from SAP BPC 10.x model into in SAP BW 7.4+ dataflow. With this solution, existing SAP BW and SAP BO reports can be quickly migrated to consume SAP BPC data.

The assumption is that you already have your Financial Consolidation reports consuming data from SAP ECC built on the top of SAP BW providers. Now you would like to use the same reports (BEx Queries and SAP BO) as-is to report SAP BPC data instead. Meaning you do not want to build all the reports from the scratch in SAP EPM client tools. If that is not the case, of course you should be using SAP EPM..

Key Concepts:

With SAP BW 7.4, the feature of using “Query as an Infoprovider” allows the extraction of data using Transient BEx Query on the top of BPC Virtual Provider.  The consulting solution presented here can be achieved by creating a SAP BW transformation using the BPC Transient Query as source. Some ABAP development on SAP BW backend is required.

Prerequisite(s)

SAP BPC 10.x and SAP BW 7.4+ on any database. Prior SAP BW modeling knowledge is required. ABAP development knowledge is strongly recommended. SAP BO knowledge is required, in case you need to adjust Webi reports, for instance. Basic knowledge of BPC is required.

Audience

SAP BI-BW Architects/Developers/Modelers, SAP Business Objects developers, Solution Architects.

 

 

Description of the solution

This article is focusing on consumption of SAP BPC data using SAP BW data model. Meaning SAP BPC data will be copied back into SAP BW data model using regular master data structure.

1. Design Overview – Model / Data Flow

 

2. SAP BPC Virtual Provider and naming convention

As well known, as per SAP BW environment, SAP BPC uses its own structure of master data which are totally different from regular SAP BW objects, for Instance ACCOUNT = 0GL_ACCOUNT. Other common samples are the “Time dimensions” and the “Key figures”. Usually BPC objects are copies from original SAP BW objects translated in a special naming convention.  Moreover, the internal technical name changes when promoting SAP BPC models through SAP BW environments (Dev, QA, PROD). To fix it the SAP Note 1689814 needs to be applied.

Sample here shows how a BPC Virtual Provider meta data looks like:

 

3. SAP BPC mapping of Dimensions x SAP BW meta data

To deal with the SAP BPC and SAP BW meta data discrepancies a custom mapping needs be created. The follow is the breakdown SAP BPC x SAP BW:

4. Customizing of the SAP BPC mapping as per the Dimensions and Technical Names

Below a very simple ABAP DDIC custom table maintained via SM30. This table is used in the SAP BW transformation through ABAP coding.

 

5. Developing of the SAP BEx Transient Query from BPC Virtual Provider

As explained before, the BEx Transient Query is used as datasource. SAP Note 1872718 has all the details how to develop it. Down here the explanation of the measures (or key figures) provided by the SAP BPC Transient Query:

 Important: I had to change the calculation for the ACCOUNT_SIGN because during the transformation the values were being zeroed out.

Before:

After:

ACCOUNT_SIGN variable uses a replacement path based on ACCOUNT to change the sign of the measure accordingly during reporting. Using BEx Transient Query as Provider this is not required. It fails and brings all values as zero.

6. SAP BW Dataflow: SAP BW Transformation to new GL Account provider using the BPC BEx Transient Query as Provider

Selecting Query as Provider from and choosing the SAP BEx Transient Query from the drop list:

For the transformation itself, it would be better to create an Expert Routine. As you may know the Expert Routine transformation has both SOURCE_PACKAGE (BEx Transient Query structure) and the RESULT_PACKAGE (which will be the new infoprovider as copy of the original GL BW cube)

The ABAP coding should not be that complex.  During the LOOP AT in the SOURCE_PACKAGE just refer to the ABAP DDIC custom mapping table and assign every dimension accordingly. Usually I use FIELD_SYMBOLS and ASSIGN. Then MOVE-CORRESPONDING to the RESULT_PACKAGE.

Heads-up: The sample presented here was done in SAP Netweaver BW 7.4 SP7. In this patch, I had issue with ASSIGN_TYPE_CONFLICT short dump. If that happens, SAP OSS Note 2164973 should resolve it.

7. SAP BW Dataflow: DTP (Data Transfer Process) settings

The DTP should be always Full Load using the Fiscal Period and any other additional filters. In fact, we should put filters as much as possible to restrict the data from the very first source. In addition, if you have Actual and Plan data you should be splitting them in two different DTPs.

In the sample above, the SCOPE refers to the BPC model. TIME is in fact the Fiscal Period. For TIME, it would be better to have an ABAP code retrieving parameter from somewhere else. STVARC for instance would do it. Or you a new parameter could also be added to the ABAP DDIC custom table.

Heads-up: At the beginning the data loads were working just fine. However, with the increase of data in SAP BPC it become very slow. Memory consumption due to MDX statements were also an issue. To couple with it we applied the SAP note 2073240. It completely resolved everything.

8. SAP BW Dataflow: Process Chains

The process chains should be very simple. Only observation would be the “Overlapping Request” process chain event. The data load from SAP BPC using the BEx Transient Query always perform full-load. In this case, the “Delete Request Overlapping” based on the DTP settings should be carried out to delete the previous request, for obvious reason.

 

9. SAP BW: Copying BEx queries from original SAP GL Account infoprovider to the new SAP GL BPC

In this step, the copy of existing BEx Queries used for Financial Statement reports should be carried out by using tcode RSZC (Copying BEx Queries in between providers). As SAP best-practices we usually create BEx Queries on the top of multiproviders, regardless if there is only one infoprovider

The meta data of both source and target must match. The BEx Query being copied must have exactly the same attributes and measures in the target provider. Otherwise the copy will fail.

 

10. SAP BO Web Intelligence: Changing the source of the BICS connection based on SAP BEx Query

Suppose you have quite a few SAP BO Webi reports that need to be replicated and now should be consuming data from the new SAP BW BPC provider. Now that we have all the BEx Queries in the new SAP BW BPC provider, that will be just a matter of changing the BICS connection accordingly:

 

That is the end of this article.

I hope you find it useful.

Cheers,

 

Fernando