How to Keep Your Primavera P6 Clean?

This article addresses to all the schedulers and project professionals who import schedules into scrubbing P6 databases, remove undesired data, export the cleaned XER, and then import to a production database or share with third-parties such as contractors or sub-contractors.

If it happens to you to go through such a process, then you might want to read this article and see the better way to “clean” a XER file, prevent external data from corrupting your database thus maintain security and keep schedule integrity.

You can achieve this with a simple tool called ScheduleCleaner.

Now, I want to explain how the tool works and how you can benefit from it.

How to get started with ScheduleCleaner?

ScheduleCleaner is a desktop application for Windows operating system. It’s not connected to a database, and does not require internet connection to use it.

The “cleaning” process of an XER file can be achieved in 5 steps as explained below.

  1. Launch the software;
  2. Add an XER File;
  3. Select the output folder;
  4. Click on the categories of data you want to remove;
  5. Click “Clean” button.

As you can see, there is no manual work, no editing of a XER file in Notepad, and no scrubbing databases.

Steps to "clean" XER file with ScheduleCleaner

The software is intuitive, easy-to-use, and works offline as a standalone desktop application.

What’s more important, the software does not modify the original project plan. Instead it creates a copy and modifications are saved in the new file. The original project plan remain untouched.

Now, let’s see what you can accomplish with this tool in more specifics.

Removing POBS

If it takes a lot of time to import XER file intro Primavera P6 database, POBS data might be the reason for that.

Overall, the POBS defect affect the performance of the application and users lose valuable during the import operation. According Oracle, the POBS data is not used yet:

“We do not utilize the POBS table yet we export/import the data from this table when completing XER Export/Import. The XER export/import should be written to exclude this data with XER export/import operations of P6 Professional.”

The removal of POBS data can be done manually, but the process is prone to errors and can be time consuming.

The impact of all these errors when managing global data in an enterprise, will ultimately result in a polluted database and unconscious mistakes on a project level.

So using a tool for removing POBS data is desirable.

You can see a significant difference of the file size before and after cleaning POBS which greatly affects the time needed to import XER file into a Primavera P6 database.

Imagine the time that can be saved for larger XER files.

Remove Units, Rates, Cost, Pricing, Progress

As the purpose of exporting data files in XER format is to transmit project data to another database, in many cases data should be kept private. For example, a general contractor wants to send the project to a sub-contractors, but without the cost of resources.

Another examples is related with the GDPR regulation. Namely project schedulers and managers share files that contain sensitive information such as resource names that can disrupt the guidelines of the GDPR.

To be GDPR compliant, companies need to hide/anonymize confidential information, and ScheduleCleaner is the perfect tool to easily and securely protect sensitive information.

Just by clicking checkboxes, users who want to share the XER schedule can pick certain categories of data that want to be removed from the schedule before sending to third-parties or upload to a Primavera P6 database.

Before “cleaning” prices
After “cleaning” prices

Mask Project Data

Similar as removing certain categories of data, you can also mask project data.

The only difference is that with masking, you can add custom codes, labels or text for the specific categories.

Add Prefix/Suffix

Inserting prefix or suffix to different categories in the project plan, can give additional information to the person who reads the information and acts according them.

To add Prefix/Suffix, you need to select the template that will contain Prefix/Suffix, select the appropriate category, and add the terms that will be words’ prefix or suffix.

Then, you go to “Clean” ribbon and click on the “Batch” button. The end result when adding prefix/suffix are given in the image below.

Converting Data

The software features an option to convert Global and EPS activity codes to Project Activity codes and EPS to Global Activity Codes. The activity codes are important to schedulers and planning engineers when creating different types of work performance reports.

So here are the type of categories that can be converted with ScheduleCleaner:

  • Convert Global/EPS to Project Activity Codes.
  • Convert EPS to Global Activity Codes

Moreover, you can convert Global calendars that are used in the project plan into project and shared resource calendar. In this way, you will avoid errors when importing the XER file into P6 database.

Save time with process automation

Who doesn’t want automation? Automation saves time and gives a sense of comfort and security.

Here, it’s not actually a full automation because you still need to click on a button in order to perform an action or combination of actions. But this is quite useful when you have a set of actions that need to done on a daily basis such as sending a daily progress report to top management or uploading recent progress into a database.

