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.


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.


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.




Confusion Matrix showing True Positives, True Negatives, False Positives and False Negatives

False Negatives: Evaluating Impact in Machine Learning

By | AI & ML | No Comments

Recently, I had the opportunity to build a regression model for one of FTS Data & AI‘s customers in the medical domain. Medical data poses an interesting challenge for machine learning experiments. In most cases when running algorithms for binary classification, the expected result in the training set will contain a large percentage of negatives. For example the goal of an experiment might be to predict if – based on a set of known clinical test results – a patient has a certain medical condition. The percentage of positive results in such a set, if it is a generic dataset for a vast number of medical conditions will most likely be very low. As a result a machine learning model when initially tested using a small set of chosen features will most likely come up with a high number of false negatives.

The latter however is a big problem in experiments involving clinical data, i.e. categorising that a patient does not have a certain medical condition incorrectly could have disastrous consequences. Once a confusion matrix is built, the model’s effectiveness is measured using indicators such as area under curve, accuracy, precision, recall and F1 score. In medical datasets, recall plays a big role as it measures the impact of false negatives. It can therefore hold significant weight in determining the most appropriate model for a given experiment.

The definition of recall is –

Recall = (True Positives) / (True Positives + False Negatives)

In the confusion matrix, the denominator in this equation makes up the total actual positives. So, recall therefore is effectively measuring the correct positive predictions over the actual number of positives in the dataset. If there were no false negatives, recall would be at the ideal score of 1, however if a large number of actual positives were predicated as negatives (i.e. false negatives), recall would be much lower.

As the model evolves and more relevant features are chosen for prediction, recall should start improving. In domains such as medicine where false negative predictions can have dire consequences, the recall score should play a vital role in choosing the most optimum model.

Getting Started with Chatbots

By | AI & ML | No Comments

Most retail websites have a chat channel these days and more often than not, there isn’t a human being on the other end. A trained computer program, i.e. a chatbot, performs the mundane job of answering repetitive questions and never gets tired of doing it. In some cases, the chatbot performs tasks that would’ve been time-consuming for a human being in a matter of seconds. And this experience is only going to get richer for the user over time.

Learning to Walk before Running

Organisations that are looking to leverage chatbots to bring efficiencies into their customer-centric processes can gain valuable expertise by first building an inward-facing chatbot that assists their staff. By building a chatbot that employees can communicate with, the organisation can provide a valuable service to its staff and in the process, get a detailed understanding of the methodologies & tools required to build a productive chatbot. These learnings can then be applied to chatbots that are made available to customers.

Getting Started

The primary use case for building a bot is automating repetitive manual tasks. In the case of a chatbot, a good use case is to help in answering questions that a user would usually search in a published document. Most organisations have an internal Wiki page or a corporate policy document which staff needs to manually trawl through periodically to get answers to specific questions. Getting a chatbot to simplify this process and making it efficient can help improve staff productivity.

The technical services and tools required to build a chatbot are now mature. Microsoft’s Azure Bot Service facilitates building & deploying a chatbot and integrating it with knowledge bases stored in cognitive services such as QnA Maker. Once the chatbot has been published, it can be integrated with chat channels such as Skype for Business & Teams.

The Next Steps

Once a chatbot that can answer questions from a knowledge base has been built, it can be made more intelligent by integrating it with cognitive services such as LUIS (Language understanding intelligent service). This makes the chatbot responsive to actual intents deciphered from the conversation. The models that power these cognitive services are constantly learning, thereby making the chatbots more responsive over time.

Once an organisation successfully implements such an inward-facing chatbot, building a customer-facing chatbot becomes a natural extension. The organisation can then look to implement more complex process flows & integrations with internal systems such as CRMs to improve the overall user experience.

Our Experience

At FTS Data & AI, we practice what we preach. We’ve developed a chatbot named ‘fts-bot’ which we’ve integrated with our Teams chat channel. The fts-bot can answer questions from FTS’s employee handbook thereby eliminating the need for staff to manually search a PDF document. Our staff, especially those who haven’t had a lot of interactions with chatbots, have found this experience productive, and we continue to receive new ideas from technical & non-technical staff.


