Analytics: Data Warehouse Developer (Siebel 7.

7)

Module 9: Working with Data Warehouse Application Console

Module Objectives
After completing this module you will be able to:
Use the tools and views of the Data Warehouse Application Console (DAC) Describe DAC objects, how they relate to each other, and their roles in Siebel Relationship Management Warehouse (SRMW) ETL

Why you need to know

DAC is used to create, configure, run, and monitor ETL for the SRMW

Module 9: Working with Data Warehouse Application Console

2 of 34

Data Warehouse Application Console

Is a centralized console, providing access to the entire SRMW application to create, configure, and execute ETL
Single command and control point for the SRMW

Provides a framework for the entire life cycle of SRMW implementations Is used to create and manage DAC objects in the DAC repository and to monitor ETL

Module 9: Working with Data Warehouse Application Console

3 of 34

Data Warehouse Application Console Continued

Accesses and updates the DAC repository to:

Register Informatica Servers and source and target databases Automate configuration of ETL for full and incremental load of definable warehouse subject areas Prioritize and load balance ETL workflow execution Compile historical tracking of diagnostic ETL logs Provide restart of ETL execution from point of failure Update database statistics on SRMW tables and indexes

Module 9: Working with Data Warehouse Application Console

4 of 34

DAC Versions

Two licensing versions available for DAC The DAC Lite version:
Standard version that comes with any Analytics application Allows you to build and execute ETL plans Is suitable for small data warehouses little or no customization

The DAC version:

Optional license version Minimizes ETL execution time by using optimization techniques Is suitable for large data warehouses with significant customization

Module 9: Working with Data Warehouse Application Console

5 of 34

DAC User Interface

Menu bar View buttons Top pane tabs DAC Server Monitor icon: orange = running; red = not running; green = executing ETL

Top pane toolbar

Editable list Navigation tree

Bottom pane child tabs

Editable form

Module 9: Working with Data Warehouse Application Console

6 of 34

DAC Views

Are accessed through the View menu or buttons Include:

Design Setup Execute

Views

Tabs

Views

Module 9: Working with Data Warehouse Application Console

7 of 34

Design View

Provides access to create and manage execution plans and related entities
Top pane contains tabs, each of which represents a component of the execution plan, in descending order Bottom pane contains child tabs that relate to the tab selected in the top pane
New entities are edited in the lower pane Execution plan Tasks preceding execution plan Tabs access DAC entities

Editable list of subject areas

Database connections

Source and target tables

Tasks following execution plan

Module 9: Working with Data Warehouse Application Console

8 of 34

Setup View

Provides access to set up DAC system properties, Informatica servers, database connections, and email notification
Set DAC system properties Register Informatica Servers Set up email recipients

Database type: either Source or Warehouse

Tables in the Source database

Module 9: Working with Data Warehouse Application Console

9 of 34

Setup View: DAC System Properties Tab

Is used to enter property values

Module 9: Working with Data Warehouse Application Console

10 of 34

Setup View: DAC System Properties Continued

Properties determine the behavior of the DAC Server Examples:

Property
Analyze Frequency (in days)
Analyze Tables Auto Restart ETL

Description
Number of days before DAC automatically updates database statistics If set to True, DAC Server automatically issues statements to update statistics when a table is truncated and loaded If set to True, DAC Server, automatically restarts ETL after a task failure

During ETL, DAC Server drops all query indexes on targets; if set to True, DAC Server groups all indexes for CreateQueryIndexesAtTheEnd creation after ETL is complete
DropAndCreateIndexes If set to True, DAC Server drops and recreates query indexes during ETL

Module 9: Working with Data Warehouse Application Console

11 of 34

Setup View: Database Connections Tab

Database Connections tab is used to define database connections for Siebel Transactional and SRMW Refresh date corresponds to LAST_REFRESH_DATE column on source table

Date = incremental load No date = full load

Module 9: Working with Data Warehouse Application Console

12 of 34

Execute View

Provides access to run, schedule, and monitor execution plans

Can also be run manually from Design view
Review history

Select and run execution plans

Schedule ETL

Successful run of the Sales execution plan

Manual restart in case of failure

ETL_PROC_WID

List of tasks run as part of the Sales execution plan

Command issued to start task

Task status

Module 9: Working with Data Warehouse Application Console

13 of 34

DAC Objects

The DAC repository stores application objects in a hierarchical format that defines a warehouse application DAC objects include:
Tasks

Unique steps executed during ETL Related to data transformation or database objects Are associated with a set of source and target database tables, calling Informatica workflows
Can also create files, call database stored procedures, and execute SQL or XML scripts or operating system commands

At run time, execute based on whether primary source and target tables are component tables of execution plan

Tables

Physical database tables in the transactional or SRMW databases Can be source, fact, dimension, hierarchy, aggregate, or staging, among others
Module 9: Working with Data Warehouse Application Console
14 of 34

DAC Objects Continued

DAC objects include:

Table groups

Logical grouping of tables Based on a main table that drives the relationship between the other tables in the group Can be star or dimension type group

Subject areas

Logical grouping of table groups Derives component tables from table groups Are included in execution plans

Execution plans

Contains one or more subject areas Plan defined on subject areas that need to be transformed at certain frequencies Derives component tables from subject areas Can be executed manually or scheduled
Module 9: Working with Data Warehouse Application Console
15 of 34

DAC Object Hierarchy with Examples

Execution Plans

Schedules

Subject Areas

Table Groups

Tables

Tasks

Dependencies
16 of 34

Module 9: Working with Data Warehouse Application Console

DAC Object Hierarchy with Example Examples

