How to dynamically copy specific tables from source systems with Azure data factory

When you are working on a data project, you often have a list of tables you want to copy to your DWH database or staging database. With Azure data factory(ADF), this is easy to set up(Kind of). In this blog post, I will show you how. The result will end up looking something like the image below.

The scenario. I have a source system called WideWorldImportersand a DWH database called DWH_DEV. After talking with the business and the developers, I have a list of tables I want to copy to my DWH. 

In my test scenario, I have the databases on the same database server as shown below, but in real life, that is not the case, but the development process is the same. 

All my work I will do in the DWH_DEV database. First, I want a schema to the sourcesystem. Like so.

CREATE SCHEMA WideWorldImporters

Then I want a table with the tables I want to copy, and information about where to copy them. Like this

CREATE TABLE [Framework].[TablesToBeCopied](
	[SourceSystem] [NVARCHAR](250) NULL,		--The sourcesystem. We can have multiple
	[SourceSchema] [NVARCHAR](250) NULL,		--The Source schema, if it has one
	[SourceTable] [NVARCHAR](250) NULL,			--The Source table 
	[DestinationSchema] [NVARCHAR](250) NULL,	--The Destination Schema in your DWH
	[DestinationTable] [NVARCHAR](250) NULL		--The Destination table in your DWH
)

I want to add the 2 tables [Sales].[Invoices] and [Sales].[Customers]. So my TablesToBeCopied table will look like this: 

When I do a bulk load from different source systems, I want to drop the tables and recreate them again when I copy them. The reason for that is, that I don’t want to worry about remapping columns, if the change datatypes or are renamed. This is maybe not what you need, and then you can just skip this step. But I always create a stored procedure for that, like so

-- =============================================
-- Author:		CKC, DataWise
-- Create date: 20230719
-- Description:	Drop all tables in a schema. 
--				Primarly used for extract tables
-- Parameters
--				@Schema - The name of the schema we want to truncate. 
--				It can not be the dbo schema. 
-- Change history
-- 
--				20230719 CKC, DataWise
--					First version

BEGIN
    /*Declare required variables*/
    DECLARE @SQLStatement NVARCHAR(MAX);

    IF @Schema = 'dbo'
    BEGIN
        RAISERROR('This procedure should not be used on the schema dbo', 16, 1);
    END;

    IF @Schema <> 'dbo'
    BEGIN
        /* BUILD TABLE TRUNCATION SQL*/
        SELECT @SQLStatement =
        (
            SELECT STUFF(
                   (
                       SELECT '; ' + 'Drop TABLE ' + [SchemaName] + '.' + [TableName] AS [text()]
                       FROM
                       (
                           SELECT SCHEMA_NAME(schema_id) AS [SchemaName],
                                  name AS [TableName]
                           FROM sys.tables
                           WHERE schema_id = SCHEMA_ID(@Schema)
                       ) x
                       FOR XML PATH('')
                   ),
                   1,
                   1,
                   ''
                        ) + ';'
        );
        /*EXECUTE TRUNCATION SQL*/
        EXEC sp_executesql @stmt = @SQLStatement;
    END;
END;
GO

Now we are ready to setup ADF

We create a new pipeline and name it something meaningful, like pipelineCopyDynamicFromWorldWide

We start by adding a script task to the pipeline, that will drop the table. We add this SQL to it

EXEC [Framework].[dropTablesBySchema]
		@Schema = N'WideWorldImporters'

After that, we add a lookup task, to get the tables we want to copy.

In the lookup task we add this SQL

SELECT [SourceSystem]
      ,[SourceSchema]
      ,[SourceTable]
      ,[DestinationSchema]
      ,[DestinationTable]
  FROM [DWH_DEV].[Framework].[TablesToBeCopied]
  WHERE SourceSystem='WideWorldImporters'

The pipeline now look like this 

We now want to iterate over the tables with the forreach iteratior. 

We drag it in, and connect to it. We click the settings, and click add dynamic content like below

Then click the get tables to copy like below

Now we want to add an activity in our foreach loop. To do that, click the add activity plus sign, and select the copy data activity like so

Then we setup the copy data activity. First we add the source. Start by connection to a table in your world wide importers database. When you are done, go to the dataset that is created. Click on the parameter tab, and add 2 like this

Then click the connection again, and click the edit button, and dynamiccontent and add the table and schema like so

Do the same with the destination dataset. 

Go to the copy activity again, and setup the source and the sink. The setup should look like this: 

Where you dynamically set the schema and table properties like shown. 

Do the same with the sink(Destination, but use the destination names from your SQL instead). 

Remember to clik the auto create table 

We can now run the pipeline. We should get a result like so

And you should see the table in your DWH like this

