Azure Synapse Analytics Power BI Integration

Azure Synapse Analytics Power BI Integration

by | Jun 24, 2020 | Azure, Azure Analyis Services, Azure Synapse Analytics, Power BI

Creating a Linked Service for Power BI

Open your Synapse Studio and select the Management Hub.

Azure Synapse PowerBI Linked Service

Add a new Linked Service

Create Synapse Studio PowerBI Connection

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.

Azure Synapse PowerBI Linked Service Existing

New Linked Service Power BI

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.

Azure Synapse PowerBI Create New DataSets

The following screen will open on the right site of your Synapse Studio.

Azure Synapse PowerBI Create DataSet

Make sure you have Power BI Desktop installed. If not you should do in now.

Azure Synapse PowerBI Server

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.

Power BI Integration

Open Power BI Desktop. Sign in with the correct credentials

Azure Synapse DataBase On-Demand

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.

Power BI report Integration

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.

 

Create an Azure Synapse Analytics SQL Pool

Create an Azure Synapse Analytics SQL Pool

by | Jun 17, 2020 | Azure, Azure Synapse Analytics

Azure Synapse Analytics

Adding a new SQL Pool

There are 2 options to create a SQL Pool. Go to your Synapse Workspace in de Azure Portal and add a new SQL Pool.

Create SQL Pool

Or go to the Management Tab in your Azure Synapse Workspace and add a new Pool.

SQL Pool Azure Synapse Studio

Creating a new SQL Pool

Create Azure Synapse 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 found here.

Additional settingsAzure Synapse SQL Pool

If you want to use Data from an existing pool, select Backup or Restore Point. Otherwise select none

Use existing data-Backup

SQL Pool Backup

Use existing data-Restore Point

SQL Pool Restore Point 

Use existing data-None

Review Azure Synapse SQL Pool

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.

Pause SQL Pool

And if you don’t use your SQL pool please PAUSE it. It will save you a lot of money.

How to create a Azure Synapse Analytics Workspace

How to create a Azure Synapse Analytics Workspace

by | Jun 15, 2020 | Azure, Azure Synapse Analytics

Creating your Azure Synapse Analytics Workspace

In the article below I would like to take you through,  how you can configure an Azure Synapse Workspace and not the already existing Azure Synapse Analytics SQL Pool(formerly Azure SQL DW):

In de Azure Portal search for Azure Synapse Analytics. Make sure you select Workspaces Preview.

Select Azure Synpase Analytics Workspace

 

Create Azure Synpase Analytics Workspace

Click on Create to start the configuration of your Workspace.

Configure Azure Synpase Analytics Workspace

Make sure you select the correct:

Subscription

Resource Group

The seconds part of this configuration is to setup your Workspace:

Workspace Name I’m using <customername><environment><wsas><department>  wsas=WorkSpace Azure Synapse prevwsdvlmwsasoxgn01
Region The desired Region <West Europe>
Data Lake Storage Gen 2 Select an existing Data Lake Storage Gen 2
Data Lake Storage Gen 2 File System I’m creating a new container here temp. This directory is used to store temporary files and workspace settings and I don’t want to mix this temporary data in 1 of my other containers.

Assign Workspace Managed Identity

 

 

 

 

The above option will assign the managed identity of the workspace the Storaqe Blob Data Contributor role with full access on the selected Data Lake Storage Gen2 file system. Leave the option to on unless you want to grant access manualy.

Assign sql admin user

You can set the sqladmin user. But it can set be in a later stage as well.

Managed Virtual Network

Leave this option empty, I will explain in a later article how this will work.

ip-address Connection

Normally you do not want to allow all IP addresses, for our initially setup we leave is as is. We can also adjust these settings in a later stage.

Overview Azure Synapse Analytics Workspace

Review all your settings and click on create. And you deployment is underway.

After the deployment is finalized, the workspace will be available in your selected Resource Group.

Azure Synapse Analytics Workspace Studio

You have now created your Azure Synapse Analytics Workspace and you can start using the new functionalities which are currently in Public Preview

Public Preview Features

Azure Synapse studio
Unified Security Model
Private endpoints
Power BI integration
Azure Machine Learning integration
Data lake exploration
Apache Spark integration
Data Movement
Pipeline Orchestration
On-demand query
Notebooks
SQL Script editor

In my next article I will walk you through the new Azure Synapse Studio. Stay tuned!

Azure Synapse Analytics

Azure Synapse Analytics

by | Jun 15, 2020 | Azure, Azure Synapse Analytics

Azure Synapse Analytics

by Erwin | Jun 15, 2020

Azure Synapse Analytics

 

Insights for all

Azure Synapse provides a breathtaking view of your data across data warehouses and big data analytics systems. Bringing these two worlds together into a single service is challenging as it requires unifying similar concepts that operate differently in each world such as security, privacy, and performance. With Azure Synapse, this seamless unification of data warehousing and big data not only simplifies a business’s analytics platform, but also breaks down silos that exist today because of teams, data, and skills. (source Azure blog)

Azure Synapse Analytics Workspace

During Ignite 2019 we already saw the first announcement about Azure Synapse Analytics. The first Public Preview was announced during Build 2020.

Immediately after Build 2020, I started playing and exploring with Azure Synapse Analytics Workspace.
Fortunately, I was off for a few days and was able to use this free time to dive a little bit into Azure Synapse.

A few days later during the Analytics in a Day workshops that I gave for my employer InSpark in collaboration with Microsoft, I immediately took the time to give a Live demo. I found the inspiration for this Live demo during a YouTube session presented by Simon Whiteley.

For many participants it is more imaginative,  to walk through the product Live than to tell a story via PowerPoint Slides.

Upcoming Articles

In the coming days I will try to write a number of articles so that you become more familiar with the various possibilities of Azure Synapse Analytics.

I have the following articles in mind:

✅ Creating your Azure Synapse Analytics Workspace

✅ Exploring the new Azure Synapse Analytics Studio

✅ Creating an Apache Spark Pool

✅ Creating a SQL Pool

✅ Integration with Power BI

And if you have more subject which needs to be explained feel free to leave them in the comments.

Happy reading!

 

 

Latest FMD Releases

Feel free to leave a comment