Supervising the PL-300 Labs at the Microsoft Business Application Cloud Week for Partners

BizApps Cloud Week

The conference is taking place April 4-8 and only requires a 2-hour commitment each day (apart from the labs). It is a virtual skilling event designed to teach attendees the practical applications of Dynamics 365 and Power Platform and prepare them for certification.
The agenda focuses on role-based certification tracks that are very relevant to my responsibilities as a what ever position they pursuit. This conference will have instructor-led courses, labs, exam prep sessions, and both live and offline Q&A.
Attending this conference would enable you to build your skills and implement them into customer projects in a meaningful way that would benefit my team and your company.
Please check out the conference registration page: Business Applications Cloud Week for Partners. The conference is free to attend with the only commitment being my time, as long as your are a Microsoft Partner.

My role in this event is supervising the labs. Specifically the labs around the PL-300 course. If you are unfamiliar with the PL-300 exam, it might be that DA-100 rings a bell!? The PL-300 certification replaces the DA-100 exam and introduces a set of changes. You can compare the skills tested here for PL-300 and here for DA-100. In a quick glance, the differences are as follows:

PL-300DA-100
Prepare the data (15-20%)Prepare the data (20-25%)
Model the data (30-35%)Model the data (25-30%)
Visualize and analyze the data (25-30%)Visualize the data (20-25%)
Deploy and maintain assets (20-25%)Analyze the data (10-15%)
Deploy and maintain deliverables (10-15%)

Obviously Microsoft has updated the content, but also moved the certification from one branch (DA) to another (PL). The DA branch only contained the DA-100 certification, as all other data related certifications either reside in the DP branch. Now it’s moved in with the other Power Platform certifications in the PL branch.

Back to the labs!

In this week of Business Applications, I am supervising the labs that run as homework for the classes in Power BI. These labs are similar, but not the same, as the ones that are available on some of the Microsoft Learn Learning Paths. If you are not familiar with these new and powerful capabilities of the online learning experience with Microsoft Learn, you should definitely check them out.

The labs are highly specialized towards a specific learning goal and of great use, when you want to fiddle about with a well refined use case.

Power BI Community Tour

Blog post in Danish 🙂

Om lidt under en måned (25/4-27/4) ruller Power BI bussen afsted og gør sit første stop på Power BI Community Touren 2022. Mere præcist, så begynder vi i Lyngby, kører videre dagen efter til Odense og runder Touren af i Aarhus. Så alt efter hvor du er i landet, vil der være god mulighed for at deltage.

På hvert stop vil der blive disket op med introdultion og best practices indefor de forskellige elementer af Power BI. Med oplæg om Introduktion til Power BI, Data Loading & Mashup, Data Modellering & DAX, Data Visualisering og Distribution og deling vil alle hjørner være dækket.

Der er tale om oplæg der retter sig mod begyndere eller meget let øvede brugere af Power BI, og du kan her få en tryggere start på din rejse med Power BI.

  • Har du brugt Power BI, men mangler at vide hvordan det hele hænger sammen?
  • Har du importeret noget data i Power BI, men mangler at vide hvordan man organiserer sine tabeller?
  • Har du lavet en Power BI rapport, men mangler at vide hvordan man bedst visualiserer dataene?
  • Har du udviklet nogle rapporter, men mangler at vide hvordan du deler dem med dine kollegaer?
  • Har du aldrig brugt Power BI, men vil gerne vide mere om hvorfor det er et af de mest populære rapporterings- og self-service BI værktøjer?

Hvis du svarer ja til ét eller flere af disse spørgsmål, så er Power BI Community Tour for dig. Hvis ikke – så send meget gerne denne information videre til relevante kollegaer!

Sign up her: https://lnkd.in/eVzcBMvp

En stor tak til JDM, Kapacity, Microsoft og Seges for at stille lokaler og forplejning til rådighed.

Setting up Azure Analysis Services database(s) refresh w/ Azure Automation

There are a lot of technical ways to achieve an updated database (to many called a model) in Azure Analysis Services, one of them is by using Azure Automation which allows you to orchestrate processes in Azure amongst other things.

