Data Manager is an SAP BusinessObjects Planning and Consolidation module that helps you manage loading, transforming, and manipulating data. Not only is running Data Manager packages a business user activity, building Data Manager packages is also an activity that the business owns. Typically, the creation of Data Manager packages is owned by a power user in the line of business who is familiar with the different sources of data that the business may need to load into SAP BusinessObjects Planning and Consolidation for use within their planning applications. We discuss setting up Data Manager packages, working with transformation and conversion files, running and scheduling packages, as well as monitoring the status of packages.

Set Up Data Manager Packages

You create Data Manager packages from process chains. To create a new package, go to Manage Data > Maintain data management > Manage packages > Add Package. You can build this package on any of the delivered SAP BusinessObjects Planning and Consolidation process chains that are delivered with the application.

You can then choose which process chain to use for Data Manager by selecting one from the drop-down menu. Only process chains that are built for SAP BusinessObjects Planning and Consolidation are displayed (Figure 1). After you select a process chain, name your Data Manager package — such as Import into Financial Application (Figure 2).

Figure 1

Select a process chain

Figure 2

Name your Data Manager package

Keep in mind that when defining the package, you have to choose a task type for this package. The task type can either be a User Package or an Admin Package. This allows you to control whether a user or an administrator has access to run the package. It only makes sense to select one of these options — if you select both, Admin Package takes precedence and the system ignores the selection of the User Package.

If you select Admin Package, only users who are SAP BusinessObjects Planning and Consolidation administrators will be able to run the package. You can still save this package to any team (even non-admin teams). With the option User Package, all users that have “Execute Data Management” task security can view the package. Team leaders and administrators can assign packages to teams using the menu option Manage team user package access. The primary purpose of the Task Type setting is to restrict users from running packages that could damage data, such as clearing all data (zero values) by accident.

That is all that is needed to define a Data Manager package. It is important to understand what packages users need access to and define your users and teams appropriately. 

Working with Transformation Files

Within the BW world, IT usually handles loading data into SAP NetWeaver BW. In most scenarios, the source data is not in the format that SAP NetWeaver BW requires. IT writes transfer rules and update rules to transform the data. Usually, this is a complex process that requires the IT administrator to carry out some type of coding.

Within SAP BusinessObjects Planning and Consolidation, business users typically require data from transactional systems such as SAP ERP within SAP BusinessObjects Planning and Consolidation to plan on, based on actual transactions in SAP ERP. Typically, the SAP ERP source systems (or other source of ACTUALS data) have a different format than SAP BusinessObjects Planning and Consolidation, so you also need to transform the data. Business users can do this within SAP BusinessObjects Planning and Consolidation by defining transformation files, which allow application administrators or team leaders to set up the rules for reading data from a source and put it in the proper format for the target application.

Figure 3 shows an example of a transformation file. An end user running a Data Manager package to import data can choose a transformation file to transform the data when importing a set of data. The transformation file defines how to map the data to a format that can be loaded into an application. The transformation definition is in an Excel file format that contains one worksheet named Instructions. The file is divided into three parts (*OPTIONS, *MAPPING, and *CONVERSION). Additionally, it references conversion files that may contain multiple worksheets.

Figure 3

Transformation file

The *OPTIONS section specifies different file formats (e.g., if the format is delimited by a comma and has a header). The *MAPPING section allows business users to map fields from the source to a target structure. Finally, the *CONVERSION section specifies specific conversions for each dimension that is being loaded.

Working with Conversion Files

You can use conversion files to convert data for specific dimensions. They allow business users to define the mapping from external to internal member names (source to target). You can do this through fixed lists, wildcards, or functions. Data Manager supports JavaScript in conversion files for defining functions. Figure 4 shows a fixed list mapping.

Figure 4

Conversion file example

To create a new conversion file, go to Manage Data > Maintain conversions > Create new conversion. In this example, we’ve mapped external to internal values. For example, anytime JAN is loaded from the source system, it is replaced automatically by 2007.JAN when loading into the target based on the conversion rules you define (see above example). Using Excel in this way makes it easy for business users to perform complex transformations without having to do lots of ABAP coding, which is typical with SAP NetWeaver BW.

For more complex mapping, use wildcards and formulas. For wildcards, you can use the asterisk (*) and question mark (?) wildcards in the EXTERNAL and INTERNAL columns. The asterisk (*) represents any character, while the question mark (?) represents any single character. For formulas, you can use either formulas for dimensions or formulas for measures.

