Maintaining relationships

(1)

Teradata Master Data Management eases hierarchy and cross-reference management.

by Bob Costa

Maintaining relationships

O

rganizing and understanding key

reference and relationship data are

essential in business analysis. But as an

organization evolves, this information will

change, posing a challenge to keep the data

clean, organized and consistent. To ensure a

complete and accurate view of the business,

these changes to the relationship data must

be managed with the same rigor and

pro-cess as is applied to master reference data.

Master data design patterns

Teradata Master Data Management

provides a framework to more easily

manage relationships among reference

data elements. This framework includes

an internal metadata model that is based

on the master data design patterns and

Web-based user interfaces (UIs) to define

and manage relationships and to view and

interact with the relationships and the

referenced master data.

As reference data is updated in the

Teradata system, its relationship data can be

simultaneously maintained and managed

by the Teradata Master Data Management

solution. This process offers a single view

of an organization’s master reference and

relationship data.

The same business rules, process flows,

approvals and publishing capabilities that

are applied to the master data are also

applied to the relationship data. Thus, as

changes occur, the relationship data can

be maintained cleanly and accurately to

ensure that it meets the organization’s

qual-ity and change-management requirements.

Business intelligence (BI) applications and

tools can then confidently leverage the data

to produce accurate reports at any time.

Manage and view hierarchies

The Teradata Master Data Management

solution can work with any type of

hierar-chy structure, including balanced

(mean-ingful levels/consistent depth), unbalanced

(inconsistent levels/inconsistent depth)

and ragged (consistent levels/inconsistent

depth). Furthermore, as data changes over

time, the user can create numerous

hier-archies as well as different versions of the

hierarchies. This allows users to not only

view the data’s current structure but also its

past and future versions.

TECH

2 TECH

applied solutions 3

BRACELET SHOES TIE WATCH SOCKS SWEATSHIRT

GUCCI ALLEN SOLLY LEE

DESIGNER WEAR LEVIS JEAN JACKET PndBrdCat MENSWEAR Hierarchy Management View Hierarchy MDM NextGen Manage Hierarchies Import Hierarchy Relationship Management – +

Viewing and managing hierarchies

From the Teradata Master data Management hierarchy viewer screen the user can navigate the product hierarchy to view and modify any related or cross-referenced product records.

Figure 1

Key terms

Reference data: Uniquely identifies a

product, customer or other business entity.

Relationship data: Describes how an

entity relates to other entities.

Hierarchy: Relationship where logical

groupings of objects or components are organized into a tree-like structure with levels arranged above (parent), below (child) and at the same level.

Cross-reference: Relationship of one

object to another, typically applied to link a single golden record to one or more source records.

(2)

The solution’s viewing and

manipula-tion capabilities, based on Adobe Flex

technology, allow users to easily search

the hierarchies, zoom in and out on their

structures and manipulate them through a

drag-and-drop interface. By clicking on an

element in the hierarchy, users are directed

to a table editor, or other custom function,

where they can manage their underlying

master data. (See figure 1, page 1.)

The user can perform various tasks from

the Teradata Master Data Management

UI, such as cross-reference relationship

management, which identifies relationships

between the data’s source and its target.

A common pattern is to map multiple

source systems to a pool of target master

entities. Within this pool, a relationship

can be defined to a standard entity, known

as a golden master. In this way, duplicate

entities can be related to one golden master.

For example, if two source master records

are linked to a common golden master

record using the underlying relationship

management model, the user can navigate

from the golden master record to view

any related cross-referenced records. The

reverse is also possible because the model

supports bi-directional navigation.

Another core service provided through

Teradata Master Data Management

is cross-reference matching and

link-ing. This allows the user to organize

records using specified parameters, such

as Soundex-based matching, defined

fields or characters. The service can be

leveraged when a master data

manage-ment (MDM) solution is being built or

purchased as an out-of-the-box

dash-board with the Teradata Customer Data

Integration application accelerator.

Teradata is also a partner of

numer-ous companies that provide best-of-breed

matching and cleansing engines, such as

Trillium, Informatica, SAP BusinessObjects

