All Posts By

Matthew Oen

PowerBI ML – How to build Killer ML with PowerBI

By | AI & ML, Data Visualisation | No Comments

PowerBI ML: Unleashing Machine Learning in Microsoft PowerBI in 5 easy steps

AI and ML are key tools enabling modern businesses to unlock value, drive growth, deliver insights and outcompete the market.  Its unmatched ability to handle massive sets of data and identify patterns is transforming decision making at every level of organisations. Consequently Data and AI strategy is therefore rapidly evolving to explore the ways in which AI can be best utilised to enhance business operations. However, pragmatically harnessing AI for business needs has remained challenging. This is because the solutions offered typically incur significant resource overhead, are hard to understand and may fail to deliver actionable business outcomes. A gap has therefore emerged between BI and AI; a failure to bridge the insights we learn, with the intelligence to improve. The most recent release of Microsoft PowerBI ML features aims to eliminate that gap, by bringing in Artificial Intelligence (AI) and Machine Learning (ML) capabilities into the practical setting of self-service analytics.

PowerBI has established itself to be a vital tool in modern data analytics. The easy to use interface coupled with powerful reporting capabilities has made it the reporting platform of choice in delivering reliable business insights. The recent inclusion of ML & AI capabilities has significantly strengthened the tool, by combining easy interactivity with cutting-edge data analysis.

Overview

PowerBI ML (Machine Learning) is now possible using Dataflows, the simple ETL tool that empowers analysts to prepare data with low-or-no code. Automated Machine Learning (AutoML) is then built off the back of Dataflows, again leveraging the interactive approach of Power BI without compromising on quality of analysis.

5 Easy Steps

  1. In a Workspace hosted by Premium capacity, select ‘+Create’ in the top right corner, and select ‘Dataflows’
  2. Choose the data source you wish to run the model on:
PowerBI ML Choosing Data Source

PowerBI ML Choosing Data Source

  1. After loading the data, the familiar Power Query screen will appear. Perform any data transformations as required, and select save & close:
PowerBI ML Power Query

PowerBI ML Power Query

  1. The dataflow should now appear underneath Dataflows in the workspace. Select the dataflow, then select the brain icon, and select ‘Add a machine learning model’:
PowerBI ML Add Model

PowerBI ML Add Model

  1. Create the model by inputting the relevant information. You will get the option to select the model type and inputs for the model:
PowerBI ML Select Model

PowerBI ML Select Model

After creating the model, you will need to train it. The training process samples your data, and splits it into Training and Testing data:

PowerBI ML Train Model

PowerBI ML Train Model

Once the model is finished training, it will appear under the Machine learning models tab in the Dataflow area of the Workspace, with a timestamp for when it was Last Trained. Following this you can then review the Model Validation report (a report which describes how well the model is likely to perform), by selecting ‘View performance report and apply model’.

Lastly, you can apply the model to the Dataflow by selecting ‘Apply model’ at the top of the validation report. This will then prompt a refresh for the Dataflow to preview the results of your model. Applying the model will create new entities (columns) in the Dataflow you created. Once the Dataflow refresh is completed, you can select the Preview option to view your results. Finally, to build reporting from the model, simply connect Power BI desktop to the Dataflow using the Dataflows connector to begin developing reporting on the results of your machine learning model.

Outcomes

With machine learning now integrated with PowerBI, users can upgrade from reporting on business performance to predicting it. From a business perspective, the addition of ML means that PowerBI reporting has gained an extra dimension. It can easily be incorporated into existing reporting and is capable of dramatically changing decision making. For the PowerBI ML user, no new skills are required, as ML leans heavily on the existing interface and user experience.

Common use cases where machine learning in PowerBI can be readily implemented include:

  • Improving your existing PowerBI CRM reporting by creating a general classification model to identify high and low value customers.
  • Boosting the value of your financial reporting by developing a forecasting model to help predict sales trends and downturns.
  • Enhancing your asset reporting by building a regression model to calculate the probability of asset failure or breakdown.
  • Refining your CRM reporting by constructing a binary prediction model to determine the likelihood of a customer leaving or staying.

If you want to know how machine learning can be implemented in your organisation, please contact us, and ask us about our AI services.

 

 

 

An Automated BI Solution: Microsoft Flow

By | Data Visualisation | No Comments

