As I discussed in my article, “How to Load Data from an InfoProvider Using Data Manager,” SAP BusinessObjects Planning and Consolidation, version for SAP NetWeaver extensively uses process chains. These process chains are automatically invoked by SAP BusinessObjects Planning and Consolidation when a user executes processes from the front end.

Should these process chains be exclusively executed only within SAP BusinessObjects Planning and Consolidation? Not necessarily. You should be able to execute them using native SAP NetWeaver BW. You may want to integrate the execution of a Data Manager package with some other custom process chain in native SAP NetWeaver BW as a step. If so, is there any way to do that? I show you a method to accomplish that goal, and as an example use loading of transactional data with a flat file so you can see the detailed procedure to automate execution of the Data Manager package.

Let’s begin by understanding the business reason to run the SAP BusinessObjects Planning and Consolidation process chain outside the planning and consolidation application. To do that you first need to understand how the optimization process works in SAP BusinessObjects Planning and Consolidation.

Optimizing the Data Model

Optimization of the data model refers to an automatic technical adjustment of the model to improve performance. As far as the end user is concerned, the data model does not change; however, in the back end, the way to store data changes so that the query performance is better. Let’s discuss how the optimization process works in SAP BusinessObjects Planning and Consolidation.

A dimension in SAP BusinessObjects Planning and Consolidation is equivalent to a characteristic in SAP NetWeaver BW, and likewise dimension members are equivalent to characteristic values. Taking this further, when a user creates a dimension, an SAP NetWeaver BW characteristic is generated in the SAP BusinessObjects Planning and Consolidation namespace. When a user creates a dimension member for that dimension, a characteristic value is generated in SAP NetWeaver BW in the master data of the characteristic corresponding to the associated dimension.

When a user creates an application in SAP BusinessObjects Planning and Consolidation by selecting dimensions, an InfoCube and a MultiProvider containing that InfoCube are generated in the namespace, which includes all the characteristics corresponding to the selected dimensions. At the same time, a transparent query is also generated for additional functional purposes. It is through this query that the data from the InfoCube generated in the namespace is interpreted. For this reason, avoid normal data mart operations with the generated InfoCube.

An SAP BusinessObjects Planning and Consolidation dimension is different from an SAP NetWeaver BW dimension. In SAP NetWeaver BW, the term dimension is used to group characteristics. How the characteristics in a SAP BusinessObjects Planning and Consolidation InfoCube are organized among the SAP NetWeaver BW dimensions depends on the number of dimensions included in the planning and consolidation application.

If the number of dimensions is 13 or fewer, then all of them are automatically modeled as line item dimensions in the SAP BusinessObjects Planning and Consolidation InfoCube. This is because SAP NetWeaver BW allows up to 13 user-defined dimensions in an InfoCube. If the number of planning and consolidation dimensions exceeds 13, then the InfoCube model is automatically generated for those dimensions.

The data modeling generated while creating the InfoCube may not remain the most optimized one as the fact table of the cube begins to grow. SAP BusinessObjects Planning and Consolidation gives the option to optimize the data model from the front end. In SAP Planning and Consolidation’s action pane, there are two options to optimize: Lite Optimize and Full Optimize (Figure 1). Optimize in this sense means changing the dimension assignments to improve performance.

Figure 1

Options to optimize the data model

The Lite Optimize option does not make any changes to the data model. It just closes the open request, compresses and indexes the cube, and updates database statistics. The Full Optimize option can rearrange the characteristics among the 13 user-defined SAP NetWeaver BW dimensions. The Full Optimize process checks if the size of the dimension table is less than 20 percent of the fact table and creates as many line item dimensions as possible (20 percent is the standard optimization logic built in). To do this reconfiguration, the Full Optimize process: 

1. Takes the application set offline
2. Creates a shadow cube with optimal data model
3. Links the new optimal cube to the MultiProvider for the application
4. Moves data to the shadow cube
5. Deletes the original cube
6. Closes the open request
7. Compresses and indexes the cube
8. Updates database statistics
9. Brings the app set online again

Though this results in creating a new InfoCube, the MultiProvider remains the same and all the SAP BusinessObjects Planning and Consolidation reports are built on the MultiProvider and not the underlying InfoCube. Hence, this optimization does not affect the SAP BusinessObjects Planning and Consolidation reports on this data. 

