Shanmukh Sattiraju

Monday, January 09, 2023

Power BI - How to generate Fiscal Year columns in Date Table using DAX Formulas

Here we are going to see How to generate Fiscal Year columns in Date Table in Power BI using DAX.

We are seeing how to create Fiscal Year Number, Fiscal Month, Fiscal Month Number, Fiscal Quarter, Fiscal Year with Quarter, Fiscal Year With Month.

Azure DevOps - Quick Solution - "No hosted parallelism has been purchased or granted Error" --Fixed!

By Shanmukh Sattiraju

You will not be given any free grant of agents by default from Microsoft with its new policy

To build your code or deploy your software using Azure Pipelines, you need at least one agent. As you add more code and people, you'll eventually need more.

When your pipeline runs, the system begins one or more jobs. An agent is computing infrastructure with installed agent software that runs one job at a time.

2 types of Agents:

1. Microsoft Hosted Agents

2. Self-Hosted Agents

Microsoft Hosted: Here the will host the agent

Self-hosted – You can host the agent to run the job for you

If you do not want to wait for 2 business days and if you are doing these tests you can host a self hosted agent in a virtual machine or in your own local machine

Azure Data Factory - Setup Continuous Integration Continuous Delivery (CI/CD) using Azure DevOps Git

By Shanmukh Sattiraju

In this video, we are configuring the Continuous Integration and Continuous Delivery (CICD) from scratch in ADF using Azure DevOps Git.

Azure Data Factory - Copy multiple SQL tables incrementally using a watermark table (Delta Load) ADF

By Shanmukh Sattiraju

Azure Data Factory - Copy multiple SQL tables incrementally using a watermark table (Delta Load) ADF

Here we are doing incremental copy of multiple SQL Tables from one Database to another Database.

Please refer the below video Step by Step explanation of pipeline for a Single SQL Database:

https://youtu.be/AOClU3s9jXw

We connected to SQL Database from On-Premise Environment for this video. To know how to setup Self Hosted Integrated Runtime,

Please refer:

https://youtu.be/d9Xp2pnjYcI

Azure Data Factory - Copying files from On premise Linux or Unix Server to Azure Datalake using ADF

By Shanmukh Sattiraju

Here we are copying files from Linux Virtual Machine to ADLS. We are created a file share from linux and we are adding that file share to windows,

using self hosted IR we are accessing the Linux File Share and we are able to copy the files from Linux to Azure DataLake

Link for configuring Self Hosted Integration Runtime to access On premise Windows file:

https://youtu.be/d9Xp2pnjYcI

To install samba utility:

-- sudo apt install samba -y

To start samba service:

-- sudo systemctl enable --now smbd

To check status of service:

-- sudo systemctl status smbd

To create a samba user:

-- sudo smbpasswd -a (username)

Configuring Samba share from Linux to Windows

Copy files from Linux to Azure Datalake using ADF

Azure Data Factory - Status of Trigger runs during their pipeline failure situation

By Shanmukh Sattiraju

Azure Data Factory - Status of Trigger runs during their pipeline failure situation

Here we are seeing behavior of Tumbling Window, Scheduled and Storage Event triggers during the event of pipeline failure.

To configure Self Hosted Integration Runtime:

https://youtu.be/d9Xp2pnjYcI

Azure Data Factory -Delete files based on Last modified date (delete more than 7 days ) using ADF

By Shanmukh Sattiraju

Azure Data Factory -Delete files from ADLS Gen2 based on Last modified date (delete more than 7 days ) using ADF

In this we are deleting files from Azure Data Factory based on the last modified date (as per requirement) , using this we can perform clean up for unwanted or stale data.

Azure Data Factory - Create a Customer Managed Key and encrypt ADF using that CMK

By Shanmukh Sattiraju

Azure Data Factory - Create a Customer Managed Key and encrypt ADF using that CMK

By default, data is encrypted with a Microsoft-managed key that is uniquely assigned to your data factory. For extra security , you can use customer-managed keys feature in Azure Data Factory.

When you specify a customer-managed key, Data Factory uses both the factory system key and the CMK to encrypt customer data. Missing either would result in Deny of Access to data and factory.

Reference Documentation Link: https://docs.microsoft.com/en-us/azure/data-factory/enable-customer-managed-key

Azure Data Factory - Send an alert email when a blob is created or deleted from ADLS Gen2 using ADF

By Shanmukh Sattiraju

Azure Data Factory - Send an alert email when a blob is created or deleted from ADLS Gen2 using ADF

Notifying about the alerts is an important part in ADF, we can achieve that by following the steps in the video. Here we are getting email alerts to GMAIL whenever a file is removed and created in our Storage account.

