BW BI Transformation

Use

The transformation process allows you to consolidate, cleanse, and integrate data. You can semantically synchronize data from heterogeneous sources.

When you load data from one BI object into a further BI object, the data is passed through a transformation. A transformation converts the fields of the source into the format of the target.

Features

You create a transformation between a source and a target. The BI objects DataSource, InfoSource, DataStore object, InfoCube, InfoObject and InfoSet serve as source objects. The BI objects InfoSource, InfoObject, DataStore object and InfoCube serve as target objects.

The following figure illustrates how the transformation is integrated in the dataflow:

A transformation consists of at least one transformation rule. Various rule types, transformation types, and routine types are available. These allow you to create very simple to highly complex transformations:

● Transformation rules: Transformation rules map any number of source fields to at least one target field. You can use different rules types for this.

● Rule type: A rule type is a specific operation that is applied to the relevant fields using a transformation rule.

For more information, see Rule Type.

● Transformation type: The transformation type determines how data is written into the fields of the target.

For more information, see Aggregation Type.

● Rule group: A rule group is a group of transformation rules. Rule groups allow you to combine various rules.

For more information, see Rule Group.

● Routine: You use routines to implement complex transformation rules yourself. Routines are available as a rule type. There are also routine types that you can use to implement additional transformations.

For more information, see Routines in the Transformation.

BW BI Transformation Rule Type

Use

The rule type determines whether and how a characteristic or key figure, or a data field or key field is updated into the target.

Features

The following options are available:

Direct Assignment:

The field is filled directly from the selected source InfoObject. If the system does not propose a source InfoObject, you can assign a source InfoObject of the same type (amount, number, integer, quantity, float, time) or you can create a routine.

If you assign a source InfoObject to a target InfoObject that has the same type but a different currency, you have to translate the source

The Transformation Library and Formula Builder BW BI Transformation Rule Type

Use

A transformation library is available in the maintenance for transformation rules (and in the update rules). You can use this in connection with the formula builder.

Do not use formulas for VirtualProviders because inversion is not allowed for them. Use routines in this case.

Features

The transformation library, in collaboration with the formula builder, enables you to easily create formulas, without using ABAP coding.

The transformation library has over 70 pre-defined functions, in the following categories:

· Functions for character strings

· Date functions

· Basic functions

· Mathematical functions

· Suitable functions

· Miscellaneous functions

In the dialog box to select an update method, you can use the information pushbutton to get a list of the available functions with a description of their syntax.

You also have the option to implement self-defined functions in the transformation library of the formula builder. You can integrate existing function modules in these self-defined functions. In doing so, you can also make available special functions to be used frequently that are not contained in the transformation library. Refer to BAdI: Customer-defined Functions in the Formula Builder.

The formula builder has two modes: Standard and expert mode. In the standard mode, you can only enter the formulas using the pushbuttons and by double clicking on functions and fields. In the expert mode, however, you can enter formulas directly. You can also toggle between the tow modes when entering a formula.

You can find more detailed operating instructions for the formula builder by means of the information button .

You can find a step-by-step guide using an example under Example for Using the Formula Builder.

Example for Using the Formula Editor BW BI Transformation Rule Type

The company code field (0COMP_CODE) is not included in your data target or InfoSource. However you can determine the company code from the first four character spaces of the cost center (0COSTCENTER).

You create the following formula for this purpose:

SUBSTRING( cost center, '0' , '4')

Syntax:

SUBSTRING( String, Offset , Länge )

Step-by-Step Procedure in Standard mode:

1. In the transformation library, on the right hand side under Show Me, choose the category Strings. From the list, select the Substring function by double-clicking on it. The syntax of the formula is displayed in the formula window:
SUBSTRING( , , )

The cursor automatically appears over the first parameter that needs to be specified.

2. From the list on the left-hand side of the screen, choose the Cost Center field by double-clicking on it.
3. Place the cursor where you want to enter the next parameter.
4. Enter the number 0 using the Constant button (for the Offset parameter). The commas are added automatically.
5. Place the cursor where you want to enter the next parameter.
6. Enter the number 4 using the Constant button (for the Length parameter).
7. Choose Back. The formula is now checked and saved if it is correct. You receive a message if errors occurred during the check, and the system highlights the erroneous element in color.

BAdI: Customer-Defined Functions in the Formula Builder BW BI Transformation Rule Type

Use