Using ETL for InfoCubes

Because the data that the user enters from the SAP BusinessObjects Planning and Consolidation front end is stored in the underlying, real-time InfoCube for that application, you might ask whether it is possible to load data to that cube with the normal SAP NetWeaver BW extraction, transformation, and loading (ETL) process. The answer is yes, but with a caveat: The ETL process may have to be recreated in case a user executes the Full Optimize process for the application, because that process may result in the creation of a new InfoCube as I describe earlier.

You can use ETL for the SAP BusinessObjects Planning and Consolidation InfoCubes. Figure 2 shows an example of a data transfer process (DTP) request to load data through a flat file to an InfoCube.

Figure 2

A DTP request to load data through a flat file

If the user performs a Full Optimize process for this application, it can result in the creation of a new InfoCube with a more optimal data model. That new InfoCube, however, is automatically linked to the MultiProvider for the SAP BusinessObjects Planning and Consolidation application, but does not inherit the ETL structure that was built on the original cube. So in Figure 2, if the user executes a Full Optimize for the FINANCE application, the new optimal InfoCube for the application may not inherit the data transfer process (DTP) created on the original /CPMB/DZID30P InfoCube. By the way, the name /CPMB/DZID30P is automatically generated by the system in the /CPMB namespace. All InfoCubes generated by SAP BusinessObjects Planning and Consolidation are generated within the /CPMB namespace.

The source system, data source, and InfoSource remain, but the transformation that links these to the InfoCube is deleted and has to be recreated. If this optimization happens in the production system, then the transformation may have to be recreated and transported up the landscape.

A way to prevent such a situation is to execute the process chains used by SAP BusinessObjects Planning and Consolidation to load data using native SAP NetWeaver BW tools outside the planning and consolidation app. In the above example, a flat file is loaded to the SAP BusinessObjects Planning and Consolidation InfoCube using ETL tools. However, SAP BusinessObjects Planning and Consolidation offers the front-end functionality of Data Manager to load data either through a flat file or from any other InfoProvider. Data Manager uses SAP BusinessObjects Planning and Consolidation process chains in the background to load the data (Figure 3). If you run these process chains outside SAP BusinessObjects Planning and Consolidation from the enterprise data warehousing layer using SAP NetWeaver BW, then not only can you integrate this with the custom process chains, but you can also prevent the issue of ETL structures being deleted on Full Optimize.

Figure 3

Standard process chains in the /CPMB namespace, with the process chain that loads transaction data from flat file highlighted

If you use the SAP open hub service, you may want to create a user-defined process chain that automates the creation of the flat file and the loading of the file into the InfoCube. In such cases, running SAP BusinessObjects Planning and Consolidation process chains outside the application becomes very important.

If this user-defined process chain can run the SAP BusinessObjects Planning and Consolidation process chain to load the data to the InfoCube, then you have an “industrial strength” solution for loading data to SAP BusinessObjects Planning and Consolidation InfoCubes using the SAP NetWeaver BW toolset. The question now becomes how to accomplish this. Let’s look at the steps involved.

Step 1. Upload the File

The open hub can place the file at any specified location on the SAP BusinessObjects Planning and Consolidation application server. Alternatively, you can upload the flat file to the SAP BusinessObjects Planning and Consolidation file service using transaction UJFS (Figure 4).

Figure 4

Use transaction UJFS to to upload a flat file

Step 2. Validate the Transformation File

The second step is to create a transformation file using the SAP BusinessObjects Planning and Consolidation front end. Although you want to run the SAP BusinessObjects Planning and Consolidation process chain with native SAP NetWeaver BW tools, in this step you must use the SAP BusinessObjects Planning and Consolidation front end because the process chain looks for the XML version of the transformation file. When you create and validate the transformation file from the SAP BusinessObjects Planning and Consolidation front end, the XML version is automatically created and stored in the file service (Figure 5).

Figure 5

The results of the validation

Step 3. Create the Answer Prompt File Manually

Now create an answer prompt file that passes the required parameters to the SAP BusinessObjects Planning and Consolidation process chain. The format of the answer prompt file is as follows:

%FILE%    'csv file path in file service'

      %TRANSFORMATION%                 'transformation file path in file service '

      %CLEARDATA%       1/0

      %RUNLOGIC%         1/0

      %CHECKLCK%        1/0