and SAS DataFlux. These can be seamlessly

leveraged within the Teradata Master Data

Management framework.

In action

To answer fundamental business

ques-tions, a complete and accurate view of the

organization’s information is required,

making clean and integrated relationship

data necessary. Simple questions such as

number of customers, number of products

sold, most profitable channel and which

suppliers to conduct business with cannot

be easily answered if the organization of

that data (hierarchy), or the definition of

product or supplier (cross-reference of

multiple identities), changes frequently and

is not properly managed and maintained.

BI tools can dynamically leverage the

hierarchy and cross-reference

implemen-tations of managed relationship data to

reflect a total and accurate single view of

the enterprise through hierarchy or

cross-reference management.

HiErArcHy MAnAGEMEnt

The relationship data in MDM (hierarchy)

can be populated and maintained to show

typical product and brand

relationships for any

stan-dard product hierarchy.

Although the example

diagrammed in figure 2

covers product

relation-ships, the same concept

can be applied to any

domain of managed

mas-ter reference data, such as

customer, supplier and so

on. With this hierarchy

pattern in place, a BI tool

can easily aggregate

trans-actions for all products

and report sales by brand

against the current view of

the hierarchy.

Changes to the product

hierarchy can be made

simply by performing a

“drag and drop” operation,

through either the

stand-alone hierarchy viewer or a

Relationship object data table Brand master table

Prod_ID

Product master table 5 6 23 Prod_Desc Shoes Tie Socks RO_ID 1 MST_TBL_PHY_NAME MST_BRAND_MASTER 2 ProductMaster MST_PRODUCT_MASTER

Relationship object mapping table

ROM_ID 1301 PARENT_RO_ID 1 CHILD_RO_ID 2 10 CHILD_ROKT_ID 11 PARENT_ROKT_ID RODV_ID 15 ROM_ID 1301 PARENT_ROKD_VAL1 66 5 ROP_1_VAL 10/25/2005 CHILD_ROKD_VAL1 16 17 1301 1301 66 300 23 11/19/2008 4/09/2008 18 1301 300 6 4/27/2009 6 R R Teradata Master Data Management Can track relationships at points in time bl b P d

Product golden master Brand_ID 100 300 Brand_Name Gucci Lee Allen Solly BrandMaster 66 Approvers Data stewards M RO_ID

Relationship object table

MST_TBL_LOG_NAME

Product hierarchy

The Teradata Master data Relationship Management data model shows how products relate to brands. in this example, product 6 (Tie) was moved from brand 66 (Gucci) to brand 300 (allen Solly) on 4/27/2009.

(3)

hierarchy viewer/report combination

UI. Dragging an element from one

parent to another—for example,

mov-ing the product Tie from the brand

Gucci to the brand Allen Solly—affects

only the relationship data and not

the product or brand reference data.

The move can be seen immediately in

the UI viewers and in any subsequent

BI reports. Without modifying the

report definition, the new aggregated

data—in this case, newly aggregated

sales amounts for both brands—will

be promptly updated.

Altering the hierarchy, however,

can automatically invoke a workflow

rule where an approval is required

before the change can become active.

For this reason, all modifications

have date and time auditing so that

when an analysis is performed, it can

be applied to the data at a particular

point in time or to a revised version

of the hierarchy.

Product HiErArcHy

ExAMPlE

The data constituting the Category ->

Brand -> Product Hierarchy is shown in

table 1 on page 4.

Product master tables and views that

con-stitute the product hierarchy for this example

are shown in code sample 1 on page 5.

cross-rEfErEncE

MAnAGEMEnt

Multiple product records can be related to

different brands but linked to a common

golden master. (See figure 3.) With a golden

master cross-reference design pattern in

place, a BI tool can easily aggregate

transac-tions of all product duplicates and report it

against the golden master entity, using the

related cross-reference entries.

Some products may be the same but are

sourced from multiple suppliers and exist

under different, brand-specific product

IDs. In these cases, as new product brand

records are loaded, matched and linked,

the cross-reference table can be updated

manually or automatically in Teradata

Master Data Management with the results

of a golden master product record search

