MBNCanada Data Warehouse Redevelopment

Stay in the loop


The Client

The Municipal Benchmarking Network of Canada (MBNCanada) was originally formed in 1998 as a collaborative of municipalities interested in measuring municipal services. MBNCanada’s purpose is to enhance municipal service delivery through collaboration, networking and the implementation of performance measurement, benchmarking and other municipal continuous improvement programs and initiatives.  Currently, there are 13 municipalities across five provinces that participate in MBNCanada programs and initiatives.

The Challenge

Since the initial launch of the Data Warehouse in 2004, the reporting needs of MBNCanada have expanded. MBNCanada currently tracks services from 13 partner municipalities with over 700 performance measures of municipal service delivery across 36 municipal areas with goals to continually expand. It is crucial that the Data Warehouse provides an excellent user experience including functionalities such as audit trails, customized permission setting, navigation and export functions. The data warehouse serves not only as a repository for tracking these performance points, but also as a central spot for both MBNCanada and partner municipalities to leverage report visualizations and export data out to build out customized report visualizations.

The Solution

Envision IT assessed the existing data warehouse and provided a consolidated roadmap to migrate the data warehouse to a modern technology stack that is primarily cloud based. This will ensure the data warehouse is able to scale to meet the future needs of MBNCanada as they continue to work with more partner municipalities.

The new MBNCanada portal provides access to the data warehouse for over 1,000 local member municipality users. Municipal leads manage their members and the roles they have. Users sign in easily with their own organizational credentials or an emailed one-time passcode, which means no new passwords to remember. Links on municipal landing pages allow users to quickly access their specific Service Areas and measures.

eum sign-in

Excel Online is used to enter the measures. Co-authoring allows multiple municipal members to work at the same time. A rich online data dictionary offers the ability for municipalities to extend their version with data source notes.

The start of the year rollover process sets the system up, with Municipal Excel sheets automatically created and uploaded to security trimmed workspaces. Measures are defined in each service area from their municipal-specific source, and a connection is established to the Data Dictionary definitions. Municipal Leads fill in a security matrix for each of their staff, and staff then fill in their measures on the Service Area Excels they have access to. Specific cells can be locked on input Excels as required, and additional security features include an audit trail and previous year lockdown. 

MBNCanada expressed interest in having the data warehouse itself be able to build out visual representations of the data, making it easier for consumers of the data to understand and identify trends. Users previously exported queried data sets from the data warehouse to Microsoft Excel or Access, then manually built out visualizations to use for presentations. Envision IT recommended leveraging Power BI to create powerful dashboards and customizable live reports that access data from multiple sources to create deep, actionable insights in a broad range of scenarios.  Using Power BI Embedded Service within the Portal allows MBNCanada to minimize licensing/running costs.

Permissions management and access controls are extremely important for MBNCanada as they need to ensure the correct users only have access to the information tailored to them. Envision IT built the new portal on a combination of our Extranet Suite of products as well as Microsoft Teams, SharePoint Online and Excel Online. User Manager, Publisher and Data Portal working with Azure AD ensure that the delegated user management model continues to allow for a hierarchy of role-based privileges.

EUM offers a simplified user onboarding experience for municipal staff. Users are invited into the portal by their Municipal leads and don’t have to rely on MBNCanada.  Municipal Leads manage registrations for service area access and Expert Panels for their municipalities. Once approved, municipal staff gain access to current and previous year data, member contact information and the Data Dictionary. Panel members gain access to their relevant panel documents, upcoming and past meeting formation and their Expert Panel Member contact information.

The Results 

The MBNCanada solution provides a single portal and sign-on for communications, panel planning, collection of measures data, and reporting / visualizations. It is simple and easy for MBNCanada staff, Municipal Leads and all Service Area Experts to manage. Delegation of user setup and role assignment for each municipality is pushed out to the Municipal Leads. Much of the manual work associated with data restatements and preparation of the Annual Report have been eliminated.