PASS summit Keynote V.20

Today PASS Summit v.20 (2018) had the first keynote with a lot of really big annoucements. I have tried to sum up some of the new cool features for you to have an overview.

Azure SQL Database

Public preview of Machine Learning Services with support for R have been announced for Azure SQL Database. In addition to the support of R for the ML Services it have been announced that it is planned to add Python at a later stage.

The capabilities covers the abilities to run R Script where the data resides and as well simply operationalize these by embedding them into T-SQL Stored procedures.

This is one of my personal favorites since this will enable a lot of business to simplify their setup for operation purposes and reduciton of services in Azure.

For more information, see the Azure SQL Server blog

Azure SQL Data Warehouse

It have been announced that Azure SQL Data Warehouse now support capabilities to prioritize query execution ensuring that high business value work gets priority.

In addition, SQL Data Warehouse now offers native row level security with integration to Azure Active Directory.

Finally SQL Server Data Tool (SSDT) support for Azure SQL Data Warehouse have been announced in preview. This is inteded to bring the well-known experience of version control, test automation and CI/CD offerings to the Azure SQL Data Warehouse.

For more information, see the Azure SQL Data Warehouse blog

Power BI

Dataflows have been announced available in public preview.

In short dataflows is the possibility to utilize Power Query in a rich web-interface. This will enable Power BI with capabilities to easily ingest, cleanse, integrate and schematize data.

It will be possible to use dataflows to do the complex data prepration which Power Query is currently helping you solve.

When data have been loaded it will be possible to place these in Azure Data Lake Service Gen2 for storage and further transformation and handling using a wide verity of Azure services such as Data FActory, Databrick, SQL Data Warehouse etc.

For more information, see the announment blog

Paginated Reports in the Power BI Service is now supported as part of the Power BI Service for customers running Power BI Premium. When the capability is enabled within Power BI Premium dedicated capacity will be in place to ensure rendringer.

For more information, see the Power BI blog

When the video is available i will ensure to link to timings for the different details

Build Tabular Models through hosted agent

Working with a modern BI setup it is important to ensure that continious deployment (build and release) is handled automatically using tools like Azure Devops or other similar tools.

On most projects i have been working we have utilized a self-hosted agent for these activites and therefore had a lot of control of what is installed once and then reused by the agent. This appraoch have been working great for a lot of situations. This have by quite some time been my preferred way of doing things when using Tabular Editor, mainly because the AMO libraries have not been available for download in any easy acessible way.

Recently i have been playing around with some scenarios to avoid installing the agent on hosted environments since a full-blown BI setup in azure does not really need any hosted machines. Due to this i have been investigating if i could get away with only using the hosted agent.

I have decided to download the latest version of the AMO library even with SSDT already installed on the host. This is simply because the hosted agent only have version 14.0.0.0 of the TOM AMO libraries installed. The currently latest version of AMO library is 15.7.0.2.

# Load security protocol to handle HTTPS connection to GitHub
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# Load Github release details for Tabular Editor
$TabularEditor = Invoke-WebRequest -Uri "http://api.github.com/repos/otykier/TabularEditor/releases/latest" | ConvertFrom-Json;

# Identify download path from for TabularEditor.zip for download purposes
$TabularEditorDownloadPath = $TabularEditor.Assets | Where-Object { $_.name -eq "TabularEditor.zip" }  | Select-Object @{N="DownloadPath";E={$_.browser_download_url }} 

# Download file to root of repository
$TabularEditorZip = join-path (get-location) "TabularEditor.zip"
Invoke-WebRequest -Uri $TabularEditorDownloadPath.DownloadPath -OutFile $TabularEditorZip

#Unzip and delete archive
Expand-Archive -Path $TabularEditorZip -DestinationPath  (get-location).Path
Remove-Item $TabularEditorZip

# Install AMO package to Tabular Editor to run
# Make sure we have Nuget.exe
$nugetFolder = Join-Path $env:LOCALAPPDATA "Nuget"

