Transformation Rules

Transformation rules for SAP BI

Applies to: SAP BI NetWeaver 2004s. For more information, visit the Business Intelligence homepage. homepage .

Summary This article discusses about transformation and the various transformation rules Author(s):

Kiran Gasmar

Company: Applexus Software Solutions Created on: 24 December 2010

Author Bio Kiran Gasmar is working as SAP BI Consultant with Applexus Software Solutions (P) Ltd. He has experience in SAP ABAP and SAP BI.

SAP COMMUNITY NETWORK © 2010 SAP AG

SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com 1

Transformation rules

Table of Contents Applies to:........................................................................................................................................................ . 1 SAP BI NetWeaver 2004s. For more information, visit the Business Intelligence homepage........................... 1 Summary........................................................................................................................................................... 1 Author Bio........................................................................................................................................................ . 1 Table of Contents.............................................................................................................................................. 2 Transformations............................................................................................................................................ 3 Transformation rules................................................................................................................................. .... 3 How to define transformation rules................................................................................................................ 4 Direct Assignment...................................................................................................................................... 4 Constants................................................................................................................................................... 5 Reading Master Data................................................................................................................................. 5 Routines..................................................................................................................................................... 6 Formula...................................................................................................................................................... 7 Initial................................................................................................................................................... ....... 8 Related Content................................................................................................................................................ 9 Copyright......................................................................................................................................................... 10




Transformations

Transformation is all about mapping fields from source systems to destination databases. Sometimes, the mapping is one-to-one i.e you have a field in the source table that maps directly to the destination. It is also possible that the fields are not matched up correctly. For example, length of the fields might be different in both or the way figures are calculated might also be different. A good example is VAT. The amount of value-added tax charged differs from country to country and this needs to be transformed (recalculated). Transformation rules are used to perform these operations.


Normally, when mapping fields on the InfoSource to the fields in the InfoCubes,the mapping was always one to one. In real time, this is not always the case. Some InfoObjects are not present in the InfoSource fields but have to be calculated. These calculations are done through transformations. There are 6 different types of tranformations. • • • • • •

Direct assignment Constants Reading master data Routines Formula Initial




How to define transformation rules

Right click on the field that needs rules and click on rule details

Select the rule type needed and give description for the rule Direct Assignment

This will copy the value in the source field directly into the target field. For example the customer number, material number will be transformed using direct assignment. By default all the




transformations are in the direct assignment rule. 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 currency into the target currency using a currency translation, or apply the source currency. If you assign a source InfoObject to a target InfoObject that has the same type but a different unit of measure, you have to convert the source unit of measure into the target unit of measure using a unit of measure conversion, or apply the unit of measure from the source. Constants

This transformation rule will fill the target field with a specified value. For example, the import data field can be filled with today’s date. The field is not filled by the InfoObject; it is filled directly with the value specified. All the records will have the same value specified in the constant.

Reading Master Data The InfoObject is updated by reading the master data table of a characteristic that is included in the source with a key and a value and that contains the corresponding InfoObject as an attribute. The attributes and their values are read using the key and are then returned. The Financial Management Area characteristic is included in the target but does not exist in the source as a characteristic. However, the source contains a characteristic (cost center, for example) that has the Financial Management Area characteristic as an attribute. You can read the Financial Management Area attribute from the master data table and use it to fill the Financial Management Area characteristic in the target. It is not possible to read recursively, that is, to read additional attributes for the attribute. To do this, you have to use routines. If you have changed master data, you have to execute the change run. By reading the master data, the active version is read. If this is not available, an error occurs. If the attribute is time dependent,




you also have to define when it should be read: at the current date (sy-date), at the beginning or end of a period (defined by a time characteristic in the InfoSource), or at a constant date that you enter directly. Sy-date is used as the default.

Read customer name from master table from customer number where customer name (WP_CNM) is an attribute of customer number (WP_CNO).

Routines