You can integrate self-defined functions in the transformation library of the formula builder. In doing so, you can also make special functions available for frequent use that are not contained in the transformation library. The Business Add-In RSAR_CONNECTOR is available for this. For more information about using Business Add-Ins (BAdIs), see Classic BAdls.

Procedure

Implementing the BAdI

...

To implement a BAdI, see Implementing a Classic BAdl. The specific special features for the implementation of the BAdI RSAR_CONNECTOR are described below.

1. Call transaction SE19. Enter RSAR_CONNECTOR as the name for the Add-In, for which you want to create the implementation.

By double-clicking the method (GET), the class builder appears. You can enter your coding for implementing the enhancement. For more information, see Structure of Implementation of a Functionand Implementation of a Category.

A function does not have a type. Therefore, the TYPE field of structure SFBEOPRND cannot be filled.

2. Save and activate your implementation.

Naming Conventions

The technical name of a self-defined function:

● cannot be empty

● must be consistent

● must begin with ‘C_’

● can only contain alphanumeric characters, that is, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_' (small letters, blank spaces and special characters are not allowed)

● can have a maximum of 61 characters

Implementing the Methods

The ABAP methods specified in the function description under class and method are called later in maintenance and formula evaluation. Therefore, the customer-defined functions must additionally be implemented as methods for BAdI implementation in an additional class. These methods must have the following properties:

● They are declared static and public.

● They can only have importing, exporting, and returning parameters. Changing parameters are not permitted.

● They can only have one exporting or returning parameter.

● Exporting parameters cannot have a generic type.

In the methods, you can use ABAP code to implement the function.

The system does not check whether the class or method specified in BAdI implementation actually exists. If a class or method does not exist, a runtime error occurs when the function is used in the formula builder.

Result

The functions you have defined are available in the transformation library in the Customer-Defined Functions selection.

Structure of Implementation of a Function BW BI Transformation Rule Type

The following table explains the structure of implementation of a function:

Coding Lines	Description
method IF_EX_RSAR_CONNECTOR~GET .
data: l_function type SFBEOPRND.	Structure with the description of the function
case i_key.	Importing parameter: key with the function category
when 'CUSTOM'.	The BAdI implementation is always accessed with the ‘CUSTOM’ key.
* description of function C_TECH_NAME 1
clear l_function.
l_function-tech_name = 'C_TECH_NAME1'.	Appears later in the Technical Name column and must be unique.
l_function-descriptn = 'description 1'.	Appears later in the Description column.
l_function-class = 'CL_CUSTOM_FUNCTIONS'.	Name of the class in which the function is implemented.
l_function-method = 'CUSTOMER_FUNCTION1'.	Name of the method in which the function is implemented.
APPEND l_function TO c_operands.	Changing parameter: table with descriptions of the function
* ... further descriptions
endcase.
endmethod.

Implementation of a Category BW BI Transformation Rule Type

You can implement your own categories and group your own functions under them.

Add the following code in the BAdl implementation:

Coding Lines	Description
data: l_s_operand TYPE SFBEOPRND.
if i_key = SPACE.
l_s_operand-descriptn = .	Description of category
l_s_operand-tech_name = .	Name of category in uppercase letters
APPEND l_function TO c_operands.
exit.
endif.

To group functions in this category, add the following code to the BAdl implementation:

Coding Lines	Description
if i_key = .
l_s_operand-descriptn = .	Description of function
l_s_operand-tech_name = .	Name of function in uppercase letters
l_s_operand-tech_name = .	Name of class that implements BAdl
l_s_operand-tech_name = .	Name of method that implements BAdl
APPEND l_function TO c_operands.
exit.
endif.

Aggregation Type BW BI Transformation Rule Type

Use

You use the aggregation type to control how a key figure or data field is updated to the InfoProvider.

Features

For InfoCubes:

Depending on the aggregation type you specified in key figure maintenance for this key figure, you have the options Summation, or Maximum or Minimum. If you choose one of these options, new values are updated to the InfoCube.

The aggregation type (summation, minimum & maximum) specifies how key figures are updated if the primary keys are the same. For new values, either the total, the minimum, or the maximum for these values is formed.

For InfoObjects:

Only the Overwrite option is available. With this option, new values are updated to the InfoObject.

For DataStore Objects:

Depending on the type of data and the DataSource, you have the options Summation, Minimum, Maximum or Overwrite. When you choose one of these options, new values are updated to the DataStore object.