$nugetExe = Join-Path $nugetFolder "Nuget.exe"

if (-not (Test-Path $nugetFolder)) {
    New-Item $nugetFolder -ItemType Directory
}

if (-not (Test-Path $nugetExe)) {
    $ProgressPreference = "SilentlyContinue"
    Invoke-WebRequest 'https://dist.nuget.org/win-x86-commandline/latest/nuget.exe' -OutFile $nugetExe
}

& $nugetExe update -self

# Download AMO Libraries
$nugetExe = [System.IO.Path]::Combine($Env:LOCALAPPDATA, "Nuget", "Nuget.exe")
& $nugetExe install Microsoft.AnalysisServices.retail.amd64 -OutputDirectory (get-location) -ExcludeVersion

# Move AMO DLL's to Tabular Editor for it to run
$AMOFilePath = Join-Path (get-location) "Microsoft.AnalysisServices.retail.amd64\lib\net45"
Move-Item -Path "$AMOFilePath\*" -Destination (get-location)

Tabular Editor 2.0

The second version of Tabular Editor have been released offering a lot of great new features. These features covers a lot which will speed up your development activities and abilities to deploy easily.

With this release the deployment wizard have been added together with the possibility to do advanced scripting using C#. In addition to these major features  a lot of smaller improvements have been implemented to simplify a lot of tasks for you as a developer.

Deployment

In the new version of Tabular Editor a deployment wizard have been introduced which introduces the option to decide which part of the model should be deployed.

Especially the support to control if connection strings and roles should be deployed is a really nice improvement.

Roles

Most of you have probably been struggling with keeping the members of the roles aligned between your deployed model and the setup in your project. Most of you working with tabular models have probably experienced unsatisfied business users when access is lost when deployment is performed.

Tabular Editor is now able to update the implemented security on table level and leaving the existing members in the roles.

Deploy Roles

This option will update configuration for tables in the database to reflect the configuration available in model.

Deploy Role Members

When this is unchecked the security setup will be updated but the members of the roles will not be changed.

Connections

Working with tabular models in different environments you have probably been in a situation where connection strings pointing towards QA or development environments has ended up in your production model.

With this option being introduced in the deployment wizard there is no longer a reason to fear this being an issues going forward.

Table Partitions

In cases where you want to keep specific partitions in your model an option have been added for you to discard any differences on these.

This is extremely beneficial in situations where some automated updated of partition have been implemented to optimise performance or processing speed.

Advanced scripting

This is a really cool feature which enables you to write small pieces of C# code to automate your work. This can be more complex code pieces doing a lot of addition or changes to the model or simplifying the trivial tasks such as applying display  folder os rename measures.

When working with the advanced script editor you can either choose to access the entire model object or working with selections which you have done in the tree browser.

Model object

Working with the model object you will have access to most of the descendant objects and methods which is part of the Tabular Object Model (TOM). This includes objects for tables, measures, translation and perspective.

Selections in the model hierarchy

This options allow you to select one or more object in the model explorer tree and then execute a script against these. To do this a Selected object is introduced which enables you to access the different type of objects which have been selected.

This is really cool an enables you to do a lot of corrections and changes - inclusive copying these between different names, perspectives, translations or other object settings.When scripting changes or modification to the model you will be able to use the build-in IntelliSense to easily see some of the available LINQ methods supported for the objects. To have a full overview of the functionality please see the LINQ-to-Objects documentation.

Other features in this release

Lots of new features in this release:

  • Undo/Redo functionality
  • Custom Actions via scripting
  • Syntax Highlighting
  • Improved Drag’n’drop support
  • Edit perspectives and cultures
  • View table partitions and DAX definitions for Calculated Tables

Within the next couple of week i will be writing some additional posts and videos to demonstrate these features and some of more complex examples over the above functions.

You will find the latest version of the Tabular Editor on the official GitHub page