Chesterfield County Improves Regional Infrastructure Planning With Long-Term Forecasts | Quisitive
Chesterfield County Improves Regional Infrastructure Planning With Long-Term Forecasts
Learn how Quisitive helped Chesterfield County develop a solution for long-term forecasting using Azure and Power BI, leading to a national award.
pubsec-Chesterfield County Logo

In this case study:

Client: Chesterfield County

Industry: Public Sector

Products and Services: Azure, Power BI, SQL Server

Country: USA

“When a county is investing as much as $35 million or more in an elementary, we have one chance to make that decision and we want to make sure we make that decision right.”
Allan Carmody
Former Finance Director, Chesterfield County

About Chesterfield

Chesterfield County, located in the state of Virginia, is recognized as the “17th Best Place to Live in America” and “One of the 100 Best Communities in America for Young People.” The county also has the lowest crime rate in the Richmond Metropolitan region, so the residents flock to live there.

 

For Chesterfield County residents, businesses, and visitors, there is an evolving need for access to public schools, parks & recreation sites, and public libraries as the county continues to grow.

Challenge

Chesterfield County was growing so fast, nine percent since the last census, that demographics were shifting, and stakeholders needed to ensure the “model of excellence in public education” stayed intact. The growth dynamics in any county presented challenges for school planning, ranging from enrollments in particular facilities up to where new facilities should be built. With the median costs for elementary, middle, and high schools at $35M, $50M, and $75M respectively, finding the right locations and subsequent population zoning is important.

 

Chesterfield County’s primary goals were to be more transparent with its taxpayers and demonstrate why decisions were made when building new schools, and to better plan for where to build schools and how big they should be. After creating a one-year forecasting model, Chesterfield found that the rate of accuracy was only 90%. With the desire to improve on the accuracy findings, Chesterfield County planned to develop a five-year prediction model that forecasted enrollment for schools in the county and prove that the school investments were a necessity. Additionally, the model needed to help discover insights to make data-driven decisions in the development of not only schools, but other capital facilities to ensure fiscal responsibility for the taxpayers.

Solution

After a Data & Analytics pilot and the discovery that the model had to go past one year and include other sources of data to be accurate, Quisitive helped Chesterfield County create a more accurate five-year forecasting model to help make data-driven decisions in school development, such as building new schools or shifting school boundaries. Quisitive validated and consolidated data from 60 schools over a 15-year timeframe, including demographic and 20 years of housing data with additional sources of data, into a data warehouse for reporting and analysis. Using machine learning and Power BI, data sets were mapped and results to insights were written to a SQL server to visually report the forecasting of school enrollment in different areas of the county.

 

Quisitive also used Azure ML Suite to build and validate production-ready time series forecasts. The data-driven visual dashboards developed, helped Chesterfield County find insights to plan for future school developments. It also helped specific school districts plan for certain needs, determined by the school enrollment forecast.

Results

The model predicts a 5-year forecast horizon and shows geographical student growth decoupled from the school districts, making this model more accurate than the legacy one-year model. Quisitive worked with the County’s data engineering team to build a new dataset based on housing parcels – a “parcel level” approach. This enabled Quisitive to build a new model that could predict student growth to various levels of geographic aggregation, such as neighborhood and census tract. The approach was successful and was integrated into the County’s planning process.

 

Quisitive also partnered with the County’s IT analysts and transferred knowledge of data science skills so they could apply the same approach to forecasting the need for building new fire stations, or other infrastructure needs throughout the County.

 

The forward-thinking county of Chesterfield increased the county’s overall data maturity and transparency to all stakeholders. With a successful student demand forecasting model, the county has begun to expand in other ways of modernizing its analytics infrastructure.

 

The elasticity of the cloud allows state and local government agencies to consolidate their data from many sources into an environment where they can take full advantage of high-grade security features, data at scale, advanced analytics, and machine learning capabilities. These combined capabilities enable public sector analysts or data scientists to build better models, the GIS team to add high-quality layers of geospatial data, and the users to consume the results in easy-to-digest dashboards and reports.

Transformative Impact

Built a secure ML Platform

Leveraged Azure Machine Learning Studio and Azure Data Factory to securely connect to Azure SQL databases and Azure Data Lakes and enable streamlined model deployment.

Improved Accuracy

Improved accuracy of long-range school enrollment forecasts using the County’s own schools, demographic, real estate, and planning data, relative to existing statistical models designed on state-level population.

Upskilled Analyst

By working alongside Quisitive experts, the County’s data analysts gained valuable data science skills to start building their own use case and using the model to make data-driven decisions when evaluating public facilities.

Award Winner

Chesterfield County was recognized with the 2022 National Association of Counties Achievement Award for this engagement, a project titled: Forecasts for Communities: Data-Driven Populations Projections. “The cross-functional team included Deputy County Administrators; Budget, Parks, and Library Directors and subject matter experts; technology leaders and staff; and data engineers and data scientists from Catapult Systems [now a part of Quisitive], a Microsoft Gold Partner.”

 

Learn more about this award.

I hope May flowers are blooming wherever you are, Microsoft is bringing us governance and great user functionality this month.

How are you minding your groups?  Are you using a governance tool?  If you are interested in governance tools, we can help with recommendations that suit your organizational size, budget, and use cases, but if you aren’t, you have a nice out of the box option to manage orphaned groups from Microsoft that is available now.

Group ownership governance policy is now Generally Available

