bw workspaces - data cleansing during flat file...

BW Workspaces – Data Cleansing during Flat File Upload


2

TABLE OF CONTENTS

INTRODUCTION INTO THE TOPIC BW WORKSPACE ................................................................................. 3 HISTORY OF THE FILE UPLOAD ................................................................................................................... 3 NEW DATA CLEANSING FUNCTIONALITY ................................................................................................... 3 Transfer File ..................................................................................................................................................... 3 Edit Columns .................................................................................................................................................... 4 Data Cleansing ................................................................................................................................................. 4 Upload Data ...................................................................................................................................................... 5 Reload Data ...................................................................................................................................................... 6

EXAMPLES ....................................................................................................................................................... 7 Simulate Upload without Data Cleansing ...................................................................................................... 7 Apply Data Cleansing ...................................................................................................................................... 8 Upload Data .................................................................................................................................................... 13 Reload Data .................................................................................................................................................... 14 Propose Type ................................................................................................................................................. 15 InfoObject Mapping and Conversion ........................................................................................................... 16


3

With BW 7.40 SP5 Data Cleansing functionality has been implemented for the Upload of Excel or

csv-Files in a BW Workspace. Additionally the error handling has been improved and statistics for

the adjusted data records are provided.

INTRODUCTION INTO THE TOPIC BW WORKSPACE

A BW Workspace is a kind of ‘small sandbox’ which IT defines. IT sets the boundaries, IT sets the amount of resources that a BW Workspace can consume and exposes some of the central data models to the BW Workspace (data of the models and their related semantics). The BW Workspace exposes the central data in a logical fashion only. The data is not copied over into the BW Workspace, which means that no data replication is needed. The goal is to enable the Key Business Users to use this functionality in a dedicated and separated environment, which is deeply embedded and integrated in the existing BW landscape. The BW Workspaces bridge the gap between the architected and the departmental data marts. Therefore, Workspaces are integrated, independent and, as the usage of the SAP Business Warehouse Accelerator or HANA 1.0 running as a database for SAP NetWeaver BW 7.3 is mandatory, they are completely in-memory. HISTORY OF THE FILE UPLOAD

Before BW 7.40 SP5 the BW Workspaces did not provide a comprehensive error handling as well as no Data Cleansing functionality. When a file contained data fields which had not been compatible with the data type of the column, and the checks (e.g. the first, middle, last 500 records of a csv file) could not find these errors, as the wrong fields were contained in the part that has not been checked, the upload stopped with the first error which occurred. The error message reported the row number and the original file had to be adjusted accordingly. Then the upload has been executed again and the user got stuck with the next error and so on.

NEW DATA CLEANSING FUNCTIONALITY

With BW 7.40 SP5 comprehensive Data Cleansing functionalities are available. Transfer File

Remark: The whole file still has to be transferred completely through the network to the ABAP server which cannot be done in parallel processes. This might take some time in case the file is big. Also there might be restrictions on the ABAP server side or HTTP side concerning memory resources.


4

Edit Columns In the ‘Edit Columns’ step, columns can be deselected. Also the data types can be proposed column-based. In this screen the new Data Cleansing functionality has been introduced. In general the upload can be simulated in this step with or without cleansing actions defined. The data is analyzed then and written to the buffer.

The sequence of the cleansing rules can be found in column ‘Order’. Data Cleansing The following options are available:

‘Initialize Conversion Error Values’ means that in case a field of a data record does not contain a value which is compatible with the corresponding data type of the column, the field value is set to its initial value according to the data type of the column. Delete Conversion Error Rows means that in case a field of a data record does not contain a value which is compatible with the corresponding data type of the column, the whole data record containing this field is skipped. This might be chosen as action in case the data record on a whole does not really make sense when the corresponding field has no meaningful value. It is also possible to replace values by other values. Also wildcards can be used when replacing values. The cleansing actions are executed in the sequence listed in column ‘Order’. The sequence can be changed by simply dragging and dropping the whole line up or down.


