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

Advertisements

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 QGIS to prepare Custom Maps in PowerBI

This blog post is to document my first experience with mapping in PowerBI, usually the reports we produce are time series and pivot tables, but in this case, there is a geographic dimension to the data, so I thought it is a good opportunity to try map chart in PowerBI, it turn out, it is a bit difficult than I thought.

So the data is in the thousand of piles, a lot of piles in a huge area, my first attempt was just to load the data in PowerBI and view it in a map, for some reason, PowerBI show an empty map.

PowerBI expect the data to be in latitude and longitude format, my the data is using easting and northing, I had to convert it, there are plenty of online converter, but there are not practical, as they don’t support batch processing, converting point by point is not an option.

After some googling, I find this excellent open source software QGIS, it was very straightforward, the software automatically convert the coordinates reference system (I think the technical term is reprojection), my data is GDA94 / MGA zone 55 and the result should be in WGS 84.

Voila the data is ready for PowerBI,

map-1

That’s a bit disappointing, PowerBI complain it cannot show all the points ( PowerBI has a maximum of 3500 points per chart).As a comparison, this is how Tableau show the data

tableau

Tableau doesn’t have a limitation on the number of points.

 

Alternative approach

 

As PowerBI cannot show all the point, one solution is to create a shape file that group the points into smaller areas, and again, it was trivial to be done in QGIS.

QGIS will group the point based on a filed you provide.

Group

 

sub-array

And volia

qgis

 

QGIS save the layer in ESRI shapefile, PowerBI require TopoJSON, I used the excellent tool mapshaper.org

Just make sure you import all the files not only .shp

export

Now the TopoJSON is ready to be loaded in PowerBI

topojson

 

Time to celebrate 🙂  not really there is a problem, I want to show different colour based on the status, if a sub-array is completed, I want it to show Yellow, if it is > 75 %, I want another colour and so on, the shape Map in PowerBI does not offer this option.

Fortunately Synoptic Panel   has more options, it use SVG as a map format, which I got from mapshaper.org

filled map

 

Preparing the custom map is a one time operation,  the color will change as per the construction progress, you need just to assign which measures you want to show

measures

What’s Next

As long as your data model is properly built,  you can do a lot of interesting stuff, you can filter by type of works (piling, Tracker installation, PV Modules), you can click on one sub-array and see in details what’s have been installed and what’s missing.

 

 

Build a Dashboard using Data Studio

Google datastudio is a new reporting service from google, it is still in beta, has rudimentary capabilities compared to established players ( PowerBI, Tableau and Qlik) and can be very slow, yet it has two killer features, it is free, with amazing sharing options.

Google business model for this product is very simple, the visualisation layer is free, but the preferred data source is BigQuery which is a paid product, but other source usage are free.

In the last two months, I have been experimenting with the product, I never managed to make it produces any meaningful charts, that was my mistake as I was thinking from a semantic model background, which is not valid here.

Data studio do not support multiple datasets, you can use multiple datasets in the same reports but not in the same chart, but you can filter multiple datasets using a common dimension, when solution is to create a master datasets that combine all the tables, if is the same concatenate table used in Qlik.

Now if you have a data workhouse you could simply write an SQL query to combined all the dimensions you want to report on, I don’t, so I will use what I have PowerBI desktop !! You can use R studio too, or any data management solution you are familiar with.

So the approach is build a data model in PowerBI desktop,  Query the model using Dax and export the result to CSV,  then load the CSV to Datastudio and share the report, Datastudio is only used as reporting service.

1- I will use this datamodel.

Data_Studio_1

2- And use this query to extract the dimensions and measures I want to report on


