Defining ETL Parameters

Certain EPM products require that you specify input parameters for ETL jobs prior to running the jobs. For example, some parameters specify row-selection criteria and load methodologies.

This chapter discusses how to define ETL parameters for:

Click to jump to parent topicDefining ETL Parameters for the Planning and Budgeting Analytical Application

This section discusses how to define financial asset item parameters for the Planning and Budgeting analytical application.

Click to jump to top of pageClick to jump to parent topicPage Used to Define Financial Asset Item Parameters

Page Name

Definition Name

Navigation

Usage

Financial Asset Item Setup

PF_FIN_AST_SETUP

EPM Foundation, EPM Setup, ETL Map Parameters, Financial Asset Item Setup, Financial Asset Item Setup

Enter the setID for the rows to be deleted in the OWE table PS_BP_ASSET_ITEMS and reloaded from BD_ASSET_ITEMS.

Click to jump to top of pageClick to jump to parent topicSetting Up Financial Asset Item

Access the Financial Asset Item Setup page (EPM Foundation, EPM Setup, ETL Map Parameters, Financial Asset Item Setup, Financial Asset Item Setup).

SetID

Enter the setID associated with the asset data.

Del. Flag (Y/N) [Delete Flag (Y/N)]

Check to indicate that the rows in the target table PS_BP_ASSET_ITEMS that match the setID on this page are to be deleted.

Click to jump to parent topicDefining ETL Parameters for the EPM Warehouses

This section describes how to set ETL parameters for data marts within the FMS and CRM warehouses.

Click to jump to top of pageClick to jump to parent topicPages Used to Define ETL Parameters

Page Name

Definition Name

Navigation

Usage

Financial Mart Account Setup

PF_FIN_ACCT_SETUP

EPM Foundation, EPM Setup, ETL Map Parameters, Financial Mart Account Setup, Financial Mart Account Setup

Enter tree and node details that represent accounts on which Profitability analysis is performed.

Financial Mart GC Cled Setup

PF_FIN_CLED_SETUP

EPM Foundation, EPM Setup, ETL Map Parameters, Financial Mart GC Cled Setup, Financial Mart GC Cled Setup

Enter values such as Business Unit, Scenario, and Period that determine the rows to be deleted from Multidimensional Warehouse table PS_F_CLEDGER and reloaded from PS_GC_CLED_MGT_F00. in the MDW table F_CLEDGER.

Financial Mart GC Flow Setup

PF_FIN_FLOW_SETUP

EPM Foundation, EPM Setup, ETL Map Parameters, Financial Mart GC Flow Setup, Financial Mart GC Flow Setup

Enter values such as Business Unit, Scenario, and Period that determine the rows to be deleted from MDW table PS_F_FLOWS and reloaded from PS_GC_FLOW_MGT_F00

Financial Rolling Average Setup

PF_FIN_RAVG_SETUP

EPM Foundation, EPM Setup, ETL Map Parameters, Financial Rolling Avg. Setup, Financial Rolling Average Setup

Enter values that are used to determine rolling averages for loading PS_F_LEDGER.

AP Mart Aging Setup

PF_FIN_AP_SETUP

EPM Foundation, EPM Setup, ETL Map Parameters, AP Mart Aging Setup, AP Mart Aging Setup

Enter values that are used in calculating the AP Aging process.

AR Mart Aging Setup

PF_FIN_AR_SETUP

EPM Foundation, EPM Setup, ETL Map Parameters, AR Mart Aging Setup, AR Mart Aging Setup

Enter values that are used in calculating the AR Aging process.

Customer Segment Ranking Map

PF_CUSEG_RANK_MAP

EPM Foundation, EPM Setup, ETL Map Parameters, Customer Segment Rank Map, Customer Segment Ranking Map

Force rank customer segments.

Click to jump to top of pageClick to jump to parent topicDefining Account Parameters for the GL and Profitability Data Mart

