SAP Business Objects Planning and Consolidation version for Netweaver effectively leverages the Netweaver infrastructure and we can use the data stored in the infocubes in the Enterprise Data Warehouse (EDW) layer. Typically ‘Actual' values are stored in the EDW Infoprovider whereas ‘Plan' values are stored in the BPC generated Infoprovider. However the reporting requirements many times demand that we report the actual and plan values in the same report. In this blog we will discuss various options available to us to do this actual versus plan reporting in BPC and the Pros and Cons of each of them.
Copy Actual from BW to BPC:
The first question to ask is where we are storing the Actual data. Traditionally, those who have SAP as the ERP system, extract the Actual data from ECC and store it in BW with the help of delivered business content or custom infocubes/data store objects. If that is your situation, then you already have the Actual data in your BW system. In that case, the first, the simplest, the most straightforward option is to copy the actual from the EDW infoproviders to BPC application using the delivered data manager packages. You can learn more about this option at: http://www.sdn.sap.com/irj/scn/weblogs;jsessionid=(J2EE3414700)ID1897954850DB00258195100095085303End?blog=/pub/wlg/14002. This option is fraught with the least amount of risk when compared with the other options that we will discuss later. You can copy the Actual data from BW Infoprovider to BPC application and then use BPC reports to report Actual vs Plan values from BPC. This will work with 7.0NW as well as 7.5NW in the same way. Another ‘Pro' for this approach is that the data models for BW Actual and BPC Plan are typically different. BPC Data Manager functionally (delivered packages for data migration, transformation files, and conversion files) can be helpful to take care of common data model issues (BW fiscal year period vs. BPC YYYY.MMM format, for example) while importing Actual into BPC. If this is the simplest, easy to do and well supported scenario, then you may ask whether it has any limitations.
The biggest argument often put forth against this option is that it involves data duplication. Let us analyze this argument. First of all, in general, the Actual values are at a much granular level than the Plan values. Planning is generally done at a higher level - for example it may be done at product group level rather than SKU level whereas the transactional data is at the lowermost level since that is where the transactions occur. Hence even if we end up copying the Actual data from EDW infoprovider to BPC, we may end up copying such aggregated values that match with the level at which planning is done. To that extent, it is not a complete duplication. Secondly, most of the customers begin the planning process by ‘seeding' the plan either by previous plan/forecast or by Actual values. If they are seeding the plan by actual values anyway, then the data is getting duplicated anyway to begin the planning process. In that case we are just retaining those Actual values there with different category for reporting purposes. Thirdly, this process of copying Actual from EDW cube to BPC can be automatic and in the background so that the end users may not be affected. This can be a daily or weekly job as the case may be or even multiple times a day if that is the requirement. Finally, if you are using 7.5NW, then you can always drill through from a BPC report that shows the aggregated actual copied over from EDW, to more granular BEx report on the Actual EDW cube using the drill through functionality in 7.5NW
Now here we are assuming that the Actual values are stored in an EDW infoprovider in BW. What if all or part of the Actual values are not stored in there? In that case, if those values need to be brought in from the source system anyway, why not bring them over to BPC directly using the data manager packages to import the data? In that case, the Actual values will be in BPC and BPC reports can easily report Plan vs Actual data.
Let us consider some other Cons of this approach of copying Actual values to BPC. In some organizations, especially those who have BW running for years, BEx may be the tool of choice for all reporting needs and such organizations may be hesitant to provide BPC licenses to their reporting users only for Plan vs Actual reporting. Moreover these users need to be trained in using BPC reports. This is a very valid concern and we will discuss this more in detail in the next topic ‘export BPC data'. Another Con is that, ostensibly, this approach requires BPC clients to be installed on all the client machines that need to view the Plan vs Actual BPC report. However a possible workaround for this may be to use Live reporting feature of BPC or Distributor and Collector' functionality to view offline reports
Export BPC data:
One of the frequently asked questions is whether we can use BEx reports for BPC NW data. Please note that using BEx queries and reports for reporting BPC data contained in the BPC namespace infoproviders (http://www.sdn.sap.com/irj/scn/weblogs;jsessionid=(J2EE3414700)ID1897954850DB00258195100095085303End?blog=/pub/wlg/11279) is not a supported scenario. Please refer to the restrictions in note 1392259 also that talks about the limitations on using generic BEx with the BPC generated virtual infoprovider. Hence, in general, we should NOT create BEx queries on /CPMB infocubes. If BEx is the preferred reporting tool and if for any reason, the internal policies require you to use only BEx for reporting any data contained in BW, then there is another option available. That is to export the data from BPC to regular (non-BPC) BW cube and then report the data from that non-BPC cube using BEx. For 7.5NW, there is a data manager package that you can use to do so. For 7.0 NW, there is how to guide that talks about exporting data out of BPC (http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/b0427318-5ffb-2b10-9cac-96ec44c73c11). The exported BPC data can then be loaded into the EDW infoproviders using ETL tools of BW. In 7.5NW, this also can be automated since therein we have a data manager package that can help us execute a native BW process chain to import the flat file to EDW infoprovider. Then these data manager packages doing export and import can be linked together in a package link in data manager in 7.5NW and can be automatically executed as a sequence. This way we can use BEx reporting for BPC data if we have to. If we want to consider the Pros and Cons of this scenario then the first Pro, if applicable, is that you can use BEx reporting for Actual and Plan values. That way we can use one reporting tool across the organization. Cons include the additional design and administrative overhead associated with creating the export structure, duplicating the plan values and the transformations needed in the ETL configuration to map the exported BPC data with the characteristics/key figures in the EDW infoproviders as well as administering additional BW security as necessary.
Creating virtual Infoprovider:
One of the other project or implementation solutions can be to use a virtual Infoprovider to pass through the Actual or Plan values instead of copying or exporting them. The options discussed above are either copying Actual values from EDW infoproviders to BPC application or export Plan values from BPC to EDW infoproviders. Using virtual infoproviders can avoid whatever data duplication these approaches entail. A virtual infoprovider built within the BPC generated multiprovider can be used to report Actual values from EDW infoproviders using BPC reports or a virtual infoprovider built in EDW can be used to report Plan values from BPC application in order to use Bex reports. If this option avoids data duplication completely and it can be used in both the scenarios of using BPC report and BEx report, then why is this not a preferred approach? Well because all this comes at a price. The first Con for this approach is that building such virtual infoproviders requires substantial ABAP coding (to facilitate the handling of data model differences between BW and BPC). Secondly, if the planning model changes then all this ABAP code needs to be changed. (In the earlier options where are using delivered data manager packages, if the planning model changes, then we can use the transformation file/conversion file in BPC data manager to a great extent to take care of the changes). Thirdly the query performance from virtual infoproviders is considerably lower than that of the non-virtual infoproviders. Moreover, special consideration will be required for transporting such virtual infoproviders that are within the BPC multiprovider. Last but not the least; please refer to the note 1392259 regarding the restrictions about reporting with virtual infoprovider. Hence please be careful before adopting the virtual infoprovider as the reporting solution.
Creating designated separate cubes within BPC multiprovider:
Here is one more option on Plan vs Actual reporting. Instead of copying the Actual data from EDW infoproviders to BPC application, can we load it to a custom infocube created within BPC multiprovider? The answer is ‘yes' if that suits your requirements.
The advantage over here is that we can leverage BW ETL constructs to load data from EDW infoproviders to the custom infocube. Further, we have the flexibility to create more infocubes if needed - for example, one infocube for each year etc. One big Con here is that with this approach, we have to be very careful while handling the transports. Also this may require the target system to be open for modification if necessary to activate the multiprovider etc. Another Con is that every time the planning model changes, the ETL may have to be modified.
Finally, a note about using SAP Business Objects tools like Voyager and Webi. We can also use Voyager and Webi to report Actual data from EDW infoproviders and Plan data from BPC applications since these tools can be source agnostic and can combine data from disparate sources. However these tools have to be installed separately and do not come with the standard installation of BPC.
To summarize, we can consider three scenarios here:
- Scenario 1: Copy Actual values from BW to BPC and use BPC reports to report Actual vs Plan values (this uses the delivered functionality)
- Scenario 2: Export Plan data from BPC to (non-BPC) BW infoprovider and use BW reporting tools to report Actual vs Plan from that cube (this way all reporting can be done with BEx)
- Scenario 3: Create virtual infoprovider on the EDW side to view Plan data from BPC if you want to use BW reporting tools or create virtual infoprovider within the generated multiprovider to view Actual values from BW. (this scenario avoids data duplication at the cost of performance and additional administrative overheads)
Thus we can see that there are various plausible options available to do Actual vs Plan reporting in BPC version for NW and the pros and cons of each should be vetted carefully before making the implementation decision.