EVALUATE
VAR Earned_Table =
FILTER (
ADDCOLUMNS (
SUMMARIZECOLUMNS (
TBPAYCODE[Commodity_ID],
TBCommodity[Commodity],
TBWBS[WBS_L3],
TBDate[Project Cut Off],
"Budget", tb_Measures[budget_hours],
"Period", tb_Measures[Earned_Hours_Period],
"Cumulative", tb_Measures[Earned_Hours_Cumulative]
),
"TYPE", "Earned"
),
MIN ( TBDate[date] ) <= [Cut off] && MIN ( TBDate[date] ) >= DATE ( 2012, 1, 1 )
)
VAR Plan_Table =
FILTER (
ADDCOLUMNS (
SUMMARIZECOLUMNS (
TBPAYCODE[Commodity_ID],
TBCommodity[Commodity],
TBWBS[WBS_L3],
TBDate[Project Cut Off],
"Budget Hours", tb_Measures[budget_hours],
"Planned Hours", tb_Measures[planned_Hours],
"planned Hours Cumulative", tb_Measures[planned_Hours_Cum]
),
"TYPE", "Planned"
),
MIN ( TBDate[date] ) >= DATE ( 2012, 1, 1 )
)
VAR Spent_Table =
SELECTCOLUMNS (
FILTER (
SUMMARIZECOLUMNS (
//TBPAYCODE[Commodity_ID],
//TBCommodity[Commodity],
TBWBS[WBS_L3],
TBDate[Project Cut Off],
"Budget", tb_Measures[budget_hours],
"Period", tb_Measures[spent_Hours_Period],
"Cumulative", tb_Measures[spent_Hours_Cum]
),
MIN ( TBDate[date] ) <= [Cut off] && MIN ( TBDate[date] ) >= DATE ( 2012, 1, 1 )
),
"Commodity_ID", BLANK (),
"Commodity", BLANK (),
"wbs_l3", [WBS_L3],
"TBDate", [Project Cut Off],
"Budget Hours", [budget_hours],
"Planned Hours", [planned_Hours],
"planned Hours Cumulative", [planned_Hours_Cum],
"Table", "Spent"
)
RETURN
ADDCOLUMNS (
UNION ( Earned_Table, plan_table, spent_table ),
"Data_Date", IF ( [Project Cut Off] = [Cut off], 1, 0 )
)

Basically I am using union to concatenate the values for Earned Hours, Planned Hours and spent Hours, I added a column type to distinguish between the different tables and in the case of spent, I assign the value blank for Commodity_ID and Commodity as the measures don’t have the same granularity.

I added a column ‘Data_Date” so I can filter only the dates where it is equal to the cut off.

3-Load the CSV

Data_Studio_2

If a field aggregation is none, then it is a dimension, otherwise it is a metric (measure), now, there are some functions to do calculations like sum, average but it is not DAX, it is really limited.

As an example for the measure Planned Hours = SUM(CASE WHEN REGEXP_MATCH(TYPE, ‘^Planned.*’) THEN Period ELSE 0 END)

4-build the report

Data_Studio_3

You can view the report here , and the datasets here , (sorry does not support internet Explorer, Only Chrome, Firefox, and Safari,  Internet Explorer works now, although not officially supported)

Personally the most interesting aspect of Data Studio is the integration with Google Drive, the reports you build will show in your Google drive and you can organize them in different folders, for example then you can group reports by departments, shop etc

Data_Studio_4

I think it has potential, they got the sharing right, now they need to add some basic features ( pivot table, controlled cache ….) , and more options in the calculated field formula, ( hint return null for example is not supported,  ok there is a workaround, Just create Metric*Metric/Metric, if it is 0 then it is an error, GDS return null in this case) and you will have data visualisation for the masses.

Using Powerquery List.Dates to generate time phased Budget and account for holidays

There are plenty of templates on the internet on how to spread a budget using Excel formula, in this blog post we will use the Powerquery function “List.Dates”, and a calendar table to account for holidays and periods with less working hours, for example under extreme weather conditions, the working hours per day will be less than standard working hours.

Obviously Planning software handle this scenario very well, but contrary to popular belief, Excel is still the most widely used planning software among non-planners. (I have yet to see a cost controller or QS using a planning software, even MS Project).

