Hyundai Motor Brazil CRM System Implémentation Project

Hyundai Motor Brazil CRM System Implémentation Project Project Code : P-12-0605July, 2012 Version: 0.9 Solution Design – ETL

Revision History

Agenda • ETL Process Overview • ETL Components • ETL Mappings and Source Tables • Appendix • Star Schema Diagrams • B. Mapping Type Prefix

Agenda • ETL Process Overview • ETL Components • ETL Mappings and Source Tables • Appendix • Star Schema Diagrams • Mapping Type Prefix

1. ETL Process Overview 1-1. Overview ETL Process • Data is loaded from the Siebel transactional database into the SRMW by extraction, transformation, and loading (ETL) process • Run using the DAC client • Running an ETL initiates: • Tasks run by the DAC Server to prepare internal tables • A series of Informatica mappings and transformations executed by the Informatica Server as requested by the DAC Server • The Informatica Server accesses metadata in the Informatica repository to define how the data moves between the Siebel transactional database and the SRMW

1. ETL Process Overview 1-2. ETL Process Steps • At a high level, the ETL process is completed in three steps • DAC Server executes as many steps in parallel as possible Loads the rearranged data into fact and dimension tables in the SRMW Reads the data necessary for analysis from the Siebel transactional database Extract Transform Load Cleans the data and rearranges as required for the star schema

1. ETL Process Overview 1-2. ETL Process Steps – cont’d • Step 1: Extract and load staging tables • Extracts and consolidates data from the transactional system and writes to the SRMW staging tables • Step 2: Transform data • Transforms data in staging tables in preparation for load • Computes calculated fields • Step 3: Load dimension and fact tables • Generates and maintains warehouse primary keys • Loads hierarchy tables • Loads dimension and fact tables

1. ETL Process Overview 1-3. ETL Process Summary • Uses Source Dependent Extraction (SDE) routines to extract data • Loads data into staging tables within the SRMW • Uses Source Independent Load (SIL) routines to transform data into star schemas within the SRMW Source Dependent Extraction Source Dependent Extract Siebel DB Full & Refresh Extraction SRMW Staging Tables Siebel DB Source Dependent Extract Source 2 Source Independent Loading SRMW Source 2 Source Dependent Extract Source 3 Source 3

Siebel DB 2. ETL Components 2-1. Siebel Analytics Architecture Client Siebel WebServer Extension Siebel Repository Siebel Enterprise Server Read Web Server Web Catalog Analytics Web ETL Repository DAC Repository DAC Server Return Analytics Results Authenticate Read/Write Informatica Repository Siebel Analytics Server Informatica Server (ETL) Read Analytics Repository Read/Write Query Write Other Data Sources SRMW

DAC Repository DAC Server DAC Client Siebel DB Siebel RMW Informatica Server (ETL) Extract Load Workflow Informatica Repository 2. ETL Components 2-2. Data Warehouse Load Process • The DAC generates a metadata driven ETL plan and executes ETL tasks • The Informatica Server performs ETL using source-to-target mappings and transformations • Extraction—source is Siebel transactional database • Transformation—cleaning, translations, aggregation (facts) • Load—target is SRMW star schemas

Data Warehouse Tables Siebel DB Siebel RMW 2. ETL Components 2-3. Process Flow • Administrator initiates ETL in DAC Client • DAC Server issues ETL tasks • Informatica-related ETL tasks are issued against Informatica Server • Informatica Server accesses workflows in Informatica repository • Informatica Server processes the workflows • Data is extracted from the Siebel transactional database • Data is transformed and then loaded in the Siebel RMW 2 6 1 DAC Repository DAC Client DAC Server Load Metadata About ETL Processing, Source, and Target Tables 4 Mappings and Metadata Informatica Server (ETL) 5 Extract Informatica Repository 3

2. ETL Components 2-4. Components • DAC Client • Used to schedule, monitor, configure, and customize SRMW ETL • Accesses metadata about ETL mappings, dependencies, and tables in the DAC repository • DAC Server • Organizes ETL tasks for processing • Informatica Repository Server • Accepts connections to the Informatica repository from Informatica server(s) and clients • Informatica Server • Processes ETL

2. ETL Components 2-5. DAC Tasks • Are stored in the DAC repository • Can be of four types: • Informatica: Calls an Informatica workflow used to process ETL • External Program: Calls an operable program on the operating system where the DAC Server is running • SQL File: Calls a SQL script in .xml or .sql format • Stored Procedure: Calls a stored procedure defined on the DAC repository’s defined databases • Example: Extract and load tasks for the Revenue fact table Extract and load tasks Name of the Informatica workflow called by the task Folder in theInformatica repository Source and target databases