displaying the relationship of all of the

related product records.

When a new product is introduced into

the workflow process and linked to a brand,

the product cross-reference function will

publish event or SQL updates that are tied to

the product. Consequently, as in the

hierar-chy management function, any subsequent

execution of the BI report will automatically

show the new aggregated data. For example,

as new products by brand are introduced,

a BI report can show consolidated orders

by golden master product ID, organized by

supplier instead of by brand.

Product cross-rEfErEncE

ExAMPlE

The data constituting the “Golden” product

master cross-reference to the “Source”

product SX master and the “Source”

product SY master is shown in table 2 and

table 3 on page 4.

Product master tables, views and sample

data that constitute the product

cross-ref-erence for this example are shown in code

sample 2 on page 7.

Secure relationships

With the process-driven framework and the

modeling design patterns provided through

the Teradata Master Data Management

solution, businesses have a powerful and

easy-to-use platform to manage

relation-ship and reference data.

Additionally, having the relationship

data managed in Teradata Database tables

makes it easy to map BI tools directly to

the relationship data, enabling companies

to gain complete and accurate insight into

their business events and transactions.

T

Bob Costa is product manager for Teradata

Master Data Management. He has been with

Teradata for more than 11 years, working

with analytical applications.

Relationship object data value table Brand master table

Prod_ID

Product master table 5 6 23 Prod_Desc Shoes Tie Socks Prod_ID

Product supplier-X table SX66 Prod_Desc Shoes-X SX55 Tie-X Prod_ID

Product supplier-Y table SY123 Prod_Desc Tie-Y SY106 Socks-Y ROM_ID 1 2 RODV_ID ROM_ID 1301 PARENT_ROKD_VAL1 66 1301 1301 66 300 1301 300 1601 6 1602 6 Teradata Master Data Management Source systems duplicates of products Approvers Data stewards P d bl P

Product golden master Brand_ID 100 300 Brand_Name Gucci Lee Allen Solly ROM_ID

Relationship object table

3 4

66

Relationship object mapping table

ROM_ID 1301 PARENT_RO_ID 1 CHILD_RO_ID 2 10 CHILD_ROKT_ID 11 1302 2 3 10 11 1303 2 4 10 11 PARENT_ROKT_ID MST_TBL_PHY_NAME MST_BRAND_MASTER ProductMaster MST_PRODUCT_MASTER MST_TBL_LOG_NAME BrandMaster MST_PROD_SX_MASTER ProductSYMaster MST_PROD_SY_MASTER ProductSXMaster 15 5 ROP_1_VAL 10/25/2005 CHILD_ROKD_VAL1 16 17 23 11/19/2003 4/09/2008 18 6 4/27/2009 19 SX66 4/27/2009 20 SY106 4/27/2009 6

Product cross-reference

The Teradata Master data Relationship Management data model shows how products are cross-referenced to suppliers.

(4)

Mst_Product pROdUCT_id pROdUCT_deSCRipTiON 2 TOpS 3 T-SHiRT 4 SHORTS 5 SHOeS 6 Tie 7 WaTCH 8 CUFFliNK 9 BRaCeleT 10 WOMeN’S peRFUMe 11 WOMeN’S JeWelRY 12 TOYS 20 JeaNS JaCKeT 23 SOCKS 25 BelT 44 FORMal SHiRT 45 SWeaT SHiRT Mst_BrAnd BRaNd_id BRaNd_deSCRipTiON 66 GUCCi 100 lee 200 leViS 300 alleN SOllY 400 lOUiS pHillippe

500 GiNi aNd JONY

9999 BiBa Mst_cAtEGory CaTeGORY_id CaTeGORY_deSCRipTiON 33 deSiGNeR WeaR 1000 MeN’S WeaR 2000 WOMeN’S WeaR 3000 Kid’S WeaR 4000 UNiSeX

Product

master tables

table 1

Golden product

master table

table 2