For numerical data fields, the system uses characteristic 0RECORDMODE to propose an update type. If only the after-image is delivered, the system proposes Overwrite. However, it may be useful to change this: For example, the counter data field “# Changes” is filled with a constant 1, but still has to be updated (using addition), even though an after-image only is delivered.

The characteristic 0RECORDMODE is used to pass DataSource indicators (from SAP systems) to the update.

If you are not loading delta requests to the DataStore object, or are only loading from file DataSources, you do not need the characteristic 0RECORDMODE.

Summation:

Summation is possible if the DataSource is enabled for an additive delta. Summation is not supported for data types CHAR, DAT, TIMS, CUKY or UNIT.

Overwrite:

Overwrite is possible if the DataSource is delta enabled.

When the system updates data, it does so in the chronological order of the data packages and requests. It is your responsibility to ensure the logical order of the update. This means, for example, that orders must be requested before deliveries, otherwise incorrect results may be produced when you overwrite the data. When you update, requests have to be serialized.

Example

You are loading data to a DataStore object. In this example, the order quantity changes after the data is loaded into the BI system. With the second load process, the data is overwritten because it has the same primary key.

First Load Process

Document No.	Document Item	Order Quantity	Unit of Measure
100001	10	200	Pieces
100001	20	150	Pieces
100002	10	250	kg

Second Load Process

Document No.	Document Item	Order Quantity	Unit of Measure
100001	10	180	Pieces
100001	20	165	Pieces

Rule Group BW BI Transformation Rule Type

Use

A rule group is a group of transformation rules. It contains one transformation rule for each key field of the target. A transformation can contain multiple rule groups.

Rule groups allow you to combine various rules. This means that for a characteristic, you can create different rules for different key figures.

Features

Each transformation initially contains a standard group. Besides this standard group, you can create additional rule groups.

If you have defined a new rule in rule details, you can specify whether this rule is to be used as a reference rule for other rule groups. If it is used as a reference rule, then this rule is also used in existing rule groups as a reference rule where no other rule has been defined.

Example

The source contains three date characteristics:

● Order date

● Delivery date

● Invoice date

The target only contains one general date characteristic. Depending on the key figure, this is filled from the different date characteristics in the source.

Create three rule groups which, depending on the key figure, update the order date, delivery date, or invoice date to the target.

BW BI Transformation Creating Transformations

Procedure

You are in the Modeling functional area of the Data Warehousing Workbench.

Choose Save.

1. In the InfoProvider tree, choose Create Transformationin the context menu for your InfoProvider.

2. Select a source for your transformation and choose Create Transformation.

3. The system proposes a transformation. You can use this transformation as it is, or modify it to suit your requirements. To the left of the screen, you see the source; to the right of the screen, you see the rule group. To show the target as well, choose Switch Detail View On/Off.

For InfoCubes with non-cumulative key figures, you cannot change the transformation suggested by the system. These transformation rules fill the time reference characteristic of the InfoCube. All other time characteristics are automatically derived from the time-reference characteristic.

4. Using the mouse, you can create new connecting arrows or change existing connecting arrows; you can delete a connecting arrow by choosing the relevant entry in the context menu for the arrow.

5. You can activate the check for referential integrity in the rule group for single characteristics.

The check for referential integrity determines the valid values of the characteristic. It is checked against the master data table (meaning the SID table) or against a DataStore object that is specified in the InfoObject maintenance for this characteristic.

6. If you double-click an InfoObject in the transformation group, the maintenance screen for the rule details is displayed. You can also

○ Select a rule type.

More information: Rule Type

○ Activate the conversion routine, if it exists. It is deactivated by default because the system assumes that the DataSource provides the internal format.

More information: Conversion Routines in BI Systems.

○ With key figures, you can specify a transformation type and define a currency translation or quantity conversion. More information:

■ Currency Translation During Transformation

■ Quantity Conversion During Transformation

○ Using the InfoObject Assignment field for a source field, you can assign an InfoObject to a DataSource from which the data is to be read. This is required to read master data and for currency translations and quantity conversions. More information:

■ Assigning InfoObjects for Reading Master Data

■ Assigning InfoObjects for Converting Amounts or Currencies

■ Assigning InfoObjects for Time Conversion

○ With Test Rule, you can check whether source values are updated to the target (for example, for analyzing errors in complex routines).
See also: Testing Rules

7. To create additional rule groups, choose Rule Group ® New Rule Group.

8. To create the corresponding routines for your transformation, choose Start Routine and End Routine.

See also: Routines in Transformations

9. With Extras