As with most in ADF, setting up is a little complicated. But when you are up and running, it is easy to add the table you want in your daily copy job, and it is very easy to maintain. 

You add multiple source systems in your table with the tables you want to copy. I normally create a pipeline for each source system. 

But this is it. I hope you can use it 🙂

Guide: Setup data factory to run locally

Azure Data Factory (ADF) is Microsofts cloud based ETL tool. It is in many ways, better than their old on-premise ETL tool “integration services”. Luckily, you can easily set up ADF to move data around on-premise. In this blog post, I will show you how 🙂

I will assume you already have an ADF running, and some SQL servers running on-premise (Or on your laptop, if you just want to do it, to test ADF features).

To get started, go to your ADF and

  1. Click the manage menu on the left, click integration runtimes
  2. Click New
  3. Click On, Azure self-hosted

As shown below.

After, click new, give it a good name, and click create.

You will get 2 keys, as shown above(1). Save them a secure place, and click the link with the express setup(2).

When you do the manual setup, you will need the keys. If you just do the express, you don’t need them. After installation, you should see something like this.

We are now ready to use the on-premise resources.

I have a local SQL server with different test databases on it. See below

From ADF I want to copy a table from adventure works to the DWH_DEV database.

To do that, I create a pipeline, and

  1. Drag the copy data task in
  2. Under source, I click new dataset
  3. I click new linked service

And fill in the connection information

Click test, and select the table you want to copy.

Do the same for the destination database and table.

Run the pipeline. You should see a confirmation as below.

You can now copy data with ADF between your databases on-prem, and do all the transformation that is possible i ADF.

You should also be able to do all your testing with ADF as you want, and you dont have to spin up expensive Azure sql servers for all the test databases you want.

I hope this was helpfull 🙂 Enjoy 🙂

Refresh a Power BI dataset from data factory

When I have my data model in my Power BI dataset, there are several reasons, I want to update it when I am done with my ETL. Among others:

  • I don’t know how long the ETL process will take over time, and I want to update the dataset as soon as it is done
  • I want to scale down the database after the power BI dataset updates

So in this blog post, I will show how to do it from data factory (ADF).

It is a lot of steps, and I will assume you have access to your Azure portal, already have created a data factory, and have admin access to your Power BI portal.

Step 1 – The Power BI rest API

To do it, we will use the Power BI rest API. The documentation for how to use the update is here:

https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/refresh-dataset-in-group

where you can see it is a post request like this

Where groupId is the workspace ID, and datasetId is the dataset ID.

To find the workspace ID, go to the workspace in Power BI, and check the URL

It is the guid after the group folder.

Click on the dataset you want to update. The dataset id is the guid after the dataset folder.

Step 2 – Allow ADF to update a Power BI dataset

To allow ADF access to update the dataset is kind of tricky, and we have to use the Azure Active Directory. Go to the Azure portal, and click it. It will look something like this

When you click it, you will see something like this.

Go to the groups as marked in the image above.

Click new group, and create a new group, where the group type is security. Give it a good name 🙂 In my example I use PowerBISP. When it is created, click the members, and click add members, and search for the name of your already created data factory, as shown below. The name of my ADF is CKCProdDF.

Step 3 – Setup Power BI

For this to work, we also need to do some setup in Power BI. First, we need to change a tenant setting. Go to the admin portal by clicking the menus shown below:

Scroll down to developer settings, and add your newly created security group. It should look something like shown below:

The last thing to do in Power BI, is to give the same security group admin access to the workspace where you want to update the dataset.

Step 4 – Create the pipeline

And now, we can finally go to ADF and create our pipeline. In ADF, create the pipeline, and drag in the web component, like shown below.

Check the secure input and output boxes.

Go to settings. It should look something like this

  • The URL is the URL from step 1
  • Method is POST
  • Body we don’t need.
  • Authentication is System Assigned Managed Identity.
  • The Resource is just https://analysis.windows.net/powerbi/api

And now, everything should work. Go ahead and validate and run your pipeline.

When the pipeline is done, go to the Power BI dataset refresh history, and have a look.

You should see a successful refresh, with the type “Via Api” as above.

All Done!!!! 🙂

Conclusion

This is kind of complicated to set up. But when it is done, it is nice you can update all the dataset quite easily, by using the same component in ADF. And all you have to change is the 2 guids from step 1.

I hope you will find this useful 🙂

How to scale your azure sql database with SQL

An Azure sql database is super easy and cheap to setup and use. However, if you want to do some serious data manipulation, you will find the cheapest versions are to slow to use, and every upgrade you make on the sql database, is a doubling in price. If you are a data engineer, like me, most of your data work will be in batches, so it will also be annoying to pay for an expensive version, if you only use it for 1 hour/day.

