Category

Data Visualisation

Power BI: Dashboard In A Day

By | Data Visualisation | No Comments

Power BI: Dashboard In A Day

Over the past few weeks, FTS Data & AI have had the privilege of hosting multiple Microsoft-sponsored Power BI: Dashboard In A Day (DIAD) events across Sydney. As a Microsoft Gold Partner for Data Analytics, the team presented over 3 days’ worth of content to a combined audience of over 100 business analysts, report developers and data professionals. Handpicked to present at Microsoft’s headquarters in North Ryde, we were able to deliver events that were both informative and beneficial for all those in attendance. For those who are curious and could not attend, this blog post covers what a DIAD event is, and why you should register to attend the next one.

L-R: Alex Gorbunov, Matthew Oen, Swetha Pakki, Sahan Vaz Goonawardhane, Ajit Ananthram

What Is It?

DIAD is a free one-day course designed by Microsoft to help analysts explore the full capabilities of Power BI. Attendees learn about Power BI in detail, follow a step-by-step lab manual, attempt 2 real-world practical examples and receive expert guidance from a team of experienced instructors. Through self-paced learning, attendees can properly develop their skills in Power BI and create a business-ready dashboard in a matter of hours.

Most importantly though, a large section of the day is devoted to providing attendees with the opportunity to develop Power BI reporting from their own datasets. This is where attendees get the most value, as they can ask questions and get assistance from experienced consultants regarding their own business’s reporting projects. The FTS Data & AI team were able to lend a hand and provide tailored advice to a vast number of businesses at various stages of their Power BI reporting journey.

Presenting to a large audience at Microsoft HQ

When Is It?

DIAD events occur throughout the year. Depending on your location, you can find dates for upcoming events by contacting us.

Attendees work on real-world practical examples

Where Is It?

DIAD events are held across the country. The Sydney events this year were held at the Microsoft head office in North Ryde. Here at the Microsoft HQ, attendees were able to fully engage with the technology, and see first-hand what Power BI and other Microsoft products are capable of, and how they could be used in their business.

See the full and latest capabilities of Power BI

How Much Will It Cost?

DIAD events run for 8 hours and are FREE. Make sure you register your interest early to ensure that you reserve a seat, as spots are limited at each event. Lunch and refreshments are also complimentary and provided throughout the day.

Learn how Power BI can be implemented at your organisation

Each attendee receives expert advice

Why Should I Go?

DIAD is an event like no other. Designed by Microsoft and delivered by professionals, DIAD empowers attendees with the skills and best practices needed to develop successful Power BI reporting solutions. Based on the overwhelming positive feedback from attendees, these events have been instrumental in getting Power BI quickly adopted in several organisations.

The opportunity to receive tailored advice from professional Power BI consultants means that you can accelerate the implementation of Power BI at your business, and be confident that you have the capability to develop powerful reporting solutions at your organisation well into the future.

See how other organisations have successfully adopted Power BI

 

If you want to know more about Dashboard In A Day events, please contact us for more information.

PowerBI Tooltip

PowerBI Tooltips

By | Data Visualisation | No Comments

PowerBI Tooltips enhance your reports

A great way to take your Power BI Reports to the next level is by using PowerBI Tooltips. Report Page ToolTips (RPT). RPT’s allow you to enhance your reports by giving your end users more information without taking up any real estate, cluttering the report canvas.

In the example below, we have a donut chart showing Revenue FYTD by Region. One great way to enhance visual this is to add a RPT showing the Top 5 Stores for each Region as your end users hover over the donut chart.

PowerBI Tooltips

Target visual for PowerBI Tooltips

 

In the example below we’ve hovered over the ‘North America’ Region and the RPT shows us the Top 5 Stores by Revenue FYTD for that Region.

PowerBI Tooltips

Tooltips in Action

 

When we hover over the different Regions the RPT changes to show the Top 5 Stores for that Region.

PowerBI Tooltips

PowerBI Tooltips Changing context

 

As well as the RPT, we have setup a drill through on this donut chart and can still perform this action by right clicking and selecting the drillthrough option as shown below.

PowerBI Tooltips

Drilling through for more detail

 

In our second example, we demonstrate that RPTs can also be used on bar charts. This bar chart shows Revenue FYTD by Country and when you hover over a Country the RPT shows the Revenue FYTD by month for that Country. These also change as you hover over the different Countries.

PowerBI Tooltips

PowerBI Tooltips on a different visual

 

NB: This report also has dynamic measures and visual titles which we will cover in upcoming blogs.

How to create PowerBI Tooltips

In only a few steps you can create PowerBI Tooltips (RPT’s) in your reports, so let’s go through those steps now.

  1. Add a page in your report and in the Visualizations tab, set the Page Information tooltip slider to On and give it a name. In this example we’ve named it TT – Country FYTD
PowerBI Tooltips

Enabling PowerBI Tooltips

 

  1. Create a visual or visuals, below we have a line chart and 2 card visuals for our RPT, within the report canvas as normal and size the page appropriately. In the Visualizations tab you can set the page size to the default Tooltip or customise the size to get the best fit for your chosen RPT visual.
PowerBI Tooltips

Defining PowerBI Tooltips size

 

  1. Once you’ve finished creating the tooltip visual, hide the page by right clicking on the page name.
PowerBI Tooltips

Hiding the PowerBI Tooltips page

 

  1. On the report page, select the visual the RPT will appear on and go to the Tooltip settings in the Visualizations tab, set the Tooltip slider to On, the Type to Report page and select the tooltip visual you created, in our example it’s TT – Country FYTD
PowerBI Tooltips

Linking the PowerBI Tooltips to a visual

 

And you’re done! It’s as simple as that.