Assigning InfoObjects for Reading Master Data BW BI Transformation Creating Transformations

An InfoObject has to be assigned to a source field of a DataSource if master data needs to be read.

1.

With SAP NetWeaver 7.0 SPS 14, you can update navigation attributes of InfoCubes and DataStore objects instead of reading master data.

2.

With SAP NetWeaver 7.0 SPS14 the performance when reading master data was optimized. With the new procedure, the master data is no longer read for each key in the data package with a SELECT command. Instead, all the master data of the data package is stored temporarily (prefetch service) and further processed from the temporary store. This reduces the number of database accesses and improves the performance when reading master data. The new procedure is set by default. You can switch back to the old procedure in the program SAP_RSADMIN_MAINTAIN (transaction SE38).
For more information, see SAP Note 1092539.

Procedure

...

1. You are in the rule details. Select the rule type Time Update or Read Master Data.

2. Select an InfoObject in the InfoObject Assignment field in the Source Fields of Rule area.

3. Fill out the field From Attrib. of.

4. If the InfoObject is time-dependent, you usually have to add a time characteristic before you can specify the period. This must be an SAP time characteristic (0CALDAY, 0CALWEEK, 0CALMONTH, 0CALQUARTER, 0CALYEAR, 0FISCYEAR, 0FISCPER). To do so, choose Add Source Fields ( ) in the Source Fields of Rule area and then select a time characteristic.

5. Determine the time at which the master data needs to be read - on the current date (sy-date), on a constant date that you enter directly, or at the beginning or end of a period (determined by the time characteristic). To do so, choose Key Date Determination.

6. Choose Transfer Values.

Example

In your transformation, you want to assign the CUSTOMER field of the DataSource to the 0COUNTRY InfoObject in your target. To do so, you assign the 0CUSTOMER InfoObject to the CUSTOMER field in the rule details so that the attribute 0COUNTRY in 0CUSTOMER can be read.

To be able to specify time dependence for reading master data, assign the CALDAY field to the 0COUNTRY rule as an additional input field. The CALDAY field of the DataSource also needs an assigned InfoObject. Assign 0CALDAY to it so that 0CALDAY's properties can be read. Then enter a time.

Assigning InfoObjects for Converting Amounts or Currencies BW BI Transformation Creating Transformations

Use

An InfoObject has to be assigned to a source field of a DataSource if currencies or units of measure need to be converted. The translation types require InfoObjects as input fields to carry out a conversion. You therefore have to assign the appropriate InfoObject to the source field.

If the target has a fixed unit, you only have to assign an InfoObject if you want to carry out a conversion. Otherwise, you can select the No Conversion option.

Procedure

...

1. You are in the rule details. Select the rule type Direct Assignment.

2. Select an appropriate key figure in the InfoObject Assignment field in the Source Fields of Rule area.

3. Choose the required conversion in the Currency field.

4. Choose Transfer Values.

Example

In your transformation, you want to assign the AMOUNT field of the DataSource to the FIX_EUR InfoObject in your target, carrying out a currency conversion. To do so, you assign the FIX_EUR InfoObject to the AMOUNT field in the rule details so that the currency can be read from the InfoObject.

Assigning InfoObjects for Time Conversion BW BI Transformation Creating Transformations

Use

An InfoObject has to be assigned to a source field of a DataSource if time conversion is required. It is required if the granularity of the source field is different from the granularity of the target field. Assigning a time characteristic allows the properties of the time characteristic to be adopted.

If you do not assign a time characteristic, a direct update takes place. The value is assigned directly and is truncated if necessary. Source fields of the DDIC type DATS are an exception. The system handles these fields as if a time characteristic were assigned.

Procedure

...

1. You are in the rule details. Select the rule type Time Update.

2. Select a time characteristic in the InfoObject Assignment field in the Source Fields of Rule area.

3. Choose Transfer Values.

Example

In your transformation, you want to assign the CALDAY field of the DataSource to the 0FISCYEAR InfoObject in your target. To do so, you assign the 0CALDAY InfoObject to the CALDAY field in the rule details so that the properties of the InfoObject can be read.

Copying Transformations BW BI Transformation Creating Transformations

Use

You can create a transformation as a copy of an existing transformation. You can then adjust the copy to suit your requirements.

You are recommended to copy a transformation in the following cases:

● Same source, similar target (e.g. for reuse in complex routines)

● Similar source, same target

● Similar source, similar target

Prerequisites