Mst_Product_MAstEr pROdUCT_id pROdUCT_deSCRipTiON 2 TOpS 3 T-SHiRT 4 SHORTS 5 SHOeS 6 Tie 7 WaTCH 8 CUFFliNK 9 BRaCeleT 10 WOMeN’S peRFUMe 11 WOMeN’S JeWelRY 12 TOYS 20 JeaNS JaCKeT 23 SOCKS 25 BelT 44 FORMal SHiRT 45 SWeaT SHiRT Mst_Prod_sx_MAstEr pROdUCT_NUMBeR pROdUCT_deTailS SX22 TOpS-X SX33 T-SHiRT-X SX44 SHORTS-X SX66 Tie-X SX120 JeaNS JaCKeT-X SX123 SOCKS-X

SY144 FORMal SHiRT-X

Mst_Prod_sy_MAstEr pROdUCT_ideNTiTY pROdUCT_NaMe SY102 TOpS-Y SY103 T-SHiRT-Y SY104 SHORTS-Y SY105 SHOeS-Y SY106 Tie-Y SY107 WaTCH SY108 CUFFliNK SY109 BRaCeleT SY110 WOMeN’S peRFUMe SY111 WOMeN’S JeWelRY SY112 TOYS

SY120 JeaNS JaCKeT-Y

SY123 SOCKS-Y

SY125 BelT-Y

SY144 FORMal SHiRT-Y

SY145 SWeaT SHiRT-Y

Source product

master tables

(5)

Code sample 1

CREATE SET TABLE MST_PRODUCT

(

PRODUCT_ID INTEGER NOT NULL,

PRODUCT_DESCRIPTION VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC, SYS_TARGET_ID INTEGER,

SYS_AUTH_ID VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,

SYS_SOURCE VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT ‘BackEnd’, SYS_CREATED_BY VARCHAR(70) CHARACTER SET LATIN NOT CASESPECIFIC,

SYS_CREATION_DATE TIMESTAMP(0),

SYS_ENT_STATE VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC CHECK ( SYS_ENT_STATE IN (‘ACTIVE’,’INACTIVE’,’NEW’,’DELETED’) ),

SYS_LAST_MODIFIED_BY VARCHAR(70) CHARACTER SET LATIN NOT CASESPECIFIC, SYS_LAST_MODIFIED_DATE TIMESTAMP(0),

SYS_NC_TYPE VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC, SYS_ERR_CODE VARCHAR(10000) CHARACTER SET LATIN NOT CASESPECIFIC,

SYS_ERR_SVRTY VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC CHECK ( SYS_ERR_SVRTY IN (‘SEVERE_ERROR’,’ERROR’,’WARNING’) )) UNIQUE PRIMARY INDEX MST_PRODUCT_PK ( PRODUCT_ID );

CREATE SET TABLE MST_BRAND (

BRAND_ID INTEGER NOT NULL,

BRAND_DESCRIPTION VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC, SYS_TARGET_ID INTEGER,

SYS_ENT_STATE VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC CHECK ( SYS_ENT_STATE IN (‘ACTIVE’,’INACTIVE’,’NEW’,’DELETED’) ), SYS_LAST_MODIFIED_BY VARCHAR(70) CHARACTER SET LATIN NOT CASESPECIFIC,

SYS_LAST_MODIFIED_DATE TIMESTAMP(0),

SYS_ERR_SVRTY VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC CHECK ( SYS_ERR_SVRTY IN (‘SEVERE_ERROR’,’ERROR’,’WARNING’) )) UNIQUE PRIMARY INDEX MST_BRAND_PK ( BRAND_ID );

CREATE SET TABLE MST_CATEGORY (

CATEGORY_ID INTEGER NOT NULL,

CATEGORY_DESCRIPTION VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC, SYS_TARGET_ID INTEGER,

(6)

SYS_ERR_SVRTY VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC CHECK ( SYS_ERR_SVRTY IN (‘SEVERE_ERROR’,’ERROR’,’WARNING’) )) UNIQUE PRIMARY INDEX MST_CATEGORY_PK ( CATEGORY_ID );

CREATE SET TABLE Product_Day_Sales (

SALE_DATE DATE FORMAT ‘YY/MM/DD’, PRODUCT_ID INTEGER,

SALES DECIMAL(18,4)) PRIMARY INDEX ( PRODUCT_ID );