Microsoft snuck this nugget in without prior communication.  This policy will help you automate the management of ownerless M365 groups in your tenant.

You’ll have to enable this functionality in your Org Settings under Services > Microsoft 365 Groups.

When creating the policy, you can specify:

The policy will find ownerless groups and then ask the most active members if they’ll accept group ownership.  View the configuration options in Microsoft Docs.


Microsoft Lists

(Updated) Quick Create – Easily Create Power BI Reports from Lists

Microsoft has created a way to visualize your lists in Power BI directly from a List.  Depending on your licensing, you may want to review your tenant settings for the most appropriate user experience.

Key points

How this will affect your organization

List users will see a new menu option in Integrate Power BI > Visualize this list, which allows users to create a new Power BI report using that list. With just one click, you’ll be able to autogenerate a basic report and customize the list of columns that are shown in the report. To take further advantage of Power BI’s advanced data visualization capabilities, just go into Edit mode. Once a report is saved and published, it will appear in the same submenu under Integrate> Power BI.

listspowerbi

What you need to do to prepare

This feature is default on, but can turned off from the Power BI Admin Portal under Tenant settings.

If this feature is disabled for tenants, users will continue to see the Power BI submenu in the List command bar, but any attempt to create or view a report will result in an error page.

Note:

Certain complex column types in Lists such as Person, Location, Rich Text, Multi-select Choice, and Image are not currently supported when the Power BI report is autogenerated.  See this Microsoft Docs article for more information


Microsoft Teams
Teams

(Updated) Mirror my video

Microsoft has updated the time line so that you don’t see text flipped in your video.

Now you will no longer have to see the text flipped. Imagine a scenario in which a primary school teacher has a small whiteboard in hand and is trying to teach school kids basic alphabets. Or imagine you have a virtual background with text in it. While the user on the other end of the meeting can see the text correctly, you see the text flipped. By providing you the option to un-mirror, you will be able to see the text correctly.

This feature will be helpful at the following locations:

This option provides the user the option to un-mirror their view and thereby see text correctly.

In-meeting Device Settings Panel

Viva

Daily Briefing Emails for users with Outlook language setting as null

Users without a language preferences set in Outlook may have been missing out on Viva daily briefing emails. Starting in mid-May – users with no language set will begin receiving this email in English.

Users will receive an email for 30 days with a note requesting them to update their Outlook language preference. If users do not provide a language preference, the email will be discontinued after 30 days.

This feature is enabled by default and options are available to admins as well as end-users to opt out.

Note: The customized email is only available to an end user; no administrator or manager can see another individual’s Briefing email. All data is stored inside the user’s mailbox. Briefing email is subject to the same GDPR compliance terms as Office 365.

(Updated) Exclude sites and files from topic discovery based on applied sensitivity labels

In mid-May, admins will be able to select sensitivity labels for content which should be excluded from Viva Topics discovery. Once this new configuration is saved, sites and files with the selected labels will no longer be included as part of the discovered list of files for an AI-suggested topic. Any description or alternate names extracted from those files will also be removed from the AI-suggested topic.

Viva Topics automatically organizes content and expertise across your organization, making it easy for people to find information and put knowledge to work.


Microsoft Build will be May 24–26 2022. Register for sessions to explore the latest innovations in code and application development—and to gain insights from peers and experts from around the world. We’ll report back on our favorite sessions next month!

In this video, I will show how to write back data to the data source from Power BI by using the Power Apps Visual.

Video tutorial shows how to add, update & delete data in data sources directly from Power BI reports by using the Power Apps Visual. The Power App will connect to the data source and perform the Write Back (CRUD) Operations. We will leverage SharePoint as a Data Source and perform the writeback.

I will also demonstrate how to leverage Dataverse for Write Back. Dataverse & SQL Support DirectQuery Import mode. This allows the Power App to initiate a near real-time refresh of the Power BI Visuals from Power Apps by using the PowerBIIntegration.Refresh() function.

The Power App will get its data from the Power BI dataset.

Enverus Case Study Feature Image
Enverus Sees Enhanced Rapid Reports with Power BI Quick Start
Learn how Quisitive was able to help Enverus by providing a working prototype for enhanced reporting with our Power BI Quick Start program.
cust-energy-enervus

In this case study:

Client: Enervus

Industry: Energy

Products and Services: Power BI Quick Start

Country: USA

The team was so impressed with the results of the Power BI Quick Start, that they decided to move forward with implementing this prototype as the framework for their entire Power Analytics reporting portfolio.

Background

Enverus is an Austin-based energy analytics company that aims to provide data-driven guidance for the oil and gas industry. Among their many different solutions is a portfolio of power demand and pricing forecasts for utility markets, which are organized into seven different offerings. The Power Analytics offering consists of three reports, each of which are designed to support trading activity for power traders and analysts.

Problem

The Power Analytics reports are produced through a series of interdependent processes that require significant manual intervention. The data is initially sourced through more than 130 comma-separated value files, which are dropped onto an FTP server at regular intervals throughout the day. An Enverus analyst must manually run a batch script within a given time period in order to transfer these files into a local directory, which is then linked to a macro-enabled Excel workbook. The workbook consists of more than 80 tabs and contains approximately 275,000 formulas, many of which contain hard-coded cell references. Macros are utilized to archive data in different tabs, which frequently overwrite historical data.

 

Additionally, visualizations are interspersed throughout the file among datasets and are subject to unintentional resizing based on data volume. These visualizations are then linked into PowerPoint files, which require an analyst to export into a PDF and manually e-mail to the customers.