Make sure that the source and the target of the transformation are active. Create any InfoObjects you require that do not already exist and activate them.

Procedure

...

1. You are in the InfoProvider tree.

2. Select the transformation that you want to use as the template for a new transformation.

3. In the context menu, choose Copy.
A dialog box specifying the source and target of the selected transformation opens.

4. Change your entries to suit your requirements. You can select any target and source with the following exceptions:

○ If the target of the selected transformation is an open hub destination, the new target must also be an open hub destination.

○ If the source of the selected transformation is a DataSource, the new source must also be a DataSource.

5. Choose Create Transformation.

6. The system proposes a transformation. You can use this transformation as it is, or modify it to suit your requirements.
More information: Creating Transformationss.

7. Activate your transformation.

Result

The transformation was created as a copy and is active. The copy has no link to the original; that is changes to the original have no effect on the copy and vice versa.

Error Analysis in Transformations BW BI Transformation Creating Transformations

You can perform an error analysis in the transformation in the following ways:

● You can check whether the source values are updated in the target using the single rule test. More information: Testing Ruless.

● With the function Simulate and Debug DTP Requests, you can simulate a transformation prior to the actual data transfer to check whether it returns the desired results. You can set breakpoints at the following points in time during processing: before the transformation, after the transformation, after the start routine and before the end routine.
More information: Simulating and Debugging DTP Requestss.

Testing Rules BW BI Transformation Creating Transformations

Use

With the single rule test, you can check whether source values are updated to the target (for example, for analyzing errors in complex routines).

Rules for which a time characteristic with time distribution is updated cannot be tested.

Procedure

...

1. The rule details screen for the rules that you want to test is displayed.

2. Choose Test Rule.

3. Enter the required data in the next dialog box and choose Check Entries.
The validity of the data is checked.

With pushbutton Display Technical Names you can display the technical names instead of the descriptions of the source and target fields.

4. Choose Execute.

Result

The values that are written in the target are displayed.

The runtime of the test is specified in milliseconds (ms).

Showing posts with label BI Transformation. Show all posts Routines in Transformations BW BI Transformation Creating Transformations

Use

You use routines to define complex transformation rules.

Routines are local ABAP classes that consist of a predefined definition area and an implementation area. The TYPES for the inbound and outbound parameters and the signature of the routine (ABAP method) are stored in the definition area. The actual routine is created in the implementation area. ABAP object statements are available in the coding of the routine. Upon generation, the coding is embedded in the local class of the transformation program as the method.

The following graphic shows the position of these routines in the data flow:

Features

The routine has a global part and a local part. In the global part you define global data declarations 'CLASS DATA'. These are available in all routines.

You can create function modules, methods or external subprograms in the ABAP Workbench if you want to reuse source code in routines. You can call these in the local part of the routine. If you want to transport a routine that includes calls of this type, the routine and the object called should be included in the same transport request.

Transformations include different types of routine: Start routines, routines for key figures or characteristics, end routines and expert routines.

The following figure shows the structure of the transformation program with transformation rules, start routine and end routine:

The following figure shows the structure of the transformation program with expert routine:

Start Routine

The start routine is run for each data package at the start of the transformation. The start routine has a table in the format of the source structure as input and output parameters. It is used to perform preliminary calculations and store these in a global data structure or in a table. This structure or table can be accessed from other routines. You can modify or delete data in the data package.

Routine for Key Figures or Characteristics

This routine is available as a rule type; you can define the routine as a transformation rule for a key figure or a characteristic. The input and output values depend on the selected field in the transformation rule. More information: the Routine section under Rule Type.

End Routine

An end routine is a routine with a table in the target structure format as input and output parameters. You can use an end routine to postprocess data after transformation on a package-by-package basis. For example, you can delete records that are not to be updated, or perform data checks.

If the target of the transformation is a DataStore object, key figures are updated by default with the aggregation behavior Overwrite (MOVE). You have to use a dummy rule to override this.

Expert Routine

This type of routine is only intended for use in special cases. You can use the expert routine if there are not sufficient functions to perform a transformation. The expert routine should be used as an interim solution until the necessary functions are available in the standard routine.

You can use this to program the transformation yourself without using the available rule types. You must implement the message transfer to the monitor yourself.

If you have already created transformation rules, the system deletes them once you have created an expert routine.

If the target of the transformation is a DataStore object, key figures are updated by default with the aggregation behavior Overwrite (MOVE).

More Information:

Example: Start Routine

Example: Characteristic Routines

Example: End Routine