P6ETL – A utility for P6 and Oracle Primavera Cloud users
Here we’re taking a look at P6ETL, a very useful utility from Mustang Technologies that simplifies the retrieval of P6 EPPM data from hosting firms.
The problem with Oracle and other Primavera hosting firms is, that they prevent users from directly accessing the P6 and OPC databases. Instead, users must extract the data using Web Services, Oracle’s Primavera Data Services (PDS) or XER/XML exports. This can be quiet a convoluted process. To use Web Services or PDS, users must build scripts or develop source code. And exporting XER/XML files is a manual intensive process.
This is where P6ETL comes to the rescue. It automatically extracts the data for you!
P6ETL’s data extraction utility automatically extracts P6 and OPC data from hosted environments, such as Oracle and Loadspring, using Oracle-recommended Web Services. It then stores the extracted data into a Microsoft SQL Server (MSSQL) database. The location of the MSSQL database is determined by the user and can be on-premise, on Azure, on AWS or on any hosted service.
Once the P6 or OPC data is extracted, users can create dashboards using Power BI, Tableau or any other report development tool, as long as that tool can connect to a MSSQL database.
P6ETL handles the difficulties when extracting large amounts of data, by using a staging and batching multi-thread process. For example, P6ETL can extract all the activity and other activity-related data for large projects with over 25K activities by extracting the data in small batches such as 500 activities at a time.
Plus, P6ETL processes multiple projects at one time to reduce the overall extraction time.
Although P6ETL was preliminarily developed to extract data from hosted environments (such as Oracle and Loadspring), some users with on-premise P6 environments use it too. This means report developers are not overtaxing the on-premise P6 database by directly accessing the P6 database during normal business hours.
Most off-the-shelf products and solutions require you to manually export the data from the hosted environments into XER or XML files and then use the XER/XML files for reporting or their solution can only read on-premise P6 databases.
While other generic integration/extraction solutions require you to create scripts (based on REST API calls or JSON ) that will extract each subject area such as Projects and Activities. These solutions are tedious and time-consuming to develop plus they do not smoothly recover from errors and timeouts.
With P6ETL, there are no scripts or manual steps – the extraction is automatic. And it handles and recovers from errors that occur during the extraction providing a seamless, robust solution.
Pros
- Automatically extracts P6 and OPC Global, Project, and Baseline data. The data includes calculated values and time-phased data for activities and resource assignments.
- Creates a Reporting database that is easy to use for experienced and inexperienced report developers.
- Configurable so that only essential projects and baselines are extracted. However, P6ETL can be configured to extract all projects and all baselines.
- Includes six Power BI dashboards to help kick off your PMIS reporting system.
- For P6, populates 66 tables which satisfies most reporting requirements. However, Mustang will add additional tables on request at no charge as part of maintenance and support.
- Once the Power BI dashboards are published to the Power BI service, the dashboards can be configured to automatically refresh from the MSSQL database providing a completely automated, seamless refresh for your dashboards.
- Mustang will install and configure P6ETL for a 30-day evaluation without any financial obligation from you.
- Although Power BI is one of the most popular tools used for BI report development, you can use any report development tool that can connect to a MSSQL database.
- Email notifications are sent for each P6ETL session with the extraction status – successful or unsuccessful.
- Using the MSSQL database, you can create an unlimited number of Power BI dashboards and share those dashboards with others in your organization.
Cons
- Data is extracted on a schedule therefore the data in the MSSQL database is not real time.
- For OPC, populates only 23 tables at this time but additional tables will be added over the next 6 to 12 months.
- Requires a P6 EPPM user license or an OPC user license.
- Mustang will add additional tables on request. New tables typically take 60 to 90 days.
- You must have a workstation to run the P6ETL utility and you must provide a MSSQL instance for the MSSQL database.
P6ETL In Use
Mustang installed and configured P6ETL for us without any financial commitment from us. Then P6ETL was extracting our P6 data into the MSSQL database in less than 3 hours. We did need to ask Oracle to increase the JAVA memory from 512MB to 4GB to handle time-phased data but there was no charge from Oracle.
As soon as our P6 data was available in the MSSQL database, we were able to view our P6 data in the included six Power BI dashboards. Pretty slick!
Mustang also offers a 30-day trial period, allowing you to develop a few Power BI dashboards and demonstrate them to management before purchasing.
Using the six Power BI dashboards and sample SQL included, our report developers had a small learning curve understanding the MSSQL scheme, however, the table names are descriptive (such as P6Project for project data and P6Activity for activity data). Also, the column names in those tables are very similar, if not exact, to the column names used on P6 layouts.
In summary, P6ETL solves a lot of problems that are beyond the technical capability of many organizations using Primavera P6 EPPM.
To learn more about P6ETL or to register for one of the monthly P6ETL webcasts, visit www.p6etl.com
Example Power BI Dashboards
Here are some of the Power BI dashboards that can be created using the MSSQL database.
Resource Assignment Look-ahead Dashboard
Page 1 of this dashboard shows the numeric data – and has a variety of filters that allow the user to view the data by different intervals (such as 4 week, 3 months, 6 months and 1 year), for specific resource(s), for specific roles(s), for specific projects(s), etc.
Page 1
Page 2 of this dashboard shows the data as a stacked histogram. In this example, the data is grouped by project. But the data can also be grouped by Resource or Role. This page also has a variety of filters that allow the user to view the data by different intervals (such as 4 week, 3 months, 6 months and 1 year), for specific resource(s), for specific roles(s), for specific projects(s), etc.
Page 2
Project Overview Dashboard
This Project Overview dashboard shows key details for a project. By clicking on one of the buttons (such as Total Activities or Critical Activities), a list of the activities under that category are shown. After clicking on Critical Activities, the critical activities are shows (See Page 2).
Page 1
Page 2