2. ETL Components 2-5. DAC Tasks – cont’d • The DAC server prioritizes ETL tasks by phases as follows (from high to low priority): • Image Build • Manage change capture tables for incremental loads using DAC tasks and Source Dependent Incremental (SDEINC) Informatica mappings • General • Load internal tables with required data • Extract Dimension • Use Source Dependent Extract (SDE) Informatica mappings to extract data from Siebel transactional tables and load dimension staging tables • Extract Fact • Use SDE mappings to extract data from Siebel transactional tables and load fact staging tables

2. ETL Components 2-5. DAC Tasks – cont’d • Load Dimension • Use Source Independent Load (SIL) Informatica mappings to load data from dimension staging tables into dimension tables • Load Fact • Use SIL mappings to load data from fact staging tables into fact tables • Visibility • Use SDE and SIL mappings to extract and load security information • Dimension Hierarchy • Use SIL mappings to load dimension hierarchy tables • Slowly Changing Dimension • Use SIL mappings to load slowly changing dimension tables • Aggregate • Use SIL mappings to load aggregate tables

2. ETL Components 2-6. Tables • Various tables in the Siebel Relationship Management Warehouse and Siebel transactional database support ETL processing • Internal tables • Change Capture tables • Staging tables • Hierarchy tables

2. ETL Components 2-6. Tables 2-6-1. Internal Tables • Are located in the Siebel Relationship Management Warehouse • Assist in the load process and contain metadata for the data warehouse including parameters used in the ETL process • Examples • W_ETL_RUN_S stores a record per every ETL run • W_PARAM_G stores parameters and defaults used in ETL • W_ETL_RUN_S stores a record per every ETL run • W_EXCH_RATE_G stores exchange rates • W_COSTLST_G stores costlist data • W_DUAL_G is used to generate calculated values • W_LST_OF_VAL_G stores list of values used in ETL process

2. ETL Components 2-6. Tables 2-6-2. Change Capture Tables • Store information about changes to data in transactional system • Exist in the Siebel transactional database • Used during incremental updates of the data warehouse • For each source (S_) table processed in ETL, there is: • One Type R (S_ETL_R_IMG_*) table • One Type I (S_ETL_I_IMG_*) table • One Type D (S_ETL_D_IMG_*) table

2. ETL Components 2-7. ETL Processing Summary Siebel Transactional Database Siebel Relationship Management Warehouse Change Capture TablesS_ETL_I_IMG_* S_ETL_R_IMG_* S_ETL_D_IMG_* Siebel DB Tables S_S_*_X DimensionTables W_*_DW_*_DX ChangeCaptureProcess Staging Tables W_*_DSW_*_FSW_*_DSXW_*_FSX Extract Fact Tables W_*_F W_*_FX Hierarchy, SCD, and Aggregate Tables W_*_DH W_*_A W_*_SCD ETL Internal Tables Data Sources ETL Runs Language Settings Currency Settings

3. ETL Mappings and Source Tables 3-1. Common ETL Mappings and Source Tables

3. ETL Mappings and Source Tables 3-2. Call Center ETL Mappings and Source Tables

3. ETL Mappings and Source Tables 3-3. Marketing ETL Mappings and Source Tables

3. ETL Mappings and Source Tables 3-4. Sales Match ETL Mappings and Source Tables

3. ETL Mappings and Source Tables 3-5. Lead Status ETL Mappings and Source Tables

Agenda • ETL Process Overview • ETL Components • ETL Mappings and Source Tables • Appendix • Star Schema Diagrams • Mapping Type Prefix Analytics Star Schemas

A. Star Schema Diagrams A-1. Activity Star Schema The Activity star schema allows you to analyze Activities by employee, customers, partner, service request, opportunities, and service region. The Activity fact is based on the S_EVT_ACT table and brings in Activity types

A. Star Schema Diagrams A-2. Asset Star Schema Assets are instances of Products and represent Financial accounts or Insurance policies for financial services customers. The asset star allows financial institutions to analyze critical metrics such as account balances, revenue, profit, policy premiums and other key asset-level metrics by critical business dimensions such as customers, households, Time, Geography, Product, Employees, and Branches. The grain of the fact is an asset (financial account or Insurance Policy) held by a retail (contact) or institutional (company) customer. The asset fact and asset dimension tables are based on the S_ASSET Table.

A. Star Schema Diagrams A-3. Campaign History Star Schema The Campaign History star schema allows you to analyze the number of people targeted for campaigns by program, campaign, stage, segment, vendor, and wave. The Campaign History fact table is based on the S_CAMP_CON table.

A. Star Schema Diagrams A-4. Person Star Schema The Person star schema allows you to analyze detailed Contact and Account relationships. The Person fact table is based on the S_PARTY_PER table. NOTE: The fact table includes only Contacts, not Prospects. The Contact dimension, however, contains both Prospects and Contacts. In order to analyze Prospects use the Contact dimension.

A. Star Schema Diagrams A-5. Response Star Schema The Response star schema allows you to analyze responses by customer, program, segment, wave, and channel. The Response fact table is based on the S_COMMUNICATION table.

Hyundai Motor Brazil CRM System Implémentation Project