Overview of PeopleSoft Data Warehouse Implementation

1
Overview of PeopleSoft Data Warehouse
Implementation

• August 3, 2006

2
EPM Architecture
3
Multidimensional Warehouse

• Facts typically numeric values to quantify or
  calculate a companys activities. In star schema
  development it is the central table used to
  connect dimensions

4
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 joinedand 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)

5
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

6
Supply Chain Warehouse

• Supports reporting for the following business
  processes
• Order Fulfillment
• Procurement

• Datamarts
• Procurement
• Spend
• Inventory
• Sales Orders

7
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

8
Hyperion Integration
9
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.

10
Overview of reporting environment

• Datamarts
• Payables
• General Ledger and Profitability

11
Overview of reporting environment
Rept Rqmt 2
Rept Rqmt 5
Rept Rqmt 1
Rept Rqmt 3
Rept Rqmt 4
Rept Rqmt 6
12
GL Profitability
D_DEPT
D_DET_PERIOD
F_JOURNAL
D_JRNL_SOURCE
D_ACCOUNT
13
GL Profitability
D_DEPT_TBL
D_DET_PERIOD
F_LEDGER
D_BOOK_CODE
D_ACCOUNT
14
GL Profitability
D_ABM_OBJECT
D_DET_PERIOD
F_PROFITABILITY
D_PRJ
D_ACCOUNT
15
Payables
D_DEPT
D_PATTERN_DAY
F_AP_ACCOUNT_LN
D_BU_LED_GRP_TBL
D_ACCOUNT
16
Payables
D_PERSON_AP_OPID
D_DAY
D_AP_VTR_TYPE
F_AP_TRAN
D_AP_PTR_TYPE
D_AP_DOC_TYPE
D_ACCOUNT
17
Payables
D_SUPPLIER
D_DAY
F_VCHR_MTCH_EXP
D_PERSON_APOPID
D_ MATCH_RULE
18
Inventory
D_LOT
D_DAY
F_INV_LDGR
D_UOM
D_ INV_ITEM
19
Inventory
D_LOT
D_DAY
F_INV_TRANS
D_RECV_LN_SHP
D_DEMAND_INF_INV
D_ PHYSICAL_INV
20
Inventory
D_LOT
D_DAY
F_PHYSICAL_INV
D_INV_LOCATION
D_PO_STATUS
D_ SUPPLIER
21
Inventory
D_LOT
F_INV_CYCLE_CNT
D_INV_LOCATION
D_ INV_ITEM
22
Procurement
D_PO_LINE
F_PO_SHIP_RCPT
D_ACCOUNT
D_ DLVRY_STATUS
23
Procurement
D_RTVLN_STATUS
F_RTV_DIST
D_SUPPLIER
D_ VOUCHER
24
Procurement
D_MTCH_STATUS
F_MTCH_ANLYS
D_MATCH_RULE
D_ INV_ITEM
25
Procurement
26
Spend
D_PO_HDR
D_DAY
F_VCHR_LN
D_SUPPLIER
D_ INV_ITEM
27
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
28
(No Transcript)
29
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.

30
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 QAd
• Development QAd and Signed off
• Naming standards used

31
Technical Side
32
Tip for organizing documentation
33
ETL Ascential Jobs
34
Tip for organizing ETL Ascential Jobs
35
Tip for organizing ETL Ascential Jobs