Consuming Data from the Hub

Consuming data from the hub is performed through mappings using source tables from the hub. These tables contain the golden and master data. In this tutorial, the consumption process targets an XML file which has the following structure:

This structure contains information about Customers and Employee (The customer’s account manager), and must be loaded using the corresponding entities in the MDM Hub. The golden data for these entities is stored in the GD_CUSTOMER and GD_EMPLOYEE tables from the SEMARCHY_DEMO_MDM schema that was previously reverse-engineered.

Creating the New Mapping

In this mapping, we are extracting data from two tables in the MDM Hub ( GD_EMPLOYEE and GD_CUSTOMER) and target the DemoXMLGolden XML file.

  1. Select the GettingStarted > Mapping folder in your project.
  2. Click the Create New Mapping button in the Project Explorer toolbar. The New Map Diagram wizard opens.
  3. Make sure that the GettingStarted/Mapping folder is selected as the parent folder.
  4. In File Name enter ConsumeGoldenCustomers and then click Finish. The ConsumeGoldenCustomers mapping editor opens.
  5. In the project explorer, expand the GettingStarted > Metadata >DemoXMLGolden > golden_customer.xsd node.
  6. From the project explorer, drag and drop the Customers root entity into the mapping editor.
  7. In the Project Explorer, expand the GettingStarted > Metadata > OracleServer > OracleServer > SEMARCHY_DEMO_MDM node.
  8. From the Project Explorer, drag and drop the GD_CUSTOMER table into the mapping editor.
  9. Repeat the previous step to add the GD_EMPLOYEE table to the mapping editor.

Filtering Golden Data

The GD tables store golden data for all data branches and editions.
Therefore, a GD table needs to be filtered in order to retrieve data:

  1. In the ConsumeGoldenCustomer editor, select the B_BRANCHID column in the GD_CUSTOMER source table.
  2. Keep the mouse pressed and drag this column on the editor’s background.
  3. Release the mouse button. A filter appears, attached to the GD_CUSTOMER table.
  4. Select the Expression Editor view. The code shows: GD_CUSTOMER.B_BRANCHID=1.
  5. Replace this code with the following expression: GD_CUSTOMER.B_BRANCHID = 0 and GD_CUSTOMER.B_CLASSNAME = 'Customer' and GD_CUSTOMER.B_FROMEDITION <= 0 and (GD_CUSTOMER.B_TOEDITION is null or GD_CUSTOMER.B_TOEDITION > 0)

This filter indicate that records retrieved:

A similar filter must be applied to the GD_EMPLOYEE table.

  1. In the ConsumeGoldenEmployee editor, select the B_BRANCHID column in the GD_EMPLOYEE source table.
  2. Keep the mouse pressed and drag this column on the editor’s background.
  3. Release the mouse button. A filter appears, attached to the GD_EMPLOYEE table.
  4. Select the Expression Editor view. The code shows: GD_EMPLOYEE.B_BRANCHID=1.
  5. Replace this code with the following expression: GD_EMPLOYEE.B_BRANCHID = 0 and GD_EMPLOYEE.B_CLASSNAME = 'Employee' and GD_EMPLOYEE.B_FROMEDITION <= 0 and (GD_EMPLOYEE.B_TOEDITION is null or GD_EMPLOYEE.B_TOEDITION > 0)

Joining the Sources

It is now necessary to join the two sources to establish the relation between the GD_CUSTOMER and GD_EMPLOYEE tables.

  1. In the ConsumeGoldenCustomer editor, select the F_ACCOUNT_MANAGER column in the GD_CUSTOMER source table.
  2. Keep the mouse pressed and drag this column onto the EMPLOYEE_NUMBER column in the GD_EMPLOYEE table
  3. Release the mouse button. A join appears, linking these two tables.
  4. Select the Expression Editor view. The code shows: GD_CUSTOMER.F_ACCOUNT_MANAGER=GD_EMPLOYEE.EMPLOYEE_NUMBER

Note: This hub mandates that a customer has at least one account manager. This join uses a default join type, that is an INNER JOIN.

Defining the Column Mappings

  1. Expand the Customers target in the mapping editor in order to see all the elements and attributes of the XML structure.
  2. In the mapping diagram, select the CUSTOMER_ID column from the GD_CUSTOMER table. Drag CUSTOMER_ID to the Customer> Customer target element in the XML structure.
  3. Repeat the same operation to perform the following mappings:
  4. Select the Customer> Customer > address target element, then select the Expression Editor view. Enter the following code: GD_CUSTOMER.INPADDRESS || ', ' || GD_CUSTOMER.INPPOSTAL_CODE || ' - ' || GD_CUSTOMER.INPCITY || ', ' || GD_CUSTOMER.INPCOUNTRY
  5. Press CTRL+S to save this mapping.

The mapping appears as shown below.

Note: To iterate and create multiple elements in the target XML file, it is necessary to provide a key indicating how to break the records into sub-elements. In this mapping, the key is provided in the Customer> Customer target element, which appears with a key indicator. One <Customer> sub-element is created for each CUSTOMER_ID value returned from the GD_CUSTOMER table, that is for each customer golden record.

Running the Mapping

  1. Select the background of the ConsumeGoldenCustomers mapping editor, right-click and select Execute
  2. The mapping execution starts.
  3. Select the Sessions view and check that the session is executed successfully.
  4. Open with a text editor the <semarchy_di>/samples/xml/golden_customer.xml file created by the mapping. It displays the golden customers records and their attached golden account manager records.

The file content is as shown below.

<?xml version="1.0" encoding="windows-1252"?>
<CORP:Customers xmlns:CORP="http://www.semarchy.com/samples"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<Customer id="1">
		<customer_name>GADGETROM</customer_name>
		<address>711 KAPIOLANI BOULEVARD, - HONOLULU, USA</address>
		<account_manager>
			<first_name>Matthew</first_name>
			<last_name>WEISS</last_name>
			<phone_number>+1.650.123.1234</phone_number>
			<email_address>mweiss@fakecompany.com</email_address>
		</account_manager>
	</Customer>
	<Customer id="2">
		<customer_name>BURLEIGH AND STRONGINTHEARM</customer_name>
...