MBNCanada Data Warehouse Redevelopment

Improving Municipal Performance Through Secure Data and Knowledge Exchange

The Client

The Municipal Benchmarking Network of Canada (MBNCanada) supports excellence in municipal service delivery though collaboration, networking and the implementation of municipal performance measurement and benchmarking. The organization has developed a unique and secure cloud-based portal for municipalities that supports data collection, analysis, and shared learnings in support of municipal innovation.

The organization initiated a project with the objectives of: moving to a single portal and sign-on for communications, panel collaboration, collection of performance measure data, and reporting/visualizations; ensuring simple and easy access for Program Office and municipal users; providing delegation of user setup and role assignment; automating the report generation.

"Feedback for our municipal users has been positive on the look and feel, ease of use, and accessibility. It has definitely been a major improvement over the old system. In terms of barriers and challenges, we're very happy that we've met the project objectives. We continue to look at further enhancements and growth of the system."

Meighan Finlay, Executive Director, MBNCanada

MBNCanada is a partnership between Canadian municipalities who believe in the power of measurement to inspire continuous improvement in the delivery of services to their respective communities. With the cooperation and collaboration of hundreds of service area experts, participating municipalities develop, collect, and analyze data on ~700 performance metrics across 36 municipal service areas. The data is used to report on current service levels, support data analysis, insight and benchmarking, facilitate the identification of efficiencies and opportunities to enhance municipal service delivery.

Service area experts from participating municipalities use the collaboration network to review and discuss performance results, identify trends and share innovative practices. Municipalities use the data and performance reporting to inform service delivery and annual budget development, to support strategic and operational planning, to facilitate program and service reviews and to update the public and local municipal Councils.

The Challenge

MBNCanada has been collecting data since 2004. In 2019, the Board identified the need to modernize its data warehouse to meet the current needs of municipalities who understand the value of data and benchmarking in informing service delivery. The aging applications and on-premises infrastructure it had previously deployed to manage the program no longer had the capacity to meet the increased demands for access to a user-friendly, real-time data portal. Recognizing the importance of ensuring a secure portal for municipal data, MBNCanada prioritized enhanced security for the new platform. The organization also needed modern data reporting and visualization tools to support performance measurement and accountability.

Moving to a modern, scalable, and secure platform would allow MBNCanada to overcome the challenges noted above, as well as enabling it to realize the potential of its mission. Providing enhanced multilevel access to a shared portal, while enabling simple access by the MBNCanada Program Office and municipal users, would increase the number of stakeholders who could benefit from this benchmarking program. Efficiencies could be achieved with a single sign-on portal for communications, collaboration, data collection and reporting/visualization. And it could unleash the full power of measurement to inspire continuous improvement with compelling functionality to support data analysis, benchmarking and networking.

The Solution

MBNCanada engaged the Extranet User Manager (EUM) team to consult on implementing a secure data warehouse that can scale to meet the future needs of MBNCanada as they continue to expand and collaborate with participating municipalities. Built in Microsoft365 and Azure, the resulting solution was a portal and data warehouse implemented using the EUM product suite, which is hosted in MBNCanada's Azure instance in the Canadian data centre. EUM manages security through Azure AD. Each municipality, expert panel and committee has its own SharePoint collection, with permissions assigned based on Azure AD groups, and group membership managed through EUM.

EUM enables five main components: municipality member onboarding, data dictionary, data entry, reporting, and expert panels (municipal service area users) and committees.

Member Onboarding

The new MBNCanada Portal provides access to the data portal for nearly 1,000 municipal users, protecting security and privacy without burdening administrators. Municipal users are invited through a customized implementation of EUM. Municipal Leads manage their users and the roles/specific access they have through an Excel-based security matrix. Permissions are assigned based on the specific user roles in each of the established MBNCanada services. Simplified sign in utilizes users' own organizational Microsoft 365 credentials or an emailed one-time passcode, which means there are no new passwords required.

Data Dictionary

The Data Dictionary is a key part of the intellectual property developed by MBNCanada and the member municipalities. It details each of the 700+ measures, what they represent, how they are calculated, and metadata about each of them. The data dictionary is both a reference area for all participants, but also the master location for the editing of all definitions using Microsoft Publisher as the CMS.

Data Entry

Staff users in participating municipalities perform data entry through the landing page. Each municipal landing page is personalized to allow users to access quickly only the specific Service Areas and measures to which they have been assigned access. Ease of use is facilitated by using Excel Online to enter the data and the Excel templates are protected to maintain consistent calculations, ensure data quality and to facilitate data entry. Co-authoring allows multiple municipal users to work at the same time and a daily data import process pulls the entered data into an Azure SQL data warehouse and Power BI dashboards.

Sample Landing Page

Sample landing page for municipal staff

Reporting

Reporting and access to the data itself is the main goal of MBNCanada, and the solution facilitates this critical function by utilizing a combination of Microsoft tools that are already licensed and familiar to all partners: Power BI for dashboards and reports, Excel for data exports and Azure SQL for the underlying data portal. All users have access to the EUM Data Portal with Azure Power BI Embedded without requiring individual Power BI licenses. The dashboards are highly interactive, the graphs and tables update in real time and can also be integrated into municipality-built datasets and visualizations.

Reporting Dashboard

Reporting Dashboard

Expert Panels and Committees

Each of MBNCanada's 36 Service Areas has an Expert Panel made up of representatives from the participating municipalities. These Panels meet several times a year to discuss, review, identify and confirm performance measures, including their definitions. The EUM Boards and Committees add-on facilitates each of these panels and committees. Microsoft 365 Groups are defined for each of the panels and committees. These are defined as EUM Groups, each with the appropriate owner, and members are assigned to each group, both through the onboarding security matrix process, and directly in EUM Admin. Documents are organized in SharePoint Online in distinct categories such as measure documents versus panel meeting documents.

Expert Panel

Expert Panel

Solution Architecture

The EUM solution provided an EUM Group for each committee. The Committee Page includes the following features:

  • General information about the committee
  • Links to important documents or files relevant to the committee
  • Link to a SharePoint library of archived documents
  • Listings of upcoming and previous committee meetings, linked to the details
  • Main committee contact
  • List of the committee members, and optionally their contact information

The Results

The MBNCanada solution provides a single portal and sign-on to facilitate performance measurement, including collection of relevant municipal data, reporting and data visualization, communications, and collaborations. The secure portal simplifies municipal access and input and enhances MBNCanada program administration. Participating municipalities can manage their local user setup, including customized access levels to support security.