The function is straightforward:

List.Dates(start as date, count as number, step as duration)

In this case, count will be the durations in days, and step will be 1 day

Let say you have a list of tasks, with a start and finish date, and some kind of budget, (cost, revenue, quantities, Effort required etc).

Budget_0

Let’s load the table Task and generate the spread.

  • First we calculate the duration ( Finish-Start)
  • We use the List.Dates to generate the list of dates between the start and finish of every task.
  • Expand the list to become a column.
  • Merge the list of date with the calendar table to get the working hours per day.
  • Now we sum the total working hours per id.
  • The % by day = working hours per day/ Total working hours per task.

Budget_1

Voila, you have % per day.  Now to spread the budget, just multiply the budget by % per day.

Then we load some dimensions table in order to slice and dice the pivot table and WE add a measure to calculate cumulative Budget time. As we are using a calendar table, we can show the budget per day, Month, year or any time period.

Here we got a nice little Star schema

Budget_2

 

 

 

Now a Pivot table with conditional formatting will show a Gantt like chart.

Budget_3

And a pivot chart to show the Budget per Period and the cumulative.

Budget_4

You can download the template here, you need Excel 2013+, if you don’t have it, you can always download PowerBI desktop for free, and import the Excel file, but you need to redo the charts though.

 

Integrated Project Control system using PowerBI

One of the most popular discussion in planning forum is how to have an integrated project control system, every practitioner has a different opinion how it must be done, and of course you get a lot of marketing from people trying to sells their systems

In this blog, we share high level description of a data warehouse built using PowerBI desktop.

Data warehouse are not widespread in the construction industry, because the reporting specifications are different from project to project, and every client has a different systems and tools, and trying to have a standard system works only if you are the main contractor but if you are a subcontractor you have to adopt the client system.

Another reason is; it required a specialised IT skills, we are just business users not programmers, we do understand data very well, but not necessarily having the skills and tools to manage it, The good news is, with the rise of self-service Business intelligence, we have exactly that, Powerful data management tools yet accessible (assuming you want to learn something else than Excel).

So at high level this is how it works.

 Project Controls Data Warehouse

  • The Data warehouse was built using PowerBI desktop, I know it should be called sematic model, (for me data model, data warehouse are fundamentally the same thing), initially it was using Excel PowerPivot but it did not scale well with the increase of the volume of data.
  • As the data is not always in the format we want, PowerQuery is very handy in this case, as virtually it can transform any source of data, example lookup the subsystem using the tag field, trying to do that if you have 8 Million rows using Excel or Access is not feasible.
  • We maintain Master tables to integrate all the different source of data (tags, WBS, subsystems etc)
  • Every week, we get new Export from the source systems (Cobra, proprietary database systems etc), we load the new data and keep the historical records, it took 15 minute to refresh, which is quite impressive, Cobra alone is a folder 60 Excel file, and nearly 2 Giga in size.
  • Usually you publish your reports into PowerBI.com service to end users once your refresh your data model, in our case we can’t use the cloud for privacy reason, instead we use Excel as a reporting tool that pull the data from PowerBI desktop, the advantage of this approach is that we have different reports for different users, Skyline, Gantt chart, Client reports (in their required format), management reports etc.

Some thoughts.

  • As you can see Primavera P6 is used only as a forecasting tool and to calculate the critical path, the earned value calculation is done in the data model, personally I think P6 should not be used as the centre of your project control system, I remember the first time I start learning Primavera P3 ( a long time ago:), we kept asking the trainer how it is possible to track the spent hours at the activity level, the answer is we don’t, actually deciding at which level you track you spent hours it is the most important decision to make when you start a new project.
  • the basic idea here is in order to have an integrated project controls system is stop trying to have one, data will be always in silo, don’t try to change other department how they manage their specific data, it will not work and they will not listen to you anyway, So instead of trying to have one system to rule them all, just use the existing systems and build a data warehouse for reporting and Integration with P6.