ADF 2: Create an Azure Data Factory Instance

There a variety of methods that can be used to create an instance of an Azure Data Factory, this blog explores how you can create an ADF instance using the Azure Portal, PowerShell and Azure Resource Manager templates.

Azure Portal

The first step is to create the Azure Data Factory. this can be performed within the Azure portal. Click on the New icon, point to Databases and then click Data Factory.


At this point the provisioning blade for ADF appears. It is in this blade that you define a name for the Data Factory instance. You then assign the instance to a subscription that you own.

The resource group enables you to define whether your ADF instance will reside in a Resource Group that already exists, or in a resource Group that you create. Resource groups are important in that they contain services that can have the billing to be visible on that container, you can define access control on a resource group, and resources held within that container to be able to communicate with each other without the need to write complex IaaS scripts to further define the communication between services.

The resource group can include all the resources for the solution, or only those resources that you want to manage as a group. You decide how you want to allocate resources to resource groups based on what makes the most sense for your organization. Therefore it is important to understand the objective for creating a resource group and plan them appropriately.

Finally, you will then assign the Data Factory instance to a region of your choice.


You can view a video here on how to setup an ADF instance in the Azure Portal.


You also have the ability to deploy Azure Data Factory instances using PowerShell. This requires that Azure PowerShell is installed on your computer. Once this is set up you can use the following PowerShell commands to create a Data Factory instance. The following commands set the Azure context to a subscription and then defines a Data Factory instance defining the resource group where the instance is hosted, followed by the name and the location.


You can view 3 minute this video on how to create an ADF instance here.

Azure Resource Manager templates

When dealing with ADF in production scenarios, or dealing with multiple ADF instances you can also make use of Azure Resource Manager (ARM) templates to ensure the deployment of multiple instances of ADF is much easier. ARM templates are JavaScript Object Notation (JSON) files that defines one or more resources to deploy to a resource group. It also defines the dependencies between the deployed resources. The JSON files can be parameterize to provide the flexibility to deploy ADF instances of different names, in different resource groups and regions.

There is an excellent article from the product group that will enable you to use ARM templates in a variety of Data Factory scenarios. I would highly recommend that you read this.

With the Data Factory instance created, it is then time to create the relevant linked services, data sets and pipelines to perform the data orchestration activities. These will be covered off individually in other blog posts.


ADF 1: Orchestrating data movement in the Cortana Intelligence Suite.

The need for batch movement of data on a regular time schedule is a requirement for most analytics solutions. Within the Cortana Intelligence Suite, Azure Data Factory (ADF) is the service that can be used to fulfil such a requirement.

ADF provides a cloud-based data integration service that orchestrates the movement and transformation of data from various data stores.

So what do we mean by orchestration?

Think about an orchestra. The most important member of the orchestra is the conductor. The conductor does not play the instruments, they simply lead the symphony members through the entire piece of music that they perform. The musicians use their own skills to produce particular sounds at various stages of the symphony, so they may only learn certain parts of the music. The conductor orchestrates the entire piece of music, and therefore is aware of the entire score that is being performed. They will also use specific arm movements that provide instructions to the musicians how a piece of music should be played.

ADF uses a similar approach, it will not perform the actual work required to transform data, but will instruct another service, such a Hadoop Cluster to perform a Hive query to perform the transformation on ADF’s behalf. So in this case, it would be Hadoop that performs the work, not ADF. ADF merely orchestrates the execution of the Hive query through Hadoop, and then provides the pipelines to move the data onto the next destination.

It also provides rich visualizations to display the lineage and dependencies between your data pipelines, and monitor all your data pipelines from a single unified view to easily pinpoint issues and setup monitoring alerts.

The Azure Data Factory Process

The process for Azure Data Factory can be summarized by the following graphic.


Data Factory supports a wide variety of data sources that you can connect to through the creation of an object known as a Linked Service. This enables you to ingest the data from a data source in readiness to prepare the data for transformation and/or analysis. In addition, Linked Services can fire up compute services on demand. For example, you may have a requirement to start an on demand Hadoop cluster for the purpose of just processing data through a Hive query. So Linked Services enables you to define data sources, or compute resource that are required to ingest and prepare data.

With the linked service defined, Azure Data Factory is made aware of the datasets that it should use through the creation of a Datasets object. Datasets represent data structures within the data store that is being referenced by the Linked Service object. Datasets can also be used by an ADF process know as an Activity.

Activities typically contain the transformation logic or the analysis commands of the Azure Data Factory’s work. This could include the execution of a stored procedure, Hive Query or Pig script to transform the data. You can use U-SQL with Data Lake Analytics or push data into a Machine Learning model to perform analysis. It is not uncommon for multiple activities to take place that may include transforming data using a SQL stored procedure and then performing Data Lake Analytics with USQL. In this case, multiple activities can be logically grouped together with an object referred to as a Pipeline.