Access the Financial Mart Account Setup page (EPM Foundation, EPM Setup, ETL Map Parameters, Financial Mart Account Setup, Financial Mart Account Setup).

Use this page to enter details of nodes that belong to the Account tree. Only accounts created under these nodes are loaded by the ETL process into the PS_F_PROFITABILITY table.

SetID

Enter the setID for the source data.

Tree Name

Enter the name of the Account tree.

Revenue Node

Enter the Account tree node that relates to revenue accounts.

Dir. Expense Node (Direct Expense Node)

Enter the Account tree node that relates to direct expense accounts.

Indir. Exp Node (Indirect Expense Node)

Enter the Account tree node that relates to indirect expense accounts.

Click to jump to top of pageClick to jump to parent topicDefining Global Consolidations C-Ledger Parameters for the GL and Profitability Data Mart

Access the Financial Mart GC Cled Setup page (EPM Foundation, EPM Setup, ETL Map Parameters, Financial Mart GC Cled Setup, Financial Mart GC Cled Setup).

The sequence number on this page indicates that the process accepts multiple sets of business unit, scenario, fiscal year, and accounting period as input. The ETL job processes each set of input data in the sequence indicated.

Load Sequence

Enter the sequence in which the ETL job processes the input parameters. The ETL process accepts multiple sets of business unit, scenario, fiscal year and accounting period as input. The ETL job processes each set of input data in the sequence indicated.

Business Unit

Enter the performance business unit.

Scenario ID

Enter the scenario ID.

Fiscal Year

Enter the fiscal year.

Accounting Period

Enter the accounting period.

Click to jump to top of pageClick to jump to parent topicDefining Global Consolidations Flow Parameters for the GL and Profitability Data Mart

Access the Financial Mart GC Flow Setup page (EPM Foundation, EPM Setup, ETL Map Parameters, Financial Mart GC Flow Setup, Financial Mart GC Flow Setup).

Load Sequence

Enter the sequence in which the ETL job processes the input parameters. The ETL process accepts multiple sets of business unit, scenario, fiscal year and accounting period as input. The ETL job processes each set of input data in the sequence indicated.

Business Unit

Enter the performance business unit.

Scenario ID

Enter the scenario ID.

Fiscal Year

Enter the fiscal year.

Accounting Period

Enter the accounting period.

Click to jump to top of pageClick to jump to parent topicDefining Financial Rolling Average Parameters for the GL and Profitability Data Mart

Access the Financial Rolling Average Setup page (EPM Foundation, EPM Setup, ETL Map Parameters, Financial Rolling Avg. Setup, Financial Rolling Average Setup).

Basis for Rolling Avg. Fact (Basis for Rolling Average Fact)

Select the basis for the Rolling Average fact. Values are:

Beginning Period Amount: The ETL process uses the Beginning Amount from the PS_F_LEDGER table to calculate the rolling average.

Ending Period Amount: The ETL process uses the End Amount from the PS_F_LEDGER table to calculate the rolling average.

Net Period Amount: The ETL process uses the Net Amount from the PS_F_LEDGER table to calculate the rolling average.

Rolling Average Periods

Enter the number of periods used in calculating the average value.

Click to jump to top of pageClick to jump to parent topicDefining Aging Parameters for the Payables Data Mart

Access the AP Mart Aging Setup page (EPM Foundation, EPM Setup, ETL Map Parameters, AP Mart Aging Setup, AP Mart Aging Setup).

SetID

Enter the setID associated with data to be used for the Aging process

Aging ID

Enter the aging ID, for example MONTH.

Calendar ID

Enter the calendar ID.

Accounting Period

Enter the accounting period.

Incl Draft flag (Include Draft flag)

Check to include drafts in AP Aging process.

Aging Date Type

Enter the Aging Date Type. Values are:

A: The application uses the Accounting Date.

T: The applications uses Invoice Date.

Fiscal Year

Enter the fiscal year.

Incl PrePay flag (Include PrePay flag)

