March 24, 2009 Anaheim, California

1. Implementing EPM: How Northwestern University Customized & Implemented EPM March 24, 2009 Anaheim, California

2. Presenters • John Ewan • Financial Reporting Team Manager, Northwestern University • Manish Amin • Lead Data Modeler, Northwestern University • Rumy Sen • President and Founder, Entigence Corporation • Jonathan Stegall • Senior Consultant, Entigence Corporation • Katie Crawford • Associate, Huron Consulting Group

3. Agenda • Northwestern University & Project Café • Reporting Strategy • EPM Installation: Modification and Implementation • ETL Development • Environments and Hardware • Row Level Security • Report Development • Lessons Learned • Questions

4. Northwestern University&Project Café

5. Northwestern University • Academics • 17,000 Students • 7,100 Faculty and Staff • Campuses • Evanston • Chicago • Qatar • Annual Research Budget • Over $1.5 billion • Sponsored Research Budget • Over $439 million

6. Northwestern & PeopleSoft • Human Resources, 1997 • HR, Benefits Administration, Payroll, Recruitment, Position Management • Student, 1999 • Admissions, Academic Advisement, Financial Aid, Student Financials, Student Records • Financials, 2008 • Grants, Project Costing, Contracts, Billing, AR, GL, Commitment Control, Purchasing, Expenses, etc. • Enterprise Performance Management (EPM), 2008 • Financials (AP, AR, GL), & Supply Chain

7. Project Café Scope • Research Pre-award (InfoEd) • Go Live: July 17, 2007 • Aligned with PeopleSoft Department Structure: November 24, 2008 • Facilities Management (FAMIS) • Go Live: December 1, 2008 • PeopleSoft Financials • Go Live: December 8, 2008 • Enterprise Performance Management (EPM) • Go Live: December 8, 2008

8. Timeline Jun ‘07 Gathered requirements from central offices, schools, and depts Created 160 report specifications, Conducted fit-gap analysis with EPM Determined major EPM enhancements Created data models Sep ‘07 Purchased EPM Nov ‘07 Created 100+ EPM-based reports Created 40+ real-time reports Testing, testing, testing…. Dec ‘07 Implemented custom stars and modified EPM facts and dimensions Apr ‘08 Oct ‘08 Conducted end-user testing Sign-off Nov ‘08 GO-LIVE!! Dec 8, 2008 Support, schema enhancements, report modifications Ongoing Ad Hoc Reporting Future

9. Reporting Strategy

10. Northwestern’s Reporting Strategy • Reporting Environment • Web-based delivery of reports • “One-stop shopping” • Row level security to control data access • Reporting Goals • Support daily operations, reconciliation, and budget management reporting needs • Integrate data from PeopleSoft and other sources • Deploy standardized prompts and report layouts • Promote self-service/ad hoc access and single version of the truth • Reporting Access • To financial data (PeopleSoft) • To each enterprise subject area (ex. InfoEd, FAMIS)

11. Reporting Strategy Delivered • Cognos 8.2 – delivery of data to the users • Report Studio – Standard Reports • Query Studio – Ad Hoc Reporting • Cognos Connection – Web portal for reports • EPM – baseline and customized mappings • PeopleSoft – Supply Chain & Financials Data • Custom-built star schemas • InfoEd • FAMIS

12. EPM Installation:Modification and Implementation

13. Requirements Gathering • Functional Team • Reviewed more than 300 report requirements from central offices, schools, and departments • Determined 160 reports for which Project Café would be responsible • Met with central offices, schools, and departments to gather data requirements • Provided mappings for report fields to corresponding PeopleSoft sources

14. Gap Analysis • Reporting Team • Compared mappings to EPM fields • Determined where gaps existed • Small Gaps such as missing fields • Large Gaps Missing Tables (e.g. Commitment Control, Grants) Data from other PeopleSoft modules (e.g. HR) External data (e.g. InfoEd, FAMIS)

15. Gap Analysis - Example

16. Data Warehouse Design • Kimball approach • Started with EPM “Out of the Box” • The Good – Dimensions (Type 2) • The Bad – Fact Tables • All inclusive, but • Did not match up with the reporting requirements • Required significant enhancement • The Ugly • EPM 8.9 does not have commitment control • No Grants data • Reporting requirements called for external data

17. Data Warehouse Design Results

18. Conformed Dimensions: KK Summary Star Conformed Dimensions: • Time • Account • Department • Project and Activity • Fund • Vendor/Supplier

19. Specialized Dimensions: Project Resource Star Specialized Dimensions: • Award attribute • Bill plan • Award mile • Flattened PeopleSoft trees (department, account, etc.)

20. Supply Chain PO Line Match fact Voucher and Voucher line fact PO line fact Receive Line fact Voucher Accounting line fact Grants Management Project Resource fact Project and Ledger Summary Effort max fact Item activity fact Grants Management Award fact General Ledger (KK) Ledger KK Summary fact Ledger KK Detail fact Bank Statement fact Ledger fact Journal fact General Ledger Expense Sheet fact Budget Journal fact Human Resource Journal fact Cost Accounting line fact Investment Accounting line fact Data Warehouse Fact Tables

21. Additional Fact Tables • Research Pre-award (InfoEd) • Award Summary fact • Request Summary fact • Request Detail fact • Award Detail fact • Facilities Management (FAMIS) • Job Control Extract fact • Purchase Order Fact • Receipt fact • FAMIS Summary fact • FAMIS Transaction fact