The following is a view on top of the MST_REL_OBJ_DATA_VAL table, which is essentially a table consisting of the known relationships (and

levels). For our example, the relationships that were created for Brand and Product was “1301” and for the Brand and Category was “1302.” (See

figure 2, page 2.)

REPLACE VIEW PROD_HIER

(ID, DESCRIPTION, PARENT_ID, PARENT_DESCRIPTION) AS

SELECT DISTINCT CHILD_ROKD_VAL1 (INTEGER) AS ID, PRODUCT_DESCRIPTION AS DESCRIPTION, PARENT_ROKD_VAL1 (INTEGER) AS PARENT_ID, BRAND_DESCRIPTION AS PARENT_DESCRIPTION FROM MST_REL_OBJ_DATA_VAL REL, MST_PRODUCT PRD, MST_BRAND BRD

WHERE REL. CHILD_ROKD_VAL1 = PRD.PRODUCT_ID AND REL.PARENT_ROKD_VAL1 = BRD.BRAND_ID AND REL.ROM_ID = 1301

UNION

SELECT DISTINCT CHILD_ROKD_VAL1 (INTEGER) AS ID, BRAND_DESCRIPTION AS DESCRIPTION, PARENT_ROKD_VAL1(INTEGER) AS PARENT_ID , CATEGORY_DESCRIPTION AS PARENT_DESCRIPTION FROM MST_REL_OBJ_DATA_VAL REL, MST_CATEGORY CAT, MST_BRAND BRD

WHERE REL. CHILD_ROKD_VAL1 = BRD.BRAND_ID

AND REL.PARENT_ROKD_VAL1 = CAT.CATEGORY_ID AND REL.ROM_ID = 1302

UNION

SELECT CATEGORY_ID(INTEGER) AS ID, CATEGORY_DESCRIPTION AS DESCRIPTION, NULL (INTEGER) AS PARENT_ID, NULL (VARCHAR(100)) AS PARENT_DESCRIPTION FROM MST_CATEGORY;

The view depicting the product hierarchy recursively is shown below. There could be many different ways and formats in which a report could

be developed depending on the features supported by the particular reporting tool. The main obejctive of this illustration is to emphasize the

depth provided by the Teradata Master Data Relationship Management data model in supporting BI reporting:

REPLACE RECURSIVE VIEW PH (LVL,ID,DESCRIPTION,PARENT_ID, PARENT_DESCRIPTION) AS (

SELECT 1 AS LVL, ID, DESCRIPTION, PARENT_ID, PARENT_DESCRIPTION FROM PROD_HIER

(7)

WHERE

PARENT_ID IS NULL UNION

ALL (

SELECT LVL+1,PROD_HIER. ID, PROD_HIER.DESCRIPTION, PROD_HIER.PARENT_ID, PROD_HIER.PARENT_DESCRIPTION FROM PROD_HIER, PH WHERE PROD_HIER.PARENT_ID=PH.ID ) );

Code sample 2

Product master tables, views and sample data that constitute the product cross-reference for this example are:

CREATE SET TABLE MST_PRODUCT_MASTER (

PRODUCT_ID INTEGER NOT NULL,

PRODUCT_DESCRIPTION VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC, SYS_TARGET_ID INTEGER,

SYS_ERR_SVRTY VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC CHECK ( SYS_ERR_SVRTY IN (‘SEVERE_ERROR’,’ERROR’,’WARNING’) )) UNIQUE PRIMARY INDEX MST_PROD1_PK ( PRODUCT_ID );

CREATE SET TABLE MST_PROD_SX_MASTER

PRODUCT_NUMBER INTEGER NOT NULL,

PRODUCT_DETAILS VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC, SYS_TARGET_ID INTEGER,

(8)

UNIQUE PRIMARY INDEX MST_PROD2_PK ( PRODUCT_NUMBER ); CREATE SET TABLE MST_PROD_SY_MASTER