Refer the below video to get custom alert email when pipeline failed : https://youtu.be/KorFyv5FntY

Azure Data Factory - Import or Copy Pipeline from One Data Factory to Another Data Factory

By Shanmukh Sattiraju

Azure Data Factory - Import or Copy Pipeline from One Data Factory to Another Data Factory

Here we are importing one ADF to another ADF, all the configurations in one pipeline will be copied to another using this

Azure Data Factory - Copy multiple files from HTTP website dynamically to Data Lake using ADF

By Shanmukh Sattiraju

Azure Data Factory - Copy multiple files from HTTP website dynamically to Data Lake using ADF

Here we are copying multiple files dynamically from HTTP website . We are using GitHub as source website to copy multiple files to Azure Data Lake Gen2

Previous video to copy single HTTP file: https://youtu.be/PNN5VPoP2zQ

Azure Data Factory - Copy data from HTTP website (GitHub) to Azure Data lake using ADF

By Shanmukh Sattiraju

Azure Data Factory - Copy data from HTTP website to Azure Data lake using ADF

Here we are copying a csv file from a HTTP website (GitHub) to Azure Data lake Storage using copy activity from ADF

Refer the below link for copying multiple files from a HTTP website dynamically to ADLS: https://youtu.be/K5ND-pyD3yE

Azure Data Factory - Incremental load or Delta load using a watermark Table

By Shanmukh Sattiraju

Here source is having incoming values with some date value, we are keeping a watermark table which keeps track of the data copied and this helps to incrementally load the data to the destination preventing copy of the old data.

Refer below to setup self hosted IR to use SQL from local Machine:

https://youtu.be/d9Xp2pnjYcI

Azure Data Factory - Copy multiple files from AWS S3 to Azure Data Lake Storage using ADF pipeline

By Shanmukh Sattiraju

Azure Data Factory - Copy multiple files from AWS S3 to Azure Data Lake Storage using ADF pipeline

Here we are getting all the files in a folder in Amazon S3. We are copying all files dynamically using Azure Data Factory

To prevent hard coded values in sensitive information, its recommended to use Azure Key Vault. Refer the below video for reference to how to use Azure Key vault in ADF:

https://youtu.be/2lPlReATWew

Azure Data Factory - Delete specific folders and specific files using lookup file in ADF

By Shanmukh Sattiraju

Azure Data Factory - Delete specific folders and specific files using lookup file in ADF

Here we are making a lookup file as reference which tells the specific files and folders to be deleted. We are using lookup , forEach and delete activity to execute this logic to delete the required files

Azure Data Factory - Create Managed VNET Integration runtime and creating Managed Private Endpoint

By Shanmukh Sattiraju

Azure Data Factory - Create Managed VNET Integration runtime and creating Managed Private Endpoint

In this we are first creating Managed VNET Integration runtime and we are establishing a private endpoint connection with ADLS Gen2 and doing a copy activity using Managed VNET Integration Runtime

Azure Data Factory - Copy specific range of cells in Excel along with sheets dynamically to CSV

By Shanmukh Sattiraju

Azure Data Factory - Copy specific range of cells in Excel along with sheets dynamically

Here we are copying only specific range of cells from an Excel file , using this we can eliminate unwanted cells from an Excel while copying

Below is the video which tells about copying excel sheets dynamically in details

https://youtu.be/n5phQgvIaxY

Azure Data Factory - Moving Files from Source to Destination in ADLS using copy and delete Activity

By Shanmukh Sattiraju

In Azure Data Factory - We do not have any move activity , we can take advantage of copy and delete activity to fulfill the requirement of moving.

In this video we are moving all the csv files from one folder to another

Azure Data Factory - Copy multiple sheets in excel dynamically into multiple files/tables /SQL table

By Shanmukh Sattiraju

Azure Data Factory - Copy multiple sheets in excel dynamically into multiple files/tables /SQL table

We are copying multiple sheets in Excel dynamically into multiple Files, which created each csv file for a sheet.

We are also copying these sheets into a SQL table and also multiple table based on the name of the sheet

Azure Data Factory - Add additional column dynamically with Row count of files in Copy activity

By Shanmukh Sattiraju

Add additional column dynamically with Row count of files in Copy activity

In this we are dynamically adding a column which tells us the row count that each file holds in the Azure Data Lake and we are copying them using Copy activity to SQL Database

Azure Data Factory - Adding Keyvault secret for password in SSIS Package in pipeline

By Shanmukh Sattiraju

Azure Data Factory - Adding Keyvault secret for password in SSIS Package in Azure Data Factory

Storing the sensitive information is an importing thing while developing a solution. Using Azure KeyVault we can store our secrets and access them using a linked service in Azure Data Factory.

