March 24 2009 anaheim california
Download
1 / 52

March 24, 2009 Anaheim, California - PowerPoint PPT Presentation


  • 95 Views
  • 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
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


Presenters
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


Agenda
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



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


Timeline
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



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

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


Secure by department
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


Secure project financials
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)


Business rules for projects
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


Project security take 1
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…


Project security take 2
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


A picture is worth a 1000 words
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


Row level security delivered
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



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


Questions
Questions


ad