Luckily, there is a solution. You can change the performance / version of the SQL server with SQL.

To check which version you are running, use this SQL

SELECT     
  slo.edition,    
  slo.service_objective    
FROM sys.database_service_objectives slo   

And you will get a result like this

You can change the version with this SQL

ALTER DATABASE DSA MODIFY(EDITION='Standard' , SERVICE_OBJECTIVE='S4')

Unfortunately, the change is not instant. My experience is, that on the standard version, the latency is around 5-10 minuttes.

What i normally do, is that I have a job that starts 30 minutes before my batch job, that update the database to a higher version. Then it is updated when the batch starts. And I set the batch job to downscale the database in the end.

For pricing and DTUs, see the screenshot below.

Pricing for all versions can be found here: https://azure.microsoft.com/en-us/pricing/details/azure-sql-database/single/

Have fun 🙂

How to create sql azure login and user

I use sql azure databases a lot. Both as backend, datamarts and also for data exchange layer with 3. parties.

For that, I need to create users fast and easy. There is no interface for doing that in SQL azure, so you have to do it with SQL.

Step 1: Connect to sql with manegement studio or sql Azure data studio with an admin account, and make sure you are connected to the master database.

Step 2: Create the login like so

CREATE LOGIN Testuser 
   WITH PASSWORD = 'complexpassword' 

You can now see the user

Step 3: Switch to the database where you want the user to have access and run this code:

CREATE USER Testuser
    FOR LOGIN Testuser

Step 4: Give the access you want the user to have. For instance reader:

EXEC sp_addrolemember N'db_datareader', Testuser

And you can now see the new user in the database under users

The default roles you can give to the users can be found here: https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver16

And seen below:

How to: Change the length of an attribute in MS MDS

You would think that it would be easy to change the length of an attribute in MDS, but going to the webinterface might make you think otherwise.  

The length parameter is greyed out. But fear not.

Instead open the table with your excel plugin


Click on the column you want to change, click attribute properties, and change the length to the desired length.

Publish your changes, and go back to the webinterface.


Tada 🙂 The length has been changed 🙂

A quick look at System-Versioned Tables in SQL server 2016

SQL server 2016 has been available for preview for some time now. One of the more exiting features I am looking forward to is system versioned tables.

As a DWH developer, one of the things, you spent a lot of time with, is developing dimension tables with columns that stores history. The history is typically in the form of Type1 or Type2, where Type1 is no history, and Type2 is where you store the history, by adding another row, when a value changes for a given natural key in the source system.
Type2 history is normally developed by using

  • A DWH framework
  • 3rd Party component
  • Developed specific for the dimension that needs the Type2 history

In version 2016, SQL server can help you with this, using system-versioned tables.

Right click tables->new-> And then you see a new option called “System-versioned Table”

There is no designer for it yet, so we will do it in SQL, which look like this.

CREATE TABLE dbo.Product
(

SK_Product int IDENTITY(1,1) Not Null,
BK_productid INT NOT NULL
CONSTRAINT PK_product PRIMARY KEY NONCLUSTERED,
ProductName NVARCHAR(50) NOT NULL,
ProductCategory NVARCHAR(50) NOT NULL,

sysstart DATETIME2(0) GENERATED ALWAYS AS ROW START NOT NULL,
sysend DATETIME2(0) GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (sysstart, sysend)
)
WITH ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo. ProductHistory ) );

Where

  • SK_Product is the surrogate key
  • BK_productid is the natural key
  • ProductName and ProductCategory are collumns we want to track history on(Well, preferably only ProductCategory, but this is not on column level yet)
  • Sysstart is the valid from
  • Sysend is the valid to
  • And PERIOD indicates which collumns to be used for valid from and to.

Required columns are of course a primary key, Sysstart, Sysend and PERIOD.
Below a screenshot of what it looks like in management studio.

As you can see, the producthistory table is placed under the product table, to show the relationship.
Let us try to insert some values:

INSERT INTO [dbo].[Product]
([BK_productid]
,[ProductName]
,[ProductCategory])
VALUES
(1,'Product1','Category1')

So, the insert, only insert a row in the current table, and no rows in the history table.
Let us try with an update:

UPDATE Product
SET ProductCategory = 'CategoryUpdated'
WHERE BK_productid=1

So, the update, updates the current table, and insert a row in the history table, hence, we will always have the current value in the “current table”, and old values in the history table.

Conclusion

When would you use this feature?
It could be helpful in an OLTP system, where they know they will need the history for reporting in the future, but does not have a DWH yet, to store the history.

There will be an overhead in performance and storage.

Would we use it in a DWH, for a dimension with type2 history?

Properly not. This is one specific implementation of it, and we cant change anything to the implementation, so we would have to use it in combination with our specific needs, and then it would be easier not to use it at all.

