Azure Analysis Services Web Designer

I’m a long time fan of Analysis Services, and this latest feature is a really cool addition to the offering. Currently in Preview, Azure Analysis Services Web Designer offers the following functionality that extends Azure Analysis Services, all through a simple web UI:

  • Add a new logical server in Azure
  • Create a new Analysis Services model from SQL DB, Azure SQL DW and….Power BI workbooks! (More data sources to come)
  • Browse an existing Analysis Services model and add new measures for quick validation
  • Open an existing Analysis Services model as a Visual Studio Project, in Excel, or in Power BI
  • Edit an existing Analysis Services model using TMSL (Tabular Model Scripting Language)

Example – Creating a Model from Power BI Desktop

In this example, I’ve created a simple Power BI workbook (.pbix file) that’s connecting to an Azure SQL DB instance running the sample AdventureWorks database:

Simple Report

Simple Power BI Report

To connect to the new service, I can access via the Azure portal or go direct to https://analysisservices.azure.com:

AAS Web Designer Portal

Azure Analysis Services Web Designer Portal

On this occasion, I’ll use the server I already have and instead go straight to adding a new model:

CreateModelFromPowerBI

Importing a Model from Power BI

Once created, I can browse the model in the Designer:

BrowseModel

Browse Model

Or open it using Power BI, Excel or Visual Studio:

Open Options

Open Model Options

Pbi From AAS Designer

Power BI From Published Model

This is in preview right now, with many features still to come. The ability to import Power BI models into Analysis Services is a massive feature in its own right, but aside from that it already shows how you can quickly create and modify models without having to delve into Visual Studio/SQL Server Data Tools. New features are coming every month, so keep on eye on the team blog (below) to follow its progress.

Further Reading

Introducing Azure Analysis Services Web Designer

Analysis Services Team Blog

Optimizing SSIS Data Loads with Azure SQL Data Warehouse & PolyBase

To gain the best loading performance with Azure SQL Data Warehouse, you want to leverage the power of PolyBase, which allows for massively parallel data loading into the underlying storage.

In standard scenarios, ETL developers using SSIS will leverage a data flow that takes data from a source (Database, File, etc) and load it directly into the target database:

SSIS Simple Data Flow

Simple SSIS Data Flow (Table to Table)

Unfortunately, adopting this pattern with an SSIS data flow into Azure SQL Data Warehouse will not naturally leverage the power of PolyBase. In order to understand this we need to take a little peek under the covers of SQL Data Warehouse:

Azure SQL DWH Overview

Azure SQL Data Warehouse Overview

As part of the underlying architecture, SQL DW has at its head the Control Node. This node manages and optimizes queries, and serves as the access point for client applications when accessing the SQL DW cluster. What this means for SSIS, is that when it makes its connection to the SQL DW using standard data connectors, this connection is made to the Control Node:

SSIS Front Load

SSIS Front Loading

This method introduces a bottleneck as the Control node serves as the single throughput point for the data flow. As the Control node does not scale out, this limits the speed of your ETL flow.

As stated earlier, the most efficient way of loading into Azure SQL DW is to leverage the power of PolyBase in a process known as “Back Loading”:

SSIS Back Load

Back loading via Polybase

Using this method, data is bulk loaded in parallel from staging files held in Blob Storage or Azure Data Lake Store, which allows for the full data processing power of PolyBase to be used.

Leveraging Polybase Within SSIS

Previously, ingesting data from files into SQL DW using PolyBase was a slightly drawn out process of uploading files to storage, defining credentials, file formats and external table definitions before finally loading the data into SQL DW using a CTAS (Create Table As Select) statement. This works perfectly well, but introduces a lot of extra SQL coding on top of the norm.

Thankfully, with the SSIS Azure Feature Back, the above is now made much, much easier with the introduction of the Azure SQL DW Upload task. This process automates the creation of the SQL scripts required, allows you to seamlessly incorporate this task into an SSIS ETL process that will fully leverage Polybase.

Azure DW Upload Control Flow

Azure SQL DW Upload Control Flow

Azure DW Upload Task

Azure SQL DW Upload Task

Further Reading

Azure SQL DW Best Practices

Azure SQL DW With Azure Data Lake Store (ADLS)

Polybase Guide