Solution

Enverus came to Quisitive looking for a solution. After a working session to analyze their reporting process, we recommended the Power BI Quick Start in order to rapidly deliver a proof of concept. Quisitive focused on a small subset of source data with the intent of reproducing one report for one target market. Instead of consolidating the data into one self-referential Excel table, the Quisitive team sourced the data directly into Power BI and organized into a data model. This new structure allowed previously complex formulas to be rewritten with DAX in clear, semantic language. In addition to a tenfold increase in refresh time, the proof of concept revealed many logical discrepancies in the original system.

Results

The Power BI Quick Start engagement not only successfully demonstrated the capability of Power BI, but also provided a working prototype for enhanced reporting. The Power Analytics team was so impressed with the results, that they decided to move forward on implementing this prototype as the framework for their entire Power Analytics reporting portfolio.

In this video, I showcase my Desk Reservation System built using multiple Microsoft 365 services – PowerApps, Power BI, Microsoft Visio & Microsoft Dataverse. The solution leverages a Power BI report which has visualizations showcasing information of desks booked by the users. Microsoft Visio Visual in Power BI has a diagram of the office space, which allows users to select a specific desk to reserve. Upon selection of a desk to reserve, the PowerApps Visual in Power BI allows the user to book a desk for a specific date. Users can also see all their upcoming desk reservations inside the Power App.

The solution can be leveraged in many scenarios – Social Distancing for reserving desks, store appointment bookings, Inventory Management, Reserving conference rooms and more.

In this video you’ll learn about:

✅ Microsoft Visio Visual in Power BI

✅ Power Apps Visual in Power BI

✅ Power BI connect to a data source.

✅ Integration of Microsoft Visio, Power Apps & Power BI

Happy Spring! Microsoft is bringing us quite a few fun and interesting feature updates for April 2021.  Here are my top Modern Workplace items of interest this month.

SharePoint Spaces

Updated Rollout Timeline – Action Required by June 1, 2021

SharePoint spaces allows the creation and sharing of immersive or browser-based mixed reality environments using assets such as 3D models, 360° images and videos, and 2D content such as videos, images, and office documents. In October 2020 (MC224942), SharePoint spaces became generally available.

Microsoft will be including spaces in the site level +New menu by default.

Microsoft 365 Roadmap ID 67135


SharePoint + PowerBI

In the May – June timeframe you’ll begin to see an additional option in the SharePoint List Ribbon for Integrate – PowerBI.

This will make it easy to quickly create business intelligence reports in Power BI using your list schema and data.

Microsoft 365 Roadmap ID 72175.

List users will see a new menu option in Integrate Power BI > Visualize this list, which allows users to create a new Power BI report using that list. With just one click, you’ll be able to autogenerate a basic report and customize the list columns that are shown in the report. To take further advantage of Power BI’s advanced data visualization capabilities, just go into Edit mode. Once a report is saved and published, it will appear in the same submenu under Integrate> Power BI.

PowerBI
SharePoint & PowerBI

Users with a Microsoft 365 E5 license or Power BI Pro license will have access to the full report authoring and viewing experience. Users without either of the above licenses will be prompted by Power BI to sign up for a 60-day free trial of Power BI Pro when they attempt to save a new report or edit or view an existing report. To turn off self-service sign-up so that the option for a trial is not exposed to List users, click here. Users with a Power BI free license may only visualize their list data, but cannot publish nor view reports.

What you need to do to prepare


Teams

Customized backgrounds for Teams coming to mobile devices

If you have enabled this feature for Teams desktop apps, users will be able blur their background in a video meeting or call or replace the background with an image.

This message is associated with Microsoft 365 Roadmap ID 82234

When this will happen

How this will affect your organization

Users will be able to add backgrounds to their meetings and calls on mobile devices; the feature respects the same policies as desktop backgrounds.

What you need to do to prepare

Mobile devices respect the policies that are in place for Teams desktop apps via the VideoFiltersMode parameter.

You might want to notify your users about this new capability and update your training and documentation as appropriate.


Allowing anonymous presenters in Teams Live Events

Your meeting organizers will now be able to schedule an anonymous presenter for a Teams live event when using the Teams desktop app.

Meeting organizers will indicate when scheduling the live event that they expect an anonymous presenter. This must be managed when scheduling the meeting.

The anonymous presenter must also use the Teams desktop app.

anonymous presenter

What you need to do to prepare

This feature is available to tenants with licenses for Teams Live events at no additional charge.

You might want to notify your users about this new capability and update your training and documentation as appropriate.


REMINDER: Microsoft Edge to replace Microsoft Edge Legacy with the April Windows 10 Update Tuesday (or “B”) release

As announced in MC237900 (February 2021), Microsoft is replacing the out-of-support Microsoft Edge Legacy application with the new Microsoft Edge as part of the April Windows 10 cumulative monthly security update, also known as the Update Tuesday (or “B”) release.

When this will happen

The April Windows 10 Update Tuesday release will be available starting April 13, 2021.

How this will affect your organization

When you apply this update, the out-of-support Microsoft Edge Legacy desktop application will be removed, and the new Microsoft Edge will be installed.

If you do not set up kiosk mode in the new Microsoft Edge prior to applying the April Windows 10 Update Tuesday release, you will experience a disruption in your kiosk scenarios because Microsoft Edge Legacy and its kiosk mode will no longer be available.

What you need to do to prepare

