Unique Solution to a Complex Challenge
Throughout the life of a multiyear capital program, many technology tools and data sources are developed as a means to assist with managing various tasks. However, not all of these tools are able to interact with each other, leading to data that may be stored in multiple siloed locations. Cumbersome processes for opening and interpreting source files from multiple locations creates difficulty in analyzing data, among other problems.
For one West Coast utility, this was the challenge confronted during a multiyear fiber rollout program. Consisting of more than 100 separate projects, the overall scope called for installing communications infrastructure at more than 80 substations and on related transmission lines. This $200 million program proved to be dynamic, ever-evolving and extremely fast-paced.
As the program ramped up to its peak in 2020, it was proceeding at a pace that was double that of prior years. It quickly became apparent that a different approach was needed for quicker data analytics and fast decision-making. A combined team from Burns & McDonnell and 1898 & Co., began developing a solution that would aggregate and normalize the data being used, enabling presentation in an easy-to-consume dashboard format.
Read The White Paper
Unique Solution to a Complex Challenge
Throughout the life of a multiyear capital program, many technology tools and data sources are developed as a means to assist with managing various tasks. However, not all of these tools are able to interact with each other, leading to data that may be stored in multiple siloed locations. Cumbersome processes for opening and interpreting source files from multiple locations creates difficulty in analyzing data, among other problems.
For one West Coast utility, this was the challenge confronted during a multiyear fiber rollout program. Consisting of more than 100 separate projects, the overall scope called for installing communications infrastructure at more than 80 substations and on related transmission lines. This $200 million program proved to be dynamic, ever-evolving and extremely fast-paced.
As the program ramped up to its peak in 2020, it was proceeding at a pace that was double that of prior years. It quickly became apparent that a different approach was needed for quicker data analytics and fast decision-making. A combined team from Burns & McDonnell and 1898 & Co., began developing a solution that would aggregate and normalize the data being used, enabling presentation in an easy-to-consume dashboard format.
Developing the Solution
The integrated team began developing a digital dashboard solution that would automatically consolidate and aggregate the project data from multiple sources into a single repository. This would eliminate many laborious and often manual data stewardship tasks.
This effort started with documenting data sources and reporting needs. This was essential in order to map what data is needed as inputs and what the outputs would look like. The 1898 & Co. team evaluated multiple data sources including ERP-type applications (Primavera P6), the document management system (Aconex), and Excel spreadsheets. Then they developed a plan to drive to dashboard visualization.
Multiple dashboards were needed for the project, but the process to develop a dashboard generally followed this path:
- Determine the data source and review existing data.
- Model the data and create the corresponding data architecture so that the appropriate relationships can be established between the data sources.
- Cleanse the data so it can be normalized into a database (much of the data in spreadsheets do not follow good standards and need some cleaning before it can be fully utilized).
- Create an automated process to periodically grab the data source.
- Ingest the data into a structured database.
- Build dashboards to visualize the data.
- Review dashboards with stakeholders and modify as necessary.
- Publish the dashboards for all users.
Several tools were utilized for this program, including:
- Oracle P6 for project management and scheduling.
- IBM’s TM1 for forecasting.
- SAP Financials for managing project costs.
- Aconex for business process workflows and document management.
- Custom spreadsheet trackers to report on key activities such as permitting and construction scoping.
Implementing these steps took about eight weeks from start to utilization and required close engagement between the dashboard team and the program management team to be certain project needs were met and the data analytics were meaningful.
The overall solution was developed utilizing tools such as Safe Software’s Feature Manipulation Engine (FME) for Extract/Transform/Load processes, Microsoft’s SQL Server on Azure for data storage, and Microsoft’s PowerBI for reporting and visualization.
Figure 1: Digital dashboards provide up-to-date summaries of all key program metrics, allowing all stakeholders to easily monitor progress.
CLICK TO ENLARGE
Better Visualization Means Better Management
The dashboard deployment has resulted in a number of benefits for the project team. These include better visualization of the data, quicker turnaround time for data requests and a forced cleansing of the existing data, which also enforces better data management going forward. With more consistent management of the program, the utility gains faster and highly accurate responses to any inquiry.
The data is now much more transparent to both the program team and the utility. All data sources contribute to the dashboards and are available to all stakeholders. This has reduced confusion, eliminating instances when a project leader or customer may receive conflicting answers to questions from multiple people. With a common view of the dashboards, no conflicting data is being supplied.
In the past, it was common for a project stakeholder to ask for a set of data that required significant time and effort by members of the project team to create and assemble it. Now, with the data stored in an easily accessible database, most of those requests no longer occur because it is readily available on the dashboards. Other requests can be fulfilled with a couple of quick SQL queries, grabbing data from multiple sources to generate an ad hoc report. This also greatly reduces the time spent searching for data.
Utilizing a database as a project data repository enforces discipline necessary to maintain data quality and upkeep.
To illustrate, spreadsheets have columns of data in which 80% might appear consistently, with the remaining 20% appearing inconsistently. This can lead to issues tracking down information. For example, it is common to see columns on a spreadsheet with only a notation such as: “Mike is checking on that,” or a similar comment indicating that data is incomplete. Obviously, this is not consistent tracking.
Uploads into a database ignore bad or incomplete data so that exception reports may be produced, leading to both cleaner data for review and actionable notifications to clean up missing or inconsistent data. Having data readily displayed on a dashboard forces project team members to stay on top of data updates, leading to even more accurate reporting.
Some data sources can be automated as well. This reduces the amount of data handling and copying — a step that often leads to additional data inconsistencies. For instance, it is typical for data to be manually extracted from P6 schedules and then copied into spreadsheets to be dispersed. The data pull from P6 can be automated and inserted into the database with no manual intervention. It then can be viewed on dashboards and pulled directly into spreadsheets, eliminating potential human errors generated via copying.
Project status reporting is another example in which automating the status to be pushed from P6 key milestone completion dates into the dashboard eliminates human interpretations of project status. Again, this removes the potential of variability that may arise from person to person.
Success Factors
For this effort, multiple groups played a role in making the fiber build dashboard development a success.
It was vital for the project team to play an active role in providing descriptions of the types of information to display. This included defining the underlying business processes and identifying sources for the data.
Once the technology team analyzed the sources, it also needed help from the project team to clean up any nonconforming data and to provide any data that was not yet captured. Though this can take significant time and commitment from the project team, it is crucial to the long-term success of the initiative.
As these dashboards take shape and begin to mature, the process is only beginning. As dynamic data tools, it is very important for the project team to utilize the dashboards in daily activities — identifying additional needs and uncovering any data issues.
The technology team also must functionally understand what is being delivered by the project team and how these metrics help the program to run efficiently. In some cases, the technology group may submit requests that are tedious and become time-consuming for the project team to define. Communication between the technology and project teams in these instances is critical. It is essential that the technology team have a clear understanding of project details. This can help the project team greatly. For example, it is often helpful to provide verbal or written examples to confirm understanding of what the technology team really needs to deliver the content. A true team effort requires clear and consistent communication to be successful and avoid needless friction that can result in wasted effort.
Having stakeholder engagement from initiation to launch is also critical to success.
In this program, training sessions were scheduled for launch with each different stakeholder group. All had the opportunity to walk through the new dashboards. This resulted in better acceptance and adoption of the new feature, fewer data enhancement requests, and higher stakeholder engagement.
This dashboard solution is dynamic and ongoing, requiring some enhancements and maintenance for continuing support. This support includes:
- Proving costs associated with utilizing the tool sets.
- Responding to requests to tweak the dashboards and tool sets.
- Managing changes to the system as source tools are modified.
- Providing general maintenance of the underlying systems.
The costs associated with tool sets and general maintenance are covered by a set fee that includes an agreed-upon amount for support. Any requests for major enhancements or new functionality are typically addressed via contract change order.
Any dashboarding project requires close collaboration among all stakeholders — including the customer — for ultimate success. These tools are true examples of technology breakthroughs, enabling members of the project team to make the dashboards part of their daily lives, driving efficiencies and offering full transparency for stakeholders.
It takes effort from multiple disciplines to focus resources to deliver outcomes that utilities are coming to greatly appreciate as they work to meet their ever-evolving mandates for system resilience and reliability.