Chatbots will become ubiquitous on the internet in the future. They will offer customers a personalised user experience and continue to learn with each interaction. Food for thought – which time-consuming process do you currently follow that could be optimised by having a chatbot assist you? Please comment.

DevOps in Database Development

By | AI & ML | No Comments

When we speak of applications development today, we assume DevOps is an integral part of the software development cycle. Modern microservices-based architectures facilitate the use of DevOps and the benefits of this are well known – agile development, quicker defect resolution, better collaboration, etc. Through containerisation using platforms such as Docker and container orchestrators such as Kubernetes and DC/OS, continuous integration and deployment become essential and not optional steps in daily activities. PaaS offerings in Microsoft Azure like AKS (Azure Kubernetes Service) make management of the platforms even simpler and thereby encourage uptake.

However, while DevOps practices have become mature in the applications development sphere, the same cannot be said when it comes to database development. To be able to build a true DataOps team that can integrate agile engineering processes encompassing IT and data teams, a DevOps mindset is essential. Many large enterprises as well as small organisations continue to follow age-old practices for developing data-related artefacts and as a result, we still see a lack of agility and at times, poor quality.

Microsoft has invested heavily to ensure that database developers can also leverage the benefits that have been reaped by application developers. Today’s SQL Server development IDE, SQL Server Data Tools (SSDT), comes loaded with features that enable a development team to collaborate and follow good programming practices. When combined with Visual Studio Team Services (VSTS), we get the environment needed to engender a DevOps-focused development culture.

Six Steps to DevOps

At FTS Data & AI, we believe DevOps is a foundational step in ensuring high-quality outcomes for our clients. Therefore, we make use of the toolsets made available by Microsoft in our development activities and adhere to strict policies, which are enforced by the tools. If you are looking to enable a similar culture in your database development team, consider the following guidelines –

  1. Version Control – Use a distributed version control system like Git for your database code. Git is ingrained in SSDT and VSTS, and for those who prefer the command line, Git can be used in a PowerShell window. Once you’ve set-up a VSTS environment, make use of a SQL Server database project in SSDT for your database development and sync it with Git.
  2. Branching Strategy – Start with a simple branching strategy in Git. There is no one-size-fits-all approach for this, so you’ll need to pick a strategy based on the complexity of the project and the size of the team. As an example, in addition to the master branch, create a dev branch and have the development team work of this branch. Create pull requests to merge the changes into the master branch. Ensure that the master branch is always stable.
  3. Development Environment – Consider making use of SQL Server 2017 hosted on Linux in Docker as a development instance. The containerised SQL Server instance is quick to boot, tear down & replace. PowerShell can be used to issue docker commands, or Kitematic can be used if the preference is for a GUI. 
  4. Continuous Integration – VSTS can be configured for automated builds which can be triggered when changes are committed. Configure continuous integration on the dev branch to ensure that the database builds successfully on every commit. 
  5. Continuous Deployment – Automate publishing changes to QA environment. This will allow testing to commence as soon as changes are committed successfully. When the process becomes mature, deployment to production can also be automated.
  6. Policies – Ensure access to the branches is only given to those who need it. Apply strict policies such as requiring a successful build as a prerequisite for a pull request to succeed. Automatically include code reviewers who would need to approve the changes before pull requests can be completed.

These initial steps will ease the team into the DevOps culture. Look to get these steps right before moving to more advanced areas like automated unit testing, NuGet packaging, coupling database with application changes, etc.

Through the use of a combination of mature tools and strict practices, a DevOps pipeline for database-related development activities is no longer a pipe dream. As MapR’s Chief Architect Ted Dunning has predicted, a sophisticated DataOps team comprising of data-focused developers and data scientists will be the way of the future (MapR press release). Sound DevOps practices will be the first step towards getting there.