If you already have the new Microsoft Edge on your device(s), and have set up any needed kiosk scenarios, no action is needed.

Learn more

Additional information:


Microsoft Teams: introducing organization-wide backgrounds (preview)

Currently, your end users can replace the background in a video meeting or call with an image, if you have enabled this feature for Teams desktop apps and Teams on the web.

teams backgrounds

Microsoft is introducing, via a preview feature, the ability for you to provide backgrounds that end users have access to across the organization.

This feature will require an Advanced Communications license when it becomes generally available in July. If users do not have an Advanced Communications license, any organization backgrounds applied during the preview will no longer be available.

When this will happen

Rollout begins late April and should be complete late May.

How this will affect your organization

Once this feature is in preview, anyone within your organization can view these backgrounds and apply them across calls and meetings via the new background settings panel.

While the feature is in preview, users will be able to view these images in a new background settings panel accessed from the  options link in the meeting controls, and then apply an image across calls and meetings.

During a meeting or call, users can access Apply background effects from the  options link in the meeting controls. Before joining a meeting, users can select Background filters below the video image.

What you need to do to prepare

You can upload images in Teams admin center for use in your tenant.

Images can be either PNG or JPG format; they should be no smaller than 360x360px and no larger than 2048x2048px. You can host up to 50 images in your tenant.


SharePoint Site Templates

This new SharePoint release of scenario-based site templates will help you expand what’s possible with content, pages, and web parts while helping you quickly get started building your own site. Each template contains pre-populated content and web parts that are fully customizable to meet the needs of your organization. This is rolling out on SharePoint Online and mobile.

template

This message is associated with Microsoft 365 Roadmap ID 70574.

When this will happen

Targeted Releases (selected users and organizations): rolling out from late April to mid-May.

Standard Release: rolling out from mid-May to late June.

How this will affect your organization

Users will be able to benefit from the ability to browse, preview, and apply site templates to a new or existing SharePoint site. Users can select a site template that meets organizational business objectives and best fits the site goal while ensuring a higher level of consistency throughout their organization. They can then review pre-populated content and customize the site to address their needs. This feature is default on with no admin control.

To apply a template to an existing site: Users can choose to browse site templates and can apply a template to an existing site at any time by accessing the template gallery from Site Settings and then Apply a site template.

To apply a template to a new site: If a site owner is visiting their new site for the first time, they may see a message asking if they want to use a template that will take them to the template gallery.

Available scenario-based site templates:

Communication site templates:

Team site templates:

For tips on improving and customizing your site, review the SharePoint Next steps panel.

Note: For changes to the existing site designs feature, be sure to review the From your organization section here.

What you need to do to prepare

Share this template guide with end-users: Learn how to apply and customize SharePoint site templates.

For admins using custom site templates (previously known as site designs and sometimes called third-party templates) review learn how to create a custom site template and site script.


(Updated) Announcing Tenant-level Organizational Theme Capabilities for Global Admins

Message Summary

Updated April 02, 2021: We have updated the post for clarity. Additionally, we have updated the rollout timeline below. Thank you for your patience.

Organizational Theme provides you the ability to define multiple themes within your organization, from the Microsoft 365 global admin center, using Microsoft 365 Groups. Each theme can be further customized by specifying mobile-specific and alternate dark mode logo experiences.

Key points

How this will affect your organization

Previously, you could only set a default theme which applied to everyone within your organization. Organizational Theme gives you the ability to create up to five custom themes for your tenant where each theme can be assigned to a specific Microsoft 365 group. Additionally Organizational Theme introduces support for:

Users will continue to have the same theme experience as before if you make no further changes. If an organizational theme has already been defined, it will now show up as the default theme. Editing the default theme or adding a new custom theme now provides the capability to specify up to four separate logos optimized for alternate and mobile scenarios.

Note: It is important to remember that the navigation bar background image will be retired and the ability to upload a new background image will no longer be supported. Upon saving any modifications made to a new custom theme, the background image will be permanently removed and users will no longer be able to see it.

What do you need to do to prepare

We will be updating this Message Center post with further instructional information in a help article for admins to follow in order to set up custom themes that can be assigned to specific Microsoft 365 groups around mid-March.

Each custom theme may be assigned to up to five separate Microsoft 365 groups (for more information on how to create a Microsoft 365 group, view this resource).


SharePoint Auto News Digest updates

You may already know about and use the SharePoint automatic news digest, which is sent to users with news that is relevant to them. Microsoft is adding new features to enhance the experience:

This message is associated with Microsoft 365 Roadmap ID: 70606

When this will happen

We will gradually roll out these updates to targeted release customers starting in mid April, and will complete the rollout to all customers by the end of June

How this will affect your organization

Users who already receive the existing automatic news digest will start receiving this updated version.

Massanutten Resort
Learn how Quisitive's On-Ramp to Azure Data & Analytics improved access to data for Massanutten Resort’s leadership and ultimately drove their journey to the cloud
cust-massanutter black

In this case study:

Client: Massanutten Resort

Industry: Retail & Hospitality

Products and Services: Microsoft Azure, Microsoft Power BI, On-Ramp to Azure Data

Country: USA

“As a platform, Azure can be intimidating, but On-Ramp to Azure Data & Analytics made it accessible to us. It also opened our eyes to what can be done and what should be done with it. It’s turning us into a modern workplace.”
Bryan Hoskins
Database Visualization, Massanutten

On-Ramp to Azure Data & Analytics Drives Resort’s Journey to the Cloud

