Nextlevel-Icon

Helfen Sie uns ein Level weiter. Nehmen Sie an unserer Umfrage teil.

Flatfile upload with SAP BPC EPM Add-In und possible alternatives

Sebastian

Written By: Sebastian - 31 March 2022
(updated on: 22 September 2022)

SAP Business Planning and Consolidation is geared towards business users. With the EPM Add-In for Excel, it offers users an easy way to upload data via flat files. In this article, we examine the possibilities and discuss possible alternatives.

The EPM add-in allows both master data and transaction data to be uploaded via Excel interface. This makes it relatively easy for business users to implement ad-hoc planning scenarios on their own. The upload takes place in several steps, which I will present below.

00-panel_SAP BPC

 

Upload to server

Before the data can be processed as master data or transaction data, the file must first be uploaded to the server. This is also done by the EPM add-in, so it can be done without the involvement of the IT department.

01-file-upload_SAP BPC

A preview of the data is possible, whereby the separator can be flexibly selected. In this way, any errors can already be detected and corrected in the first step.  However, the system does not carry out a consistency check in this step, e.g. against existing master data.

02-preview_SAP BPC

After the data has been uploaded to the server, it is loaded into the actual InfoProvider using a so-called data package. BPC works with transformation and conversion files, which contain the parameters for the upload and conversion routines. In the next section, I will briefly introduce this mechanism.

Transformation file

The transformation file allows you to set rules for reading data from the flat file and transform it into the correct format for your system database.

05-transformation-files_SAP BPC

It is a simple Excel file with three sections: Options, Mapping and Conversion. "Options" define the format of the file such as delimiters, header or number format.  Via "Mapping" you can determine the assignment of the source columns to the BPC dimensions. "Conversion" defines the conversion routines for specific dimensions.

06-transformation-file-example_SAP BPC

In addition to the explicit assignment of the columns to dimensions, the assignment can also be made via the header of the file. Nevertheless, it is a relatively rigid concept. If, for example, the separator changes, the transformation file must be adjusted.

Conversion file

Just like transformation files, conversion files are also simple Excel documents. They offer even inexperienced users the possibility to implement their own conversion logic. The conversion transfers the data from the external to the internal format.

07-conversion-files_SAP BPC

Different approaches are possible. On the one hand, you can make one-to-one conversions, such as saving version 010 as ACTUAL. In this case, you must create a dedicated rule for each case.

External

Internal

010

ACTUAL

 

On the other hand, you can also convert all records based on a specific pattern. For example, you can add the prefix CC_ for cost centers or leading zeros.

External

Internal

*

CC_*

*

000*

 

You can use a similar approach to convert the date JAN to 01 for January. This applies to all years that are represented as the first four characters.

External

Internal

????.JAN

????.01

 

In the conversion file you can also use formulas, e.g. for markups. For example, the formula Amount*1.20 would increase the value by 20%. You can also use this function to perform simple currency conversions.

As you can see, the syntax is relatively simple and can also be used by the business users. But the conversion files are rather unsuitable for complicated and flexible logic. The conversion logics are hard-coded and require constant adjustment.

Upload to the InfoProvider via Data Package

After the file has been uploaded to the server and transformation and conversion files have been maintained, the actual upload can begin. In Business Planning and Consolidation the upload takes place via so-called Data Packages. These can be started via the EPM Add-In in the Data Manager Group.

data_manager_group_SAP BPC

The packages are technically mapped as BW process chains and can be used to upload master data and transaction data.

data-packages_SAP BPC

After the package has been started, relevant options are defined in various steps: the file to be uploaded is selected and the corresponding transformation file is determined. In addition, the user can define the update method, the handling of duplicate records, the application of the standard logic after import as well as the check of the work status. In total, six windows with selection options are displayed before the actual upload begins. This can easily overwhelm the user.

10-data-packages_SAP BPC


Master data maintenance in SAP - Five tools in comparison

Master data maintenance NextTables


Status

After the Data Package has been started, the user can view the status and thus check whether the upload was successful. This is also done via the EPM add-in in the familiar Excel interface. No system logs need to be evaluated.

11-data-packages_SAP BPC

If the upload was not successful, the user can view the details of the loading process. Detailed information is displayed there.

12-log_SAP BPC

In this way, the user can see which error occurred in which dimension.

13-rejected-list

The incorrect records can also be displayed, which undoubtedly helps with troubleshooting.

13-rejected-records_SAP BPC

 

NextTables as alternative

