Overview of PeopleSoft Data Warehouse Implementation

August 3, 2006

EPM Architecture

Multidimensional Warehouse

Facts – typically numeric values to quantify or calculate a company’s activities. In star schema development it is the central table used to connect dimensions

Multidimensional Warehouse

Dimensions - Allow analytics across subject matter areas Uses Conformed dimensions - dimensions that mean

the same thing in every possible fact table to which they can be joined—and therefore the same thing in every functional warehouse.

Common (Calendar, Time, Business Unit, Time Zone, Unit of Measure, Currency, Language)

Shared (Department, Item, Account, Person, Jobcode, etc.) Subject Area (Billing Status –GL & Prof, Aging Category-

Payables)

PeopleSoft Mart

Mart refers to a PeopleSoft product that contains specific subject areas related to one of the delivered PeopleSoft functional warehouses.

Marts are derived from the MDW and are modeled to support analytic requirements, but not limit you to reporting only in that subject area.

Must license a supported reporting tool separately (e.g. Hyperion Reports) to access data – open reporting structure

Supply Chain Warehouse

Supports reporting for the following business processes Order Fulfillment Procurement

Datamarts Procurement Spend Inventory Sales Orders

Financial Warehouse

Supports reporting for the following business processes Procurement Financial Control and

Reporting Project Management Financial Control and

Reporting

Datamarts

Payables General Ledger and

Profitability

Hyperion Integration

Implementation Consideration

Our project timeline and scope will be dependent on Transaction applications.

Understanding Source data, customizations and configuration is necessary to validate delivered transformations, dimensions, and facts.

Overview of reporting environment

Datamarts

Payables General Ledger and

Profitability

Overview of reporting environment

Rept Rqmt 1

Rept Rqmt 2

Rept Rqmt 3 Rept Rqmt 4Rept Rqmt 5

Rept Rqmt 6

PS_JRNL_LN PS_F_JOURNALPS_JRNL_HEADER

PS_BU_LED_GRP_TBLPS_S_SET_CNTRL_RECPS_D_DEPTPS_D_PRODUCTPS_D_PRJPS_D_PROGRAM_FDMPS_D_BUSINESS_UNITPS_D_DET_PERIODPS_D_BUDGET_REFPS_D_OPER_UNITPS_D_ACCOUNTPS_D_ACCOUNTPS_D_LEDGERPS_D_JRNL_SOURCEPS_D_FUNDPS_D_FUNDPS_D_STAT_CODEPS_D_SCENARIOPS_D_CHARTFIELD1PS_D_CHARTFIELD2PS_D_CHARTFIELD3PS_D_GL_ADJ_TYPEPS_D_CLASS_FIELD PS_D_BOOK_CODE PS_D_PATTERN_DAY

General Ledger and Profitability Mart helps finance organizations analyze their organization's assets,liabilities, equity, and profit and loss position to better understand the factors that influence profitability.

GL & Profitability

F_JOURNAL

D_DEPT

D_JRNL_SOURCE D_ACCOUNT

D_DET_PERIOD

PS_LEDGER PS_F_LEDGERPS_LEDGER_BUDG

PS_S_SET_CNTRL_RECPS_BU_LED_GRP_TBLPS_D_DET_PERIODPS_D_PRJPS_D_LEDGERPS_D_ACCOUNTPS_D_ACCOUNTPS_D_DEPT_TBLPS_D_SUBLEDGERPS_D_BUSINESS_UNITPS_D_FUND PS_D_PRODUCT PS_D_CHARTFIELD2PS_D_BUDGET_REFPS_D_STAT_CODEPS_D_BOOK_CODEPS_D_CHARTFIELD1PS_D_OPER_UNITPS_D_PROGRAM_FDM PS_D_GL_ADJ_TYPE PS_D_CLASS_FIELD PS_D_CHARTFIELD3 PS_F_LEDGERPS_P_RAVG_SETUP

General Ledger and Profitability Mart helps finance organizations analyze their organization's assets,liabilities, equity, and profit and loss position to better understand the factors that influence profitability.