Located in Virginia’s breathtaking Shenandoah Valley, the Massanutten Resort is a 6,000-acre family-centric destination. Unique in size, variety of amenities, and outstanding condo and hotel accommodations, Massanutten hosts thousands of guests to its property every year. Offering a robust ski and snowboard area, a water park, two golf courses, a high adventure ropes course, four restaurants, four retail stores, a mountain bike park, and a spa, Massanutten has earned its reputation for being a premier all-seasons resort destination on the East Coast.

 

Because word-of-mouth recommendations are so critical to the hospitality industry, Massanutten, like other resorts, pays close attention to online customer reviews. Keeping up with the comments in a timely manner, however, became a challenge that needed to be solved. The method of searching for comments and reviews, collating them into a spreadsheet, and then distributing the information once per week simply wasn’t quick enough to effectively move the needle. As such, the IT team at Massanutten knew they needed a way to evolve their processes by taking advantage of the cloud.

 

“At Massanutten we have four core values: we take care of our guests, our property, ourselves, and each other. To achieve this in today’s world means we must take care of our data and be good stewards of it,” said Bryan Hoskins, Database Visualization, at Massanutten. “It’s why we knew we needed a transformation, and that a modern approach to data management was necessary.”

 

A second driver for the company to adopt the Azure platform was an understanding of the economics of utilizing the cloud. According to Hoskins, the company had made significant investments in infrastructure over the years but knew that it made financial sense to move operations to the cloud.

 

The team at Massanutten had previously worked with Quisitive, so when the time came to consider a move to Azure, the decision to partner with them again was an easy one.

We knew we needed a bit of hand holding to get our courage to make the leap to the cloud, and Quisitive was that partner to help us through it. They gave us more than a vision. They filled in the gaps in our vision and proceeded to step with us through the process we needed to complete from point A to point B and then to point C down the road. We came away with a much larger perspective of what’s possible.”
Bryan Hoskins
Database Visualization, Massanutten

Taking the On-Ramp to Azure Data & Analytics

After meeting with Hoskins and the Massanutten team, Quisitive’s Managing Principal Patrick Gallucci understood the resort’s needs, and recommended using the On-Ramp to Azure Data & Analytics approach to quickly get the team to where they needed to be.

 

Quisitive’s OnRamp to Azure Data & Analytics approach leverages the company’s deep understanding of what works best when it comes to cloud migration, and provides prescriptive, step-by-step guidance to give organizations the resources they need to migrate and innovate successfully.

 

For Massanutten, this meant kicking off with a one-day architecture design session, where the Quisitive team met with stakeholders to discuss scenarios in order to engineer and build the right platform. This was followed quickly by three days of building the platform and the next 30 days working on top of the platform alongside the Massanutten IT team.

 

“Our process is lightning fast, but when we walk away from the engagement the client is left able to check all the right boxes. We can do this because we’ve flipped the traditional model. We built the platform, secured it, managed and operated it, all while educating the Massanutten team on core capabilities within Azure,” said Gallucci.

 

This approach was extremely beneficial for the Massanutten team, according to Hoskins. “Working with a partner with a prescriptive method that’s so well documented reduces our risk when it comes to being able to administer the infrastructure,” he said. “We took the On-Ramp to Azure, but it was also a big organizational on-ramp to being cloud-capable.”

 

For Hoskins, the hands-on approach of the On-Ramp to Azure method boosted his team’s confidence and set the project up for success. “Quisitive met me where I was and brought me to a place that was higher and more sophisticated without requiring me to learn all the intermediary steps in between.”

 

"Working with a partner with a prescriptive method that's so well documented reduces our risk… Quisitive met us where we were and brought us to a place [in the cloud] that was higher and more sophisticated."
Bryan Hoskins
Database Visualization, Massanutten

Results Critical for Post-Pandemic Operations

The decision to migrate to an Azure platform was made long before the onset of COVID-19. With the hospitality industry impacted by the global pandemic, having real-time access to customer data will be more important than ever.

 

“Having asynchronous updating of comments and data points is critical to our resort strategy and has put us on good footing,” said Hoskins. “Our data warehouse on the Azure platform, with infrastructure provided by Quisitive, means that our data is immediately available by power BI to a supervisor, that supervisor’s supervisor, and to the general manager.”

 

He explained that the resort uses the data to address immediate customer issues, analyze highlights and lowlights, and make directional changes in real-time. This access to data in turn benefits the customers, who are heard and receive responses quickly and efficiently.

 

“As a platform, Azure can be intimidating, but On-Ramp to Azure Data & Analytics made it accessible to us,” Hoskins said. “It also opened our eyes to what can be done and what should be done with it. It’s turning us into a modern workplace.”

In Log Analytics we can quickly export to Power BI just by running an Export to Power BI (M Query). However, if you are like me, you may not do a good job of keeping track of all of the queries that you write – especially if they are going to be used somewhere else like Power BI. This blog post will show how you can take the queries you built into Power BI and bring them back into Kusto for editing.

How to get data out of Log Analytics into Power BI?

Log Analytics makes it easy to query Log Analytics as we just paste these into the advanced editor in Power BI and voila – you have Log Analytics data in Power BI.

If you are like me, you have used this functionality a lot and over time you may have run into this. What happens when you want to make a change to the Kusto query you built the M Query from? Here’s the quick trick I use for this.

How to get your Kusto query back out of the MQuery:

