Hyundai motor brazil crm system impl mentation project
This presentation is the property of its rightful owner.
Sponsored Links
1 / 56

Hyundai Motor Brazil CRM System Implémentation Project PowerPoint PPT Presentation


  • 63 Views
  • Uploaded on
  • Presentation posted in: General

Hyundai Motor Brazil CRM System Implémentation Project. Project Code : P-12-0605 July, 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.

Download Presentation

Hyundai Motor Brazil CRM System Implémentation Project

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


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


Hyundai motor brazil crm system impl mentation project

Revision History


Hyundai motor brazil crm system impl mentation project

Agenda

  • ETL Process Overview

  • ETL Components

  • ETL Mappings and Source Tables

  • Appendix

    • Star Schema Diagrams

    • B. Mapping Type Prefix


Hyundai motor brazil crm system impl mentation project

Agenda

  • ETL Process Overview

  • ETL Components

  • ETL Mappings and Source Tables

  • Appendix

    • Star Schema Diagrams

    • Mapping Type Prefix


Hyundai motor brazil crm system impl mentation project

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


Hyundai motor brazil crm system impl mentation project

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


Hyundai motor brazil crm system impl mentation project

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


Hyundai motor brazil crm system impl mentation project

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


Hyundai motor brazil crm system impl mentation project

Agenda

  • ETL Process Overview

  • ETL Components

  • ETL Mappings and Source Tables

  • Appendix

    • Star Schema Diagrams

    • Mapping Type Prefix


Hyundai motor brazil crm system impl mentation project

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


Hyundai motor brazil crm system impl mentation project

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


Hyundai motor brazil crm system impl mentation project

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


Hyundai motor brazil crm system impl mentation project

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


Hyundai motor brazil crm system impl mentation project

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


Hyundai motor brazil crm system impl mentation project

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


Hyundai motor brazil crm system impl mentation project

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


Hyundai motor brazil crm system impl mentation project

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


Hyundai motor brazil crm system impl mentation project

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


Hyundai motor brazil crm system impl mentation project

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


Hyundai motor brazil crm system impl mentation project

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


Hyundai motor brazil crm system impl mentation project

Agenda

  • ETL Process Overview

  • ETL Components

  • ETL Mappings and Source Tables

  • Appendix

    • Star Schema Diagrams

    • Mapping Type Prefix


Hyundai motor brazil crm system impl mentation project

3. ETL Mappings and Source Tables

3-1. Common ETL Mappings and Source Tables


Hyundai motor brazil crm system impl mentation project

3. ETL Mappings and Source Tables

3-1. Common ETL Mappings and Source Tables


Hyundai motor brazil crm system impl mentation project

3. ETL Mappings and Source Tables

3-1. Common ETL Mappings and Source Tables


Hyundai motor brazil crm system impl mentation project

3. ETL Mappings and Source Tables

3-1. Common ETL Mappings and Source Tables


Hyundai motor brazil crm system impl mentation project

3. ETL Mappings and Source Tables

3-1. Common ETL Mappings and Source Tables


Hyundai motor brazil crm system impl mentation project

3. ETL Mappings and Source Tables

3-1. Common ETL Mappings and Source Tables


Hyundai motor brazil crm system impl mentation project

3. ETL Mappings and Source Tables

3-1. Common ETL Mappings and Source Tables


Hyundai motor brazil crm system impl mentation project

3. ETL Mappings and Source Tables

3-2. Call Center ETL Mappings and Source Tables


Hyundai motor brazil crm system impl mentation project

3. ETL Mappings and Source Tables

3-2. Call Center ETL Mappings and Source Tables


Hyundai motor brazil crm system impl mentation project

3. ETL Mappings and Source Tables

3-2. Call Center ETL Mappings and Source Tables


Hyundai motor brazil crm system impl mentation project

3. ETL Mappings and Source Tables

3-2. Call Center ETL Mappings and Source Tables


Hyundai motor brazil crm system impl mentation project

3. ETL Mappings and Source Tables

3-2. Call Center ETL Mappings and Source Tables


Hyundai motor brazil crm system impl mentation project

3. ETL Mappings and Source Tables

3-2. Call Center ETL Mappings and Source Tables


Hyundai motor brazil crm system impl mentation project

3. ETL Mappings and Source Tables

3-2. Call Center ETL Mappings and Source Tables


Hyundai motor brazil crm system impl mentation project

3. ETL Mappings and Source Tables

3-3. Marketing ETL Mappings and Source Tables


Hyundai motor brazil crm system impl mentation project

3. ETL Mappings and Source Tables

3-3. Marketing ETL Mappings and Source Tables


Hyundai motor brazil crm system impl mentation project

3. ETL Mappings and Source Tables

3-3. Marketing ETL Mappings and Source Tables


Hyundai motor brazil crm system impl mentation project

3. ETL Mappings and Source Tables

3-3. Marketing ETL Mappings and Source Tables


Hyundai motor brazil crm system impl mentation project

3. ETL Mappings and Source Tables

3-3. Marketing ETL Mappings and Source Tables


Hyundai motor brazil crm system impl mentation project

3. ETL Mappings and Source Tables

3-3. Marketing ETL Mappings and Source Tables


Hyundai motor brazil crm system impl mentation project

3. ETL Mappings and Source Tables

3-4. Sales Match ETL Mappings and Source Tables


Hyundai motor brazil crm system impl mentation project

3. ETL Mappings and Source Tables

3-4. Sales Match ETL Mappings and Source Tables


Hyundai motor brazil crm system impl mentation project

3. ETL Mappings and Source Tables

3-5. Lead Status ETL Mappings and Source Tables


Hyundai motor brazil crm system impl mentation project

Agenda

  • ETL Process Overview

  • ETL Components

  • ETL Mappings and Source Tables

  • Appendix

    • Star Schema Diagrams

    • Mapping Type Prefix

Analytics Star Schemas


Hyundai motor brazil crm system impl mentation project

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


Hyundai motor brazil crm system impl mentation project

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.


Hyundai motor brazil crm system impl mentation project

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.


Hyundai motor brazil crm system impl mentation project

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.


Hyundai motor brazil crm system impl mentation project

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

A. Star Schema Diagrams

A-6. Service Request Star Schema

The Service Request star schema allows you to analyze Service Requests by Customer, Channel, Product, Time, and Geography. The Service Request fact table is based on the S_SRV_REQ table.


Hyundai motor brazil crm system impl mentation project

A. Star Schema Diagrams

A-7. Vehicle Star Schema

The Vehicle star schema allows you to analyze the automotive sales data by vehicle, dealer, contact, household, geography, and time. The vehicle fact table is based on S_VHCL_SALES table.


Hyundai motor brazil crm system impl mentation project

A. Star Schema Diagrams

A-8. Sales Match Star Schema

The Sales Match star schema allows you to analyze the sales match data by dealer, contact, lead, and time. The Sales Match fact table is based on W_VEHICLE_F, WC_OPTY_F, W_CAMP_HIST_F table.


Hyundai motor brazil crm system impl mentation project

B. Mapping Type Prefix

B-1. SDEINC_FindAux_

- Identify the new or modified data in OLTP database in the primary tables


Hyundai motor brazil crm system impl mentation project

B. Mapping Type Prefix

B-2. SDE (Source-Dependent Extraction)

- Extract data from the Siebel OLTP database and load it into the staging tables.


Hyundai motor brazil crm system impl mentation project

B. Mapping Type Prefix

B-3. SIL (Source-Independent Load)

- Query data from the staging tables, perform final transformations, and load the dimension and fact tables


  • Login