03/01/2019
by Marlon Ribunal
1 Comment

Transforming Data With Azure Data Factory Data Flow

Azure Data Factory (ADF) offers a convenient cloud-based platform for orchestrating data from and to on-premise, on-cloud, and hybrid sources and destinations. But it is not a full Extract, Transform, and Load (ETL) tool. For those who are well-versed with SQL Server Integration Services (SSIS), ADF would be the Control Flow portion.

You can scale out your SSIS implementation in Azure. In fact, there are two (2) options to do this: SSIS On-Premise using the SSIS runtime hosted by SQL Server or On Azure using the Azure-SSIS Integration Runtime.

Azure Data Factory is not quite an ETL tool as SSIS is. There is that transformation gap that needs to be filled for ADF to become a true On-Cloud ETL Tool. The second iteration of ADF in V2 is closing the transformation gap with the introduction of Data Flow.

Let’s build and run a Data Flow in Azure Data Factory v2.

Overview

Just to give you an idea of what we’re trying to do in this post, we’re going to load a dataset based on a local, on-premise SQL Server Database, copy that data into Azure SQL Database, and load that data into blob storage in CSV Format.

In this post we discuss the following topics in a step-by-step fashion:

  1. Azure Data Flow
  2. Data Transformation in Azure Data Factory with Data Flow
  3. Data Flow Components:
    • Select
    • Derived Column
    • Aggregate
    • Pivot
    • Sort
    • CSV Sink

First thing first: If this is the first time you’re using Azure Data Factory, you need to create a data factory in Azure, and for the purpose of the demo in this post, you also need to set up a blob storage.

Prerequisite: Copy the Working Data

Create a new pipeline with copy activity. If you follow the instruction from the previous post, Copy Data From On-Premise SQL Server To Azure Database Using Azure Data Factory, that is our first step.

Change the copy activity source and sink as follow:

Source Query:

SELECT c.CustomerName                    
       , p.FullName AS SalesPerson
       , o.OrderDate
       , s.StockItemName
       , l.Quantity
       , l.UnitPrice
 FROM Sales.Orders o
 JOIN Sales.OrderLines l
   ON o.OrderID = l.OrderID
 JOIN Sales.Customers c
   ON o.CustomerID = c.CustomerID
 JOIN Warehouse.StockItems s
   ON l.StockItemID = s.StockItemID
 JOIN Application.People p
   ON p.PersonID = o.SalespersonPersonID ;

Sink (New table)

CREATE TABLE SalesReport ( 
 CustomerName NVARCHAR (100), 
 SalesPerson NVARCHAR (50), 
 OrderDate DATE, 
 StockItemName NVARCHAR (100), 
 Quantity INT, 
 UnitPrice DECIMAL (18, 2)) ;

Create A Data Flow

Navigate to your Azure Data Factory. Click the Author & Monitor tile to open the ADF home page. From there, click on the pencil icon on the left to open the author canvas.

  1. Click on the ellipsis next to Data Flows (which is still in preview as of this writing).
  2. Select Add Dataflow in the context menu.
  3. Let’s name our Data Flow DataFlowTest001.

Add the Data Source

As of this writing, there are only a few supported sources in Data Flow: Azure Data Warehouse, Azure SQL Database, CSV, and Parquet (column-oriented data storage format of Hadoop). Other sources such as Amazon S3, Oracle, ODBC, HTTP, etc. are coming soon.

Let’s set up the Source.

  1. Click on the Add Source tile.
  2. Let’s name our source as DataFlowTest001DataSource.
  3. Next, create a new dataset.

Create New Source Dataset

We need to set up a new dataset. You should have completed the Prerequisite (see above) by now so you will have a working data for this demo.

There are only a few supported data sources as of this writing: Azure SQL Data Warehouse, Azure SQL Database, CSV, and Parquet (column-oriented data storage format for Hadoop).

  1. In the Source Dataset, click New.
  2. Select Azure SQL Database. Click Finish.