In Power BI open the query in the Advanced Editor, and highlight the query section (highlighted in the graphic below) and copy the text into a blank Word document.

In my case this is the text of the query I am working on (the actual query doesn’t matter here, this is just a query I put together for some custom data we are gathering on Windows Operating System versions):

LaunchReporting_CL
| where TimeGenerated > (ago(120days)) and Report_s == “”OSVersionsAll””
| project TimeGenerated, Value_s, ClientId_d, Count_d
| sort by TimeGenerated asc

Do a replace for “” with ” (hint – Control-H brings up replace)

Copy and paste the new version into your Log Analytics workspace query and you are now good to go!

Summary: If you need to get the original Kusto query back from an M Query pull up the advanced editor for the data source in Power BI, copy out the section with the Kusto query, replace the double “” with a single ” and then just re-paste the query into Log Analytics.

Medisys Case Study Feature Image
Medisys Leverages the MazikCare Platform to Deliver Centralized, Collaborative Healthcare Services and Clinic Management
Learn how Medisys leveraged the MazikCare Platform to deliver centralized, collaborative healthcare services and clinic management.
medisys-logo

In this case study:

Client: Medisys Health Group (now TELUS Health)

 

Industry: Healthcare IT Services

 

Products and Services: Quisitive MazikCare, Microsoft Dynamics AX, Microsoft Dynamics CRM, Microsoft Power BI, Microsoft SharePoint, Microsoft Surface Pro 4

 

Country: Canada

“MazikCare provided an out-of-the-box, fully integrated, end-to-end solution on top of Dynamics AX that addressed the entire patient journey, from appointment scheduling to patient flow through the clinic, electronic medical records, billing, and post-discharge follow-up,”
Michel Bouchard
IT Director, Medisys

Company Overview

Medisys Health Group (now TELUS Health) is committed to leveraging world-leading technology and a passionate team to revolutionize access to healthcare and drive remarkable health experiences for the people in Canada. A leader in virtual care, tele homecare, e-medical and health records, benefits, and pharmacy management, Medisys gives health authorities, healthcare professionals, patients, and consumers the power to turn information into a healthier future. By leveraging the power of technology to deliver connected solutions and services both in-person and virtually, Medisys is improving access to care and revolutionizing the flow of information while facilitating collaboration, efficiency, and productivity for physicians, pharmacists, health authorities, allied healthcare professionals, insurers, employers and people globally, progressing its vision of transforming healthcare and empowering people to live healthier lives.

The Need for Client Record Automation, Centralization and Insights

Business growth at Medisys was being hindered by a mix of traditional, manual recordkeeping systems and processes at its clinics. These limitations drove Medisys to search for a new platform that would enable process and file centralization and automation. Medisys partnered with Quisitive to deploy the MazikCare platform for automated client record and clinic management.

Background

Prior to launching MazikCare, Medisys was experiencing limitations in its legacy business processes that threatened to slow growth, tarnish corporate relationships, and inhibit the delivery of best-in-class patient care. 

 

Rapid growth and a series of strategic acquisitions had left the organization operating with manual and outdated booking and information systems, which included paper and spreadsheet-based processes, and a variety of outdated healthcare applications.

 

Gaining insights into key performance indicators (KPIs) that would help the business improve was also time-intensive for the organization’s leadership team. 

 

“We were working hard to overcome the inefficiency of our patient workflow systems,” said Joseph Lo, Chief Executive Officer, Medisys Executive Health Services.

“We didn’t have the information we needed to develop deeper personal relationships with our executive guests. We knew that if we were to grow, we needed to get our back office in order, create consistent, efficient patient workflows across all our clinics, and provide real-time data that our corporate clients could use to steer their business.”
Joseph Lo
Chief Executive Officer, Medisys Executive Health Services

Getting Started with MazikCare

Medisys had long used Microsoft Dynamics CRM to record patient interactions and appointment timing. Additionally, it used Microsoft Dynamics AX as its financial management platform. The organization needed a solution that could complement and augment its existing Dynamics infrastructure. After a careful analysis of current and future needs, Medisys officially selected MazikCare due to its native integration with Dynamics and extensive toolset of options to serve the entire business and customer continuum. 

 

“MazikCare provided an out-of-the-box, fully integrated, end-to-end solution on top of Dynamics AX that addressed the entire patient journey, from appointment scheduling to patient flow through the clinic, electronic medical records, billing, and post-discharge follow-up,” says Michel Bouchard, IT Director, Medisys.

 

With MazikCare, Medisys was able to create consistent patient workflows across all its clinics nationwide and provide its clients with real-time insight into the health of their businesses. 

The Benefits

Since starting with MazikCare, Medisys has realized significant benefits. By providing greater consistency and efficiency, improved visibility into scheduling, billing, and patient data, and by automating vital tasks, the organization is better equipped to do what they do best — help clients. 

  • Manual entry and processes have been eliminated, shifting staff’s time from administrative tasks to more strategic activities 
  • Teams can review scheduling, billing, and client reports, quickly and from anywhere 
  • Leadership can access real-time accurate data for better planning & decision-making
“MazikCare provided an out-of-the-box, fully integrated, end-to-end solution on top of Dynamics AX that addressed the entire patient journey.”
Michel Bouchard
IT Director, Medisys

Significant Time Savings and Enhanced Client Experience