Once all the work is complete you can then use Data Factory to publish the final dataset to another linked service that can then be consumed by technologies such as Power BI or Machine Learning.

Therefore the process discussed above can be summarized by the creation of the following objects as shown in the graphic.


Use cases

But I can move data with other tools right? Absolutely. Data movement could occur for example using SSIS to load data from SQL Server to Azure DW. However, if you have seen the video on data loading in Azure DW you will be aware that SSIS is not the ideal tool to use to load data into Azure SQL DW if performance of the data loads is the key object. PolyBase is, and we can use Azure Data Factory to orchestrate the PolyBase execution to load data into SQL Data Warehouse.

The other use case is if you want to call on demand services such as Hadoop clusters. ADF can be used to create an on demand cluster when it is required, and to shut it down once your work is complete. In which case you can control the usage of the Azure resources to process your data, and only pay for what you use.

We can also use ADF to push data into Machine Learning models, this is particularly useful once a model has been productionized and you want to automate the process of feeding data to the Machine Learning model to perform its analytics. ADF can perform this activity with ease.

These examples, along with others will be explored in future blogs. In the meantime you can watch this 3 minute video that summarizes the capability of Azure Data Factory





Why use the Cortana Intelligence Suite?

“We want a real time data warehouse” said the IT Director.

“OK, so at the moment your doing a daily refresh of the warehouse and the cubes, right?” I replied.

“That’s right, but we have always wanted real time” was the reply.

“Well this is possible, but there would have to be a lot of investments made. First you would have to upgrade the disks from the 10K RPM that you have and have a combination of at least 15K RPM for the warm data and SSD’s for the hot data. Also, I think that the throughput on the backplane on the SAN is too slow, we need to get them…..”

“Hold on a minute!” interrupted the IT Director “I was hoping we can make use of the kit we have.”

“OK, but to get real time, we need to have the supporting hardware to meet that need, and if your not willing to make the investments, I can’t see how it could be achieved. Lets start again. What does real time really mean for your business….”

And after a couple of hours…

“OK, so you agree that at best, with the infrastructure we have in place, we are looking at a half day refresh of the cubes?” I conclude.

“Yes, but if you can sprinkle some of your magic and get that down, that would be great!”

Sound familiar?

Let’s consider another conversation

“We have a range of data source, name relational, but we do have some spreadsheets and XML files that we want to incorporate within the solution.” said the BI developer.”Oh, and Delia in procurement has an access database with information that we use as well”

“Sure, that wont be a problem. We can extract those files and place them in the staging area using SSIS.”

“We have video in the stores that record activity near the tills.  We have always wanted to be able to store this data. At the moment they are on a tape and they are overwritten every week. We have had instances where we have had to review the videos from over a month old. Could we accommodate that?” States the Solution Architect.

“Its possible, but the impact of the size and nature of the data would mean that there would be a performance impact on the solution. Is it a mandatory requirement….”

Again. Does this sound familiar?

In many of the Business Intelligence projects I have undertaken, there is usually an area of compromise. The most common aspect is with real time or near real time data warehousing. The desire for this outcome is there. This is not a surprise as the velocity with which a business requires their information can impact the operational reports that can help to make decisions. But once there is a realisation of the capital and operational costs to implement a solution that is even near real time, the plans are abandoned due to complexity and cost, or the business will decide to pull “reports” from the OLTP system to meet their needs. Which we all know will place contention on the OLTP system.

The same decisions can also occur when it come to the types of data being sourced for a solution. Many project can handle the data that is required for a BI project, but increasingly, there is a thirst for a greater variety of data to become part of the overall solution. Traditional BI solutions can struggle to make chronological use of media files, and even at a simple level, other files types may be abandoned. And its not just the unstructured types of data that are not used. The sheer volume of the data sizes can be overwhelming for an organisations infrastructure.

This is where the Cortana Intelligence Suite can help. Notice that the keys variables for compromise in the above scenarios involve speed, data types and size. Put another way, some potential blockers to solution adoption can be because the infrastructure cannot handle the velocity, variety and/or volume of the data. These are the common three tenants that define the characteristics of a Big Data solution. And the Cortana Intelligence Suite has been engineered to specifically accommodate these scenarios, whilst at the same time still providing the ability to work with relational data stores.

So which technologies can help in these scenarios


A couple of options spring to mind. You could make use of the Azure Streaming Analytics service to deal with real time analytical scenarios. Stream Analytics processes ingested events in real-time, comparing multiple streams or comparing streams with historical values and models. It detects anomalies, transforms incoming data, triggers an alert when a specific error or condition appears in the stream, and displays this real-time data in your dashboard. Stream Analytics is integrated out-of-the-box with Azure Event Hubs to ingest millions of events per second. Event driven data could include sensor data from IoT devices.