Set up the Source Dataset

Set the dataset as follows:

  1. Name the dataset as AzureSQLSalesReport.
  2. Select the Linked Service AzureSqlDatabase.
  3. For the Table, select the SalesReport. Click Finish.

If you are wondering what that Linked Service is, that would be the connection in SSIS. It is easy to set up. At the bottom of the left-hand blade, click on Connection. This gives you the list of your Linked Services and Integration Runtimes. You can also create new Linked Services from this page.

The AzureSqlDatabase linked service was set up as follows:

Add a Select Component

  1. To add a component to the Data Flow, click the Plus sign at the bottom of the DataFlowDataSource component.
  2. In the Row Modifier section of the context menu, select Select.

Set up the Select Component

What the Select component does is just that, selecting columns like you would if execute a Select command.

  1. Name the component as SalesPersonData.
  2. Add the following columns if they aren’t yet added.
    • SalesPerson
    • OrderDate
    • Quantity
    • UnitPrice

You may notice the information on the left-hand side that says, “4 items: 2 column(s) from the inputs left unmapped.” That’s what we want.

You may want to Inspect and Preview the data. But make sure you switch the Debug mode on top before you preview.

Add a Derived Column Component

  1. Name the Derived Column component or stream as DerivedYearOrderDate. Make sure that the incoming stream is the SalesPersonData (Component).
  2. Add a new derived column called Year and set the expression to year(Orderdate)

Add an Aggregate Component

  1. Name the Aggregate component as LineSalesTotal. The incoming stream should be DerivedYearOrderDate.
  2. Click the Group By Column and add the following Columns
    • SalesPerson
    • Year (derived column based on the OrderDate)
  3. Click the Aggregates tab. Add a new column and call it Linesales. Set the expression to sum(multiply(UnitPrice, Quantity))

Add a Pivot Component

  1. Name the component as PivotSalesPersonPerYear. Make sure that the incoming stream is from our aggregate, LineSalesTotal.
  2. Click on the Group By tab. Select SalesPerson. Keep the alias as is or assign a different alias.
  3. Click on the Pivot Key tab. Select Year.
  4. Click on the Pivoted Column tab. Set up as follows:
    • For the Column name pattern, select the first pattern in the dropdown.
    • Select Lateral for Column arrangement.
    • Type [space] Total Sales in the Suffix box.
    • In the expression box, add sum(LineSales)
    • Leave the column prefix empty.

Add a Sort Component

Let’s sort the stream by name, alphabetically sorted ascending.

  1. Name the Sort component as SortSalesPersonByName. Make sure that the incoming stream is PivotSalesPersonPerYear.
  2. On the Sort Condition, select SalesPerson.
  3. Toggle the Order arrow so it is pointing up.

If you preview the data on the Sort component, you should now see something like this:

Add Destination Sink Component

  1. Let’s call the Sink, SalesReportByYearCSV.
  2. I have previously created a CSV dataset on Azure Blob Storage . You can also create a New dataset in your Data Flow. Create, or in our case, select SalesReportCSV dataset from the Sink dataset dropdown.
  3. Navigate to the Settings tab. On the File name option, select Output to single file.
  4. For the File name, type 2013-2016_SalesReportByYear.

Note: The Column Count on the last 3 components don’t look right. Even the mapping is not showing the right number of the streamed columns. The preview shows the expected results, though.

Overview of the Azure Data Factory Data Flow

If you’ve completed all the above steps, you should now have something like this:

Run the Data Flow

Just like in SSIS, you can not run the Data Flow itself in the data flow context. It needs to be in the Control Flow, or Pipeline in ADF, for its execution.

  1. Create a new Pipeline. Name it DataFlowSalesReport.
  2. Navigate to Activities > Move & Transform. Drag the Data Flow activity component to the canvas. That opens the blade on the left-hand side where you can set up the Data Flow that we just created above with the pipeline activity.
  3. In the Adding Data Flow blade, select Use Existing Data Flow.
  4. Select the Existing Data Flow dropdown, DataFlowTest001. Click Finish.

