MBNCanada Data Warehouse Redevelopment

The Client

MBNCanada is a leader in performance measurement that supports excellence in municipal service delivery.

Mission: To enhance municipal service delivery through collaboration, networking and the implementation of performance measurement, benchmarking and other municipal continuous improvement programs and initiatives. www.mbncanada.ca 

The Challenge

• Aging application and on-premises infrastructure
• Manual and time-consuming processes, particularly the report generation
• Need for enhanced security management and improved functionality
• Lack of data reporting and visualization tools
• Create enhanced multilevel access to a shared data portal

Objectives

• Modernize the data and collaboration portal
• Single portal and sign-on for communications, collaboration, data collection and reporting / visualizations
• Simple access for MBNCanada Program Office and municipal users
• Municipal management of users and security
• Enhanced functionality to support data analysis, benchmarking and networking

The Solution

Envision IT assessed the existing data warehouse and provided a consolidated roadmap to migrate the platform to a modern technology stack that is fully cloud based. This ensures the data warehouse can scale to meet the future needs of MBNCanada as they continue to expand and collaborate with  participating municipalities.

There are five main areas to the solution:

• Municipality member onboarding
• Data Dictionary
• Data Entry
• Reporting
• Expert Panels (municipal service area users) and MBNCanada Committees

You can also refer to the Solution Architecture for details on the underlying technology.

MBNCanada Portal

Member Onboarding

Member Onboarding

The new MBNCanada Portal provides access to the data portal for nearly 1,000 municipal users.

• Municipal users are invited in through a customized implementation of Envision IT’s Extranet User Manager
• Under the hood these users are Azure AD B2B guests in the MBNCanada Microsoft 365 tenant
• Municipal Leads (Coordinators) manage their users and the roles/specific access they have through an Excel based security matrix
• This is processed to invite in new users and remove inactive users
• Permissions are assigned based on the specific user roles in each of the established MBNCanada services areas and may include:
  1. Data entry, read-only or edit privileges
  2. Expert Panel primary or general participant designation
  3. Committee membership (if applicable)
• Simplified sign in utilizing users’ own organizational Microsoft 365 credentials or an emailed one-time passcode, which means no new passwords to remember

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 of the definitions and details.

Data Dictionary

Data Dictionary Measure Metadata

Data Entry

Data entry is done by staff at each member municipality through the landing page for each municipality.

• Each municipal landing page is personalized to allow users to quickly access only the specific Service Areas and measures they have been assigned access to
• Excel Online is used to enter the data
• Excel templates are protected to maintain consistent calculations to ensure data quality and to facilitate data entry (i.e., only specific cells can be updated)
• Previous years’ data, municipal source notes and relevant municipal comments are carried over from year to year
• Co-authoring allows multiple municipal users to work at the same time
• A daily data import process pulls the entered data into an Azure SQL data warehouse
• The annual  rollover process resets the system each year, with Municipal Excel sheets automatically created and uploaded to security trimmed workspaces by a scripted process

Sample landing page for municipal staff

Reporting

Reporting and access to the data itself is the main goal of MBNCanada.

• To support this, a combination of Microsoft tools is used
  1. Power BI for dashboards and reports
  2. Excel for data exports
  3. Azure SQL for the underlying data portal
• Power BI Embedded and EUM Data Portal are used to provide on demand access to the dashboards and reports without requiring individual Power BI licenses for all users
• Dashboards are designed to be highly interactive
  1. As items are selected, the graphs and tables update in real time
  2. Users can hover over for details, and drill through to finer grained visualizations
  3. Future plans will facilitate integration from external data sources such as StatsCan, Environment Canada and the Government of Canada Open Data
  4. They can also be integrated into municipality-built datasets and visualizations

Reporting Dashboard

We also incorporated Paginated Reports, which are formatted to fit well on a page. The advantage is their ability to be print friendly, and they can be used to generate PDF versions of the reports.

Power BI Paginated Report

Expert Panels and Committees

Each of MBNCanada’s 36 Service Areas has an Expert Panel made up of representatives from each of the participating municipalities. These Panels meet three times a year to discuss, review, identify and confirm performance measures, including their definitions. The Expert Panels also engage in knowledge exchange through the use of the collaboration portal and ad hoc networking meetings using MS Teams. There are also a number of MBNCanada standing committees, including the Board of Directors.

To facilitate each of these panels and committees, the Boards and Committees EUM add-on is used.

• Microsoft 365 Groups are defined for each of the panels and committees
• These are defined as EUM Groups, each with the appropriate owner
• 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 different categories such as measure documents versus panel meeting documents
• A meeting request form and workflow sets up new meetings
  1. Creates the meeting in the organizer’s mailbox
  2. Configures it as a Teams meeting if it is online
  3. Creates a document set in SharePoint Online to organize the meeting documents
  4. Links everything together
  5. Meeting organizer then populates the content and adds all the members to the calendar invite

Expert Panel

Solution Architecture

The solution is fully implemented in the Microsoft cloud. It leverages both Microsoft Azure and Microsoft 365.

Solution Architecture

• The MBNCanada Portal itself is implemented using the EUM Portal product
  1. This is a .NET 5 portal hosted in MBNCanada’s Azure in the Canadian data centre
• Security is managed through Azure AD
  1. Municipal members are Azure AD B2B guests in the MBNCanada tenant
• Each municipality, expert panel, and committee is a SharePoint site collection
  1. Permissions are assigned based on Azure AD groups
  2. Group membership is managed through EUM
  3. Automation of this is done through Logic Apps
• Data entry is done through protected Excel workbooks in Excel Online
• Data import is a .NET application that reads from the Excel and Data Dictionary data into an Azure SQL database
• The Power BI data model is refreshed from the Azure SQL database
• Power BI reports are presented in the EUM Portal using its Data Portal functionality built on top of Microsoft’s Azure Power BI Embedded service

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 are able to manage their local user setup, including customized access levels to support security.