Using Microsoft Azure Stream Analytics and Power BI: Real-time Telco fraud detection

This post is referenced from Microsoft Azure main articles, during my free time I want to try out Azure Stream Analytics and know more about the Stream Analytics where I found two great articles which help me to understand in detail:

Below are those articles:

1) https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-real-time-fraud-detection

2) https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-power-bi-dashboard

Here one can learn related to Azure Steam Analytics and how Power BI visualization dashboard can be used for real time data analysis and helps to visualize fraudulent phone calls that are detected by a Streaming Analytics job in real time. Since, I am also currently working in Telco sector so this analysis helps me a lot.

This tutorial provides an end-to-end illustration of how to use Azure Stream Analytics. You learn how to:

  • Bring streaming events into an instance of Azure Event Hubs. In this tutorial, you’ll use an app that we provide that simulates a stream of mobile-phone metadata records.
  • Write SQL-like Stream Analytics queries to transform data, aggregating information or looking for patterns. You will see how to use a query to examine the incoming stream and look for calls that might be fraudulent.
  • Send the results to an output sink (storage) that you can analyze for additional insights. In this case, you’ll send the suspicious call data to Azure Blob storage.
  • Also send the results to an output sink (Power BI) where you can analyze for additional insights. How one can build real time telco fraud detection visualization dashboard in Power BI.

In this tutorial, we use the example of real-time fraud detection based on phone-call data. But the technique we illustrate is also suited for other types of fraud detection, such as credit card fraud or identity theft.

Scenario: Telecommunications and SIM fraud detection in real time

A telecommunications company has a large volume of data for incoming calls. The company wants to detect fraudulent calls in real time so that they can notify customers or shut down service for a specific number. One type of SIM fraud involves multiple calls from the same identity around the same time but in geographically different locations. To detect this type of fraud, the company needs to examine incoming phone records and look for specific patterns—in this case, for calls made around the same time in different countries. Any phone records that fall into this category are written to storage for subsequent analysis and also send those results to an output sink (Power BI) where one can analyze for additional insights and helps in real-time fraud detection based on live phone-call data .

I have followed step by step by following above article 1 and article 2, so one should go in details through those articles. Below are some snap from steps I followed:

Now, Streaming Analytics job starts looking for fraudulent calls in the incoming stream. The job also creates the dataset and table in Power BI and starts sending data about the fraudulent calls to them.

Once we finished creating Azure Stream Analytics job and output of that job to Power BI sink table name as ‘Telco_Fraud_Demo’ inside Data and BI Summit 2018 Workspace in Power BI Service. After that one can create real time visualization dashboard in Power BI.

For this just login to Power BI Service, and then under your Workspaces in my case ‘Data and BI Summit 2018 ‘ create dashboard by clicking +Add title tab where you will see Custom streaming Data in REAL-TIME DATA section and then once you select Custom Streaming Data you will see the same output power BI sink table which we had created in Azure Streaming Analytics Job Output

By selecting different visualization Type we can visualize and analyze for additional insights and helps in real-time telco fraud detection based on Streaming dataset.

Also, in near future we can directly connect the dataset as Streaming dataset in near future as per below Power BI feature notice.

Thanks,

Anil Maharjan

Senior BI Engineer | Nepal Power BI User Group Leader

How to create Microsoft Azure SQL Database and Visualize along with Power BI.

This blog is mainly about creating Microsoft Azure SQL DB and visualizing with the help of Power BI Desktop.

Firstly, we need to have a Microsoft Azure subscription in order to create the azure sql database.  One can try for free for one month worth $200 azure subscription for free. You can find more on this from below link:

https://azure.microsoft.com/en-us/free/?v=18.03

Then, you need to create Azure SQL database. For this please follow below link which will help us to create Azure SQL database step by step. I have gone through each step in detail.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-design-first-database

Below are some of steps snap which you will see while creating Azure SQL database.

 

Meanwhile, It will help if you know more about Database Transaction Units (DTUs) and elastic Database Transaction Units (eDTUs). Once can know more on below link: which helps to figure out the Azure SQL database monthly subscription charges.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-what-is-a-dtu

Also, one can know more about the elastic database pool.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-what-is-a-dtu

Once you successfully create the Azure SQL DB then you will see as similar to below snap. Here I have create AzureSQLDB as database name and with AdventureWorks test data sample tables. I have set my server name as anilmaharjan.database.windows.net with 2GB in total disk storage.

After that I tried to connect my Azure SQL  database ‘AzureSQLDB ‘  but got the below error. Which means we need to allow client IP address to access the windows azure server SQL DB. So for this we need to add Firewall settings for this . Once success Firewall setting, now you are good to go to connect Power BI Desktop to windows Azure SQL DB you just created.

Do visualization by using Power BI Desktop and then can upload in Power BI cloud service too. Find more on different kind of Power BI visualizations:

https://docs.microsoft.com/en-us/power-bi/power-bi-visualization-types-for-reports-and-q-and-a

Now, you had successfully created Azure SQL database and do different visualization using Power BI . Also, it’s good to see the data explore option with Azure and we can direct query and analysis of Azure SQL database in Azure portal itself. Which is great. One can setup Azure cost management in order to optimize your cloud spend and which is also a quite good feature.

#AzureSQLDatabase ,#WindowsAzure, #PowerBI,In summary, you have successfully setup Azure SQL database and do different kinds of visualization by using Power BI Desktop.

Thanks,

Anil Maharjan

Senior BI Engineer | Nepal Power BI User Group Leader