5

Finally duplicates can be deleted if the checkbox is on in the ‘Set Provider Properties’ tab. A duplicate is a row where all values (characteristics and key figures) are identical. The cleansing actions work columnar-based except for the deletion of duplicates. This operation is always done at the end, after having executed all other cleansing steps and on level of the whole Data Provider. Upload Data The ‘upload data’ process is always a two-part process. A simulation is done implicitly, also when the upload is executed directly without simulation. But when there is no error, the data is uploaded to the Local Provider directly. Otherwise the system will show the statistics with the erroneous data. When a simulation is done and the ‘upload data’ button is pressed afterwards, the data is not analyzed again but the data is taken from the buffer to upload it to the Local Provider. The upload process to the Local Provider has been improved. With BW 7.40 SP5 the upload process is started in asynchronous mode. The default is one asynchronous process. To be able to execute the upload with parallel asynchronous processes the setting ‘Tasks For File Upload’ can be maintained in transaction RSWSP. Here the maximum number of usable tasks can be set.


6

SM50: Executing the upload with 5 parallel dialog processes

Reload Data When a reload with changes is executed, new rules for the added column(s) can be defined and the old ones can be replaced/adjusted. Also the already existing rules are applied for newly added data records.


7

EXAMPLES

Simulate Upload without Data Cleansing

The statistics show that there are conversion errors in columns 0DATE and 0TIME. This could now be corrected by using Data Cleansing operations. Remark: Category ‘Values Cut’ has been introduced because, after a maximum length of 250 characters in a column, the values are cut off.


8

Apply Data Cleansing

By pressing push button ‚add‘, Data Cleansing operations can be chosen. Let us assume value ‘Monday’ in field 0DATE should be replaced by 31.12.2013 and the rows which contain ‘around*’ in field 0TIME should be initialized. Select column and add the Data Cleansing operation


9

Simulate upload


10


11

Further options

You can also replace different characterisic values for one column of the file.


12


13

Upload Data After the upload the statistics are shown

Remark: Initialized Conversion Error Values: 13


14

Reload Data

A new record (which contains the value ‘timeless’ in 0TIME and which will be initialized when Data Cleansing is applied) has been added to the Excel File:

The file is cleansed and uploaded. Remark: Initialized Conversion Error Values: 14


15

Propose Type Propose Type is executed on basis of all values in the selected column with the cleansing settings considered. This allows the user to cleanse cells which e.g. contain STRING values (e.g. N/A or a € symbol is added to the value in each cell due to Excel formatting of cells) whereas most of the columns contain numbers and the column should be used as a key figure column.

Cannot be selected as Key Figure Column

Propose Type applies cleansing. The column can be selected as Key Figure now.


16

A change from character string to key figure is possible with the rule (Replace Value N/A) applied after having pressed ‘Propose Type’. InfoObject Mapping and Conversion With the introduction of the new Data Cleansing features also the mapping functionality of InfoObjects has been enhanced. It is now possible to let the system check if characteristic values are available in the master data tables.


17

“Use conversion rountine” and “master data check” are flagged - simulate:

Uploaded data:


18

Only “master data check” is flagged - simulate:

© 2013 SAP AG. All rights reserved.

SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP

BusinessObjects Explorer, StreamWork, SAP HANA, 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 Software Ltd. Business Objects is an SAP

company.

Sybase and Adaptive Server, iAnywhere, Sybase 365, SQL

Anywhere, and other Sybase products and services mentioned herein

as well as their respective logos are trademarks or registered

trademarks of Sybase Inc. Sybase is an SAP company.

Crossgate, m@gic EDDY, B2B 360°, and B2B 360° Services are

registered trademarks of Crossgate AG in Germany and other

countries. Crossgate 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.

www.sap.com

bw workspaces - data cleansing during flat file...

Documents