This could be useful in an archive layer in a DWH, where you want to store all changes of the extracted values over time.

This blog post is based upon a CTP version of SQL server, so much could still change.
Read more about it here: https://msdn.microsoft.com/en-us/library/dn935015.aspx

Doing Windows development on my android tablet with Azure

The scenario: So, I’m on the run, to a family party, a boring meeting, or something similar 🙂 Suddenly, I get this great idea for the BI project I’m working on. I didn’t bring my monster laptop, because it is to heavy & ugly. Instead I’m sitting with my android tablet. What to do now??? 🙂 A solution I have started to use, is the Azure virtual machines. You can create your own on the azure portal, and you can even get them with the software you need preinstalled. I won’t describe the procedure here, because it is simple and out the box. One of the new good things, is that you only pay for it, when it is online, so remember to shut it down afterwards. So how to do it? It’s described in the pics below:

My setup, an android tablet, with bluetooth keyboard

Navigate to the azure portal, and start your VM

From the portal, you can download the connection file, so you don’t have to remember the connection information. I keep it saved in dropbox. When I click it, the MS remote desktop connect to the VM.

And now I’m connected. Windows 2012 R2 on my android tablet with Metro style.

Searching the apps

Running datatools. I could connect to TFS in the cloud, and get alle the sourcecode, and start doing some work.

Querying a SQL database, potentially fixing a bug 🙂

All done! Remember to stop the VM, when you are done.

Conclusion: For me, the best way to develop, is using a powerfull laptop, connected to a good keyboard, mouse, and some big screens. However, if you need to do something fast, it is possible to do some work, just using your android tablet and an Azure VM.

GOTO day 3 – Keynote: “How to become accomplished”

Seriousely, the keynotes at GOTO are really good this year. Today was by Chad Fowler. It was about, as the title says, how to become accomplished. To me, Chad Fowler is like a motivational speaker for programmers, and he is really really good. 

In short, you are going to spend a lot of your life working, so why not do it good, love the process, accomplish some stuff on the way and be successful. Chad Fowler knows how to do that, he has an incredibly life story.

I really cant recap what the talk was about. Just go buy his book. You will not regret it.

http://www.amazon.com/The-Passionate-Programmer-Remarkable-Development/dp/1934356344/ref=sr_1_1?ie=UTF8&qid=1380707932&sr=8-1&keywords=the+passionate+programmer

If you are in to motivationel stuff, you may know that life is the answer to the question we give our self. Chad Fowler had some question, you should ask yourself. I cant remember them precisely, but here is what I kind of remember 🙂

Are You Better Than Yesterday?

-> If not, then just be better 🙂

It is “Hell yeah” or no.

-> If it is not hell yeah, then it is no.

Ask Why you are doing what you are doing once a day.

There were some other good take aways, but I can’t remember them now. Just buy the book. But last take away.

Surround yourself with people who are better than yourself

Funday Monday: Day 1 at GOTO

So today was day 1 at the GOTO conference. Today none of the tracks related directly to what I work with daily, so I think I was around almost all the tracks for different sessions.

Solving Real and Big (Data) Problems Using Hadoop
So the first session actually was the only one, that related a litle to my work. I think approximately, once a year, a new technology is born in business intelligence land, which promise that it can replace the old enterprise data warehouse. This year it is Hadoop 🙂

I can understand why salespeople want to get that message out, because it is a good business case, since the development of an enterprise data warehouse can be expensive, and if you can replace it with something that is almost free, hey, why not.

But it is not going to happen, for many obvious reasons I don’t want elaborate on in this post.
However, Hadoop is really cool, and it can enrich many companies BI & DWH solutions(Not replace them).

I have seen a lot of presentations at different conferences and user groups about Hadoop, but the presentation by Eva Andreasson from Cloudera was the best so far, and she almost convinced me to buy the book “Definitive Guide to Hadoop” in the Factum bookstore.

Microsoft Devices and Services
As an old .Net developer, it is allways exciting to see what MS is up to. I attended 2 session from MS. “Windows Phone app development” and “Windows Azure Mobile Services”. One thing that amazes me, is how fast it is to develop to windows phone and all the azure things.

Reliving the history of multiplayer games
I also went to the session “Reliving the history of multiplayer games”. I don’t do game development myself, but hey, I do a lot of gamming. It was fun to see the presenters be so excited about games. My key take away: I want to work on something that relate to computer games in the near future 🙂

Taking The Hippie Bus To The Enterprise
My last session of the day. Again, not something I work with directly, but it is always cool to catch up with the trends in .Net land. A lot of code examples and overall a great presentation by Microsoft MVP Mogens from d60.

All in all, an exciting day.