GL & Profitability

F_LEDGER

D_DEPT_TBL

D_BOOK_CODE D_ACCOUNT

D_DET_PERIOD

PS_PF_LEDGER_F00 PS_F_PROFITABILITY

PS_SET_CNTRL_RECPS_PF_SCENARIO_DFNPS_D_DET_PERIOD_EPS_D_BUS_UNIT_PFPS_D_ACCOUNTPS_D_DEPTPS_D_PRJPS_D_OPER_UNITPS_D_FUNDPS_FUND_TBLPS_D_PRODUCTPS_D_PF_SOURCE PS_D_CUSTOMERPS_D_SCENARIOPS_D_CHARTFIELD1PS_D_CHARTFIELD2PS_D_CHARTFIELD3PS_D_ABM_OBJECTPS_D_CHANNELPS_D_DIMENSION1PS_DIMENSION1_TBLPS_D_DIMENSION3PS_DIMENSION3_TBLPS_D_DIMENSION2PS_DIMENSION2_TBLPS_R_ACCOUNT

General Ledger and Profitability Mart helps finance organizations analyze their organization's assets,liabilities, equity, and profit and loss position to better understand the factors that influence profitability.

GL & Profitability

F_PROFITABILITY

D_ABM_OBJECT

D_PRJ D_ACCOUNT

D_DET_PERIOD

ACCOUNTS PAYABLE (AP)Source Table Name Target Table Name Lookup Table Name

PS_VCHR_ACCTG_LINE PS_F_AP_ACCOUNT_LNPS_VOUCHER

PS_S_SET_CNTRL_RECPS_D_BUSINESS_UNITPS_D_BUSINESS_UNITPS_D_PRJPS_BU_LED_GRP_TBLPS_D_LEDGERPS_D_ACCOUNTPS_D_OPER_UNITPS_D_PROGRAM_FDMPS_D_CLASS_FIELDPS_D_CHARTFIELD1PS_D_CHARTFIELD2PS_D_DT_PATTERNPS_D_DEPTPS_D_BUDGET_REFPS_D_STAT_CODEPS_D_CHARTFIELD3PS_D_FUNDPS_D_SUPPLIERPS_D_PRODUCTPS_D_PATTERN_DAY

Payables

F_AP_ACCOUNT_LN

D_DEPT

D_BU_LED_GRP_TBL D_ACCOUNT

D_PATTERN_DAY

Payables Mart provides information about suppliers, vouchers, match exceptions, and payments to help youclosely manage your cash position.

PS_VOUCHER PS_F_AP_TRANPS_PAYMENT_TBL

PS_S_SET_CNTRL_RECPS_D_SUPPLIER PS_D_BUSINESS_UNITPS_D_DAYPS_D_PERSON_APOPID PS_D_AP_DOC_TYPEPS_D_AP_VTR_TYPEPS_D_APPOST_STATUS PS_D_PYMNT_TRMSPS_PYMNT_VCHR_XREFPS_D_AP_VTR_STAT PS_S_BUS_UNIT_APPS_D_PERSON_APOPID PS_D_AP_DOC_TYPEPS_D_AP_PTR_TYPE PS_D_SUPPLIERPS_D_PYMNT_MTHDPS_D_BANK_ACCTPS_D_AP_PTR_STATPS_D_APPOST_STATUS

Payables

F_AP_TRAN

D_PERSON_AP_OPID

D_AP_DOC_TYPE D_ACCOUNT

D_DAY

Payables Mart provides information about suppliers, vouchers, match exceptions, and payments to help youclosely manage your cash position.

D_AP_VTR_TYPE

D_AP_PTR_TYPE

PS_VCHR_MTCH_EXCPT PS_F_VCHR_MTCH_EXP PS_VOUCHER

PS_S_SET_CNTRL_RECPS_D_BUSINESS_UNITPS_D_MATCH_RULEPS_D_DAYPS_D_PERSON_APOPIDPS_D_SUPPLIER

Payables

F_VCHR_MTCH_EXP

D_SUPPLIER