Automation is ScheduleCleaner is viable through creating Templates, save them and apply to imported XER files.

Batch Clean

“Batch Clean” is a feature that works with templates. User must create at least one template and assign it to a file in order to use the batch file cleaning.

“Quick Clean” on the other side is more suitable when user wants to modify very small number of project files, while “Batch Clean” is useful when large number of data files, usually located in different folders, need to be modified.

Final Words

ScheduleCleaner enables you to quickly remove or anonymize confidential data in XER data files exported from Primavera P6, while keeping the schedule integrity.

It replaces the many work when “cleaning” XER file prior to sharing the file or import to a production database.

As the manual process of removing or anonymizing project data is time-consuming and unreliable, performing Batch Clean in combination with Templates can speed up the process.

Organizations can significantly improve their productivity, communication and security by integrating ScheduleCleaner in their working environment.

P6 Date Formats – Quick Data Hacks!

In dealing with P6 data, sometimes what you expect, is not what you get. When it comes to date formats, this is quite relevant. Here is a guide to transform XER and P6 copy-pasted values into proper date formats.

In dealing with P6 data, sometimes what you expect, is not what you get. When it comes to date formats, this is quite relevant. Here is a guide to transform XER and P6 copy-pasted values into proper date formats.

Problems with XER file format

When dealing with a native XER file, you need to be careful because you can’t always use the field you want. Below is a screen print from a typical TASK dataset.

XER_date_format

When an actual start/finish date has been captured (as seen in the “act_start_date” and “act_end_date” fields above), then the dates that are stored in the “early_start_date” and “early_end_date” fields are no longer valid.

Thus, you need a routine to check if an actual start date exists, and use that in lieu of the early dates. Ultimately there is no good way to deal with, excel to write a routine somewhere in your data import routines – if importing in a database. Another option is to edit your XER import excel file to add 2 new columns for simply “start” and “finish” that will run your check for you in the native excel file before you import the data into a database.  This is an easy hack anywhere you manage your data: However, a core issue here is:

Where will you clean your data?

For me excel is often easier, but that does mean your processes will not be fully automated. Either way, the fix is fundamentally as seen below

 START=IF(ISBLANK(act_start_date),early_start_date,act_start_date)

FINISH=IF(ISBLANK(act_end_date),early_end_date,act_end_date)

 

Problems with P6 format (copy-paste)

If you are not dealing with an XER, you will likely simply copy-paste directly from P6 into Excel. Here again, we have to deal with a few (minor annoying) complications. Everyone I know in the planning world deals with this and has their own routines. I wish I could post them all, because some truly elegant solutions exist. The below is not meant to be “do it this way”, just more of an indication that if perhaps you are running into difficulty “this will work!”.

Start Finish
22-Oct-18 A 9-Sep-19
09-Sep-19*
09-Sep-19*
10-Sep-19 10-Sep-19
05-Jul-19* 10-Sep-19
27-May-19 A 10-Sep-19
03-Dec-18 A 10-Sep-19

Above we can see a typical copy-paste result from P6. Obviously, this is filled with non-date formatted cells. This is caused by the ” A” indicator for activities that have an actual date, and a “*” for activities that have some sort of constraint applied. I have seen a few equation based solutions to strip the bad characters out. I find a code based solution to be slightly more elegant. It also means I do not have to deal with adding extra columns to my file, or performing any copy-paste values. But again, I am sure we all have nice solutions.

The easy fix I use: Scrubb_Data().

Sub Scrubb_Data()

Dim xChars As String
xChars = “*”

Sheets(“P6_CURRENT”).Select
ActiveWindow.SmallScroll ToRight:=3
Columns(“V:V”).Select
Selection.TextToColumns Destination:=Range(“V1”), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=True, OtherChar:=xChars, FieldInfo _
:=Array(Array(1, 3), Array(2, 9), Array(3, 9)), TrailingMinusNumbers:=True

End Sub