The only setting that I would change in the pipeline setting is the Timeout. Set the pipeline to time out after 30 minutes.

Publish the Changes and Trigger the Activity Now
  1. Click Publish (should be on top, right-hand-side.
  2. Click on the Trigger, then select Trigger Now to execute the pipeline (and essentially the Data Flow).

Here are some of the few ways of checking the status of the pipeline activity processing:

Check the CSV File Output
  1. To check the file, open your Azure Storage Explorer and Navigate to your Blob storage. You can download the Azure Storage Explorer here.
  2. And you should find the CSV output file produced by the Data Flow.

02/21/2019
by Marlon Ribunal
1 Comment

Copy Data From On-Premise SQL Server To Azure Database Using Azure Data Factory

Let’s take a break from our SQL Server 2017 Reporting Services Basics Series and jump to Azure Data Factory (v2). The latest installment on our SSRS Series is about adding a simple parameter to the report, which you can find here.

Azure Data Factory (ADF) is a data integration service for cloud and hybrid environments (which we will demo here). ADF provides a drag-and-drop UI that enables users to create data control flows with pipeline components which consist of activities, linked services, and datasets. of course, there’s an option to set up components manually for more control. More info on ADF can be found here.

ADF is basically a fully-managed Azure service that provides a platform for orchestrating data movement or Extraction, Transformation, and Loading (ETL) of data from sources to target databases or analytics services. If you’re thinking of SSIS, that would be a similar concept.

In this post, we’ll learn how to copy data from our local instance of SQL Server into an Azure SQL Database by using Azure Data Factory (v2).

First thing first, if you do not have access to an Azure Subscription, sign up for a trial account here. Then, create the following services:

  • Azure SQL Database
  • Azure Data Factory (v2)
  • Local Instance of SQL Server 2017
  • WideWorldImporters Database (in your local instance)

Choosing a Pipeline Template

From your Azure Portal, navigate to your Resources and click on your Azure Data Factory. In the ADF blade, click on Author & Monitor button.

That will open a separate tab for the Azure Data Factory UI. For this demo, we’re going to use a template pipeline.

From the Template Gallery, select Copy data from on-premise SQL Server to SQL Azure.

That will open the panel for setting up the pipeline. Since this is the first time we’re copying a local SQL Server into Azure SQL Database on this ADF, we need to set up the connection between our local database and ADF.

Create the Datasource

From the panel, in the Datasource field, click the dropdown and select New. You can also click the link to the documentation for this template which gives you information about capabilities, prerequisites, and other info of this pipeline.

Create A Linked Service

Clicking New as shown above will open another panel for setting up a Linked Service that will facilitate the connection between our local database and the Azure database. Let’s name our SQL Server linked service LocalDBToAzureDB. Add a description.

In the Connect via Integration Runtime, select New.

“But what is Integration Runtime?”, you ask:

The Integration Runtime (IR) is the compute infrastructure used by Azure Factory to provide data integration capabilities across different network environments. Azure Integration Runtime can be used to connect to data stores and compute services in public network with public accessible endpoints. Use a self-hosted Integration Runtime for Private/On-Premise Networks.

Azure Data Factory Integration Runtime Info

In the Integration Runtime Setup panel, select Self-Hosted.

Click Next. Let’s name our runtime LocalDBAzureDBRuntime. Add a description if you want. The runtime Type, Self-Hosted, should already be selected by the system. Click Next again.

The next panel will provide two options to install the run time – Express or Manual. It also provides a couple of authentication keys to use if you want to add more nodes.

Let’s select Express setup. The link will download an executable. Save it to a convenient location. Run the executable.

The installer will download, install, and register all the necessary components for the runtime. It will also install a configuration manager that we can use to set up, diagnose, and update the runtime. When you get the successful installation message, click Close.

Setting up the Linked Service to Local SQL Server

Next, we need to change settings of the runtime. Find and open the Microsoft Integration Runtime Configuration Manager.

Click the Settings tab. On the Status, there’s a link that says Change. Click that. For the purpose of this demo, let’s select the basic option. Of course, you may want to encrypt the service if this is a production server.

Click OK. That will restart the runtime. Wait until it reconnects to the cloud. You can test the connections on both ends – local SQL Server and Azure Database – in the Diagnostics tab.

Go back to the New Linked Service panel to complete the datasource setup. Provide the local Server name and Database name. I have a local sysadmin account (sqladmin) that I used to access the local database. Test the connection.

Click Finish.

Create the Destination Table

Now before we set up the destination Azure SQL Database, let’s connect to the Azure Database via SQL Server Management Studio (SSMS) and create a new table called StockGroupLineProfit.

CREATE TABLE StockGroupLineProfit ( 
       StockGroupName VARCHAR (255)
     , LineProfit FLOAT ) ;

Let’s go back to the template setup and select the DataDestination. I have already set up my Azure SQL Database in advance. Select the Azure SQL Database. Set a new one if you haven’t done so.

Click Use This Template.

In the pipeline designer UI, set the Timeout of the General properties to 30 minutes. That’s enough time for the service to copy the amount of data that we’re going to set in the pipeline.

Set The Pipeline Data Source

Let’s create a new Source. Let’s name it LocalSQLWideWorldImporters. For the Connection, let’s select the Linked Service that we created above.

Let’s go back to the Source setting of our pipeline. Under the Source Dataset, select Query and input the following query.

SELECT sg.StockGroupName
	 , SUM( il.LineProfit ) AS LineProfit
FROM Sales.InvoiceLines il
JOIN Warehouse.StockItems si
  ON il.StockItemID = si.StockItemID
JOIN Warehouse.StockItemStockGroups sis
  ON si.StockItemID = sis.StockItemID
JOIN Warehouse.StockGroups sg
  ON sis.StockGroupID = sg.StockGroupID
GROUP BY sg.StockGroupName ;

If that T-SQL Code is familiar, it is because it’s the same query we used for our SSRS Report here.

Preview the data so we can have an idea of what we are loading to our Azure SQL Database.

In the Sink tab, let’s create a new sink dataset. Click New. Select Azure SQL Database. Click Finish.

Let’s name our sink dataset AzureStockGroup. For the linked service, I am selecting the Azure SQL Database that I have already set up prior. You have to create a new service if you haven’t done so. This is our target or destination Azure SQL Database. For the Table, select the table that we just created via SSMS, StockGroupLineProfit.

In the Schema tab of the sink setup, add the following columns:

  • StockGroupName (of type String)
  • LineProfit (of type Decimal)

The preview of the destination should be empty at this point.

Now that both the data source and destination for the pipeline are set up, go back to the pipeline designer. Select the Mapping tab. The source/destination mapping should now show up; if not, click the Import Schemas button.

Publish the Pipeline

To publish the pipeline and the linked services that we just created, click Publish All button at the top of the designer.

You should get a notification on the status of the published objects.

Run the Pipeline

Let’s click Debug in the pipeline designer. What debug does here is to actual run the pipeline and copy from the data set we defined in our data source and into our destination. Wait until the Succeeded message appears.

And, if we query our Azure SQL Database, we should now have the data in our destination table.

Enjoy orchestrating your data using Azure Data Factory!

02/15/2019
by Marlon Ribunal
2 Comments

SQL Server 2017 Reporting Services: The Basics Part 4

Note: I’ve been meaning to start a series on the basics of SQL Server Reporting Services (SSRS). The last time I did this was on a book on SQL Server 2012 Reporting Services published by Packt. SSRS has since evolved into a better reporting platform with the addition of KPI’s, mobile reports, etc.

We’re now in Part 4 of our SSRS Basics Series, in which we cover simple parameterized report. Before we proceed, if you are a total newbie to SSRS and you’ve never created nor deployed an SSRS report, Part 1 shows you how to install SSRS. Part 2 shows you how to create a simple table report. And, Part 3 shows you how to deploy that report to the SSRS Report Server.

First thing first: Create the following stored procedure. This contains the dataset of our next report.

CREATE PROCEDURE OrderTotalQuery ( @vOrderTotal FLOAT = NULL )
 AS 
 BEGIN
    --Set Default OrderTotal Value
    IF @vOrderTotal IS NULL
       SET @vOrderTotal = 1000 ;
    SELECT c.CustomerName
         , SUM( sol.Quantity * sol.UnitPrice ) AS OrderTotal
    FROM Sales.Orders so
    JOIN sales.OrderLines sol
      ON so.OrderID = sol.OrderID
    JOIN sales.Customers c
      ON so.CustomerID = c.CustomerID
    GROUP BY c.CustomerName
           , sol.OrderID
    HAVING SUM( sol.Quantity * sol.UnitPrice ) >= @vOrderTotal
    ORDER BY OrderTotal DESC ;
 END ;

We have one parameter in this stored procedure, called vOrderTotal. We are assigning it the NULL marker as a default. But let’s aside this NULL for our next post. For now, take note that it’s there and that we will use it later in the next post.

Basically, what this query does is return the Customers with equal to or more than X amount, where X = variable @vOrderTotal.

What a parameter does is it allows us to specify a value on runtime. We don’t need to go back in the database and hard-code the value. But of course, you already know that.

Create the Report

Open the existing project. Go back to Part 2 here if you’re not sure what I am referring to. Create a new report and name it rdl_SSRSTutorial_001_OrderTotalQuery.

Create the Dataset

Create a new dataset under the Shared Dataset in the project. Again, if you’re not sure how to do that, check the Part 2 of this series. Name it ds_SSRSTutorial_001_OrderTotalQuery. For the data source, we use the same Data Source that we previously created, DS_SSRSTutorial_001. We’re not creating a new Data Source because we’re basically connecting to the same database.

Select Stored Procedure for the Query Type. And select the OrderTotalQuery stored procedure that we created above.

Select the Parameters in the pane on the left. You may notice that the parameter of our stored procedure has been automatically detected. Let’s leave it as is for now. We’re going to go back here in the next post.

Click OK.

Parameterized Report

Drag the Table component into the report canvas. Like what we’ve seen in Part 2, dragging a table component into the designer displays the Dataset Properties dialog. Let’s name our report’s dataset as rpt_table_ds_SSRSTutorial_001_OrderTotalQuery. Use Shared Dataset and select the ds_SSRSTutorial_001_OrderTotalQuery dataset.

Again, you may notice that our parameter is detected automatically.

Click OK.

Preview the Report

Let’s preview the report to make sure it’s working. Input 10000 in the Order Total box. Click the View Report button.

You may notice that the parameter is named v Order Total, which is automatically copied from the parameter of our stored procedure. Let’s rename that by removing the letter v.

Rename the Report Parameter

Click the Design tab. Make sure you select the the design tab, or you won’t see the Report Data option in the View menu. Navigate to Menu > View > Report Data or Click CTRL + ALT + D.

In the Report Data pane, navigate to Parameters > vOrderTotal. Right-click the parameter and select the Parameter Properties. Select the General properties if it’s not already selected.

Change the Prompt. Remove the letter v. Click OK.

Build and Deploy the Parameterized Report

Build and deploy both the dataset and report. If you’re not sure how to do that, refer to Part 3 of our series which covers report deployment.

Parameterized Report in Action

Here’s our basic parameterized report in action.

Let’s make our parameter intelligent in the next installment of our SQL Server 2017 Reporting Services Basics Series. Next is about Range Value Parameter.