NextTables is a simple solution for editing and maintaining SAP tables. The add-on offers an intuitive, user-friendly interface and is characterized by straightforward user guidance. Particularly noteworthy is the ability to upload data to the system with just a few clicks. The upload can be done either via drag & drop or by copying data from the clipboard.

14-import_SAP BPC

The import settings, such as decimal separators and thousands separators, can be stored in the user profile. If required, the settings can also be adjusted on the fly. Compared to the EPM Add In, the transformation file does not have to be laboriously adjusted. The provided import templates make the process even easier.

15-options

During the upload, the status display provides a real-time overview of the current import process.

16-status-import_SAP BPC

After the upload, the user is provided with a summary of the steps including validation results. These can be exported to Excel at the push of a button. Particularly noteworthy is the possibility to store customer-specific validation checks via BAdI (Business Add-In).

17-import-overview_SAP BPC

The user gets an overview of all warnings and errors at a glance.

18-ergebnisse

Moreover, immediate correction of the errors is possible. The user does not have to correct the source file first and then click through all EPM windows again. This significantly speeds up the data import.

19-korrektur_SAP BPC

Moreover, frequently occurring errors can be corrected immediately. Manual correction by the user is not necessary. For example, currency symbols are automatically removed from amount fields. However, these corrections can also be customized per table and field to automatically clean up the usual suspects.

The executed changes are transparent to the user. The user is notified about the adjustments and can either confirm them or overwrite them manually.

import-nextlytics_SAP BPC

Last but not least, the flexible sequence of characteristics and key figures should be mentioned. In this way, errors can be avoided. Error messages caused by incorrect file structure are thus a thing of the past.

In addition, NextTables offers the following advantages:

  • variable assignment of InfoObjects to columns
  • Import from files (Excel and CSV) as well as from clipboard with immediate validation report
  • Out of the box import corrections (e.g. currency symbols are automatically removed from amount fields)
  • Various validation options included, more can be added table-specifically
  • Warning for records with the same key
  • Import dialog as standalone display or integrated into any table display
  • Formatting-independent through automatic column recognition via column header
  • Excel template for imports can be downloaded

Tool comparison

As you have seen, the handling of the EPM Add Ins is relatively simple. Useful is also the possibility to implement conversion logic with Excel. However, the syntax is somewhat cryptic and takes some getting used to. The menu windows also seem antiquated these days.

NextTables, on the other hand, offers a user-friendly state of the art interface. While the BPC EPM add-in only works with Excel, NextTables uses a platform independent web interface.

In contrast to NextTables, where the settings such as separator and decimal notation can be flexibly changed on the fly, the separator in the EPM add-in is defined via the transformation file and must be adjusted in the Excel file.

Although conversions via the conversion file are relatively simple, they are rather unsuitable for complicated and flexible logic. Many conversions are simply hard coded. NextTables, on the other hand, can be enriched with BAdIs and offers almost unlimited possibilities. For example, you can look up master data and perform currency conversions with daily rates.

Tabelle_SAP BPC

BPC Standard utilizes separate InfoProviders that use their own namespace and are not fully integrated with SAP Business Warehouse (BW). NextTables, on the other hand, offers seamless integration into SAP BW. All InfoObjects, InfoProvider types and tables are supported.

Compared to EPM Status, which only shows where the error occurred, NextTables also offers the possibility not only to display the errors, but also to correct them immediately. Automatic correction of frequently occurring errors is also possible. With the EPM Add-In, on the other hand, the file must be corrected first and then all steps must be gone through again.

Future viability also plays an important role. The SAP BPC EPM add-in will not be developed further in the future, but will go into maintenance. SAP's focus is on BPC Embedded. Unfortunately, BPC Embedded does not offer this upload functionality. Although files can be uploaded with Analysis for Office via a planning function, the option for conversion routines and status display is missing. NextTables, on the other hand, is continuously being developed and expanded with new functions.

So if a smooth upload is important to you and you want to offer your department a simple and freely configurable solution, NextTables can be an alternative for you.

Summary

As you can see, NextTables is very suitable for data imports into SAP BW. With a holistic approach, you can cover a range of scenarios - from master data maintenance, transaction data uploads to simpler planning applications. At the same time, this platform-independent solution is continuously improved and thus offers a secure investment for the future.

Would you like to learn more about NextTables (nexttables version 9)? Request your personal test version now, free of charge and without obligation. If you have any questions, please do not hesitate to contact us.

Learn all about smart data maintenance with NextTables

Topics: SAP BW/4HANA, Master Data & Data Import in SAP BW

Share article