A second option can include using Storm Cluster to process real time data. Storm is a distributed real-time computation system for processing large volumes of high-velocity data. Storm is extremely fast, with the ability to process over a million records per second per node on a cluster of modest size.


A range of technologies can be used to handle volume. Azure SQL Data Warehouse can provide storage for TB of relational data that can integrate with unstructured data using PolyBase.

HDInsight is an Apache Hadoop implementation that is globally distributed. It’s a service that allows you to easily build a Hadoop cluster in minutes when you need it, and tear it down after you run your MapReduce jobs to process the data. It can handle huge volumes of semi and unstructured data. There are variation of different types of Hadoop clusters. We have seen two examples here with Storm and HDInsight. I will write another blog about the differences in another post.


A number of the CIS technologies can handle a wide range of data types. For example you can use a CTAS statement to point to a semi structured file to be decomposed before storing in Azure Data Warehouse. Azure Data Factory can connect to a wide range of data types to be extracted. Azure Data Lake can act as repository for storing the data.

The Cortana Intelligence Suite provides a wide range of technologies that can deal with the barriers to complete BI solutions in the past. As the infrastructure is hosted in Azure, these blockers can now be removed with consideration primarily being given to the operational expenditure required to implement a solution, and less on the capital expenditure. So the IT Director may be more inclined to look into the potential of a real time solution.

So next time you are around a table talking about your latest BI project. Remember that the Cortana Intelligence Suite has the capabilities to deal with velocity, volume and variety, take a look at this interview I did in Holland for a summary.



And now, for our “Features” presentation.

In my last blog, I discussed how members of my team helped me to understand how the techniques that I used in data migrations and data warehousing could contribute immensely to the world of Data Science. Namely through the discipline of Features Engineering. Before you read this blog, it would first be well worth watching Buck Woody’s short video on an Introduction to the Cortana Intelligence Suite (CIS) to give you some context. I will see you back here in 20 minutes……

…. Ok, so in Buck’s presentation he talks about the CRISP-DM process and how it maps to the Cortana Intelligence Suite of technologies. In the Data Preparation part of the process, he explains how we can generate Feature and Labels, and how these can be consumed within Machine Learning models, R or Python to perform predictive or pre-emptive analytics.

Feature selection refers to the process of selecting the input data for processing and analysis, or of finding the most meaningful inputs. A related term, feature engineering (or feature extraction), refers to the process of extracting useful information or features from existing data.

Hold on a minute! Don’t we do this already as SQL Server professionals? Well, we do. If you have worked on a BI or data warehouse project, or perhaps you have done a data migration, one of the most important aspects of these project is to identify the source data that we require to migrate to the new system or to a data warehouse. During the load we are highly likely to cleanse and transform that data to make it useful for our destination.

Within Data Science, it is widely regarded that Features Engineering is an important aspect of the field. The Data Scientists who I work with agree that it is just as, if not more important than, choosing the right model to use in your machine learning experiments.

It seems to be a subject that is talked about very little in the Data Science world, but it is a discipline that can utilise many of the skills that a SQL Server Professional possesses. Don’t get me wrong, there are still plenty of new skills to be learned in this process and in using the Cortana Intelligence Suite, but it is worth knowing that the skills that we currently have will be useful in this arena.

So now I’m really starting to feel like I am part of a Data Science team. It’s a bit like a team that performs cardiovascular surgery. OK, so I may not be the surgeon, or the anaesthetist, but as the scrub nurse it’s important that I pass on the right tools for the job and that those tools are clean. And my role is seen as just as important as the surgeon who ties the knots. In fact, Ryan Swanstrom has written a great post on Data Science and the Perfect Team.

The first task in the data preparation phase is to extract the features from the source data. At this point you may cleanse it, transform it or indeed augment the data with additional data. In the past SQL Server Integration Services (SSIS) would have been the tool of choice to do this. And you can still use SSIS, however you have the ability to extract and perform data manipulations with the following technologies in the Cortana Intelligence Suite.

  • Azure Data Factory – a service that orchestrates data movement and cleansin in Azure
  • R – an open source language that can perform data manipulation and statistical analysis
  • Azure Streaming Analytics – a service that simplifies complex event processing using standard Transact-SQL statement to manipulate datasets.

I will go through these tools in future blogs, and I know R isn’t strictly in the CIS, but as you become more familiar with the language you will find that it can be integrated with many of the CIS technologies that are available, to great effect.

And for completeness. Labels are the attributes that you are trying to predict in a model. So Features Engineering plays a particularly important role in ensuring that your features are primed as best as they can given the business understanding of the data. But remember, like Buck states in his video, you may not even do any modelling with the features that you select, and just push the features to a Power BI Dashboard for descriptive analytics.

So what we have established in this blog is that we already have some of the skillsets as  SQL Server professionals that can contribute to data science through the field of Features Engineering. Its time to get the popcorn and look into this a bit more.