Skip to end of metadata
Go to start of metadata

A retail store wants to do a market campaign.  The company wants to be able to identify a list of addresses that are within certain distance from a given location for target mailing, so that they can send the promotion flyers or brochures to promote any new product offerings or promotion programs.

Remarks: Retail stores usually can get the target territory location from Google Map.


In this lab, you will:

1. Create a dataflow to use Geocoder Geo Spatial Search feature to find a LIST of addresses from the given latitude/longitude geographic coordinates using Geocoder transform.

2. Import Geocoder XML Schema “geo_result_list.xsd”, which provides the data format for the Geocoder RESULT_LISTS output type.

3. Convert the Geocoder Result Lists XML String output type into a Nested Relational Data Model structure using Query extract_from_xml() function call.

4. Use Query transform Unnest the NRDM structure to store the Geocoder multiple results into a database table.


Assume you have a input file named "geo_addr_search_input.txt" with the content of below:

1|CA|A0A||NL|N/A||NEW|RD||1||ADDR|47.76915|-52.76829|||2|10|Map Lat/Long Fields 

Completed Dataflow
Create a New Project, Batch Job, and Dataflow

1. Create a new project: 

  • From the Project menu, select New>Project.
  • In the Project name field, enter GEO_Training_3.
  • Click Create.

2. Create a Batch Job:

  • Right-click the Geo_Training_3 project and select New Batch Job.

  • Type GEO_POI_Search and press Enter.

3. Create the Dataflow:

  • From the Project menu, select New>Data Flow.
  • Make sure the GEO_POI_Search job tab is highlighted.
  • Click the Data Flow icon on the right sidebar.
  • Click in the canvas (design area).
  • Name the dataflow POI_Search.


Create a New Input File

1. In the Local Objects Library, choose the Formats tab.

2. Right-click Flat Files and select New.


3. On the File Format Editor window, make the following changes. If an overwrite warning dialog pops up, click Yes.

  • Name: geo_poi_search
  • Root Directory: < D:\DKT_Files\Geocoder (or you can click the icon beside it to select).
  • File Name: geo_addr_search_input.txt (this is the file you saved to VM previously; you can click the icon besides it to navigate to it)
  • Column: |
  • Skip row header: Yes


Note: If you see the file content display on the right part of the window, it indicates you made the right changes.

4. Change the attributes for the fields listed on the right side:

  • Data Type: varchar
  • Field Size: 20 for Latitude and Longitude

5. Click the Save and Close button to return to the main Data Services window.

6. Add the input file to the dataflow as the source.

  • In the Local Objects Library pane, select geo_poi_search.
  • Drag it to the dataflow canvas.
  • Select Make Source.

Note: you can click the “zoom” icon to view the input data in the lower panel.

Add the Geo Transform

1. Open the Transform tab.

2. Expand the Data Quality node.

3. Expand the Geocoder node.

4. Select the ResultListGeocode transform and drag it to the dataflow canvas.

5. Name it geo_poi_search.

6. Link the “geo_poi_search.txt” input file to the geo_poi_search transform.

Configure the Geo Transform Options

1. Double-click the geo_poi_search transform to open the Options Setup window.

2. On the Input tab page, select the Best practice radio button.

3. Map the Latitude and Longitude as shown below.

4. Remove any other existing mappings.

5. Choose the Options tab.

6. Select the All radio button.

7. You want to find a maximum of 100 addresses within two kilometers from the given latitude and longitude (given neighborhood). Change Max Record to 100.

8. Choose the Output tab.

9. Select the Best Practice radio button.

10. Select the check boxes for all three fields.

Note: The results will be delivered in an XML file.

Add an XML to NRDM  Query Transform

Next, add a Query transform to use extract_from_xm() to build a hierarchy (NRDM) output structure from the geocoder RESULT_LIST XML String output type. Import the XML Schema Format for Suggestion_lists output field.

1. In the Local Objects Library window, choose the Format tab.

2. Right-click XML Schema and select New.

3. In the Format Name field, enter GeocoderResultList.

4. Browse to the file C:\Program Files (x86)\SAP BusinessObjects\Data Services\DataQuality\geocoder\geo_result_list.xsd and click Open.

5. From the Namespace list, select urn:bobjsap_geo.

6. From the Root element name list, select RESULT_LIST

7. Click OK

Add a Process Result Query Transform

Add a Query transform to convert the XML string into NRDM hierarchy data.

1. Choose the Geo_POI_Search - Data Flow tab, or select the dataflow in the Projects pane.

2. Select the Query icon on the right side bar.

3. Click in the canvas (design area).

4. Name the query process_result.

5. Link the geo_poi_search input file to the process_result transform*.*

Configure the Process Result Query Transform

1. Double-click the process_result query

2. Right-click process_result and select New Function Call.

3. In the Select Function window, select Conversion Functions and click Next.

4. In the Define Input Parameter(s) window, click the down arrow button of the XML field name list.

5. Select geo_poi_search and click OK.

6. Select RESULT_LIST and click OK.

7. From the DTD or Schema name list, select GeocoderResultList and click OK.

8. In the Enable validation list, type 1.

9. Click Next.

10. Select RECORD and click the Add button.

11. Click Finish. You will see the screen below.

Add Unnest Query Transform

Add another Query transform to process the un-nest the previous query result.

1. Select the Geo_POI_Search dataflow.

2. Select the Query icon on the right sidebar.

3. Click in the canvas (design area).

4. Name the query unnest_result.

5. Link the process_result transform with the unnest_result transform.

Configure Query Transform

1. Double-click the unnest_result transform

2. Drag RECORD from the Schema In pane to the Schema Out pane. 

3. In the Schema Out pane, right-click RECORD and select Unnest.

Notice that the icon is now changed as shown below:

Add Temporary Table

Add a temporary table to show the results.

1. In the Project Area, select the Geo_POI_Search dataflow.

2. Select the Template Table icon on the right sidebar.

3. Click in the canvas (design area).

4. In the Name field, enter Show_Result.

5. In the In datastore list, select Data_DS.

6. Click OK.

7. Link the unnest_result transform to the Show_Result table.

Execute the Job

1. Right-click the GEO_POI_Search job and select Execute

2. In the Execution Properties window, accept the default value and click OK.

Check the Result

1. Select the POI_Search dataflow.
2. Click the zoom icon on SHOW RESULTS.

3. The Distance column shows the distance from the latitude/longitude location.


  • No labels