Custom Oracle BI
Reporting for Unifier

Overview

The Sanitation District had been utilizing Unifier for project management and recently implemented recording contractor pay applications. They needed to output a printed report of the schedule of values progress and requested assistance from DRMcNatty to implement and design.

Challenges

With Unifier hosted in the original Skire shared database, no direct access to the data schema is possible. The query process in Unifier does not provide adequate error trapping when writing the Data View for a report query. Additionally, the Unifier data design complicates data retrieval and display because of how the data is stored.
The most significant challenges were:

• No direct access to the Sanitation District database schema.
• Pay Application (Pay App) data stored in the Pay App BP tables are incomplete and can only record progressed items. This requires creating a more complex SQL query to retrieve a simple report.

Below is a list of some complex issues for the report ing effort:

• No Cost Breakdown Structure (Cost Sheet) by WBS Codes used in Pay Apps. Instead, cost broken down using Work Packages (Bid Items) created outside of the Cost Sheet.
• Mapping tables were used to make the association between Contract, Contract Line Items, SOV, Pay App, Pay App Line Items, Change Order and Change Order Line Items.

• Requirement to display all Bid Items in the report for the latest Pay App regardless of whether a Bid Item has been progressed or not. This required referencing the full SOV in addition to current Pay App line item data. In Unifier, Pay App Line Items are only created if progress is entered.
• District hosted legacy environment differs slightly to the newer Unifier versions.

  – No access to Enterprise Views (basic info on tables/views)

  – No direct query access to the database using a query tool such as SQL Developer.

• This resulted in the District not knowing which tables are needed to create a report. Debugging complex SQL directly from Unifier DataView is time consuming since error handling is not always adequate.

Solution

DRMcNatty renewed the data structure utilizing a demo instance of Unifier. This allowed our direct access to database tables through a database query tool (SQL Developer). We incorporated our understanding of the Pay Application process (found in other tools such as Primavera Contract Management) and our expertise in SQL and report generation to complete this design.

Results

DRMcNatty created the data model required and the report layout based on the client’s original markup, successfully mining the data from the various table sources found in the Unifier data structure.