D_PERSON_APOPID D_ MATCH_RULE

D_DAY

Payables Mart provides information about suppliers, vouchers, match exceptions, and payments to help youclosely manage your cash position.

PS_CM_ACCTG_LINE PS_F_INV_LDGR PS_S_SET_CNTRL_REC

PS_TRANSACTION_INV PS_D_LOT

PS_D_BUS_UNIT

PS_X_DEPLETION_E

PS_D_UOM

PS_D_INVST_REASON

PS_D_INV_ITEM

PS_D_DAY

Inventory

F_INV_LDGR

D_UOM D_ INV_ITEM

D_LOT

Inventory Mart enables you to analyze inventory accuracy, demand, movement, and turns, so you can shaveexcess without endangering your flexibility.

D_DAY

PS_TRANSACTION_INV PS_F_INV_TRANS PS_S_SET_CNTRL_REC

PS_DEMAND_INV

PS_SHIP_INF_INV

PS_PHYSICAL_INV

PS_SF_ISS_TRANSFER

PS_DEMAND_INF_INV

PS_RECV_LN_SHIP

PS_PO_LINE_DISTRIB

PS_RECV_LN_DISTRIB

PS_BU_ITEMS_INV

PS_D_DAY

PS_D_INV_LOCATION

PS_D_BUSINESS_UNIT

PS_D_INV_ITEM

PS_D_LOT

PS_X_DEPLETION_E

PS_SF_PRDNID_HEADR

Inventory

F_INV_TRANS

D_DEMAND_INF_INV D_ PHYSICAL_INV

D_LOT

Inventory Mart enables you to analyze inventory accuracy, demand, movement, and turns, so you can shaveexcess without endangering your flexibility.

D_DAY

D_RECV_LN_SHP

PS_PHYSICAL_INV PS_F_PHYSICAL_INV PS_D_BUSINESS_UNIT

PS_D_INV_ITEM

PS_D_LOT

PS_D_INV_LOCATION

PS_S_SET_CNTRL_REC

PS_D_DAY

PS_D_INV_ITEM

PS_D_SUPPLIER

PS_D_BUYER

PS_D_PO_STATUS

PS_D_CNCL_STATUS

Inventory

F_PHYSICAL_INV

D_PO_STATUS D_ SUPPLIER

D_LOT

Inventory Mart enables you to analyze inventory accuracy, demand, movement, and turns, so you can shaveexcess without endangering your flexibility.

D_DAY

D_INV_LOCATION

INVENTORYSource Table Name Target Table Name Lookup Table Name

PS_COUNT_HDR_INV PS_F_INV_CYCLE_CNT PS_D_INV_LOCATION

PS_TRANSACTION_INV PS_D_INV_ITEM

PS_D_LOT

PS_S_SET_CNTRL_REC

PS_CM_DEPLETE,PS_CM_DEPLETE_COST

PS_D_BUSINESS_UNIT

Inventory

F_INV_CYCLE_CNT

D_INV_LOCATION D_ INV_ITEM

D_LOT

Inventory Mart enables you to analyze inventory accuracy, demand, movement, and turns, so you can shaveexcess without endangering your flexibility.

Procurement PS_RECV_LN_SHIP, PS_F_PO_SHIP_RCPT PS_S_SET_CNTRL_REC

PS_RECV_HDR PS_PO_DISTRIB_PRV

PS_PO_HDR PS_PO_LINE

PS_PO_LINE_DISTRIB

PS_PO_LINE_DISTRIB

PS_D_BUS_UNIT

PS_D_DLVRY_STATUS

PS_D_PO_STATUS

PS_D_CNCL_STATUS

PS_D_RECV_STATUS

PS_D_COMP_STATUS

PS_D_CARRIER

PS_D_CHANNEL

PS_D_SUPPLIER

PS_D_ACCOUNT

PS_D_INV_ITEM_E

PS_PO_LINE PS_F_PROC_CTLT PS_REQ_HDR

PS_PO_HDR PS_REQ_HDR

PS_D_BUYER

PS_D_SUPPLIER

