For project owners that have commenced the digital transformation path for their capital construction projects delivery, they would usually find that there are at least six sources of data that need to be used to provide them with a 360-degree single version of the truth monitoring, evaluation, and visualization of the progress status and performance of their projects. To start with, there is the planning and scheduling data from Primavera P6 or MS Project, building information model (BIM) data from Revit, geospatial data from ESRI GIS, construction cameras feed, and the data from PMWeb for the 300 plus business processes needed to manage project scope, schedule, cost, quality, communications, resources, risks and issues, procurement, contracts, health, safety and environment (HSE), sustainable development, claims, financial among others. Of course, some project owners might also use dedicated applications for cost estimating, risk analysis, document management, etc. which means having more data sources.
The project owner’s project management team needs to be able to create and share reports from those data sources to provide project stakeholders with the insight to make faster and better-informed decisions. Nevertheless, they need to achieve this without replicating data capturing or paying for the costly data sources integration. They also understand that there could be different owners for each data source and they might not be willing to change how their data is captured and generated. On the other hand, they all will agree on fir the need for having a few standardized coding structures that must be respected by all data sources. Those include for example the project name, contracts number, Work Breakdown Structure (WBS) levels, Cost Breakdown Structure (CBS) levels, locations, building systems identification, specification sections, project schedule activity numbers, resources names among others.
MS Power BI is a business intelligence and data visualization tool that can facilitate the multi-data source consolidated monitoring, evaluation, and visualization of progress status and performance of capital construction projects. It can show the location of the organization’s projects portfolio on a map, show the building systems’ objects and spaces on a BIM model, display bar charts, histograms, donuts, pie charts, and other types of visuals, display data tables and matrix tables among others. All those visuals could be designed to display data from the multiple data sources for which they will common data fields to associate, blend and visualize the data from the different data sources. The best data field to associate those data sources is the project schedule activity.
The first data source is the BIM model created using Autodesk Revit which will be used to provide the data on the project’s building systems objects and spaces. It is therefore very important that the used BIM model, regardless of its level of detail, have all the data required to be visualized. For each BIM element, this data could include the BIM ID, system category, property, level, quantity among all other information that the BIM team can provide.
The BIM model and associated data will be exported in a format that can be read, used, and visualized in MS Power BI. This will be achieved by using the “3DBI” application which will be installed as a Revit Add-in. The 3DBI visual developed by KG-DEV (https://kg-dev.be/) is an application that exports the BIM model database as well as the BIM Model in a JSON file format to enable reading and visualizing it in MS Power BI.
Now the BIM model data along with the BIM model will become available to be viewed in MS Power BI. Bar chart and donut visuals and data table, as well as the 3DBI BIM visual, will provide the project management team with an understanding and visualization of the BIM model and its components. Since MS Power BI is an interactive reporting tool, the BIM model and other visuals will automatically adjust themself depending on the selected data.
The Primavera P6 or MS Project schedule will be imported into PMWeb using the default schedule link command. This will bring the data fields for activity ID, description, original and remaining duration, early start and finish dates, total float values, WBS level value among others. Unlike the Revit data, MS Power BI can be set to read PMWeb data directly from the PMWeb MS SQL database depending on the provided credentials.
In other words, MS Power BI will use this data to report on the project schedule performance directly and without the use of Primavera P6 or MS Project applications. This is a very critical requirement as for most capital construction projects, the project owner will usually depend on the schedule data provided by the contractor or the project management consultant to report the project’s schedule performance and delays.
The BIM data will be also imported into the PMWeb Cost Estimate module. This will serve two important purposes. The first is to develop the project cost estimate if the BIM model has captured the take-off quantities of the different objects to be priced for which PMWeb will be used to provide the unit price to come with the project cost estimate. Using PMWeb, the approved cost estimate would become the basis for generating the project budget, budget adjustments and the bid packages to be released for bidders before they are procured. Of course, there is also the option to import the cost estimate from any other cost estimating software through MS Excel template.
The second purpose for importing the BIM data into the PMWeb cost estimate module is to create the data association between BIM model objects IDs and the project schedule activity. For each BIM ID, the project team can select the project schedule activity that covers this scope of work. This BIM- Schedule association document is a very important document that needs to be formally reviewed and approved.
The next step is to identify the geospatial location of each project that the project owner has and wants to report progress on. PMWeb project module allows defining the latitude, longitude, and elevation of each project to be managed. This will allow showing each project as a single point on the ESRI, Google, Bing, or any other map supported by MS Power BI.
There is also the option to display the boundaries of each project as defined in ESRI GIS. To enable showing the project or projects’ boundaries layout on MS Power BI, the MapBox visual needs to be used. A Shapefile needs to be generated from ESRI GIS to provide the coordinate points for the boundaries for each project. This Shapefile will be converted into a JSON file format to enable MapBox to generate the map with each project boundary. Of course, the great benefit of showing project boundaries is for metro, railway, pipeline, roads, highways, and other types of linear projects and other horizontal projects like forestry, landscaping, playgrounds among others.
The project management data captured from the three hundred plus business processes managed in PMWeb as well as the data for the BIM ID and Activity association table and project schedule detailed above will be accessed directly by MS Power BI by providing secured access to PMWeb SQL Server Database. This will enable the project team to run the different data queries to extract the data tables and fields that will be used to create the different reports and dashboards using MS Power BI. This direct integration with the PMWeb database will ensure that the project management team will always have access to the latest projects’ data captured in PMWeb. MS Power BI can be configured to schedule the refreshing of the data extracted from the different linked data sources.
MS Power BI will automatically detect and create relationships between the different data sources using fields that are in common in the different data tables. Those would usually include the data fields that need to be standardized across the different data sources as stated above. For example, those would usually include project name, contracts number, Work Breakdown Structure (WBS) levels, Cost Breakdown Structure (CBS) levels, locations, building systems identification, specification sections, project schedule activity numbers among others.
In addition to the data sources detailed above, the MS Power BI report can include hyperlinks to time-lapse cameras on the construction site like those of Evercam (www.evercam.io). This can be achieved by providing hyperlinks to the camera’s IP addresses for which the project team member can view the video stream on another window when the hyperlink is selected. In addition, there is also the option to show progress pictures as well as recorded progress videos on the progress reports.
Of course, the MS Power BI model can be appended with other data sources if needed. For example, the project owner might be interested to have an in-depth analysis of the Primavera P6 schedule data which might require other data fields than those which were imported into PMWeb. In this case, integration can be created between PMWeb and Primavera to import the needed data not only the raw data format but also modeled data to reflect for example the time-spread data for resources and cost. The MS Power BI report below created by Mustang Technologies (www.mustangtechnologies.com) shows an example of a Primavera P6 schedule that was imported into MS Power BI. This report could also blend the data captured by PMWeb and Primavera P6 for which it will use the budget cost and actual cost captured in PMWeb along with the earned cost and forecast to complete cost captured in Primavera P6 to produce the cashflow report shown below.
About the Author
Bassam Samman, PMP, PSP, EVP, GPM is a Senior Project Management Consultant with 40-year service record providing project management, project controls services, and project management information systems to over 200 projects with a total value over the US $100 Billion. Those projects included Commercial, Residential, Education and Healthcare Buildings and Infrastructure, Entertainment, Hospitality, and Shopping Malls, Oil and Gas Plants and Refineries, Telecommunication, and Information Technology projects. He is thoroughly experienced in complete project management including project management control systems, computerized project control software, claims analysis/prevention, risk analysis/management (contingency planning), design, supervision, training, and business development.
Bassam is a frequent speaker on topics relating to Project Management, Strategic Project Management, and Project Management Personal Skills. Over the past 40 years, he has lectured at more than 350 events and courses at different locations in the Middle East, North Africa, Europe, and South America. He has written more than 300 articles on project management and project management information systems that were featured in international and regional magazines and newspapers. He is a co-founder of the Project Management Institute- Arabian Gulf Chapter (PMI-AGC) and has served on its board of directors for more than 6 years. He is a certified Project Management Professional (PMP) from the Project Management Institute (PMI), a certified Planning and Scheduling Professional (PSP), and Earned Value Professional (EVP) from the American Association of Cost Engineers (AACE) and Green Project Management (GPM).
Bassam holds a Masters in Engineering Administration (Construction Management) with Faculty Commendation, George Washington University, Washington, D.C., USA, Bachelor in Civil Engineering – Kuwait University, Kuwait and has attended many executive management programs at Harvard Business School, Boston, USA, and London Business School, London, UK.