Today, when a prospective guest calls Medisys clinics for an appointment, scheduling agents can book it in less than a minute without the prep work that was previously involved. “Our previous setup was so complex that it took weeks to train a scheduling agent,” explains Bouchard. “They had to familiarize themselves with our various preventive healthcare providers, know which medical specialists served which clinics and pore over those specialists’ schedules to find openings. To our executive guests and corporate clients, appointment scheduling appeared easy, but behind the scenes, it was a lot of work.” 

 

Once the guest is inside a Medisys clinic, check-in is simple and more personalized because the staff has a detailed visit history and the guest’s complete medical record in the palm of their hands, on a tablet. As a guest steps through the various exam stations, clinicians check them in and out and record results digitally. 

 

When the visit is complete, Medisys uses MazikCare to automatically generate a personalized detailed results report for the guest and an invoice for their employer while maintaining the highest standards of data security and personal health information privacy protection. 

 

Medisys physicians can securely access MazikCare from any location or device to enter patient information such as medications and allergies, which is then shared with the patient’s care team. Physicians can even add patient videos and photos to the system in a highly secure, confidential, regulatory-compliant manner. 

 

“We were delivering a great guest experience before, but through Herculean effort,” Bouchard says. “Our efficiency has skyrocketed with MazikCare, and guests are impressed to see our caregivers using tablets versus paper.”

Ready to get started with MazikCare?

Powered by Microsoft Dynamics and the Microsoft Cloud for Healthcare, Quisitive MazikCare is a complete platform purpose-built for the healthcare and life sciences industry. From day 1, MazikCare delivers powerful functionality to help improve patient engagement, automate operations, streamline and unify patient records, optimize the supply chain, enhance clinician and patient satisfaction, improve revenue cycles, and deliver exceptional real-time insights. 

This post should serve to inform you as a manager or executive what to expect from analysis presented in Microsoft Power BI. This primarily non-technical post will cover terminology, design, visuals, data connections and updates, and user empowerment.

Terminology

Desktop, Service, Mobile App:

The Power BI product has three primary components: Desktop, Service and Mobile App. Executives are more likely to use the Power BI service or Mobile App than Power BI Desktop. Here is a quick summary of the differences between these alternatives:

Power BI Desktop is a traditional Windows application like Excel, Word, PowerPoint and Outlook.  It is the primary authoring environment used by analysts and developers to create Power BI reports and the visuals they contain. It can also be used to view and interact with Power BI reports. In Desktop, a business intelligence specialist can build a sophisticated data model by connecting to one or more data sources, relating tables, creating calculations, splitting or combining fields, etc… What is Power BI Desktop missing? The ability to build or view a “dashboard” and the ability to share your analysis.  Dashboards exist only in Power BI Service, and cannot be authored or viewed in Power BI Desktop. To share what has been authored in Power BI Desktop, you could copy it to a fileshare or SharePoint site like we’ve been doing since Lotus 1-2-3 was a thing (and try to keep up with all the versions that grow out of that copy), but a far better way is to publish the report to the Power BI Service.

Power BI Desktop Screenshot
Power BI Desktop

The Power BI Service resides at https://powerbi.microsoft.com. It is a software-as-a-service product connected to your Office 365 account. The primary purpose of the Power BI Service is sharing insights. A Power BI Service user can:

Report in Power BI Service
Report in Power BI Service (https://powerbi.microsoft.com)
Dashboard in Power BI Service (https://app.powerbi.com)

The third Power BI client is the mobile app available from the Apple, Google Android and Microsoft Windows app stores. It is a touch-optimized application that enables viewing Power BI dashboards. In addition, the mobile app allows you to set data alerts and view visuals based on cached data while offline.

Power BI Mobile App (Windows, iOS, Android)

Visual:

A chart, graph, table, or other graphic depiction of data and data relationships. More about visuals below.

Report:

A report in Power BI is analogous to a workbook in Excel. Reports can have one or more tabs.

Dashboard:

A dashboard is a canvas on which visuals are “pinned” from reports. A dashboard is well named; its purpose is to enable at-a-glance views of business metrics. Clicking through any of the dashboard visuals will open the report from which the visual was pinned, so that the user can explore the context of the visual. As stated above, dashboards exist in powerbi.com and the Power BI mobile apps, not in Power BI desktop.

One killer feature that can be enabled in Power BI dashboards is “Q&A”. This feature enables a user to type (or speak, with Microsoft Cortana integration) natural language questions and receive charts, graphs and maps in response. Here I typed “count of violations by type” and Power BI presented me with a bar chart. I then changed “type” to “Zip Code” and it drew me a map.

Power BI Q&A

App:

Unfortunately, there are two completely different uses of the term “app” in the Power BI world. First, there is the above-mentioned mobile app that installs from an app store on an iPhone or other device. Second, there is the concept of a group of reports and dashboards published together in the Power BI Service. These “Apps” make it easy for your IT organization to publish and maintain secure access to groups of reports.

Visuals

Power BI currently (as of October, 2017) ships with 29 visuals. One of its many killer features is the fact that this visual library is extensible. Any developer can build and submit visuals for Microsoft to evaluate and publish to their online store, and many have. A quick count showed 113 custom visual offerings in the store, ranging from key performance indicators to gauges, maps, temporal charts, filters, etc. Most of these custom visuals are license free.

Providing Feedback on Visuals

With such a vast selection of visuals from which to choose, it can be tempting for a Power BI report developer or analyst to use as many as possible. In many cases, a custom visual can provide insight or “tell a story” better than any of the out-of-the-box visuals. However, the best report design advice is always “simpler is better.” Keep that in mind if you have the opportunity to provide report design feedback. Report design should prioritize the conveyance of important business information with a minimum of distraction. More on design later.

Report developers should use specific visual types to convey specific data insights. There are many perspectives on this topic, including a great illustration by Andrew Abela.

Ribbon Chart (http://bit.ly/2y92dDc)
Scatter Chart

When presenting controversial insights, or when presenting complex business metrics to experts, more raw data is better than more pretty visuals. Designers should rely in simple charts augmented with tables.

Another thought with regard to visuals and reports – keep in mind whether the information you are being presented is declarative or exploratory. Declarative visuals indicate statements of fact, e.g. “third quarter sales of potatoes on the western U.S. were down 7.6% year over year”, and they don’t change often. Report writers have time with these to apply design and polish. In contrast, exploratory visuals are the result of innovative thinking, usually by a team, about the business, e.g. “we compared tuber sales to dark green veg sales in the west, and found a negative correlation; we think the ketogenic diet trend is decreasing demand for starchy veg”. These visuals are usually less color coordinated, polished, and shiny than declarative visuals, because they are fresh. As an executive, focus your feedback on the content of exploratory visuals, not their beauty, to encourage quick and open sharing of innovative thought.

Interactions of Visuals

Power BI visuals can act as interactive report filters. For example, imagine clicking on the “Midwest region” column of a “Net Fiscal Quarter Sales by Region” column chart. Power BI will immediately filter the entire report tab to the Midwest region. This capability is on by default, and it is developer configurable to highlight rather than filter the report.

Report designers can use a visual called a “slicer” to give you an easy way to interactively filter a report. These have been around in Excel for quite a while. Often however, a small visual like a “call volume by call center” bar chart can be used as both an informational visual and a report filter. This can be a better use of screen real-estate than a slicer.

Drilldown with Visuals

The drilldown button: in the upper right corner of a data visual will toggle drilldown mode for that visual. With it off, clicking on a line or column will simply select and focus that segment of the visual, like this:

Toggle the drilldown button on, then click on a report data point, and the visual will drill down to the next level of the x-axis hierarchy, if one is available. In the visual below I have toggled drilldown on, then clicked on the “2015” data point of the VanArsdel line. The report is now focused on 2015.

In contrast, the “go to the next level in the hierarchy” button: in the upper left corner of a visual will do exactly what is says. It will focus the whole visual down one level of the x-axis hierarchy. For example, after clicking that button, the report below shows sales summed by month over all of the years in the database. It is clear from the visual below that June is the top sales month for VanArsdel over the past 17 years.

The “expand all down one level in the hierarchy” button: in the upper left of the visual will increase “grain” in the chart by including the next level of detail from the next lower hierarchy level, as in this example:

Focus Mode

Have someone looking over your shoulder while discussing a specific visual, or need to focus discussion one one specific visual during a meeting? Click on the “focus mode” icon () in the top right corner, and it will zoom to fill the canvas. This is a temporary display mode – nothing will happen to the layout of the rest of the report if you use this.

Data Connections and Updates

The most powerful feature of Power BI may be the “get data” button. It lists 76 data sources to which the product can connect out-of-the-box, and the data connector API is fully documented, so third parties can write their own connectors. Further, Power BI has its own on-board query processor, meaning it can consume data from multiple sources and combine the results. In the example below, a standard sales database has been augmented with population data to create a “sales per capita” calculation. This was done by screen-scraping a couple of Wikipedia pages (Population by US City and State Codes by State) from Power BI desktop, then relating the resulting tables to the existing geography table on city and state.

It is not always feasible or practical to combine multiple data sources, so please be understanding if your data geek says it can’t be done.

Streaming data can be shown real-time in a Power BI report. More traditional non-streaming data can be updated up to eight times daily to the Power BI Service, or forty-eight times daily on the new “Premium” Power BI product. A developer can easily place an “Updated on: [date] [time]“ card on a report tab to indicate the freshness of the data in the report.

User Empowerment

In Power BI Desktop, and to a certain extent in the Power BI Service, users are empowered to build their own reports and visualizations. Given some training and practice, users can answer their own questions and perform self-service research without involving their IT group.

This leads to a classic two-edged sword in business intelligence: multiple versions of the truth, also known as “Excel Hell.” As shown below, three different analysts’ sales models might define “Territory Sales Growth” in three different ways. Tony uses a denominator that includes last year’s prorated sales, while Pat uses last year’s total fiscal year-to-date, and Sri factors in adjustments that were made after the FY closed in both the numerator and the denominator. Which is correct? From which calculation should business decisions be made?

Multiple Versions of the Truth (FYTD = fiscal year to date)

Catapult Systems advocates a tiered system of report tagging in order to signal the degree of trust that should be attributed to a calculation in a report. This can serve to encourage data exploration and sharing while signaling an appropriate level of caution. We will be happy to tell you more about it in person.

Design

If you have the chance to specify the design of Power BI reports or provide feedback, here are some common rules of thumb.

Summary

The purpose of this article is to summarize Power BI for managers and executives being asked to use the product. It was intended to help you get smart quickly on the essential features of the product, and to enable you to give some feedback on report design. This is the most rapidly evolving major product I have ever seen out of Microsoft. There are many capabilities and technical features that were not covered here such as security, bookmarks, parameters, data models, DAX calculations, etc., that your report developers should understand and utilize. Now is a great time to be in the business analytics field.

Feedback always appreciated.