22. ETL Development

23. ETL Approach & Goals • Keep a safe copy of the delivered code • Clone the code and use “NU_” naming standard for customized code • Use out-of-the-box ETL flow • Staging • Data load • Fit into a nightly window • Parallel processing when feasible

24. ETL Delivered • Staging • 200 ETL staging jobs/scripts • Copied tables from the source system to the staging area • Minimized impact / disruption of the source system • Reduced risk of data inconsistency & partial transaction capture • Requires about an hour to execute • ETL • 100 DW ETL jobs/scripts • Requires about 3 hours to execute

25. Environments & Hardware

26. EPM Installation • Environments • Development • Test • UAT (User Acceptance Test) • Production

27. EPM - Architecture

28. EPMHardware Delivered    Component Development Test / QA       Production Note

29. Row Level Security (RLS)

30. Paradigm Shift 30 • Legacy Strategy • Access based on combinations of chart-strings • Secure but difficult to maintain • Café Objective • RLS with an eye on flexibility and maintenance: • Users to have access based on a set of departments • Users to have access to a set of projects • Only select users have access to highly confidential data items such as salary information

31. Secure by Department 31 • Departmental security relatively easy • Leverage natural relationship between user and department(s) • Allow user to be associated with more than one department • Use PeopleSoft’s user-to-department associations to drive RLS security in Cognos

32. Secure Project Financials 32 • Project Security more complex • Who should be allowed to see financials for projects? • Users belonging to department that owns project (ex: dept administrator) • User’s assigned project-level access (ex: PI and project team) • Users allowed to charge to the project (ex: cost-share activities)

33. Business Rules for Projects 33 • Users belonging to department that owns project • If user’s dept = project’s dept, allow access • User’s with project-level access • Explicitly granted access in PeopleSoft • Use PeopleSoft’s project_sjt table to drive user-to-project filters in Cognos • Users allowed to charge to the project • Implicitly derived access • Tough! • Information on who is charging to the project is only available through transaction history

34. Project Security: Take 1 34 • Create a “master” userid-to-projectid security table: • Source #1: by default user can see all projects for their departments • Use dept_sjt table in PS to derive a list of projects for user • Source #2: explicit authorization • Use project_sjt table in PeopleSoft • Source #3: implicit access • Using ETL: • Go through all transactions and build a user-id to project-id association • Incredibly time consuming ETL • But it worked…

35. Project Security: Take 2 35 • For Source #3, push the heavy-lifting to PeopleSoft • Use SmartERP to develop a dept-id to project-id combo table in PS • Enhance project security ETL to use SmartERP combo table to populate a final project security table (“sec”) • Project sec table now contains rows for users and related projects using explicit and implicit associations • One-time set-up followed by incremental maintenance overhead • Net effect: • SmartERP-provided comprehensive department-to-project associations drives RLS effectively and efficiently

36. A Picture is Worth a 1000 Words PS_D_DEPT_NU_SJT PS_SMERP_COMBO_NU PS_D_PRJ_NU_SJT “Implicit Access” List of projects within user’s dept and projects that user’s dept is charging to “Explicit Access” List of projects that user has access to outside of user’s dept PS_D_DEPT_SEC_NU PS_D_PRJ_SEC_NU 36 Notes: • In reports, users allowed to pick only primary departments, not departments associated with projects • User id and role-names are embedded in sjt and sec tables as appropriate for Cognos’ use

37. Row Level Security Delivered 37 • Secure department and project prompt values: • If user can’t pick a value then they can’t see the financials • Alias dimensions and join to “sjt” (for dept) and “sec” (for project) tables to automatically filter values • Secure ad hoc queries: • Filter fact tables with “or” condition on available department_sjt or project_sec values for userid and role-names

38. Report Development

39. Report Development • 11-month development timeframe in conjunction with ETL development • Iterative process with the functional team

40. Report Demonstration GM044 Sponsored Project Portfolio 1 line per project activity GM045 Sponsored Project Budget Statement 1 line per account GL008 Revenue and Expense Activity Report 1 line per transaction type GL059 Transaction Detail Drill-Thrus 1 line per transaction line

41. Sponsored Project Portfolio

42. Sponsored Project Budget Statement

43. Revenue and Expense Activity Report

44. Payroll Drill

45. Expense Report Drill

46. Voucher Drill

47. Lessons Learned

48. Lessons Learned: Functional • Agree on data definitions prior or during gap analysis • Provide detailed source mapping for each report requirement • Ensure the functional team understands the foundations of data warehousing/business intelligence (e.g. de-normalization) • Work closely with technical team to ensure they understand the business rules • Complete functional specs with an EPM context • Develop a list of “reporting standards” to guide development

49. Lessons Learned: Technical • Large increase of consultants staff will help, but… • Learning curve for different functional areas • Heavy parallel work increases potential for rework • Need to actively promote reuse • Offshoring worked (Infosys) • Majority of ETL coding • Some Cognos report development • Don’t offshore the way we did! • Use of EPM saved months of development • Conformed dimensions • Easier to customize than create from scratch • Get enough hardware for EPM (avoid virtual servers) • Take advantage of Oracle Customer Service • Beware of the deleted Journal entry! • Destructive vs. incremental reloads

50. Lessons Learned: Row Level Security • RLS was expected by user community; think outside the box! • Avoid building complex security rules on the reporting side; difficult to maintain incrementally • Cognos provides tremendous flexibility for role-based security and RLS • Think about ad hoc reporting when securing standard reports