Guest Blog Post: Case Study: OBIEE/APEX Integration Project at UC Berkeley

At Kscope last month, I heard great things about Teal Sexton’s presentation on OBIEE and APEX integration. Of course, I was interested as I gave a presentation on APEX and Essbase integration. My presentation (which I will post in a coming article) was more of how you can do it in an APEX cloud environment with Essbase on-premises using REST API. Teal’s is how UC Berkeley is actually *doing* the integration between OBIEE and APEX. And with that, I’ll let her take the blog post away! Thanks, Teal!

Introduction

Thank you Sarah for the opportunity to write this guest blog post!

I presented on this project at KScope16 and received the Top Speaker award for the Business Intelligence and Data Warehousing track.

This project was successfully completed using two different Oracle tools to provide robust, enterprise wide financial reporting and projections functionality for the management of faculty research awards and other funding. What is so cool about this project is that we married OBIEE and APEX to deliver the best of both applications! All the robust BI dashboard features of OBIEE, including PeopleSoft Finance general ledger data, along with PeopleSoft Human Capital Management data on employees, both from our data warehouse… plus the amazing input functionality and Oracle database features of a custom APEX application!

In short…Users view dashboard reports in OBIEE where they can click a button to open an APEX form where we capture valid user-entered projections, generate all the derived projections in the database using stored procedures, then make all the updated data available for immediate viewing upon browser refresh in the OBIEE dashboard! Read on to learn more about this amazing set of tools.

The Case for Change

As the #1 public research University in the world, UC Berkeley is constantly managing a multi-billion dollar research portfolio of tens of thousands of individual funds using a 12 field chart string in its general ledger (PeopleSoft Finance).

Faculty/Principal Investigators (PIs) had inconsistent access to see expenses and projections regarding these research funds for which they are responsible. This made it difficult for them to make timely, informed decisions.

Deans, Chairs and Administrators had inconsistent access to see which faculty managed funds were projected to end in deficit within their unit.

Over 175 Research Administrators (RAs) created manual projections in Excel and inconsistently sent them to Faculty for review, each following a different, manual process for creating, maintaining and communicating about the projections.

The variation in business processes for managing faculty funding sources was highly inefficient and created the opportunity for errors.

The lack of metrics made it difficult to identify problems or track RA performance, reducing the satisfaction with service delivery.

Without a standard practice, there was no set of training materials to support users and to expedite onboarding of new Faculty and RAs.

Faculty accountability was low because the service and information delivered to faculty by RA varied widely across campus, increasing the risks for overspending and manual errors.

Access to human resources data from PeopleSoft HCM that is necessary for accurate projections on personnel costs varied from unit to unit.

Project Approach

The PI Portfolio Project began a few years ago with a stand-alone OBIEE (branded on campus as “Cal Answers”) dashboard that provided budget and actuals for faculty managed, sponsored funds such as research awards. The second phase of the project added non-sponsored funds such as gifts and Dean’s allocations. The third and most recent phase of the project brought in the ability to create projections using an APEX form imbedded in the OBIEE frame.

The project team included stakeholders from across campus who participated in a large, robust UAT process to ensure the new tools would meet all the unique business needs of our diverse colleges.

Solution Description

The delivered solution is an integrated OBIEE dashboard and APEX input form with corresponding business process guides for research administrators (RAs) and Faculty to manage financial activities related to faculty managed funds including projections and verification of actual expenses. These tools replace existing Excel systems, providing projection functionality for all funds, by Faculty Member as delegated by their assigned chart field value.

The new APEX projections input tool contains data specific to the selected Faculty Member, such as the Funds and chart field values in use by or attributed to the Principal Investigator (PI). The chart field columns in the input form utilize predictive type to help users quickly find the values they are looking for. A user could type “Haas” into the Dept ID field and return all Departments with “Haas” in the name.

The OBIEE platform provides a variety of useful sharing and configuration features that make it a viable tool for facilitating communication between research administrators and Faculty, such as Export options, Customized Views and bookmark links. Export options include Excel, PDF, XML, CSV and more. The PDF export option provides a nice report with all the headers, filters, graphs and notes visible in the dashboard pages.

Architecture

  1. On the OBIEE Projections Report we added a button to open the APEX form.
  2. When the User clicks on the “Click to Open Input Form” button.

– A custom JavaScript function is invoked to open the Apex form embedded within the OBIEE frame, utilizing cookies to pass the Active Directory user name to the APEX application.

– On the APEX server a dedicated database session is created tied to this user’s APEX session.

  1. The database for Cal Answers is an Oracle virtual private database (VPD). VPD controls access to data based on a user’s security context.

– The APEX database session runs a procedure to set the security context for the session based on the user’s roles from OBIEE.

– When the input form loads, it is populated with data from the database based on the user’s security context.

  1. The form dropdowns contain lists of valid values for each field. This enables data to be validated on input into the form.
  2. When a projection is saved, the data is immediately written back to the database.
  3. Database triggers are fired and a stored procedure is run in the background to create associated projections for any benefits, liability insurance and/or overhead (Indirect Costs) that may apply.

7. When the user closes the input form, the newly generated projections can be viewed in the dashboard reports immediately, upon a browser refresh.

Training Materials

The project team created two YouTube videos to introduce the new functionality to Faculty and RAs. Part 1 of the two part video series is geared toward all users of the tools, particularly Faculty (PIs). Part 2 provides demonstration of the advanced features of the tool, including the APEX Projections Input Form. In addition to a complete User Guide, we provide the campus with a wiki site developed in Confluence that contains data definitions for all columns in the dashboard reports and APEX form.

KScope16 Presentation

View the slide deck from my KScope16 presentation here and make sure to turn up your audio for the slide with the video demo! A recording of the full presentation will become available in a few weeks, so I will post that when it is ready.

If you have any questions or comments feel free to reach out to me on LinkedIn or Twitter.

Thanks for your interest!

Best,

Teal Sexton

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s