March 24 2009 anaheim california
1 / 52

March 24, 2009 Anaheim, California - PowerPoint PPT Presentation

  • Uploaded on

Implementing EPM: How Northwestern University Customized & Implemented EPM. March 24, 2009 Anaheim, California. Presenters. John Ewan Financial Reporting Team Manager, Northwestern University Manish Amin Lead Data Modeler, Northwestern University Rumy Sen

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'March 24, 2009 Anaheim, California' - mahdis

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
March 24 2009 anaheim california

Implementing EPM:

How Northwestern University Customized & Implemented EPM

March 24, 2009

Anaheim, California


  • 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


  • Northwestern University & Project Café

  • Reporting Strategy

  • EPM Installation: Modification and Implementation

  • ETL Development

  • Environments and Hardware

  • Row Level Security

  • Report Development

  • Lessons Learned

  • Questions

Northwestern university
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

Northwestern peoplesoft
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

Project caf scope
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


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


Nov ‘08


Dec 8, 2008

Support, schema enhancements, report modifications


Ad Hoc Reporting


Northwestern s reporting strategy
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)

Reporting strategy delivered
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

Epm installation modification and implementation

EPM Installation:Modification and Implementation

Requirements gathering
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

Gap analysis
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)

Data warehouse design
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

Conformed dimensions kk summary star
Conformed Dimensions: KK Summary Star

Conformed Dimensions:

  • Time

  • Account

  • Department

  • Project and Activity

  • Fund

  • Vendor/Supplier

Specialized dimensions project resource star
Specialized Dimensions: Project Resource Star

Specialized Dimensions:

  • Award attribute

  • Bill plan

  • Award mile

  • Flattened PeopleSoft trees (department, account, etc.)

Data warehouse fact tables

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

Additional fact tables
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

Etl approach goals
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

Etl delivered
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

Epm installation
EPM Installation

  • Environments

    • Development

    • Test

    • UAT (User Acceptance Test)

    • Production

Epm hardware delivered
EPMHardware Delivered

   Component Development Test / QA       Production Note

Paradigm shift
Paradigm Shift


  • 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

Secure by department
Secure by Department


  • 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

Secure project financials
Secure Project Financials


  • 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)

Business rules for projects
Business Rules for Projects


  • 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

Project security take 1
Project Security: Take 1


  • 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…

Project security take 2
Project Security: Take 2


  • 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

A picture is worth a 1000 words
A Picture is Worth a 1000 Words




“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





  • 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

Row level security delivered
Row Level Security Delivered


  • 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

Report development1
Report Development

  • 11-month development timeframe in conjunction with ETL development

  • Iterative process with the functional team

Report demonstration
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

Lessons learned functional
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

Lessons learned technical
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

Lessons learned row level security
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

Further information
Further Information

  • Available upon request…

    • Detail design of the data warehouse & ETLs

    • Details of our row level security implementation


  • Contact Information

    • John Ewan -

    • Manish Amin -

    • Rumy Sen -

    • Jonathan Stegall -

    • Katie Crawford -