Leon's BI Blog

Wednesday, March 3, 2021

Azure Data Factory – Automated Email Notifications using Logic Apps

Logic Apps allow you to easily create a workflow in the cloud without having to write much code. Since ADF has no built-in mechanism to send e-mails we are going to send them through Logic Apps.
1. In the top search bar of the Azure Portal, Type as logic apps and select.

2. In the next window click Add -> Consumption

3. Click Review + create then create
4. Under the created Logic Apps click View in Logic Apps designer
5. We need the HTTP request ("When a HTTP request is received") as the trigger, since we're going to use the Web Activity in ADF to start the Logic App. From ADF, we're going to pass along some parameters in the HTTP request, which we'll use in the e-mail later. This can be done by sending JSON along in the body of the request.

6. Use the below Jason schema


{
    "properties": {
        "BodyTitle": {
            "type": "string"
        },
        "Color": {
            "type": "string"
        },
        "EmailTo": {
            "type": "string"
        },
        "ErrorLine": {
            "type": "string"
        },
        "Message": {
            "type": "string"
        },       
        "Status": {
            "type": "string"
        },
        "StatusNote": {
            "type": "string"
        },
      
        "Title": {
            "type": "string"
        }
    },
    "type": "object"
}

7. Click on New step to add a new action to the Logic App. This new step will design the email that needs to be sent. When you search for "Initialize Variable", you can find this action:

8.Name: Email Body
Type: String
Value: (refer the below HTML/CSS)

9. You can select the defined values in the Jason code from the dynamic content.

10. Click on New step to add a new action to the Logic App. This new step will send the e-mail. When you search for "mail", you'll see there are many different actions:

11. Save your Logic Apps.
Send Notifications from an Azure Data Factory Pipeline
12. Go to your Azure Data Factory
13. Select your Pipeline that you want to notify the result.
14. Add to Web activities and rename them: one for success and other for failure. Refer the image below:

15. Go back to your Logic apps you created and copy the HTTP POST URL in the When a HTTP request is received Step.

16. In the ADF pipeline Select the failure notification Web activity.
17. Paste the copied Value for URL and fill out the rest of the values as shown below:
Method: POST
Headers:
a. Name: Content-type
b. Value: application/json

18. Click the Body to enter the value:
Use the below:

{ "Title":"Pipeline Executions", "BodyTitle":"Pipeline Execution was failed.", "StatusNote":"in Error.", "ErrorLine":"We will get back to you with the root cause. For more details please contact the support team ", "Color":"Red", "Status":"Failed", "EmailTo":"user@company.co" }

19. Select the success notification Web activity.
20. Follow the step 17
21. Click the Body to enter the value:
Use the below:

{ "Title":"Toyota UAT - Pipeline Executions", "BodyTitle":"Pipeline Execution was successful.", "StatusNote":"Successful.", "Color":"Green", "Status":"Succeeded", "ErrorLine":, "EmailTo":"user@company.co" }

22. Execute the pipeline and check whether you are getting the Email as per the result of the execution to the configured "EmailTo" value as in the Body.

Thursday, August 20, 2020

Process for Advanced Users to use Raw Data

Overall Plan

Data Sources

Reading Raw Data and converting it to a readable/formatted form

Tools and Technologies:

Azure Data Lake
Azure Data Factory
Python
Jupiter Notebook

The purpose of this process is to enable High Level users to work with data in an integrated data friendly environment. As mentioned above, in Azure environment we are allocating a sandbox space to do all the necessary operations.

For this example, we will work on a converting a Parquet File to a CSV.

Step 1

To start things with, we need to create two containers under your preferred storage area in Azure portal

raw – To land all the data in raw form

sandbox – Where the user is going to do all the operations with data

Step 2

Upload the parquet file, that you want to convert to csv into your raw container.

Step 3

Change the access level of the parquet file to Container.

Step 4

Copy the path of the uploaded .parquet file

Step 5

Open Jupyter Notebook in your web browser and select Python.

Step 6

Type the below python script to read the uploaded file inside Jupyter Notebook.

import pandas as pd

pd.read_parquet(r' <paste the copied path in Step 4 here>')

Step 7

You should get a view such as below:

Complete transformation of a parquet file to a readable format in Azure Data Lake

Step 1

Create a pipeline – drag and drop a copy Operation.

Step 2

Select your parquet file that you uploaded to raw as the Source dataset.

Step 3

Select your csv (DelimeterdText) as the Sink Dataset and point it out to your Sandbox container.

Note:

When creating this text based (csv) dataset please select the “First row as header option”.

Up to this point, you have selected your parquet file (source) from Raw environment and you are about to convert it to text format and store in sandbox for further analysis.

Step 4

Now you can run the pipeline to start the conversion.

Step 5

Once the process is completed, you can fine the converted file in form of .txt in your sandbox.

Step 6

Download your file from sandbox

Note:

Along with the above described data conversion method, the same purpose can be achieved in your local environment using Python and Jupyter Notebook.

Connecting PowerBI with the source in Sandbox

Step 1

Open and sign into PowerBI

Step 2

Get a new data source. Get data -> More..->Blob Storage

Step 3

Type your storage account name as Account name or URL

Step 4

You should see your containers on the left side and the data sources on the right side of the screen.

Since we need to use the sources in sandbox, select sandbox and click Load.

Step 5

Under Fields you will find the sandbox as below:

Step 6

We have to transform the sandbox data into a understandable format.

Click Data -> Home -> Transform Data -> Transform

Step 7

Click Binary next to the source file you want to analyze

Step 8

Click Close & Apply

Step 9

Now you can visualize and work with the formatted data in PowerBI.