Category

Data Visualisation

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.

 

 

 

Microsoft Lead Gartner 2019 Magic Quadrant for Analytics and BI

By | Data Visualisation | One Comment

Hot BI news is the recently announced 2019 Gartner Magic Quadrant for Analytics and BI and Microsoft was the clear leader, driven in part without doubt by the explosive growth of PowerBI .

2019 Gartner Magic Quadrant BI & Analytics

2019 Gartner Magic Quadrant BI & Analytics

PowerBI is the story

PowerBI is advancing at a rate which its key competitors – Qlik & Tableau – are struggling to match. Further underpinning Microsoft’s lead is its ongoing investment in the underlying Azure Data & Analytics Platforms which give it an edge that competitors just can’t match.

One thing I am frequently hearing from the market is that other platforms are struggling to match PowerBI’s compelling price point – some might cynically say Microsoft are using their deep pockets to undercut everyone else. However in a recent conversation I had with a long standing Cognos customer, once they understood what the product could do – and how much cheaper and faster it would be – it drove them to reconsider their strategy.

I freely admit was initially cynical about Self Service BI a few years ago as it rapidly became transparent that for all the slickness of creating great looking reports the tools were still beholden to a clean set of well managed data. Now modern data platforms are reducing the cost and complexity of providing this data, I am now holding the position that self service BI can really deliver on its value rather than just provide a final polish to a Data Warehouse – as long as it is paired with such a platform.

I continue to maintain my (unpopular in some circles) position that Qlik has nothing unique to offer any more and is doomed to irrelevance unless it innovates or at the very least catches up with its competitors. Tableau remains a solid tool for self service analytics, but the absence of an underlying data platform is going to start hurting it before too long. I would expect it’s longevity to be tied to being acquired by someone suitably huge.

I also note the absence of any serious competition from the other two cloud megavendors. Google offers Data Studio & Amazon has Quicksight – but neither rate a mention. I would watch this space carefully as the pace of innovation by both companies is fierce and Google in particular has strong AI / ML capabilities. Both are also ramping up their own data platform services.

Outside the Big 3

If you are on any of the legacy on premise tools in the 2019 Gartner Magic Quadrant for Analytics and BI such as Cognos/IBM, Oracle or SAS then i’d be seriously considering where you go next. The pace of innovation in the cloud is hard to ignore and users risk lagging behind their competitors if they cling to these.

SAP and Salesforce have a strong story within their own source but have their weakness in using data from outside the native platform. Doing anything in SAP is horribly expensive, and my conversations with Salesforce BI users have not left a very positive impression of the tools’ capabilities.

The remainder either are strong in their niches and / or have minimal presence in Australia (Microstrategy is basically unsupported here as there’s no people doing it) so i’ll not pass comment on them.

Where to from here?

If you have reviewed the 2019 Gartner Magic Quadrant for Analytics and BI and decided you want to know more about PowerBI and the Microsoft Data Platform, we can help.

Full disclosure – FTS Data & AI are a Microsoft Gold Partner so this post is a bit biased. However if you are not using PowerBI and are looking at migrating to a more cost effective platform, want to understand how cloud capabilities are transforming data and analytics – or work for Qlik and want to lure me into a dark alley – please contact us.

FTS Data & AI are Microsoft Gold Partners Data Analytics

Microsoft Gold Partners Data Analytics

 

D365 FinOps Reporting options

By | Data Visualisation | No Comments

The D365 FinOps Reporting landscape is a bit tricky to navigate. The documentation has not keeping up with the pace of product development. This makes the technical complexities difficult to navigate. In this quick post we provide an overview of what options you have. Dynamics 365 Finance and Operations (aka D365 FinOps) is a powerful ERP that can significantly improve business process efficiency, but for us at FTS Data & AI, we also see that there is a lot of additional value to be obtained from the data it captures.

What are the D365 FinOps Reporting options?

The three key tools within the D365 FinOps Reporting suite are:

  • Financial Reporting (aka: FRW)
  • PowerBI
  • SQL Server Reporting Services or SSRS (aka: Document Reporting Services)

There is also a fourth way – which is to pull the data out (aka BYOD or Bring Your Own Database) and report on it with whatever you like, but that’s for another post.

Financial Reporting

The simplest option – Financial Reporting (previously Management Reporting) – has a designer built into the D365 environment. It’s purpose is to provide Management Financial Reporting – i.e, Balance Sheet, Cash Flow, P&L – with some ability to customise your financial reports to your organisations viewpoint, with some filtering available for operational segments.