Enter the value to indicate whether to include prepay in the AP Aging process. Values are:

Y: Include prepay.

N: Do not include prepay.

Unrecord Liab Ind (Unrecorded Liability Indicator)

Enter the value to indicate whether to include unrecorded liability in the AP Aging process Values are:

Y: Include unrecorded liability.

N: Do not include unrecorded liability.

Effective Date

Enter the effective date of the record.

Source System Identification

Enter the name of the source system.

Click to jump to top of pageClick to jump to parent topicDefining Aging Parameters for the Receivables Data Mart

Access the AR Mart Aging Setup page (EPM Foundation, EPM Setup, ETL Map Parameters, AR Mart Aging Setup, AR Mart Aging Setup).

SetID

Enter the setID for the data used for the AR Aging process.

Aging ID

Enter the number of days used for days sales outstanding (DSO) in the form of a band, for example 30–60.

Fiscal Year

Enter the fiscal year.

Incl Grace Days (Include Grace Days)

Enter the value to indicate whether to include grace days in the AR Aging process Values are:

Y: Include grace days.

N: Do not include grace days.

DSO Fiscal Year (Days Sales Outstanding Fiscal Year)

Enter the fiscal year used as input to DSO.

Bad Debt Reason

Enter the reason code for bad debt.

Tot AR Specialist (Total Accounts Receivable Specialist)

Enter the number of AR specialists.

Effective Date

Enter the effective date of the record.

Accounting Period

Enter the accounting period.

Sales History ID

Enter the customer sales history ID.

DSO Acct Period (Days Sales Outstanding Accounting Period)

Enter the accounting period used as input to DSO.

Tot Credit Analyst (Total Credit Analyst)

Enter the number of credit analysts.

Tot Collect Analyst (Total Collect Analyst)

Enter the number of collection analysts.

Source System Identification

Enter the name of the source system.

Click to jump to top of pageClick to jump to parent topicDefining Customer Segment Ranking Parameters for the Customer Segment Data Mart

If you perform customer segment analysis, you may need to define parameters to set up customer segment ranking prior to moving data into the MDW. You must do this if you plan to populate the Segment dimension and perform customer segment ranking. The Segment dimension source the marketing tables RA_LIST and RA_LIST_REC, which come from the OWS.

If the OWS tables RA_LIST and RA_LIST_REC are populated and you plan to analyze customers by segment, you must rank customers on the Customer Segment Ranking Map page before you populate the Segment dimension in the MDW. If you have not populated these source marketing tables, you do not need to set up the customer ranking parameters.

Accessing the Customer Segment Ranking Map page

Access the Customer Segment Ranking Map page (EPM Foundation, EPM Setup, ETL Map Parameters, Customer Segment Rank Map, Customer Segment Ranking Map).

Use this page to enter the numeric ranking for each customer segment. You must use one unique ranking for each segment. If a customer belongs to more than one segment within a segment group, the ETL process puts that customer in the highest ranking (lowest number) segment within the selected segment group.

If you do not find any values on the search page for the Customer Segment Ranking Map page, you do not need to use this page; that is, there are no customer segments for you to rank.

To retrieve data to populate this page, run the OWS ETL jobs J_STAGE_PS_RA_LIST and J_STAGE_PS_RA_LIST_REC.

SetID

Displays the setID for the customer segment group.

Source ID

Displays the source ID in the source database.

Segment Group

Displays the identifier for the segment group.

Segment

Displays the identifier for the segment in the segment group.

Note. The first time you populate the Segment dimension, you are prompted to enter a segment group value. This segment group value remains, unless you must change the value for subsequent runs of the ETL job to populate the Segment dimension. To change the value of the segment group, in DataStage Designer, click the job property J_DIM_PS_P_SEGMENT, click the Parameter tab, and enter the new value for Segment_Group.

Name

Displays the segment name.

Rank

Enter the unique numeric ranking of the segment within the segment group.