(

PRODUCT_IDENTITY INTEGER NOT NULL,

PRODUCT_NAME VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC, SYS_TARGET_ID INTEGER,

SYS_ERR_SVRTY VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC CHECK ( SYS_ERR_SVRTY IN (‘SEVERE_ERROR’,’ERROR’,’WARNING’) )) UNIQUE PRIMARY INDEX MST_PROD3_PK ( PRODUCT_IDENTITY );

The SQL below creates an operational data store table for the consolidated transactions (this could also have been developed in a view): CREATE TABLE PRODUCT_DAY_ORDER_ODS

( PRODUCT_ID INTEGER, PRODUCT_DESCRIPTION VARCHAR(100), CONS_ORD_DATE DATE, ORD_QUANTITY INTEGER, ORDER_AMOUNT DECIMAL (18,4) )

PRIMARY INDEX ( PRODUCT_ID) CT DATE_TAB

( DATE_COL DATE); INS DATE_TAB SEL CURRENT_DATE ;

In the next set of SQL, RANDOM is used to simulate transactions amounts directly in the source master tables. This data would normally be

collected from actual transaction tables and then joined with the source master tables to produce data as shown below:

INS PRODUCT_DAY_ORDER_ODS SELECT PRODUCT_NUMBER AS PRODUCT_ID , PRODUCT_DETAILS AS PRODUCT_DESCRIPTION, DT.DATE_COL AS CONS_ORD_DATE, 1.5* RANDOM ( 1,100) AS ORD_QUANTITY, 423* RANDOM ( 1,100) AS ORDER_AMOUNT FROM

(9)

MST_PROD_SX_MASTER, DATE_TAB DT UNION SELECT PRODUCT_IDENTITY , PRODUCT_NAME, DT.DATE_COL AS CONS_ORD_DATE, 1.5* RANDOM ( 23,77) AS ORD_QUANTITY, 423* RANDOM ( 23,77) AS ORDER_AMOUNT FROM MST_PROD_SY_MASTER, DATE_TAB DT;

The subsequent view generates the aggregate order quantities as it relates to the single golden product master from the cross-referenced source

prod-uct master tables. Note that when recreating this view in your environment, you will replace the ROM_ID’s with the values generated in your system:

REPLACE VIEW AGG_CUST_ORD AS

(

SELECT CONS_ORD_DATE,PRODUCT_ID, PRODUCT_DESCRIPTION, SUM( ORD_QUANTITY) AS SUM_ORD_QTY, SUM( ORDER_AMOUNT) AS SUM_ORD_AMNT FROM (

SELECT ODS.CONS_ORD_DATE AS CONS_ORD_DATE, COALESCE ( G. PRODUCT_ID, ODS.CUSTOMER_ID) AS PRODUCT_ID ,

COALESCE (G.PRODUCT_DESCRIPTION, ODS.PRODUCT_DESCRIPTION) AS PRODUCT_DESCRIPTION, RODV. PARENT_ROKD_VAL1 AS PARENT_ROKD_VAL1, SUM( ODS.ORD_QUANTITY) AS ORD_QUANTITY, SUM( ODS.ORDER_AMOUNT) AS ORDER_AMOUNT

FROM CUSTOMER_DAY_ORDER_ODS ODS INNER JOIN MST_REL_OBJ_DATA_VAL RODV ON ODS. PRODUCT_ID = RODV. CHILD_ROKD_VAL1

INNER JOIN MST_PRODUCT_MASTER G ON G. CUSTOMER_ID = RODV. PARENT_ROKD_VAL1 WHERE RODV.ROM_ID IN ( 1601, 1602)

GROUP BY 1,2,3,4) A GROUP BY 1, 2,3 );

Product_dAy_ordEr_ods

pROdUCT_id pROdUCT_deSCRipTiON CONS_ORd_daTe ORd_QUaNTiTY ORdeR_aMOUNT

SX22 TOpS-X 4/14/2009 52 24534 SX33 T-SHiRT-X 4/14/2009 42 13536 SX44 SHORTS-X 4/14/2009 66 24111 SX66 Tie-X 4/14/2009 45 18612 SX120 JeaNS JaCKeT-X 4/14/2009 76 20304 SY106 Tie-Y 4/14/2009 42 31302