Academy of Better Business

EasyMAP BI: data warehouse development made easier

Efficient data transformation

For companies using the SAP BW data warehouse BCC has prepared a new product for data transformation  EasyMAP BI. This user-friendly tool combines the advantages of applications based on mapping tables with the best of solutions using the configuration through reports. EasyMAP BI is an effect of the research and development project carried out by BCC with co-financing by the Ministry of Science and Higher Education.

Necessity is the mother of invention

Data transformation issues are dealt with by an administrator of information stored in a data warehouse on an everyday basis. When data is downloaded, information is usually cleaned and processed into the form used by reports. The most common operations include data transformation, derivation of characteristics and assignment of reporting dimensions as a special case of characteristics derivation.

Transformations are usually simple conversions of input data. For example, product numbers from several production plants which use different ERP systems can be standardised for reporting purposes. As a result of transformation a company material number can be changed into a group number used in reporting to ensure enterprise-wide uniqueness.

The derivation of characteristics means supplementing of input data with new information inferred (derived) on the basis of this data. Such derivation can be often easily defined using a table that assigns a combination of values of source characteristics to a new characteristic value. Determination (assignment) of a trader on the basis of a product number and a distribution channel can serve as an example.

Assignment of reporting items is a special case of derivation. In reporting, it is often easier to use aggregated elements called reporting items than to refer to accounts, material numbers or cost centre numbers. Thus, instead of being defining through a combination of many characteristics, a reporting row  is simply specified using a reporting item, e.g. gross margin.

The mechanism of assigning reporting items is a mechanism for derivation of characteristics, except that the number of combinations of characteristics that define one derived item is usually quite large.
 
All three of the data transformation types mentioned above are usually based on the use of tables containing rules for determining target characteristics on the basis of one or more source characteristics. Such tables are usually called mapping tables.

Ad-hoc solutions

In the SAP BW data warehouse, special extensions of the standard are often created to facilitate the maintenance of assignments of source objects to target objects. These extensions enable even variable rules of transformation or derivation to be maintained with little effort. Usually, such extensions can be divided into extensions based on dedicated mapping tables and extensions based on the configuration of assignments using SAP BW reports (queries).

Universally or efficiently

The easiest way to implement the transformation rules is to develop dedicated solutions based on the tables with assignments of source data to target data, the so-called mapping tables, for purposes of a specific data transformation. For such a table, it is necessary to create a user interface that allows for the introduction of changes to the data contained in it and to develop data transformation rules in the ABAP language.


Unfortunately, the preparation of such a solution is labour-intensive, and the results in the form of a user interface and management mechanisms often leave a lot to be desired. For this reason, among others, they are usually used for simple transformations and derivations.

Dedicated solutions based on the configuration through reports can be a way to increase the universality of mapping mechanisms. A key to this solution is the assignment of a report item (an SAP BW query) instead of a combination of source characteristics to target characteristics. This allows for derivation of target characteristics based on various sets of source characteristics (defined through defining report rows). The flexibility of configuration of source characteristics is ensured.

Unfortunately, the use of reports for data processing results in a significant decrease in performance and this solution is not suitable for transformation of large data volumes. In addition, in the case of simple derivations the creation of queries and, additionally, the maintenance of mapping is labour-intensive. This solution is most often used to derive reporting items without details.

It is difficult to combine water and fire  the solution obtained is either hardly universal, but efficient, or its universality is achieved at the expense of efficiency. Every administrator of an extensive SAP BW data warehouse dreams about a flexible solution, which is friendly for a user maintaining mapping rules as well as efficient enough to allow for reporting at the maximum level of detail, and easy as regards access and development management. The ideal would be the possibility to refer in assignments to hierarchical structures of groups and sets of master data frequently defined in the ERP system.

EasyMAP BI – a combination of water and fire

EasyMAP BI is a tool that combines the advantages of solutions based on mapping tables with the advantages of solutions that use the configuration through reports. This in-house developed BCC’s solution has been created in response to repeated inquiries from customers and on the basis of the  knowledge and unique experience gained during several dozen SAP BI projects. It has been developed as part of a research and development project co-financed by the Ministry of Science and Higher Education.

EasyMAP BI is a tool for data transformation in the SAP Business Intelligence system. One of its key features is its universality. A single EasyMAP BI solution can handle many different data processing procedures in the SAP BW data warehouse. Therefore, it is no longer necessary to create a new dedicated solution for a new processing procedure. A simple configuration similar to the configuration of processes in SAP ERP is sufficient to define a new procedure for transformation or derivation of characteristics.

EasyMAP BI configuration screens

The solution has a friendly interface for a user who introduces mapping rules. It has been developed using the best operation interface introduced in SAP GUI, the so-called ALV grid. The ALV grid allows the user to make any adjustments in the layout of the rule maintenance screen (selection of the order and width of columns, hiding of redundant columns, etc.). Importantly, it is possible to copy the data previously prepared in MS Excel to the ALV grid interface and also to export data to MS Excel.

The solution incorporates secure management of authorisations to introduce mapping rules using a supplied authorisation object. This allows for the use of the same transactions by different departments in an enterprise, with separation of access to mapping rules.

EasyMAP BI enables direct access in mapping rules to groups/sets of controlling tools in SAP ERP (cost centre groups, order groups, cost element groups), making it easier to define data processing rules.

Importantly, these groups do not have to be laboriously transferred to SAP BW, since Easy-MAP BI enables a data warehouse to retrieve their definitions directly from SAP ERP. The use of groups already defined and maintained in SAP ERP significantly facilitates building relationships between combinations of characteristics in data mapping tables.

Optimization of the solution performance allows it to be used to process millions of records. Thus, despite its universal character, the solution is very efficient, which is very important in the data world.

EasyMAP BI has a built-in capability to manage data access at the branch/department level. This is especially important in companies where the responsibility for maintaining mapping rules is shared by the company’s branches. Each branch can change only "its" mapping rules, but for the BW system they together form a whole.

Each of the mentioned features of the solution is important, however only the combination of them all determines the high usability of the solution, which can significantly facilitate the development of SAP BI systems.

Benefits of the solution

EasyMAP BI can be used in many ways in every complex SAP BW installation. It is especially beneficial for companies that want to collect actual data in aggregated items, e.g. reporting items with the possibility to drill down to detailed information (e.g. an account, a cost centre, a material index).

This tool is also useful where the transfer of data to a data warehouse must be preceded by its cleaning or transformation using rules that can be stored in mapping tables (e.g. standardisation of material in-dex identifiers from several external systems).

The derivation of additional information when data is transferred to the data warehouse enables Easy-MAP BI to be used also to identify certain dependencies of characteristics based on rules in mapping tables (e.g. for yellow and square products for the current period, derive the "fashionable" characteristic).

Furthermore, it is possible to easily use master data groups of the SAP ERP controlling module (cost centre groups, order groups, cost element groups) in the SAP BW data warehouse.

Immediately available

EasyMAP BI is offered as a package containing both the product and services related to the deployment of the solution, which help get it up and running quickly.
The EasyMAP BI offer includes:

• a perpetual license to use the code for the company’s purposes,
• a guide for the solution administrator,
• a guide for users maintaining mapping rules,
• support for the solution installation,
• a workshop presenting options of the solution configuration and use.