PeopleSoft Application Engine Basics for Building a Data Mart

1
App Engine 101

• PeopleSoft Application Engine Basics for Building
  a Data Mart

2
Agenda
App Engine What its for
Read an App Engine Program
Create an App Engine Program
Configure a Run Control to run an App Engine
Techniques (Data Modeling 101?)
3
Whats App Engine For
Processing Can be a Program performs a process.
Example, puts away inventory in a batch process
based on staged receiving
Data Extract, Transform, Load Source and target
data, performs aggregations, transformations,
conforms data to an analyze-able model
DML Database Operations, for example, dropping
tables and creating indexes
4
Compare and Contrast SQR to App Engine
SQR App Engine
3rd Party Proprietary, part of PeopleTools
Doesnt use PeopleTools macros Uses PeopleTools macros
Used extensively in early versions of PeopleSoft PeopleSoft decided around version 7.5 to stop using SQR and use App Engine
Strength is handling of ETL, Processing of Procedures, DML Strength is handling of ETL, Processing of Procedures, DML
Lots of programmers Scarcity of programmers
Less visual in last PeopleSoft release (SQR 6.0) Steps are somewhat visual in Application Designer
Good reporting capabilities Create a reporting table and use another tool
Hyperion is up to release 9 in SQR with many
new features
5
Read an App Engine Program
6
Read an App Engine Program (cont)
7
Equivalent in SQR
Nested DO
Nested DO
8
Features in App Engine
Steps are numbered Commit after statement Can
abort on error or continue Can click on or off
the Active box to not use that step Can abort or
continue if no rows Can print out the whole app
engine Can include all macros available in
PeopleCode Step can be a PeopleCode step, capable
of processes Many other features
9
Create an App Engine
Easiest way to Create an App Engine Take an
existing App Engine, click File, Save as Rename
App Engine
App Engines can be put in Projects and imported
from one database to another
10
Configure a Run Control to Run an App Engine
11
Technique for Building the Fact

• Only do a full load once a month (incremental on
  15th)
• Drop Indexes before massive INSERTs and then
  build indexes after
• Lookup Key - Projects that are
• Current Projects
• Public Sector
• Create Narrow Tables based on the Lookup Key
• Select Foreign keys to join in later steps
• Select only data needed for this datamart
• Join PROJ_RES to VCHR_ACCTG for ATX, TLX
• Join PROJ_RES to EX_ACCTG for Subcont, ACT, EXP
• Effect a Triangulated Reconciliation of Cost, AP,
  EXP, Proj Resource, GL because we are tying out
  to the source of the GL, the ACCTG LINE tables.

12
QA
Steven Stein, Senior Consultant BearingPoint 1
00 Matsonford Rd One Radnor Corporate
Ctr Radnor, PA 19087 Tel [phone]
Cell 1.267.872.9838 E-mail Steven.Stein_at_Bearing
Point.com