Transcript Overview of PeopleSoft Data Warehouse Implementation August 3, 2006
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 4 Rept Rqmt 5 Rept Rqmt 6
GL & Profitability D_DEPT F_JOURNAL D_JRNL_SOURCE
PS_JRNL_LN PS_JRNL_HEADER PS_F_JOURNAL
D_DET_PERIOD D_ACCOUNT
PS_BU_LED_GRP_TBL PS_S_SET_CNTRL_REC PS_D_DEPT PS_D_PRODUCT PS_D_PRJ PS_D_PROGRAM_FDM PS_D_BUSINESS_UNIT PS_D_DET_PERIOD PS_D_BUDGET_REF PS_D_OPER_UNIT PS_D_ACCOUNT PS_D_ACCOUNT PS_D_LEDGER PS_D_JRNL_SOURCE PS_D_FUND PS_D_FUND PS_D_STAT_CODE PS_D_SCENARIO PS_D_CHARTFIELD1 PS_D_CHARTFIELD2 PS_D_CHARTFIELD3 PS_D_GL_ADJ_TYPE PS_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 D_DEPT_TBL F_LEDGER D_BOOK_CODE D_DET_PERIOD D_ACCOUNT
PS_LEDGER PS_LEDGER_BUDG PS_F_LEDGER PS_S_SET_CNTRL_REC PS_BU_LED_GRP_TBL PS_D_DET_PERIOD PS_D_PRJ PS_D_LEDGER PS_D_ACCOUNT PS_D_ACCOUNT PS_D_DEPT_TBL PS_D_SUBLEDGER PS_D_BUSINESS_UNIT PS_D_FUND PS_D_PRODUCT PS_D_CHARTFIELD2 PS_D_BUDGET_REF PS_D_STAT_CODE PS_D_BOOK_CODE PS_D_CHARTFIELD1 PS_D_OPER_UNIT PS_D_PROGRAM_FDM PS_D_GL_ADJ_TYPE PS_D_CLASS_FIELD PS_D_CHARTFIELD3 PS_F_LEDGER PS_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 D_ABM_OBJECT F_PROFITABILITY D_PRJ D_DET_PERIOD D_ACCOUNT
PS_PF_LEDGER_F00 PS_F_PROFITABILITY PS_SET_CNTRL_REC PS_PF_SCENARIO_DFN PS_D_DET_PERIOD_E PS_D_BUS_UNIT_PF PS_D_ACCOUNT PS_D_DEPT PS_D_PRJ PS_D_OPER_UNIT PS_D_FUND PS_FUND_TBL PS_D_PRODUCT PS_D_PF_SOURCE PS_D_CUSTOMER PS_D_SCENARIO PS_D_CHARTFIELD1 PS_D_CHARTFIELD2 PS_D_CHARTFIELD3 PS_D_ABM_OBJECT PS_D_CHANNEL PS_D_DIMENSION1 PS_DIMENSION1_TBL PS_D_DIMENSION3 PS_DIMENSION3_TBL PS_D_DIMENSION2 PS_DIMENSION2_TBL PS_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.
Payables D_DEPT ACCOUNTS PAYABLE (AP) Source Table Name
PS_VCHR_ACCTG_LINE PS_VOUCHER
Target Table Name
PS_F_AP_ACCOUNT_LN
D_PATTERN_DAY F_AP_ACCOUNT_LN Lookup Table Name
PS_S_SET_CNTRL_REC PS_D_BUSINESS_UNIT PS_D_BUSINESS_UNIT PS_D_PRJ PS_BU_LED_GRP_TBL PS_D_LEDGER PS_D_ACCOUNT PS_D_OPER_UNIT PS_D_PROGRAM_FDM PS_D_CLASS_FIELD PS_D_CHARTFIELD1 PS_D_CHARTFIELD2 PS_D_DT_PATTERN PS_D_DEPT PS_D_BUDGET_REF PS_D_STAT_CODE PS_D_CHARTFIELD3 PS_D_FUND PS_D_SUPPLIER PS_D_PRODUCT PS_D_PATTERN_DAY
D_BU_LED_GRP_TBL D_ACCOUNT
Payables Mart provides information about suppliers, vouchers, match exceptions, and payments to help you closely manage your cash position.
Payables
PS_VOUCHER PS_PAYMENT_TBL PS_F_AP_TRAN
D_PERSON_AP_OPID D_AP_DOC_TYPE F_AP_TRAN D_DAY D_AP_VTR_TYPE D_AP_PTR_TYPE D_ACCOUNT
PS_S_SET_CNTRL_REC PS_D_SUPPLIER PS_D_BUSINESS_UNIT PS_D_DAY PS_D_PERSON_APOPID PS_D_AP_DOC_TYPE PS_D_AP_VTR_TYPE PS_D_APPOST_STATUS PS_D_PYMNT_TRMS PS_PYMNT_VCHR_XREF PS_D_AP_VTR_STAT PS_S_BUS_UNIT_AP PS_D_PERSON_APOPID PS_D_AP_DOC_TYPE PS_D_AP_PTR_TYPE PS_D_SUPPLIER PS_D_PYMNT_MTHD PS_D_BANK_ACCT PS_D_AP_PTR_STAT PS_D_APPOST_STATUS Payables Mart provides information about suppliers, vouchers, match exceptions, and payments to help you closely manage your cash position.
Payables
PS_VCHR_MTCH_EXCPT PS_VOUCHER PS_F_VCHR_MTCH_EXP PS_S_SET_CNTRL_REC PS_D_BUSINESS_UNIT PS_D_MATCH_RULE PS_D_DAY PS_D_PERSON_APOPID PS_D_SUPPLIER
D_SUPPLIER F_VCHR_MTCH_EXP D_DAY D_PERSON_APOPID D_ MATCH_RULE
Payables Mart provides information about suppliers, vouchers, match exceptions, and payments to help you closely manage your cash position.
Inventory D_LOT
PS_CM_ACCTG_LINE PS_TRANSACTION_INV PS_F_INV_LDGR PS_S_SET_CNTRL_REC 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
D_DAY F_INV_LDGR D_UOM D_ INV_ITEM
Inventory Mart enables you to analyze inventory accuracy, demand, movement, and turns, so you can shave excess without endangering your flexibility.
Inventory D_LOT F_INV_TRANS D_DEMAND_INF_INV
PS_TRANSACTION_INV PS_F_INV_TRANS
D_DAY
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
D_RECV_LN_SHP D_ PHYSICAL_INV
Inventory Mart enables you to analyze inventory accuracy, demand, movement, and turns, so you can shave excess without endangering your flexibility.
Inventory D_LOT
PS_PHYSICAL_INV
D_DAY
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
F_PHYSICAL_INV D_PO_STATUS D_INV_LOCATION D_ SUPPLIER
Inventory Mart enables you to analyze inventory accuracy, demand, movement, and turns, so you can shave excess without endangering your flexibility.
Inventory F_INV_CYCLE_CNT
INVENTORY
Source Table Nam e
PS_COUNT_HDR_INV PS_TRANSACTION_INV
Target Table Nam e
PS_F_INV_CYCLE_CNT
Lookup Table Nam e
PS_D_INV_LOCATION PS_D_INV_ITEM PS_D_LOT PS_S_SET_CNTRL_REC PS_CM_DEPLETE,PS_CM_DEPLETE_COST PS_D_BUSINESS_UNIT
D_LOT D_INV_LOCATION D_ INV_ITEM
Inventory Mart enables you to analyze inventory accuracy, demand, movement, and turns, so you can shave excess without endangering your flexibility.
Procurement
PS_RECV_LN_SHIP, PS_RECV_HDR PS_PO_HDR PS_F_PO_SHIP_RCPT PS_S_SET_CNTRL_REC PS_PO_DISTRIB_PRV 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
D_PO_LINE F_PO_SHIP_RCPT D_ACCOUNT
PS_PO_LINE PS_PO_HDR
D_ DLVRY_STATUS
PS_F_PROC_CTLT Procurement Mart captures information about buyers, requisitions, purchase orders, shipments, and product quality, giving you a complete picture of buyer and supplier performance PS_REQ_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_DEPT
Procurement D_SUPPLIER F_RTV_DIST
PS_RTV_LN_DISTRIB,PS_RT V_LN, PS_RTV_HDR,PS_PO_LINE_D ISTRIB,PS_PO_LINE,PS_PO_H DR,PS_PO_LINE_SHIP, PS_F_RTV_DIST PS_RECV_HDR
D_RTVLN_STATUS
PS_S_SET_CNTRL_REC PS_RECV_LN_SHIP 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
D_ VOUCHER
Procurement Mart captures information about buyers, requisitions, purchase orders, shipments, and product quality, giving you a complete picture of buyer and supplier performance
Procurement
PROCUREMENT
Source Table Nam e
PS_VCHR_MTCH_EXCPT, PS_VOUCHER & PS_VOUCHER_LINE
Target Table Nam e
PS_F_MTCH_ANLYS
Lookup Table Nam e
PS_S_SET_CNTRL_REC 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_PO_HDR PS_F_PO_DIST
D_MTCH_STATUS
PS_S_SET_CNTRL_REC 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
F_MTCH_ANLYS D_MATCH_RULE
PS_PO_LINE and PS_PO_HDR PS_F_PO_LINE.
D_ INV_ITEM
Procurement Mart captures information about buyers, requisitions, purchase orders, shipments, and product quality, giving you a complete picture of buyer and supplier performance 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_SHIP
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_VOUCHER PS_PO_LINE_DISTRIB,PS_PO_DI SPATCHED,PS_RECV_LN_SHIP,P S_PYMNT_VCHR_XREF D,PS_VOUCHER PS_RTV_LN,PS_RTV_HDR,P S_PO_LINE,PS_PO_HDR and PS_RECV_HDR PS_F_RTV Procurement Mart captures information about buyers, requisitions, purchase orders, shipments, and product quality, giving you a complete picture of buyer and supplier performance 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
Spend
SPEND
Source Table Nam e
PS_VOUCHER,PS_PYMNT_VCHR_XREF
Target Table Nam e
PS_F_VCHR
Lookup Table Nam e
PS_PAYMENT_TBL PS_D_BUSINESS_UNIT PS_D_SUPPLIER
D_DAY D_SUPPLIER F_VCHR_LN
PS_VOUCHER,PS_VOUCHER_LINE
D_PO_HDR D_ INV_ITEM
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 costs without sacrificing product and service quality
Justification: Done Planning: Need Project Plan Business Analysis: Not done Design: Not done but a huge head start Construction: Not done but a huge head start Deployment: 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