SAP Business Objects - Data Quality USA Address Cleanse Transform

[Former Member]

Applies to:

SAP Business Objects Data Services and Data Quality.

Summary - This document will explain the step by step to do the ‘USA Address Cleanse’ in Global Address Cleanse Transforms.

Author : Sreelatha V.P

Company: Collabera Enterprise Software Solutions Pvt. Ltd

Created on:  24^th January 2012

Author Bio

Sreelatha V.P is working as a SAP BI/BO Development Consultant with Collabera Enterprise Software Solutions Pvt. Ltd. Sreelatha has involved in various Business Objects Data services and BW development projects.

Table of Contents

        1. Overview : SAP BusinessObjects Data Quality Transforms ...........................................................  3

        2. Global Address Cleanse Transforms ............................................................................................ 3

        3. USA Address Cleanse Transform ................................................................................................ 4

               1.1  Business Scenario : USA Address Cleanse Transform ........................................................ 4

                                       a) Customer_Master Input File...........................................................................4

                                           ETL Dataflow............................................................................................... 4

                                        b) Query Transform......................................................................................... 5

                                            Step 1....................................................................................................... 5

                                        c) USA Address Cleanse Transform - Input field Mapping..................................  5

                                            Step 2 .......................................................................................................5

                                        d) Options...................................................................................................... 9

                                            Step 3 .......................................................................................................9

                                        e) Output field Mapping................................................................................... 6

                                            Step 4 .......................................................................................................6

                                        e)  USA Address Cleanse Output File - XX_Customer_Master............................ 7                   

        Related Content .............................................................................................................................8

        Copyright .......................................................................................................................................9

Overview: SAP Business Objects Data Quality Transforms

Data Quality transforms are a set of transforms that help you improve the quality of your data. The transforms can parse, standardize, correct, and append information to your customer and operational data.

            Data Quality transforms include the following transforms:

•    Associate

•    Country ID

•    Data Cleanse

•    Global Address Cleanse

•    Global Suggestion List

•    Match

•    USA Regulatory Address Cleanse

•    User-Defined

          Global Address Cleanse Transform

                   

The Global Address Cleanse Transform takes input as any address information and matches that, using different engines, against its Address Dictionary. The transform uses its internal knowledge how address lines are written to parse it into its segments and also corrects typing errors.

Normally, when an address cleanses transform looks up an address in the postal directories, it finds one matching record. Sometimes, because of incomplete information, there may be two or more records (or suggestions) in the postal directories that could possibly be the correct record. Suggestion lists provide you with a list of "matching" addresses, so that you can choose which the best address is.

The suggestion lists feature is available for real time data flows only and is available in both the Global Address Cleanse transform and the USA Regulatory Address Cleanse transform (not for CASS certification). For other countries, there is a Global Suggestion List transform configuration that you can set up in your data flow.

When the transform looks up an address in the postal directories, it finds exactly one matching record. When the input data is good, the transform should be able to determine exactly one matching record—one combination of city, state, and postal code—in the City and Postcode directories. Then, during the lookup in the Address directory, the transform should find exactly one record that matches the address.

       USA Address Cleanse Transform:

          USA Address cleanse transform gives you back a corrected, complete, and standardized form of your original address data. With the USA Address                 Cleanse Transform and for some countries with the Global Address Cleanse transform, address cleanse can also correct or add postal codes.

       Business Scenario: USA Address Cleanse Transform:

We have cleansed the address of ‘Customer Master’ Input file using USA Address Cleanse Transform.

1. Derived the Parsed, Cleansed and Standardized address.
2. Derived the ‘County' name from ‘Billing Address’ in the Source filed.

Input data - The address cleanse transforms accept discrete, multiline, and hybrid address line formats.

     In our example, the input table “Customer Master” is shown below:

                 a)     Customer_Master - Input file

              

• ETL – Dataflow

                    b) Query Transform

            Step 1:

           Filter_Records : Select the required Address filelds using the Query Transform

                  

                    c) USA Address Cleanse Transform – Input field Mapping

           Step 2:

• Map the corresponding Input fileds in the Input tab.

The Input tab displays the available field names that are recognized by the transform. You map these fields to input fields in the input schema area. Mapping input fields to field names that the transform recognizes tells the transform how to process that field.

                   

               d) Options

     Step 3:

The Options tab contains business rules that determine how the transform processes your data. Each transform has a different set of available options. If you change an option value from its default value, a green triangle appears next to the option name to indicate that you made an override.

                    e) Output field Mapping

          Step 4:

• Select the required Output fields in the Output Tab.

The Output tab displays the field names that can be output by the transform. Data Quality transforms can generate fields in addition to the input fields that that transform processes, so that you can output many fields. These mapped output fields are displayed in the output schema area.

      Filter and sort

The Input, Options, and Output tabs each contain filters that determine which fields are displayed in the tabs.

• Best Practice - Displays the fields or options that have been designated as a best practice for this type of transform.
• In Use - Displays the fields that have been mapped to an input field or output field.
• All – Displays all available fields.

                    Query transform

      Derive Address :  Select the required Address fields in Output table.

                        

                    f) USA Address Cleanse Output File - XX_Customer_Master

               Output data – When you set up the USA Regulatory Address Cleanse transform or the Global   Address Cleanse transform; you can add output             fields that contain:

                    1.     Parsed address components, which correspond to the input fields, such as locality, region, and postal code.

                    2.     Best address components, which are processed data standardized according to the options set in the transform.

                    3.     Information about whether any data was changed, added, used, or not used in a corrected component.

                      Derived the following fields from ‘Billing address’ field in Source file.

1. Parsed, Cleansed, Standardized address, County name ,Full Address, House number,    Street Type, Postal Delivery Name, ISO_COUNTRY_CODE_2CHAR, Postcode, Status Code, Actual Assignment Level and Address Type.

                        

1. Derived Billing Address - the Parsed, Cleansed and Standardized Address with respect to source address.
2. Street Type - Abbreviated street type, such as "St," "Ave," or Primary_Type1 "Pl."
3. County Name - Fully spelt county name. Derived from address dictionary.
4. Region - State, province. Territory or region.
5. Direction - Abbreviated or non-abbreviated directional (N, South, NW, SE) that follows a street name.

                         6.  Status Code - SC000 indicates corrected region and locality

• SC300 indicates corrected region and locality, corrected pre/post directional and primary type.
• SC400 indicates corrected region and locality, corrected primary name.
• SE000 indicates Corrected postal code, region, and locality.

                          7. Address Type - S indicates Street Address, P indicates Postal address.

Related Content

    1. SAP Business Objects Data Services Designer Guide -

http://help.sap.com/businessobject/product_guides/boexir32SP1/en/xi321_ds_designer_en.pdf

    2.   http://wiki.sdn.sap.com/wiki/display/BOBJ/Global+Address+Cleanse+Transform

    3.  SAP Business Objects Data Services Reference Guide -

http://help.sap.com/businessobject/product_guides/boexir32/en/xi32_ds_reference_en.pdf

http://help.sap.com/businessobject/product_guides/boexir31/en/xi31211_ds_reference_guide_en.pdf