PS_PO_DISPATCHED

PS_VOUCHER

PS_RECV_LN_SHIP

PS_S_SET_CNTRL_REC

PS_PYMNT_VCHR_XREF

PS_D_BUSINESS_UNIT

PS_ITM_VENDOR_LOC

PS_PO_LINE_DISTRIB

PS_D_CHANNEL

PS_D_DAY

PS_D_INV_ITEM_E

PS_D_ORIGIN

PS_D_DEPTProcurement Mart captures information about buyers, requisitions, purchase orders, shipments, and productquality, giving you a complete picture of buyer and supplier performance

F_PO_SHIP_RCPT

D_ACCOUNT D_ DLVRY_STATUS

D_PO_LINE

Procurement

PS_RTV_LN_DISTRIB,PS_RTV_LN, PS_F_RTV_DIST PS_S_SET_CNTRL_RECPS_RTV_HDR,PS_PO_LINE_DISTRIB,PS_PO_LINE,PS_PO_HDR,PS_PO_LINE_SHIP, PS_RECV_LN_SHIP

PS_RECV_HDR PS_D_RTRN_REASON

PS_D_SUPPLIER

PS_D_BUSINESS_UNIT

PS_D_UOM

PS_D_CNCL_STATUS

PS_D_PO_STATUS

PS_D_RTVLN_STATUS

PS_D_RTV_STATUS

PS_D_RECLN_STATUS

PS_D_INV_ITEM

PS_D_DAY

PS_D_BUYER

PS_D_DEPT

PS_VOUCHER

PS_D_CARRIER

PS_D_CHANNEL

PS_D_SHIPTO

Procurement Mart captures information about buyers, requisitions, purchase orders, shipments, and productquality, giving you a complete picture of buyer and supplier performance

F_RTV_DIST

D_SUPPLIER D_ VOUCHER

D_RTVLN_STATUS

Procurement

PROCUREMENTSource Table Name Target Table Name Lookup Table Name

PS_VCHR_MTCH_EXCPT, PS_VOUCHER & PS_F_MTCH_ANLYS PS_S_SET_CNTRL_REC

PS_VOUCHER_LINE PS_D_BUYER

PS_D_MTCH_STATUS

PS_D_INV_ITEM

PS_D_SUPPLIER

PS_D_BUSINESS_UNIT

PS_D_MATCH_RULE

PS_PO_LINE_DISTRIB, PS_PO_LINE & PS_F_PO_DIST PS_S_SET_CNTRL_REC

PS_PO_HDR PS_D_CHANNEL

PS_D_DEPT

PS_D_UOM

PS_D_BUSINESS_UNIT

PS_D_DAY

PS_D_INV_ITEM

PS_D_SUPPLIER

PS_D_BUYER

PS_D_PO_STATUS

PS_D_CNCL_STATUS

PS_PO_LINE and PS_PO_HDRPS_F_PO_LINE. PS_PO_DISTRIB_PRV

PS_S_SET_CNTRL_REC

PS_D_BUYER

PS_D_PO_STATUS

PS_D_CNCL_STATUS

PS_TRANSACTION_INV

PS_D_ACCOUNT

PS_D_BUSINESS_UNIT

PS_D_LOT

PS_D_SUPPLIER

PS_D_INV_ITEM

PS_D_INV_ITEM_E

PS_PO_LINE_DISTRIB

PS_PO_LINE_SHIP

PS_RECV_LN_SHIPProcurement Mart captures information about buyers, requisitions, purchase orders, shipments, and productquality, giving you a complete picture of buyer and supplier performance

F_MTCH_ANLYS

D_MATCH_RULE D_ INV_ITEM

D_MTCH_STATUS

Procurement PS_REQ_LINE,PS_REQ_HDR PS_F_REQN PS_S_SET_CNTRL_REC

PS_D_UOM

PS_D_BUSINESS_UNIT

PS_D_BUYER

PS_S_PSOPRDEFN

PS_REQ_LN_DISTRIB

PS_D_REQ_STATUS

PS_D_REQLN_STATUS