Complete ETL Monthly Sales Plan

Execution Plans

Schedules

All Subject Areas

Subject Areas

INVOICE FACT GROUP

Table Groups

W_INVOICE_F

Tables

Tasks

Extract for Invoice Fact

Extract for LOV

Dependencies
17 of 34

Module 9: Working with Data Warehouse Application Console

Tasks

Tasks are lowest objects in DAC hierarchy that perform unique ETL steps Tasks are prioritized in queue by DAC Server based on phase
Image Build General Extract Dimension Extract Fact Load Dimension Load Fact Visibility Dimension Hierarchy Slowly Changing Dimension

Module 9: Working with Data Warehouse Application Console

18 of 34

Tasks: Task Definitions

Task Definitions tab is used to configure tasks For example, the Extract for Invoice Fact task extracts the invoice data

Task name

Folder in Informatica repository Task phase Informatica task type

If there is no refresh date for the source table during extract, this command is issued to Informatica Server; otherwise, Command for Incremental Load is issued

Module 9: Working with Data Warehouse Application Console

19 of 34

Tasks: Source and Target Tables Child Tabs

Source Tables and Target Tables tabs are used to specify source and target tables for tasks
S_INVOICE is the main source table for Extract for Invoice Fact task W_INVOICE_FS is the main target table for Extract for Invoice Fact task

Primary source table

Primary target table

Module 9: Working with Data Warehouse Application Console
20 of 34

Tasks: Build Image

The Build Image flag invokes the Change Capture process for the base source tables listed for the task Change Capture is an internal DAC task that populates image tables of the corresponding base source tables

Builds image for S_INVOICE source table

Module 9: Working with Data Warehouse Application Console
21 of 34

Tasks: Build Image Continued

The Build Image flag invokes the Change Capture process for both Primary and Auxiliary source tables
Primary: Primary source of data; track change in a single table Auxiliary: Secondary source of data; track change in more than one table

Module 9: Working with Data Warehouse Application Console

22 of 34

Tasks: Build Image Continued

All Primary or Auxiliary table sources in the Siebel database are assigned an Image Suffix to assist with the change capture process For example, S_ETL_R_IMG_73, S_ETL_I_IMG_73, S_ETL_D_IMG_73 are the image tables for S_INVOICE base source table

73 is the Image Suffix for the S_INVOICE table

Module 9: Working with Data Warehouse Application Console

23 of 34

Tasks: Depends on Tasks Child Tab

Depends on Tasks tab is used to specify prerequisite tasks For example, Extract Missing Codes for List of Values from Invoices is the prerequisite task for the execution of Extract for Invoice Fact

Prerequisite task

Module 9: Working with Data Warehouse Application Console

24 of 34

Tasks: Dependent Tasks Child Tab

The Depends on Tasks tab displays dependent tasks Can be specified in either Depends on Tasks or Dependent Tasks tab depending on which task you are working on
For example, Load into Invoice Fact is a dependent task for Extract for Invoice Fact

Dependent tasks

Module 9: Working with Data Warehouse Application Console

25 of 34

Tables

Physical database tables in the transactional or SRMW databases Tables tab is used to configure tables in DAC Fact or Dimension tables belong to more than one Table Group
W_INVOICE_F is the main table for INVOICE FACT GROUP

List of tasks that source the table List of indexes

Suffix number of any image tables

List of tasks that target the table

Module 9: Working with Data Warehouse Application Console

26 of 34

Table Groups

Logically groups tables Contains a central or main table that drives the logical relationship between itself and other tables in the group For example:
W_INVOICE_F is the main table in the INVOICE FACT GROUP INVOICE FACT GROUP table group contains other component tables

Module 9: Working with Data Warehouse Application Console

27 of 34

Table Groups Continued

Table Groups Tab is used to configure table groups Used to create new table group

Select New to create a new table group

W_SRVREQ_D is a conforming dimension for the Invoice star

W_INVOICE_F is the main table in INVOICE FACT GROUP.

Module 9: Working with Data Warehouse Application Console

28 of 34

Subject Areas

Logical grouping of table groups Consists of one or more table groups

For example, INVOICE FACT GROUP is a member of the All Subject Areas subject area

Subject Areas Tab is used to create new subject areas

Select New to create a new subject area

List of all tables in the subject area

Module 9: Working with Data Warehouse Application Console

29 of 34

Execution Plans

Contains one or more subject areas defined based on business requirements All Subject Areas is a member of the Complete ETL execution plan Execution Plans tab is used to configure execution plans

Select New to create a new subject area Execution plans can be run from the Design view

Preceding tasks include setting values in internal tables

Module 9: Working with Data Warehouse Application Console

30 of 34

Dependency Report

Used to analyze dependencies between tasks that are executed for the selected DAC object To generate a dependency report, select an object and click the Preview button in the Design view
Available for the following objects: table groups, subject areas, and execution plans

Module 9: Working with Data Warehouse Application Console

31 of 34

Resetting the SRMW

To delete Refresh Dates and run a full load of the SRMW

Select Tools > ETL Management > Reset Data Warehouse Confirm that you want to perform a full truncate and load

Module 9: Working with Data Warehouse Application Console

32 of 34

Summary
This module showed you how to:
Use the tools and views of the Data Warehouse Application Console (DAC) Describe DAC objects, how they relate to each other, and their roles in Siebel Relationship Management Warehouse (SRMW) ETL

Module 9: Working with Data Warehouse Application Console

33 of 34

Lab

In the lab you will:

Explore the DAC

Module 9: Working with Data Warehouse Application Console

34 of 34