Environment Canterbury speeds up outcome tracking with the Power Platform

Environment Canterbury (ECan) is a part of the local government for the Canterbury region in New Zealand. They used the Power Platform (PowerApps, Microsoft Flow and Power BI) to enable a consistent approach for managing and reporting on freshwater and natural resource projects. The solution was implemented in under eight weeks by Datacom Systems, a Microsoft partner based in New Zealand. It has served as a proof point for moving ECan from old paper and Excel-based processes into new ways of working with digital technologies.

BUSINESS SCENARIO

Environment Canterbury works in partnership with the communities of Canterbury to promote the sustainable management of natural resources. This involves using innovative, cost-effective and technically excellent methods, and ensures that decision making is based on the highest quality information. They work on long term environmental outcome programs that consist of multiple milestones and related projects. ECan needed an affordable solution that would provide greater consistency across projects, higher levels of visibility, and quicker access to data.

BEFORE POWER PLATFORM

The region managed by ECan is divided into ten “water zones” for freshwater management, biodiversity and biosecurity projects. Each zone had their own series of Excel files and paper-based process to track their work, with no consistency across zones. This led to several challenges:

• It did not effectively meet their needs for consistency of approach or for reporting.
• There was a time lag of up to a week to compile multiple spreadsheets and paper into digital reports.
• There was no efficient method of reporting accurately on the progress of projects toward larger environmental milestones and long-term plan outcomes.

What they really needed was a fully featured custom solution that gave them “information at their fingertips”.

Example Excel spreadsheet used to manage and report on projects (content blurred to maintain confidentiality)

POWER PLATFORM SOLUTION – “INZONE APP”

Environment Canterbury partnered with Datacom, an award-winning Microsoft partner and New Zealand’s largest IT provider, to develop a custom solution using the Power Platform. Initially they were considering a standalone bespoke web solution but had a short time period to get the pilot up and showing benefits. Datacom proposed the use of PowerApps and Microsoft Flow combined with their on-premises SQL environment to allow for a rapid build.
The initial pilot solution was built in 8 weeks and deployed to production following comprehensive user acceptance testing by Environment Canterbury. A number of minor iterations were deployed to fine tune the application and it continues to be updated on a monthly cycle based on requests from users.

PowerApps: Zone Managers and project participants use a PowerApp to search and view the projects they are involved with, as well as the status of other projects that may be related to them. All projects are tagged with the zone and specific milestones, and all milestones are tagged with the long-term outcomes. The app provides a customized color scheme, which makes it easy to differentiate and navigate between Projects, Milestones, and Outcomes. All screens for projects are green, milestones are blue, and outcomes are orange. Each item, such as a project or a milestone, has a friendly readable view that is printable as a PDF (see “Additional screenshots” section below).

Microsoft Flow is used behind the scenes to provide notifications at set periods, and provide escalations if projects are not updated at the required time. The flows are also used to call specific SQL views, render date/time into New Zealand date/time formats, and generate HTML table-based digest emails listing all items relating to a specific person in a single email.

All the data is stored in an on-premise SQL database and accessed via the data gateway. Existing and new Projects, Milestones and Outcomes are stored in three main SQL tables, with 7 relational tables used to capture additional project metadata. Projects, Milestones and Outcomes also track their monthly and quarterly status updates through the application.

Power BI and SSRS are used for dashboard reports and provide print friendly reporting pages per item. The reports are provided to internal and external stakeholders.

IMPACT AND BENEFITS

Within a month of the pilot being rolled out, ECan started to see labor savings from the increased visibility and easy search capabilities across the five hundred projects entered into the system. Overall, some of the key benefits from using the Power Platform is as follows:

• The solution was quick to create and integrate with SQL. They were able to have a solution running in production within eight weeks, which was a much shorter time and at a lower cost than building a bespoke web-based application.
• Faster to input data and could be reported on within minutes versus 1-2 weeks.
• High visibility for the freshwater management and other projects adopting a cloud-based approach.
• Automated notifications ensure that projects no longer “fall through the cracks”.
• Agility – ECan is able to continue to update the app and Flows based on feedback from Zone Managers.

Although this introduces a whole new way of working, which can be disruptive, users are quickly seeing the benefits and have been actively engaged with the app. Based on the progress and ROI from the cost savings gained from this rollout, there is a potential of similar apps being developed for other environmental disciplines that ECan works in, such as Air and Transport.

SOLUTION ARCHITECTURE

PowerApps and Microsoft Flow connect to an on-premise SQL server via the data gateway. Power BI and SSRS is used to generate reports.

ADDITIONAL SCREENSHOTS

View of active projects for logged in user. Projects, Milestones and Outcomes screens are each distinctly color coded.

Multiple filtering options available

Form to create a new project

Every item has a friendly readable page that is printable as a PDF

Outcomes screen with orange color theme for easy differentiation

POWER PLATFORM PRACTICE AT DATACOM SYSTEMS

Datacom has been building solutions with PowerApps and Microsoft Flow since mid-2017. Rich Burdes leads the Power Platform practice consisting of four fulltime PowerApps developers plus additional temporary staff. They’ve successfully deployed apps into production for over fifteen different clients. These solutions involve a mix of brand-new solutions as well as refactoring legacy solutions that were built in Lotus Notes, InfoPath, SharePoint on-premise, and even a couple in Nintex Forms. They are actively involved in the PowerApps community and regularly post videos and samples.