Automation capabilities - src: https://docs.microsoft.com/en-us/azure/automation/automation-intro
src: https://docs.microsoft.com/en-us/azure/automation/automation-intro

One of the components of Azure Automation is the concept of a Runbook. A Runbook contains some sort of a script i.e. Powershell or graphical representation, which can be scheduled or activated by a Webhook. A webhook is an HTTP endpoint, which means you can activate the runbook from almost any service, application and/or device. In fact, if you can do a POST to the HTTP endpoint you are good to go.

So really, this comes down to how you create the runbook, because once created, you can think up a gazillion scenarios to invoke the script. Could be Power Apps, could be Power Automate, could be Azure Data Factory or a completely different process where you need to kick of an automation script.

To complete this guide, you will need the following services created:

For the Azure Analysis Services Model we can simply use a sample data option, provided by creating a new Model in Azure Analysis Services. This allows you to select a sample data which creates an Adventure Works sample model.

Create new Model
Choose Sample Data Model
Adventure Works Model

Now that we have our Automation Account and model ready, we can go ahead and stitch everything together.

In order for us to run this unattended, we will be needing an App Registration in our Azure Active Directory (make sure it’s in the same tenant). Microsoft has a guide here. You will need to record the Application ID (Client ID) and also the Secret you have created. With this information, our first step is to create our Automation Account Credentials in the Shared Resource section of the Automation Account.

Give the credentials a meaningful name (1), maybe even be bold and name it the same as you did when registering the App 😉. (2) use the Application ID (Client ID) as user name and finally the Secret as Password (3) – repeat for confirmation. Once these credentials have been setup, we can reference them from our Runbook, which is the next step.

Next step is to generate the Powershell script that we will schedule or call from the outside via a webhook.
This is done by creating a new Runbook, in the Automation Account.

Find the menu item Runbooks

Create a new Runbook, select a meaningful name, select the Runbook Type which in our case is Powershell. Lastly provide the correct version of Powershell you will be using – make sure the correct libraries are loaded, see how to manage the modules here.

And now to the actual Powershell code.
We will begin by defining the parameters for the Runbook, which are DatabaseName, AnalysisServer and RefreshType. All three combined makes a good starting point for a dynamic way to expose the option to refresh a model in Azure Analysis Services. The code looks like this:

param
(
    [Parameter (Mandatory = $false)]
    [String] $DatabaseName,
    [Parameter (Mandatory = $false)]
    [String] $AnalysisServer,
    [Parameter (Mandatory = $false)]
    [String] $RefreshType
)

This way, we can from the outside tell the Runbook which database on which server to refresh.
Then we will assign the tenant id to a variable (this could arguably be set from a Runbook variable or parameter) and then we will assign the credentials we just created to another variable. Please replace #!#CredentialName#!# with the name that you have created the credentials under.
As soon as we have the credentials assigned, we can log in to the Azure Analysis Services instance and perform the ProcessASDatabase method. Note that the refresh type has to match the definition below.

# Get the values stored in the Assets
$TenantId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
$Credential = Get-AutomationPSCredential -Name "#!#CredentialName#!#"

# Log in to Azure Analysis Services using the Azure AD Service Principal
Add-AzureAnalysisServicesAccount `
    -Credential $Credential `
    -ServicePrincipal `
    -TenantId $TenantId `
    -RolloutEnvironment "northeurope.asazure.windows.net"