For example, suppose you wanted to create formulas for your CATEGORY dimension to ensure your plan was flagged as ACTUAL or BUDGET. However, the source system doesn’t have the values ACTUAL or BUDGET. Therefore, you need to convert the data. To do so, you write formulas for dimensions within the INTERNAL column. The formula in row 2 in Figure 5 does the following: If the value for the category in the source file begins with “act,” then the formula maps this value to ACTUAL. Otherwise, the formula checks if the value for the category in the source you are loading from is four characters long. If it is, the formula writes the value as BUDGET. This way, your planning category flags your data as ACTUAL or BUDGET.  

Figure 5

Formulas for dimensions

Now that we’ve looked at conversions for dimensions, you can also write formulas to convert the transactional measure values. For example, assume that the source data you are loading is from SAP ERP and you only need the data to have two decimal places, but the SAP ERP source has 10 decimal places. For this scenario, you would write formulas for measures within the FORMULA column. The formula in Figure 6 takes the value you load, rounds it to two places, and then loads the value into the target.

Figure 6

Formulas for measures

In this way business users have flexibility in transforming data without the complexity of having to create BW transfer rules, update rules, transformation groups, or data transfer processes (DTPs). All this configuration is available via the BPC Excel client, so business users can have full control of the dataset they need to work with.

Schedule and Run Packages

After you define the Data Manager packages, transformation files, and conversion files, end users can use these packages to load data into their applications. To schedule or run packages, follow menu path Manage Data > Run a Data Management Package. From the Run Package dialog box, select a team from the left (if desired), select a Package Group folder, and then select a package from the list on the right. Click the Run button at the bottom of the dialog box.

The screens for each package differ depending on the chosen package. Each package allows you to set options for running the package, scheduling the package, or both. End users typically follow the instructions on the screen because the delivered Data Manager packages all have instructions on what needs to be filled in.

Instead of just executing the package, end users can schedule a package to run periodically (Figure 7). Users who may want to refresh their data in SAP BusinessObjects Planning and Consolidation from a source system such as SAP ERP, may want this update to occur on a daily or weekly basis. This is the reason to schedule a job periodically. However, if they are just altering data in a flat file and want to post offsets to the data, they would just execute the package once instead of scheduling it as a recurring job. To access the screen in Figure 7, select the Schedule option for the Data Manager package.

Figure 7

Schedule a Data Manager package periodically

After supplying the required information, the package is sent to the server and executed. You can view the status of the package by clicking the View Status button. Right-click the package and select Detail. Here you can view the details such as the time it took to complete the job as well as the reason for any errors or rejected records (Figure 8). If any errors occur, you receive an error message that describes the cause of the issue. Usually it is the result of incorrect transformation or conversion files that you need to correct.

Figure 8

Data Manager log example

When using Data Manager packages, the trick is to understand and define which packages should be granted to which users. It is best to spend some time identifying the business functions different users execute. This way, you can efficiently define the Data Manager packages.

Ryan Leask

Ryan Leask currently runs the SAP BusinessObjects Planning and Consolidation solution management team for SAP, based out of Palo Alto, CA. Prior to this position, he led the EPM solution architecture team with a main focus on the design of SAP BusinessObjects Planning and Consolidation 7.0, version for SAP NetWeaver. Ryan has also worked on SAP xApp Analytics, SAP NetWeaver Visual Composer, SAP NetWeaver BW, SAP SEM, ABAP, SAP CRM, analytics/data mining, and whatever else seemed interesting. He has also co-authored SAP xApp Analytics (SAP PRESS, 2006), written many articles, and presented at numerous conferences.

You may contact the author at ryan.leask@sap.com.

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

Prakash Darji

Prakash Darji is an experienced professional with more than 10 years of end-to-end experience in enterprise software. He has a broad depth of experience including corporate strategy, sales, product management, architecture, and development. He has experience in product launch activities, including positioning, packaging, and pricing. He has delivered numerous product releases in a variety of capacities through his career. He thrives on building high-performing, scalable teams to achieve strategic deliverables, whether they close strategic sales deals, roll in product features, or roll out new releases. He is a recurring author for several publications and a speaker at SAP conferences around the world. Prakash is on LinkedIn at https://www.linkedin.com/in/prakashdarji.

You may contact the author at editor@BIexpertOnline.com.

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