PASS SQLSaturday is a free training event for professionals who use the Microsoft data platform. These community events offer content across data management, cloud and hybrid architecture, analytics, business intelligence, AI, and more.
My first virtual event
I like to interact during my session, so I'm curious if that will work. Last week I recorded my session for SQL Bits and that is quite strange when you look back. I am really looking forward to it, my session starts at 14:30.
The complete schedule can be found here. Are there is still time to register!!
My Session
Session Title:
Azure Key Vault, Azure Dev Ops and Data Factory how do these Azure Services work perfectly together!
Session Details
Can we store our Connectionstrings or BlobStorageKeys or other Secretvalues somewhere else then in Azure Data Factory(ADF)? Yes you can! You can store these valuable secrets in Azure Key Vault(AKV). But how can we achieve this in ADF? And finally how do we deploy our DataFactories in Azure Dev Ops to Test, Acceptance and Production environments with these Secrets ? Can this be setup dynamically? During this session I will give answers on all of these questions. You will learn how to setup your Azure Key Vault, connect these secrets in ADF and finally deploy these secrets dynamically in Azure Dev Ops. As you can see a lot to talk about during this session.
Data Saturday Stockholm Bra så alla tillbaka personligen This means great so everyone back in person. Last weekend I had the honor to speak at Data Saturday in Stockholm. About one of my favourite topics Azure Synapse Analytics. It was nice to see everyone in person...
Data Toboggan This Saturday I've joined the Data Toboggan to talk about Azure Synapse Analytics. Azure Synapse Analytics Today I've been talking on how to deal with all the different roles in Azure Synapse Analytics during Data Toboggan. An event 100% focussed...
Data, Bratwurst and Beer An event that started 5 years ago as a small event, that has grown into an event with 200 participants, 50+ sessions, 5 tracks, 2 days and a BBQ in a small place in Germany, called Lingen. William Durkin and Ben Weissman are the main...
DataMinds This Tuesday I've joined the DataMinds user Group to talk about Azure Purview. Azure Purview It was the first session of the year for both me and DataMinds. Some great questions during the session, thank you for that. You can find my slides below on...
SQL BITS 2022 What a great time I had Wednesday 9th of March the time had finally come, at 1830 my plane would leave for London City. After all the preparations over the past few weeks, the time was there. After arriving in London City and checking in at the hotel, I...
October=Speaking Month DataSaturday Holland and DataMinds in Belgium Like last year I have been selected again this year for both events to speak. Cool to be able to speak at 3 different events within 7 days, but also to see many known people and to met new people....
SQLBits 2020 SQLBits is the largest Microsoft Data Platform conference in Europe taking place between 29nd September and 3rd October2020 at the Excel London. Proud to be speaking I am very proud and happy that one of my sessions was selected for SQLBits. It's not the...
DataWeekender 4.2 This Saturday I've joined the Van and Spoke at DataWeekender Azure Purview I presented a session on Azure Purview Microsoft's answer to Data Governance and Data Lineage You can find my slides below on Slideshare: Data weekender4.2 azure purview erwin...
Unleashing the Potential of Metadata-Driven ELT Framework in Synapse Analytics and Microsoft Fabric Today, I had the opportunity to speak at the Data Community Austria Day 2024, a full day of sessions dedicated to data enthusiasts and professionals. The event was held...
For my Azure Data Factory solution I wanted to Parameterize properties in my Linked Services. Not all properties are Parameterized by default through the UI. But there's another way to achieve this.
Linked Service
Open your existing Linked Services.
In this situation I want to Parameterize my FTP connection so that I can change the Host name based on a Azure Key Vault Secret.
By default is this not possible through the UI but in the Bottom of your Linked Service there is a Advanced box
If you enable this box you can start building your own connection, but also create your own Parameters for this connection.
How to start:
As a base we will use the default code or our connection
After you have done this, you need to specify for which properties you want to use this parameter. In my case I want to read the parameter form my Azure Key Vault for my HOST propertie.
The JSON code below will now use above parameter as an input.
Save your connection and you will see that your UI is changed and that you have to define all your setting through the Advanced Editor.
If you test your connection you will now see that you have to fill in a parameter.
And now you can create parameters of every TypeProperties within your connection.
The code below will create Parameters for your Host, Username and Password entries with Azure Key Vault enabled. For the authenticationType you have to choose between Basic and Anonymous. But can also at this to your Azure Key Vault.
More and more projects are using Azure Data Factory and Azure Synapse Analytics, the more important it is to apply a correct and standard naming convention. When using standard naming conventions you create recognizable results across different projects, but you also create clarity for your colleagues. In addition to that, it is easier to add these projects to other services such as Managed Services, Azure DevOps, etc etc, because standards are used.
To start with these naming conventions, I have made a list of suggestions with most common Linked Services. The list is not exhaustive, but it does provide guidance for new Linked Services.
There are a few standard naming conventions that apply to all elements in Azure Data Factory and in Azure Synapse Analytics.
*Names are case insensitive (not case sensitive). For that reason I'm only using CAPITALS.
*Maximum number of characters in a table name: 260.
All object names must begin with a letter, number or underscore (_).
Following characters are not allowed: “.”, “+”, “?”, “/”, “<”, ”>”,”*”,”%”,”&”,”:”,””
These rules are also defined on the following link
This post has been updated on Feb 2nd, 2023 with the latest connectors.
If your connector is not described(mostly connectors which are in Preview), please let me know. For more details for all the different connectors, check the connector overview
Pipeline
Even for Pipeline you can define naming conventions. I think the most important thing is that you always start your pipeline with PL_ followed by a Logic Name for you. You can for example use:
TRANS: Pipeline with transformations
SSIS: Pipeline with SSIS Packages
DATA: Pipeline with DataMovements
COPY: Pipeline with Copy Activities
Divers
NB: Notebook
DF: Mapping Dataflows
SQL: SQL Scripts
KQL: KQL Scripts
JOB: Spark job definition
Once again these naming conventions are just suggestions. The most important thing is that you start using naming conventions and that you use the folder structure within the Pipelines (categories). Like the picture below as an example.
If you have suggestions just let me know by leaving a comment below.
Open your Synapse Studio and select the Management Hub.
Add a new Linked Service
If you haven't connect to Power BI before, you will see the screen above. If you want to add another Power BI Linked Service(Workspace).
Search for Power BI in the available Linked Services.
Connect or Select the Power BI Icon.
Select the correct Power BI Workspace Name. You can only selected a Power BI Workspace which is connect to the same Azure tenant as your Azure Synapse Analytics Workspace. So both Services needs to be in the same Azure Tenant.
Once you have connected to your Workspace, you can see all the existing Reports from that Workspace.
Browse in your Synapse Studio to the Develop Tab.
Create a new Power BI dataset
From the same develop tab , you can also create a new dataset for Power BI. Click on the New Power BI dataset.
The following screen will open on the right site of your Synapse Studio.
Make sure you have Power BI Desktop installed. If not you should do in now.
Select a Data Source. You will find here your SQL Pool and if you have created Databases as SQL on Demand as well. How to create a SQL on Demand database I will explain in another article.
Download the created .pbids file.
Open Power BI Desktop. Sign in with the correct credentials
Now you can start building your report. Once you're ready, upload the report to your Workspace and the DataSet and the Report will be visible in your Synapse Studio.
You can now use your report, create new reports or change the reports.
Currently you can not download the report. So once you have changed the report, you have to go back to the Power BI Service to download the pbix file. Same for all other Power BI settings, like scheduling.
In 1 of my next articles I will go deeper into the various possibilities such as import, direct query or the use of Azure Analysis Services in combination with Power BI.
Thanks for reading and like always, feel to free send questions.
There are 2 options to create a SQL Pool. Go to your Synapse Workspace in de Azure Portal and add a new SQL Pool.
Or go to the Management Tab in your Azure Synapse Workspace and add a new Pool.
Creating a new SQL Pool
SQL Pool Name (SQL pool name should not be greater than 15 characters) and select the correct Performance Level. Be careful in selecting the Performance Level. DW 1000c will cost you € 12,73 per hour excluding storage. My advice is to start small and scale up when necessary. More about DWc units can be foundhere.
If you want to use Data from an existing pool, select Backup or Restore Point. Otherwise select none
Use existing data-Backup
Use existing data-Restore Point
Use existing data-None
Review your settings and your SQL Pool will be created.
Remarks
Be aware that if you already have another Azure SQL DW running, you cannot select or add it to your Azure Synapse Workspace. Adding an SQL pool is only possible via the Azure Synapse Workspace(Azure Portal or Studio).
If you install it as a standalone component through the Azure portal, the SQL Pool cannot be added to the Azure Synapse Workspace.
And if you don't use your SQL pool please PAUSE it. It will save you a lot of money.