Simple automation can often deliver big improvements in the context of BI solutions. A great example of this is when using Microsoft Flow, OneDrive for Business and Power BI. As part of a broader Data & AI strategy, the combination of these applications can deliver an impressive automated result, dramatically increasing the value of deploying simple BI solutions.

Opportunities for automation in simple BI solutions are often overlooked, as the perceived cost of such a project would outweigh the benefits. However, good data platform delivery involving rigorous assessment of business processes can help identify instances where automation will deliver scalable value. As a real-world example, a client had a set of Excel files that were sent each week from their ERP system via email. And someone would take those Excel attachments and manually perform some transformations in the files, before importing to Power BI and hoping that the reports would turn out ok. Such a process was not only time consuming, but also riddled with potential manual error, ultimately failing to deliver the insights and value that they were after in a BI solution.

How to Setup the Flow

Enter Microsoft Flow and OneDrive for Business. Flow is an application that helps automate tasks by integrating workflow between different applications. In our case, implementing Flow was clear:

  1. Create a Flow that automatically saves the Excel attachments from those emails into OneDrive for Business (whilst performing some renaming and archiving along the way for good measure)
  2. Connect Power BI to those files in OneDrive

Designing the flow was simple, requiring only basic information such as:

  • Where the email was being sent from
  • The subject of the email
  • What to name the Excel file
  • Where to save the file

The Flow would then take this information into its ‘trigger’ and ‘action’ steps, forming a logical and repeatable workflow.

Once the flow was set-up and Power BI connected to OneDrive for Business, the previously manual process to deliver key business reporting was transformed into an entirely automated one. Time lost to handle-and-wrangle data was now able to be spent on core value activities and enhancing data visualization.  Higher quality reporting was now delivered, granting previously unseen insights into the business.

 

Understanding where automation can provide value in BI solutions is crucial, particularly in simple BI projects. As evidenced above, a simple combination of Microsoft Flow, OneDrive for Business and Power BI can be the difference between success and failure for a BI solution.

Did you enjoy this article?
Signup today and receive free updates straight in your inbox. We will never share or sell your email address.
I agree to have my personal information transfered to MailChimp ( more information )

Power BI Custom Visuals Series: Table Heatmap

By | Data Visualisation | No Comments

In this series, we take a look at some of the Power BI custom visuals available on the Office store, and shed a light on what the visualisation is, how the Table Heatmap works and the impact it can have in a Microsoft Power BI report.

For the most part, data visualisation delivery is less of a science, and more of an art. If given a set of data, it is up to the artist to determine in what way that data will be presented, such that its appeal to the reader is so strong that it can influence decision making within an organization.

For that reason, Power BI has an expanded list of custom visuals to help data artists craft the masterpiece. It turns complex and unengaging data into an impactful and effective source of information that decision-makers can rely on.

Power BI Custom Visual – Table Heatmap

Good Data Strategy dictates that an effective endpoint for data analysis is the point at which insights can be actioned as part of an informed decision.  What ignites those insights are strong visualisations that tell clear narratives. One such visualisation is the Table Heatmap:

Table data can be easy to understand, but difficult to read. Rows of numbers appear the same, differences between figures are not obvious, and the overall message that the table intends to tell has become altogether unclear. By adding a colour-schemed heat map, however, users can see behind the numbers and quickly identify differing levels of relative performance without having to perform mental gymnastics. This ability to visually discriminate numbers is increasingly becoming crucial in making prompt and effective business decisions.

The Table Heatmap takes a simple table and turns it into a visually compelling and dynamic source of information for decision making. Combining the intuitive format of a table with the instinctive nature of a colour gradient, this visualisation makes for a far more effective representation of information without overwhelming or misinforming users.

Use Cases

Typical use cases include:

  • Sales figures per product, across time
  • Incident counts per incident type, across employees
  • Revenue growth per month, across financial years
  • Budget variances per account, across months

Additional Functionality

As a bonus, the colour scheme can be customized to match corporate colours, adding unique and impactful personalization to the visual.

Summary

By adding a heat map to a table, users can expect to instantly identify areas of interest or concern, empowering them to make informed decisions about their business. By harnessing the power of colour gradient perception, the Table Heatmap will prove useful in analysing operations, determining where resources need to be allocated and understanding performance patterns.’