PS_D_ORIGIN

PS_D_DEPT

PS_D_PERSON

PS_D_INV_ITEM

PS_REQ_LINE,PS_REQ_HDR PS_F_REQN_ANLYSIS PS_S_SET_CNTRL_REC

PS_D_BUSINESS_UNIT

PS_S_PSOPRDEFN

PS_REQ_LN_DISTRIB

PS_D_BUYER

PS_D_REQ_STATUS

PS_D_INV_ITEM

PS_D_REQLN_STATUS

PS_D_PERSON

PS_D_ORIGIN

PS_D_DAY

PS_D_DEPT

PS_PO_DISPATCHED

PS_RECV_LN_SHIP

PS_VOUCHERPS_PO_LINE_DISTRIB,PS_PO_DISPATCHED,PS_RECV_LN_SHIP,PS_PYMNT_VCHR_XREF D,PS_VOUCHER

PS_RTV_LN,PS_RTV_HDR,PS_PO_LINE,PS_PO_HDR and PS_RECV_HDR PS_F_RTV PS_S_SET_CNTRL_REC

PS_D_CNCL_STATUS

PS_D_UOM

PS_D_BUSINESS_UNIT

PS_D_BUYER

PS_D_SUPPLIER

PS_D_SHIPTO

PS_D_PO_STATUS

PS_D_RTVLN_STATUS

PS_D_RECLN_STATUS

PS_D_RTV_STATUS

PS_D_CARRIER

PS_D_DAY

PS_D_INV_ITEM_E

PS_D_RTRN_REASON

Procurement Mart captures information about buyers, requisitions, purchase orders, shipments, and productquality, giving you a complete picture of buyer and supplier performance

Spend

SPENDSource Table Name Target Table Name Lookup Table Name

PS_VOUCHER,PS_PYMNT_VCHR_XREF PS_F_VCHR PS_PAYMENT_TBL

PS_D_BUSINESS_UNIT

PS_D_SUPPLIER

PS_VOUCHER,PS_VOUCHER_LINE PS_F_VCHR_LN PS_VCHR_ACCTG_LINE

PS_PO_HDR

PS_PO_LINE

PS_D_BUSINESS_UNIT

PS_S_SET_CNTRL_REC

PS_D_INV_ITEM

PS_S_SET_CNTRL_REC

PS_PYMNT_VCHR_XREF

PS_PO_LINE_SHIP

PS_D_DAY

PS_D_BUYER

PS_D_DAY

PS_D_UNSPSC

PS_D_LEDGER

PS_D_CA

PS_D_PYMNT_MTHD

PS_D_SUPPLIER

PS_PO_LINE_DISTRIB

PS_D_AP_DOC_TYPE

PS_D_ACCOUNT

Spend Mart provides full visibility into the source-to-settle process to help you to drive down unit costswithout sacrificing product and service quality

F_VCHR_LN

D_SUPPLIER D_ INV_ITEM

D_PO_HDRD_DAY

Justification: DonePlanning: Need Project PlanBusiness Analysis: Not done Design: Not done but a huge head startConstruction: Not done but a huge head startDeployment: Not done

Delivered Vanilla Design and Construction is STRONG - Huge Head Start

Recommended reporting solutions Divided into major categories

Data Warehouse On-line Lookup Delivered report (i.e. SQR) Defer until later phase Download Not needed

Considerations: Sufficient granularity Sufficient timeliness (i.e. Data Warehouse 1 day lag) Has all the fields? Issues – decision still pending on data capture Issues – requires further development in Data Warehouse Historical data will not be there day one

Goal: To have the best solution for this phase, with time and resource constraints.

Status of report development Data in Warehouse Models in Warehouse % complete (i.e. 7 out of 40) Design status

Design pending issues, Design Not signed off, Design signed off

Development status In development Developed, ready to be QA’d Development QA’d and Signed off

Naming standards used

Technical Side

Tip for organizing documentation

ETL Ascential Jobs

Tip for organizing ETL Ascential Jobs

Tip for organizing ETL Ascential Jobs