However its key limitation is that it is Financial in nature only and geared towards providing fairly static, statutory type reporting. Add to that the design and build of reports is very manual and it quickly reaches its limits. A typical example of such a report is shown below – you can see it is very much geared towards line item summaries.

D365 Finance Report Writer

D365 Finance Report Writer

PowerBI

PowerBI is planned to be the key reporting tool for D365 and may well replace the other options over time. A significant number of the embedded reports are already built in PowerBI. Part of any D365 Subscription is a PowerBI Embedded instance. All of the PowerBI reports in D365 are managed through this. Typically the generic reports do not suit the individual business needs and thus they need customising or extending. This is possible – complex to get started – but simple enough to execute once initiated, for example leveraging our Data Visualisation team who have deep experience doing this. Below is a “Before & After” set of examples to show the difference between out of the box reports and ones that have been enhanced by our team.

D365 FinOps PowerBI Report

D365 FinOps PowerBI Report – Before

 

D365 FinOps PowerBI Report

D365 FinOps PowerBI Report – After

A key restriction is that the embedded reports have to run off of the data in the Entity Store. This is a subset of the data in D365, with predefined and scheduled aggregations. To add content to the Entity Store requires a D365 developer, which adds cost to the report development process.

SQL Server Reporting Services (SSRS)

In every module in D365 there is a substantial set of SSRS reports that are included under the name of “Document Reporting Services“. These are embedded into D365 and present views of data with very simple parameters. You can view the technical details of included reports here and there are at least a thousand of them.

As with the PowerBI option it is possible to customise, brand and extend these reports. In this case the data source is any query that can be written against the Application Object Tree (AOT). Importantly this can show transactional as well as aggregate views. This allows for the production of customer facing content such as invoices.

Summary

So there are three options, each with their quirks:

  • FRW for detailed Financial Reporting
  • PowerBI for Aggregate Analytical Reporting
  • SSRS for highly customised reports

It’s not the clearest or most well documented landscape to navigate, but it is generally possible to produce the content you need to meet your organisations reporting requirements.

PowerBI Partner Showcase is published

By | Data Visualisation | No Comments

We are excited to announce the publication of our first PowerBI Partner Showcase! The Government Contract Analysis Tool is a demonstration of our ability to deliver powerful data visualisation solutions. It’s a great piece of work by our team and a big recognition of their capability.

Power BI Partner Showcase - Government Contract Analysis Tool

Power BI Partner Showcase – Government Contract Analysis Tool

 

Based on Open Data this PowerBI Partner Showcase demonstrates our ability to visualise data to provide insight on Federal Government Spending patterns – check it out on the official PowerBI site. It’s interesting to see how many contracts are awarded in June. This is visible in the bottom chart of the first page. In 2002 a whopping 83% of contract were awarded in June. Surprisingly, not an election year.

The Government Contract Analysis Tool

So what purpose does it serve? Government contracting is a vast industry worth billions of dollars with thousands of contracts awarded every year. Such large data presents difficulties in offering useful analysis, resulting in the commercial trends going unnoticed.

Typical questions are:

  • Who are my major competitors for this service?
  • What procurement method should I tender with?
  • When is the best time to tender for work?
  • Where are the most contracts being awarded within Australia?
  • How long will I need to commit for a contract with this agency?
  • What growth has there been for contract values across agencies over time?
  • How much should I tender for?

To remain competitive and drive revenue growth, decision makers need accurate and real-time answers to the above questions. Unfortunately, direct and constructive analysis is difficult with the vast quantity and low quality of the data provided.

FTS Data & AI took the raw data and crafted an effective analysis tool that provides answers to the critical questions that decision makers demand.

Users can now:

  • Identify who are their major competitors, and how much they have earned in contract revenue, thus helping shape price and value propositions.
  • Determine what procurement method is preferred for each service and agency, reallocating resources and refining tender strategy to better suit the favoured method.
  • See when most contracts are awarded in a financial year, and how much the average value is for that contract, allowing ample time to plan executable strategy.
  • Locate where most contracts are being awarded in Australia, enabling users to lift and shift resources into areas with greater potential for work.
  • Track how long different agencies and services are demanding contracts span, giving users the ability to budget time and associated costs of work.
  • Recognise trends in growth of contract value for each agency and service, empowering decisions around service offerings and prices.
  • Monitor market prices for each agency and service, sliced across numerous fields, informing users of various price pressures and trends.

Using Power BI, FTS Data & AI has transformed a low-quality dataset into a fully-interactive reporting tool capable of providing clear and concise answers to the questions that decision makers are tasked with solving to ensure continued business success.