# Perform a Process Full on the Azure Analysis Services database
Invoke-ProcessASDatabase `
    -Server $AnalysisServer `
    -DatabaseName $DatabaseName `
    -RefreshType $RefreshType

Refresh Type definitions (see detailed description here):

ProcessFull, ProcessAdd, ProcessUpdate, ProcessIndexes, ProcessData, ProcessDefault, ProcessClear, ProcessStructure, ProcessClearStructureOnly, ProcessScriptCache, ProcessRecalc, ProcessDefrag

Once we are at this stage, we can publish and/or test our Runbook by pressing Publish or opening the Test Pane. Note: You cannot run a Runbook that is not published.

When published, we have several options to invoke the Runbook, either by Schedule or by Webhook.

The Webhook creates a URL which we can use in other applications to invoke the Runbook. The parameters need to be assigned once the Webhook is defined. This means you can have a unique URL for each set of parameters you have.
Note, you need to copy and store the URL generated when creating the Webhook – as the warning says, you cannot go back and retrieve it.

Creating a Webhook

Last step is to define the parameter values. Assign the name of the Database and the Server as well as the Refresh Type you desire.

After the parameter assignment, you should end up with a final wizard page looking like this:

Once we click Create, the Webhook is live and usable.

I hope this post will be of use, or at least of inspiration to someone out there, on the great things possible in Azure.

SqlSaturday is back in Town

SQLSaturday #963 - Denmark 2020

At last we can look forward to having anew edition of SqlSaturday in Denmark on April 25th. As previously the event will be hosted at Microsoft HQ in Lyngby, so the venue will be familiar to many of you who are returning attendees.

This year we are looking at session in all of the following categories:

  • Analytics and Visualization
  • Application & Database Development
  • BI Platform Architecture, Development and Administration
  • Cloud Application Development and Deployment
  • Enterprise Database Administration and Deployment

Currently we have received over 100 abstracts to select from, which is always a daunting task. Luckily Bent Nissen Froning (t|b|l), Claus Lehmann Munch (t|b|l), Just Thorning Blindbæk (t|b|l) , David Bojsen (t|l) are all very accomplished professionals, who will be making the right choices I am sure.

On the day before the conference, the team is offering three (3) pre-cons with world renowned professionals on:

Image result for rob sewell
Image result for dbberater"
Bent (Nissen Pedersen) Nissen Froning
Image result for asgeir mvp"

For more details, go to http://www.sqlsaturday.dk and book your seat today!

Extracting SSAS Tabular Models w/ PowerShell

As a response to a comment on a previous blog post on how to extract SSAS Multidimensional [MD] databases with PowerShell, I decided to write a new blog post, to address the tabular version [Tab].

The main differences working with MD and Tab, programatically, is that MD is represented by XML for Analysis and Tab is using JSON. In management studio this makes no difference however, as you paste XMLA and JSON using the same query type; XMLA (I wonder when/if that will change?)

Obviously, the two technologies MD and Tab are vastly different in almost every other aspect as well, but for the scope of this exercise, we will keep it at that.

Just as in the previous example, we will be using the ability to load assemblies in PowerShell and leverage the functionality the product team has provided. With Analysis Services comes a library of objects to programatically access and manage an Analysis Services instance.

The namespace for MD:
https://docs.microsoft.com/en-us/dotnet/api/microsoft.analysisservices?redirectedfrom=MSDN&view=analysisservices-dotnet

The namespace for Tab:
https://docs.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.tabular?view=analysisservices-dotnet

In this documentation, you can dig into the details of options available. All of this extensible from both C# and PowerShell.

Now, back to the problem at hand. We wanted to extract the models from one or more servers, to deploy to another (single) server them or even just persist them locally. To do this, we need to load the Tab version of the assembly, which is that first difference to the original script. Next we need to leverage different functionality within the assembly, to export the json.

The script in all it’s simplicity 🙂

#Load the Tabular version of the assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Tabular") >$NULL

#Add a comma seperated list of servers here
$SourceServers = @( "<SOURCE SERVERS HERE>" ); #Source
#Add a single server here
$TargetServer = "<TARGET SERVER HERE>"; #Target

cls;

#Uncomment to deploy to target server
#$TargetServer.Connect();

#Loop servers
ForEach( $srv in $SourceServers ) {
    
    #Connect to current server
    $server = New-Object Microsoft.AnalysisServices.Server
    $server.connect($srv)

    #Loop al databases on current server
    ForEach(  $database in $server.Databases ) {

        #Generate Create Script - Other options are available, see https://docs.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.tabular.jsonscripter?view=analysisservices-dotnet
        $json = [Microsoft.AnalysisServices.Tabular.JsonScripter]::ScriptCreate($database, $false)

        #Create Path
        $Path = "<INSERT DUMP LOCATION AND FILE NAME>" + ".json";        

        #Export the model to file
        $json | out-file -filepath $Path 
        
        #Uncomment to deploy to target server
        #$TargetServer.Execute($json);
    }    
}