Azure Data Factory - Run SSIS packages in ADF as File System (Project Model) using SSIS-IR

By Shanmukh Sattiraju

Azure Data Factory - Run SSIS packages in ADF as File System (Project Model) using SSIS-IR

In this we are using Project Deployment Model, we are taking the .ispac file that is getting generated after build. From that we are uploading the .ispac file and the package(.dtsx) to Azure File Share.

If you want to know the FileSystem (Package),

Please visit: https://youtu.be/LzvoUwriio8

Azure Data Factory - Run SSIS packages in ADF as File System (Package Model) using SSIS-IR

By Shanmukh Sattiraju

Azure Data Factory - Run SSIS packages in ADF as File System (Package Model) using SSIS-IR

In this we are using package deployment model and storing our Configuration File (.dtsConfig) and Package(.dtsx) in an Azure File Share.

Using Azure Data Factory we are calling the package as File System (Package) and giving the configuration file with package

ADF looks for the configuration file and performs the action based on the content /variables that we given in the configuration file

Azure Data Factory -Run SSIS-IR using Self Hosted IR as proxy to access On premise SQL Database Demo

By Shanmukh Sattiraju

Azure Data Factory -Run SSIS-IR using Self Hosted IR as proxy to access On premise SQL Database Demo

Here we are accessing Onpremise SQL Database Data using Self Hosted IR as a proxy, We are deploying the SSIS-IR in the Azure SQL Database.

Azure Data Factory - Lift & Shift SSIS -Execute SSIS package in ADF using Project deployment Model (SSISDB)

By Shanmukh Sattiraju

Azure Data Factory - Lift & Shift SSIS -Execute SSIS package in ADF using Project deployment Model (SSISDB) by creating SSIS - Integration Runtime

In this video we are using Project Deployment model . We are deploying our Project into SSISDB which is being Hosted in Azure SQL Database Server.

We are executing the packages from Azure Data Factory which are hosted in the SSISDB.

SSIS - Deploy SSIS-DB Catalog in local SQL Server - Project Deployment Model

By Shanmukh Sattiraju

Here we are deploying the SSISDB catalog into on premise local SQL server

Azure Data Factory - Send a custom alert email for Activity or Pipeline Failure using Logic App

By Shanmukh Sattiraju

Azure Data Factory - Send alert email for Activity or Pipeline Failure using Logic App

We can send a custom alert email for any activity or pipeline failure using Logic Apps from Azure Data Factory

Azure Data Factory - Invoke an Azure Databricks notebook from an Azure Data Factory Pipeline

By Shanmukh Sattiraju

Azure Data Factory - Invoke an Azure Databricks notebook from an Azure Data Factory Pipeline

We have a notebook which performs a transformation and generates a csv file.

We can execute that Azure Databricks notebook from Azure Data Factory using Databricks Activity.

Azure Data Factory - Copying Today's files with date and timestamp in name of the file

By Shanmukh Sattiraju

Azure Data Factory - Incremental data copy Copy files that are Created / Modified today

This helps us to get files which are added or modified on Today's date

Similarly there are multiple approaches to get today's file

If you want only non-empty file and modified or created date with today,

please follow below

https://youtu.be/LOo9JC-HtLk

If you want Copy Today's files with date and timestamp in name of the file and copy based on the name, please follow below

https://youtu.be/u67xQ1u6NjU

Azure Data Factory - Copy files which are not empty and last modified is today to ADLS container

By Shanmukh Sattiraju

Azure Data Factory - Copy files which are not empty and last modified is today to ADLS container

This approach helps us to get only files which are not empty

Expression is @greaterOrEquals(activity('Lookup1').output.count,2)

This checks file along with the header, Sometimes files can also contain header but it could be an empty file with no further rows. The above expressions evaluates row count from 2 or more and that will be treated as non empty file.

For True conditions,

Take a copy Activity,

Source is same dataset given to lookup activity

FileName as parameter, should have @item().name as value

Sink as dataset which points a folder

and copy behavior as Preserve hierarchy

For False conditions,

Just create a Wait activity

Azure Data Factory - Read Files From On Premise File System to Azure Blob Storage - Practical Demo

By Shanmukh Sattiraju

To access one premise file system, we need to setup a Self Hosted Integration runtime.

Default Azure runtime will have only scope up to Azure environment, to access files/resources outside azure we need to configure Self Hosted Integrated Runtime.

Here to stimulate an On premise environment we had a Windows 10 virtual machine and we installed Self Hosted IR in that

Azure Data Factory - copying multiple table from a schema and saving them as files in ADLS

By Shanmukh Sattiraju

Using Lookup activity and forEach activity we can copy multiple SQL tables to multiple files dynamically taking their tablename as file name into Azure Datalake Storage.