This routine only works on 1 column at a time. For me, I can simply copy and paste the select statement and insert a routine for each excel column with a P6 date  (typically just start and finish). After pasting in P6 data, I will open the routine and click “play”. You will need a macro enabled file and will need to be at least a little comfortable with “view code”. There are a few options to auto call this function using a button, call the function when you close the file, many different options.. They all require a bit of VBA knowledge, although, like seriously, who isn’t in our world.

For the above, I ripped most of this from a routine someone else wrote (a routine that stripped the ” A” off the string. I had to add the xChars = “*” aspect.

For a find/replace statement, it is possible to use the below string to strip the “*” off. Here we have to use ~*~ because if we use *, it will replace the entire string. Again, a million ways to handle this.

Capture

Conclusion

Again, there are a 100 ways to skin this cat, and all achieve the same result. When we deal with data from P6, I find it so amazing that when you ask 10 people how we deal with the data, you will end up with at least 20 replies. Ultimately,  I believe this issue is a telling critical flaw in the underlying software. In the digital world, we need a completely different paradigm shift in the way we store and manage data.

Specifically in the Project Management world, I doubt anyone who uses tools such as JIRA or DevOps have to deal with annoyances such as this. Thus, perhaps we too should be using those tools!

ScheduleReader

Introduction to ScheduleReader™

Companies and organizations always strive to work hard towards stimulating employees’ motivation, improving company’s performance and, ultimately, achieving project success.

At times when all these aspirations depend on different factors, it is inevitable to adopt certain practices that would advance the working environment. For example, for the companies working on complex projects in large scale sectors such as construction, industrial manufacturing, oil and gas, and public utilities, it is imperative to practice project management.

The process of project management consists of several phases that are critical for successfully finishing a project so each needs to be carefully and properly executed. But the project management flow continually needs to be ameliorated so that key project’ deliverables are achieved. One essential factor in the project management process is the use of good project management software.

Organizations in the above-mentioned sectors generally plan their projects in Oracle® Primavera P6. For accessing these project plans, which are stored in Oracle’s database, is required to have a license. Obtaining licenses for all project participants might be costly, therefore many decide to buy a couple and to go with the .pdf reader as the tool for reading project plans.

But the upper management isn’t fully aware that this can be a risky way of managing a project, which can cause the project to fail. It can complicate the performance of all project participants and consequently prevent the progress or accomplishment of the project.

ScheduleReader is the software solution which can replace the .pdf reader and simplify the process of managing a project. It is considered as effective solution that contributes to all four phases that comprise the process of managing a project. By implementing the software in a specific working environment every project participant will be able to access the project plans without the need for an expensive Oracle license. It is possible to make the management of the project faster, more comfortable and definitely more functional.

The implementation of ScheduleReader in your environment can seem a challenging and unpleasant change for your project team, however it is extremely simple as ScheduleReader is a completely standalone solution and at the end, it will certainly bring a positive impact to your organization.

primaverareader

It is developed as an independent standalone software application, with the objective to offer a simple way of viewing project data without having Primavera P6 license. It supports project schedules exported in .XER, .XML and .XLS file formats. The viewer is designed with a highly customizable interface and overall organizational structure that resemble Oracle® Primavera P6.

This enables users to easily navigate through different tasks in the project. Choosing ScheduleReader™ as a software solution offers advantages to all project participants by giving them the possibility for a dynamic overview of the project, simple comparison of project’s baselines, easy navigation through activities and prompt reporting on project’s status.

Key benefits of ScheduleReader™ that boost success

As must have P6 addition to you project management software suit ScheduleReader™ offers   improved visualisation of project data and sharing of project information.

When it comes to the company’s and project teams’ benefits, it guarantees establishing better communication and collaboration. On the other hand, when it comes to the project, ScheduleReader™ enables better budget and time management, facilitates the process of monitoring and guarantees improved control, contributing to on schedule project delivery.

ScheduleReader Standard and PRO

There are two versions of the ScheduleReader software that are developed to answer different company and project needs. ScheduleReader Standard is designed to modernize the work and provide benefits for all project teams and stakeholders who are currently viewing the plans in PDF or XLS.

On the hard, for the experienced project professionals who need to analyze schedules, create reports, better visualize and present summaries of complex project data, ScheduleReader PRO was developed – a version consisted of ScheduleReader Standard with additional features for reports generation and KPI analysis that allows you to quickly generate rich visual reports with a single click of a button.

What can you do with this software?

There are many key features that help positioning ScheduleReader ™ as the optimal solution for viewing project plans created in Oracle® Primavera P6. Some of them are the following:

  • Activity View

Allows users to have a clear overview and a detailed graphical representation of all open project activities. With this customizable window, users can have an organized view of project’s specifics; they can create Groups, Filters and Sorts with multiple codes and UDFs; save the views as a layout and even customize the bars in the Gantt chart.

  • Trace Logic View

Combined with ‘Activity View’, this feature can be used for in-depth analysis of the project schedule activities. For example, when one activity is selected in the ‘Activity View’ the user can view its predecessor and successor activities in the ‘Trace Logic View’. For the users who would need to compare up to four baselines for a particular project schedule and get a graphical display of the project’s specifics, ScheduleReader™ provides the feature ‘Baselines View’.

Activities

  • Progress Update

Very important feature which gives the possibility to give constructive feedback, as well as to propose updates for % Complete, Actual Start, Actual Finish, Activity Status and Activity Codes, which can be accepted and rejected by their superiors. This feature is perfect for field work as it gives you the opportunity to propose activity updates without interfering with the original project schedule file.

Progress-update

  • Resource Usage Profile View

By setting the timescale for displaying data values, the users can view the quantity information for resources or roles or analyze their costs.

  • S-Curves

Help for a graphical presentation of cumulative costs and units. With this feature users can see how planned and actual quantities are plotted.

Resource-usage-profiles

  • Bar Customization

Enables adding new bar types, removing the unnecessary ones or making modifications. User can create view that is specific for its working team and share it with his team members.

  • Graphical Reports Generation

Create default and custom reports, analyze project KPIs and schedule quality, measure the project progress and communicate information more effectively with all project stakeholders.

Reports-and-dashboards

Note: this is a guest Post by ScheduleReader

Float Erosion report between two XER

I just added a new report in the Xer reader to show the float Erosion Report between two XER, I am using the “Harbour Pointe Assisted Living Center” from Primavera P6 default installation as example.

1- Download the pbix file here

2- Save a two XER in this folder C:\XER

2

3- Refresh PowerBI Desktop

3

4- Review the report, and export for Excel for sharing/Further analysis.

4

5- if you have PowerBI service in your company then publish it there, so other users can interact with the reports.

let me know if you want to see other reports.

 

 

Rewriting Xer Reader using PowerBI

4 years ago, we had to customize the xer parser for a client, that work end up as Xer Reader, turn out the file was very popular, but unfortunately, as it was written in VBA, users get all kind of different errors, and I was not able to provide a proper support.

now I am trying to rewrite the same  Excel Macro but now using PowerBI, the file is hosted in github

in order to open a pbix file, you need PowerBI Desktop, it is a free download

I think it will be an interesting experience, some stuff are much easier using VBA, and there is no equivalent in PowerBI, I will try to document the progress.

in the main time here is my progress so far

stay tuned

Using PowerQuery to read Primavera Xer file

You can download the file here  or view the reports online

The last time I tried parsing an Xer file, I was using VBA and it was a horrible experience, anyway that was three years ago, The problem was not VBA per se, but supporting the different combination of Excel and Windows, I still receive sometimes emails from users complaining that Xer reader do not work for them, after a while I concluded it is not worth the effort.

Yesterday for no reason, I got this idea why not trying Powerquery , in 10 minutes I had a working prototype without writing a single line of code!!!  even better it reads in bulk, copy a bunch of xer in the same folders, and there are loaded, only conditions they needs to have the same revisions( don’t mix P6 rev 7 with rev 8), as different revisions of P6 introduce some minor changes in the Xer schema, ( they add or remove some columns)

It is only a proof of concepts, I only loaded some tables, (activities and activities code), the data is loaded in the Data Model, and you need at least Excel 2013 with the free PowerQuery Installed.

1

Make sure you write the path to where your Xer is stored

2

You need to refresh the Queries here

6-6

Here is a pivot with some measures, now you can group only by Project and Activity codes and obviously by activity id.

6-3

I test it with two Xer that contains 47 projects