This method allows you to create the answer prompt file manually. For expert users, creating the answer prompt file manually is the quickest way. The answer prompt file must be a tab delimited file, and each prompt should start on a new line. If you follow this rule, then the answer prompt looks similar to the one shown for ZBICGR01 in Figure 6.

Figure 6

Example of an answer prompt file that is created manually

Create Answer Prompts from the Data Manager Package Log

Is the format discussed above for all answer prompt files? Can you get any clues about the format of each package anywhere in the system? When you execute the Data Manager package manually, what happens to the prompts that you entered? At a later date, can you see what prompts you entered while executing that Data Manager package?

First, although all answer prompts need to be tab delimited, the detailed format is not the same for all packages. You can learn about the format for each package by referring to the Data Manager package log. Here’s how to use that approach:

When a user executes a Data Manager package manually by answering the prompts presented during the execution, those answers are stored in the Data Manager log file in the file service. In SAP BusinessObjects Planning and Consolidation, you can access the file service using the SAP GUI and executing transaction UJFS. The file service the app sets is as shown in Figure 7.

Figure 7

File service applications

The Data Manager log files that contain the answer prompts are stored in the privatepublications folder (Figure 8).

Figure 8

Open the privatepublications folder to see the Data Manager log files

Within the privatepublications folder, access the domain, and within the domain, access the user folder that has executed the Data Manager package (for example, user i805260 in Figure 9). For that user, the answer prompt files are stored in the folder called tempfiles.

Figure 9

Answer prompt files for user i805260Answer prompt files for user i805260

To view this log, you need to download it. The log looks similar to what is shown in Figure 10.

Figure 10

Data Manager package log

Notice that the answer prompt that you created manually in the earlier step (ZBICGR01) is included in the top portion of the log in Figure 10. To create the answer prompt file from this log, you copy the pertinent portion about the answer prompt before the task names and warnings, and clean up the remaining log, including and after the task names and warnings. By doing so, you can create the answer prompt from one of the successful manual executions of the Data Manager package log.

Without looking at the Data Manager package log, it would be difficult to manually construct the prompt file for automating this package since the format of the prompt file in this case is not intuitive. However, the trick behind using the Data Manager package log makes your work very easy: Just copy the log, clean it up a bit, and get your answer prompt file. This answer prompt file can then be used in the delivered program UJD_TEST_PACKAGE in order to automate any Data Manager package — even those that are not delivered.

Step 4. Use the Answer Prompt File

The fourth step is to run program UJD_TEST_PACKAGE with the right app set and application. Use the answer prompt file created in the above step in the program UJD_TEST_PACKAGE. You can select the answer prompt file using the drop-down from the answer prompt field in Figure 11 and saving the variant for the program. The variant can be accessed by clicking the icon next to execute icon. 

Figure 11

Select and save the answer prompt file

However, note that the UJD_TEST_PACKAGE program was originally designed to assist in debugging Data Manager packages. Therefore, it is a good idea to copy this program to a user-defined program and use the user-defined program in the next step. Doing so keeps you on the safer side so that if future development changes the nature of UJD_TEST_PACKAGE program, you won’t encounter any unforeseen issues.

Step 5. Create the Custom Process Chain

In the final step, you are ready to create a custom process chain that executes the SAP BusinessObjects Planning and Consolidation process chain (Figure 12). Create a user-defined process chain using transaction RSPC and include a process type to execute the ABAP program. Create the process chain by clicking the create icon, and select the process type for executing ABAP program. Include the UJD_TEST_PACKAGE program (or the user-defined program created based on the UJD_TEST_PACKAGE) with the saved variant.

Figure 12

Custom process chain showing the ABAP program with variant

Select the process chain to activate, and then execute it by clicking the activate icon. Figure 13 shows an activated custom process chain.

Figure 13

Activated custom process chain

Thus you can automate any Data Manager package from SAP BusinessObjects Planning and Consolidation. These steps work not only for a process chain to load a flat file into an SAP BusinessObjects Planning and Consolidation InfoCube with an open hub, but also for loading data from another InfoProvider to an SAP BusinessObjects Planning and Consolidation InfoCube or other custom Data Manager packages created in SAP BusinessObjects Planning and Consolidation.