In upcoming blogs we’ll go through some more advanced concepts, dynamic measures/attributes as well as dynamic visual titles so stay tuned for that.

If you’d like to take your Power BI Reports and Dashboards to the next level and need help, please contact us to discuss how we can assist your organisation.

EMu PowerBI Collections Reporting

By | Data Visualisation | No Comments

EMu PowerBI Collections Reporting

Although they may have more interesting stories to tell, museums are no different to any other organisation when it comes to the need for management system reporting. Where most businesses require comprehensive reporting from their ERP, a museum needs to be able to extract the data from their collection management system and craft a report that effectively informs management about the status of the objects in their collections catalogue. So they needed some EMu PowerBI reporting to help do this.

FTS Data & AI were recently tasked by a museum with the delicate job of developing collections reporting from the museums’ EMu Collections Management System (EMu), a collections management system specifically designed for museums and historical institutions. This museum was undertaking an assessment program, reviewing and recording object information on their entire collection of over 100,000 historical artefacts. Monitoring the progress of this program was vital for management, as it informed them about timelines, capabilities and project resource planning.

The Problem

As previously mentioned, EMu is a collections management system specifically designed for museums. The rich data that is maintained within this system must first be extracted in order to reap the reporting rewards. However, this is easier said than done. EMu’s Texpress database engine, ODBC connectivity and ADO RecordSet connectors are not exactly conducive to PowerBI or a modern reporting outcome. So Emu PowerBI reporting is not as simple as for some systems.

Instead, a more sophisticated approach was required to extract the data. As part of the museums’ commitment to public transparency, they had developed a modern GraphQL web API that could serve collection information. Developed in-house, data was first extracted from EMu using a Harvester program which then wrote into a MongoDB cluster that serves the API. After careful examination, we identified that this API could be used to meet the reporting requirements of the assessment program. A custom query was then written, and Power BI was able to successfully connect and pull the relevant data needed for reporting. The extracted data, in JSON format, was then cleaned and transformed into a working, healthy dataset.

The Report

The report design was driven by our understanding of user workflow. An overall dashboard page, followed by a heat-formatted column chart, which then drilled through to the object details report page created a natural reporting rhythm that could be easily interpreted by report consumers:

Emu PowerBI Report
Emu PowerBI Report
Emu PowerBI Report

Custom functionality including filtering, formatting and forecasting meant that additional insights were gleaned from the dataset. We were able to not only report on the progress of the assessment program, but also provide guidance as to what objects the collections team should review and assess in the next month, proving incredibly useful in managing the resources needed in the project.

The Outcome

Starting with careful data excavation via the GraphQL API, then continuing with the injection of focused reporting design principles, we ultimately end with a sophisticated collections reporting experience that meant that the museum could leverage their existing EMu collections management system with a modern reporting tool to locate efficiencies in a large internal project.

If you have a reporting challenge and need help, please contact us to discuss how we can assist.

Azure ML PowerBI

By | AI & ML, Data Visualisation | No Comments

Leveraging Azure ML Service Models with Microsoft PowerBI

Machine Learning (ML) is shaping and simplifying the way we live, work, travel and communicate. With the Azure Machine Learning (Azure ML) Service, data scientists can easily build and train highly accurate machine learning and deep-learning models.  Now PowerBI makes it simple to incorporate the insights from models build by data scientists on Azure Machine Learning service and their predictions in the PowerBI reports by using simple point and click gestures. This will enable business users with better insights and predictions about their business.

This capability can be leveraged by any PowerBI user (with an access privilege granted through the Azure portal).  Power Query automatically detects all ML Models that the user has access to and exposes them as dynamic Power Query functions.

This functionality is supported for PowerBI dataflows, and for Power Query online in the PowerBI service.

Schema discovery for Machine Learning Service models

Unlike the Machine Learning studio (which helps automate the task of creating a schema file for the model), in Azure Machine Learning Service Data scientists primarily use Python to build and train machine learning models.

Invoking the Azure ML model in PowerBI

  1. Grant access to the Azure ML model to a Power BI user: To access an Azure ML model from PowerBI, the user must have Read access to the Azure subscription. In addition:
  • For Machine Learning Studio models, Read access to Machine Learning Studio web service
  • For Machine Learning Service models, Read access to the Machine Learning service workspace
  1. From the PowerQuery Editor in your dataflow, select the Edit button for the dataset that you want to get insights about, as shown in the following image:
Azure ML PowerBI Edit Dataset

Azure ML PowerBI Edit Dataset

 

  1. Selecting the Edit button opens the PowerQuery Editor for the entities in your dataflow:
Azure ML PowerBI PowerQuery

Azure ML PowerBI PowerQuery

 

  1. Click on AI Insights button (on the top ribbon), and then select the “Azure Machine Learning Models” folder from the left navigation menu. All the Azure ML models appear as PowerQuery functions. Also, the input parameters for the Azure ML model are automatically mapped as parameters of the corresponding PowerQuery function.
Azure ML PowerBI AI Insights

Azure ML PowerBI AI Insights

  1. To invoke an Azure ML model, we can specify the column of our choice as an input.

 

  1. To examine/preview the model’s output, select Invoke. This will show us the model’s output column, and this step also appears (model invocation) as an applied step for the query.
Azure ML PowerBI Invoke

Azure ML PowerBI Invoke

Summary

With this approach we can integrate all ML models (built using either Azure ML service or studio) with PowerBI reporting. This enables business to effectively utilise the models built by data scientists by any user (typically BI analyst) for relevant datasets based on the problem we are trying to solve (either classification/regression) or to get predictions. Utilising all these new enhancements of Microsoft PowerBI will enlighten business users with better insights and this in turn aids in better decision making.

Let our Data Visualisation and Machine Learning experts help you explore the potential – contact us today!

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.