Business Analytics-Modern Data Management & Business Intelligence Solved

You are going to use Azure Stream Analytics to process a data stream of ATM transactions and answer stream queries. The schema of the stream is: (ATMCode, CardNumber, Type, Amount)

 

1.       Create a students account at: https://azure.microsoft.com/en-us/free/students/  

2.       Setup an Event Hub.

3.       Generate a Security Access Signature (use a terminal with windows operationg system): https://github.com/sandrinodimattia/RedDog/releases  

4.       Edit Generator.html (open with a text editor, e.g.: Sublime or Notepad++) and update the CONFIG variables. Keep the “js” folder in the same folder as the Generator.html file.

5.       Feed the Event Hub with the use of Generator.html (In order to start the Stream Generator, open the Generator.html with a web browser, e.g.: Chrome and press the “Send Data” button.)

6.       Setup a Storage account.

7.       Upload the Reference Data files to your storage account.

8.       Setup a Stream Analytics Job.

9.       Use the Event Hub + Reference Data Files as Input.

10.   Create a Blob Storage Output.

 

SCENARIO 

 

You have access to a data stream that’s generated from ATMs. Each event contains data related to the transaction that took place. You are asked to create an Azure Analytics solution for the tasks listed in the “QUERIES” section.

 

REFERENCE DATA 

 

GENERAL NOTES
-  Use only the parts of the datasets that you need for the queries.

-  Use Sublime or Notepad++ to have a proper view of the datasets.

 

AREA.json
-  Geographical Information.

-  Connects each area_code with a city and a country.

-  The “area_code” of this dataset can be joined with the “area_code” of ATM.json

ATM.json
-  Information about the ATM.

-  Connects each ATM with an area.

-  The “atm_code” of this dataset can be joined with the input’s “ATMCode” section.

Customer.json
-  Information about each customer.

-  Provides demographic information about each customer.

-  The “card_number” of this dataset can be joined with the input’s “CardNumber” section.

 

DATA STREAM INPUT 

 

GENERAL NOTES
Events generated have the following format:

{ 

"ATMCode": 13, 

"CardNumber": 5610827137784218, 

"Type": 0, 

"Amount": 37

} 

 

FIELDS DESCRIPTION
-  ATMCode: Information about the type of the ATM. Can be joined with Atm.json

-  CardNumber: The card number related to the transaction. Can be joined with Customer.json - Type: The type of the transaction. There are two types of transactions, type “1” and type “0”.

-  Amount: The amount of the transaction.

 

QUERIES 

Query 1: Show the total “Amount” of “Type = 0” transactions at “ATM Code = 21” of the last 10 minutes.

Repeat as new events keep flowing in (use a sliding window). 

Query 2: Show the total “Amount” of “Type = 1” transactions at “ATM Code = 21” of the last hour. Repeat once every hour (use a tumbling window). 

Query 3: Show the total “Amount” of “Type = 1” transactions at “ATM Code = 21” of the last hour.

Repeat once every 30 minutes (use a hopping window). 

Query 4: Show the total “Amount” of “Type = 1” transactions per “ATM Code” of the last one hour (use a sliding window). 

Query 5: Show the total “Amount” of “Type = 1” transactions per “Area Code” of the last hour. Repeat once every hour (use a tumbling window). 

Query 6: Show the total “Amount” per ATM’s “City” and Customer’s “Gender” of the last hour. Repeat once every hour (use a tumbling window). 

Query 7: Alert (Do a simple SELECT “1”) if a Customer has performed two transactions of “Type = 1” in a window of an hour (use a sliding window). 

Query 8: Alert (Do a simple SELECT “1”) if the “Area Code” of the ATM of the transaction is not the same as the “Area Code” of the “Card Number” (Customer’s Area Code) - (use a sliding window)