Routines are special code written to handle complex cases. There are start routines, end routines and expert routine Start routines are used to pre process the data before transformation, for example they can be used to convert the amount to another currency. This makes the process efficient when there a lot of records being imported and a handful will be empty fields. End routines are used to post – process the data on a package-by-package basis. This can be used for integrity checks etc. Routines are mostly written by ABAP consultants. The field is filled by the transformation routine you have written. The system allows you to select whether the routine is to be valid for all attributes for this characteristic, or only for the attributes displayed. Transformation rules generally only have one return value. If you select Return Table, the corresponding key figure routine no longer has a return value; it has a return table. You can then generate any number of values from a data record. For DataStore objects and InfoObjects: you cannot use the return code in the routine for data fields that are updated by being overwritten. If you do not want to update specific records, you can delete these from the start routine. If, for the same characteristic, you gene rate different rules for different




key figures or data fields, a separate data record can be created for each key figure from a data record of the source. With InfoCubes: You can also select Routine w ith Unit. The return parameter 'UNIT' is then also added to the routine. You can store the required unit of the key figure, such as 'ST', in this parameter. You can use this option, for example, to convert the unit KG in the source, into tons in the target. If you fill the target key figure from a transformation routine, currency translation has to be performed using the transformation routine. This means that automatic calculation is not possible.

Formula

The InfoObject is updated with a value determined using a formula. For more information, see Transformation Library and Formula Builder. Major function available in the formula editor are Concatenate, sub string, condense, left/right (n characters),1_trim, r_trim, replace, date routines like DATECONV, date-week, add_to_date, date_diff, logical functions like if, and.




Initial The field is not filled. It remains empty for all the records.




Related Content Transformation Rule Type SAP BI Training : Transformation rules Problem with transformation rules for master data reading




Copyright © Copyright 2010 SAP AG. All rights reserved. No part of this publication may be reproduced or transmitted in an y form or for any purpose without the express permission of SAP AG. The information contained herein may be changed without prior notice. Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors. Microsoft, Windows, Excel, Outlook, and PowerPoint are registered trademarks of Microsoft Corporation. IBM, DB2, DB2 Universal Database, System i, System i5, System p, System p5, System x, System z, System z10, System z9, z10, z9, iSeries, pSeries, xSeries, zSeries, eServer, z/VM, z/OS, i5/OS, S/390, OS/390, OS/400, AS/400, S/390 Parallel Enterprise Server, PowerVM, Power Architecture, POWER6+, POWER6, POWER5+, POWER5, POWER, OpenPower, PowerPC, BatchPipes, BladeCenter, System Storage, GPFS, HACMP, RETAIN, DB2 Connect, RACF, Redbooks, OS/2, Parallel Sysplex, MVS/ESA, AIX, Intelligent Miner, WebSphere, Netfinity, Tivoli and Informix are trademarks or registered trademarks of IBM Corporation. Linux is the registered trademark of Linus Torvalds in the U.S. and other countries. Adobe, the Adobe logo, Acrobat, PostScript, and Reader are either trademarks or registered trademarks of Adobe Systems Incorporated in the United States and/or other countries. Oracle is a registered trademark of Oracle Corporation. UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group. Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame, and MultiWin are trademarks or registered trademarks of Citrix Systems, Inc. HTML, XML, XHTML and W3C are trademarks or registered trademarks of W3C®, World Wide Web Consortium, Massachusetts Institute of Technology. Java is a registered trademark of Sun Microsystems, Inc. JavaScript is a registered trademark of Sun Microsystems, Inc., used under license for technology invented and implemented by Netscape. SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP Business ByDesign, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and other countries. Business Objects and the Business Objects logo, BusinessObjects, Crystal Reports, Crystal Decisions, Web Intelligence, Xcelsius, and other Business Objects products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of Business Objects S.A. in the United States and in other countries. Business Objects is an SAP company. All other product and service names mentioned are the trademarks of their respective companies. Data contained in this document serves informational purposes only. National product specifications may vary. These materials are subject to change without notice. These materials are provided by SAP AG and its affiliated companies ("SAP Group") for informational purposes only, without representation or warranty of any kind, and SAP Group shall not be liable for errors or omissions with respect to the materials. The only warranties for SAP Group products and services are those that are set forth in the express warranty statements accompanying such products and services, if any. Nothing herein should be construed as constituting an additional warranty.



Transformation Rules

Recommend Documents