There is a growing trend among professionals and entire companies to move away from email as a primary means of communication and are even adopting alternative technologies. One of the most popular of these is Slack. Slack is a new kind of messaging and communication platform between colleagues, team or community members that allows them to integrate a lot of services including Visual Studio Team Service, Jira and GitHub.
How then can we integrate Power BI data alerts to Slack? In this article we’ll answer this question and create a demonstration to show how.
The below image illustrates architecture of Power BI data alerts and Slack integration demonstration. In short; we will connect Power BI to SQL Azure and when the data alert is created by Power BI Microsoft Flow, send this alert to Slack Chanel.
In this section, we can mention Microsoft Flow. Microsoft Flow is a cloud-based workflow tool which allows us to automate business processes. Particularly, Microsoft Flow offers to integrate and automate processes and tasks between different services. We will use this advanced integration feature of Microsoft Flow for communicating between Power BI and Slack.
Demonstration
In this demonstration, we will complete the following steps.
- Create AdventureworksLT sample database in Azure SQL (Platform as a Service)
- Create a simple report with Power BI and publish this report to Power BI Portal
- Create Power BI data alert
- Integrate Power BI data alert notification and Slack with Microsoft Flow
Create AdventureworksLT sample database in Azure SQL
We will create an Azure SQL sample database whose named AdventureworksLT (AdventureWorks Light) and get data from this database to Power BI. We don t need any extra operations to install AdventureworksLT sample database in Azure SQL. When we want to create a SQL database on Azure Portal, it offers the option to create AdventureworksLT by default. You can find details of database creation steps in the article Microsoft Azure SQL Database – Step by Step Creation tutorial by Venkatesan Jayakantham. We’ll skip the details.
Create a simple report with Power BI and publish this report to Power BI Portal
In this step; we will create a basic Power BI report and publish it to Power BI portal.
Open Power BI Desktop edition, click Get Data and then select More… option
Find Azure SQL database and click Connect
Fill the server and database name and paste the following query to SQL Statement box. This query will return some details about AdventureworkLT orders.
|
1 2 3 4 5 6 |
select Product.Name , VCat.ProductCategoryName,VCat.ParentProductCategoryName,SaleDet.LineTotal from SalesLT.SalesOrderDetail SaleDet INNER JOIN SalesLT.Product Product ON SaleDet.ProductID = Product.ProductID INNER JOIN SalesLT.vGetAllCategories VCat ON VCat.ProductCategoryID=Product.ProductCategoryID |
Select DirectQuery and click OK button
Chose Database tab and then fill the proper User Name and Password
Tip: If your IP address does not have permission to access Azure SQL you will get this error message.
If you experience this issue, you have to give access permission to your IP address. We can configure this permission in Azure Portal or with sp_set_database_firewall_rule procedure. Minette Steynberg has an awesome article details about this configuration shown here Configuring the Azure SQL Database Firewall
After these steps, a little part of dataset will show in the screen.
In this step, we will add a card visual which illustrates the total value of LineTotal column.
Drag and drop LineTotal column to card visual field.
Click the Publish button and publish the report to Power BI Portal.
You will get the following alert.
Tip: This alert defines that data source username and password does not send it to Power BI portal. Because of this reason, we have to manually re-enter this dataset credentials in Power BI portal.
Login Power BI Portal, find published dataset under the workspace and then click Settings of dataset.
Click Edit credentials and select Basic Authentication Method. Fill the User Name and Password text box.
In this step, we will set Schedule cache refresh option. This option lets us refresh data set identified time frequency. Refresh history option shows us the log of data refresh tasks.
Open the report and click the pin visual option and send it to Power BI card visual to a dashboard. This setting allows us to create data alert in dashboard.
Click Go to dashboard.
Create Power BI data alert
All the steps which we completed until here were for creation Power BI dataset and Power BI dashboard. In the next sections, we will create a data alert and send this alert to Slack. Power BI Data Alert is a notification mechanism that sends information messages when the data values exceed the limits. We can create data alerts only for card, KPI and gauge visuals.
Click there point (…) of card visual in dashboard and select Manage alerts
Click Add alert rule and add a new alert. This is very basic screen; we can set it when notification is triggered. In the below demonstration; we will create a data alert that notifies us once an hour if the LineTotal value reaches down to 650 K (650.000). When you check “Send me email too” option you will get an e-mail about this notification.
When you check Send me email to option you will get an e-mail about this notification. This e-mail notification message will look like this.
You will see a new message alert in the notification tab of Power BI Portal. It means that a notification is fired and a new message is notified. When you click this message icon new notification or notifications will be shown.
Integrate Power BI data alert notification and Slack with Microsoft Flow
In the next steps of this demonstration; we will log into Microsoft Flow and then connect Power BI notification to Slack. We will find Alert your team when a Power BI data alert is triggered template in Microsoft Flow.
We will set the Slack and Power BI account credentials and click continue.
In this screen, we will select the name of data alert which we want to notify Slack channel.
Finally, Power BI alert sends it to Slack app. The below image was taken from a mobile app of Slack.
As a result, we demonstrated Power BI and Slack integration by aid of Microsoft Flow.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023