Options to Refresh our Tabular Models Gaston Cruz

Options to Refresh our Tabular Models Gaston Cruz, Solution Principal, SLALOM - USA

Own your career with interactive learning built by community and guided by data experts. Get involved. Get ahead. Explore your PASS community Free online webinar events Local user groups around the world Connect with the global data community . org Online special interest user groups Learning on-demand delivered to you Get involved

Missed PASS Summit 2019? Get the Recordings Download all PASS Summit sessions on Data Management, Analytics, or Architecture for only $399 USD More options available at PASSstuff. com

We are thrilled to say the first ever PASS Virtual Summit will be taking place this November! We will be launching PASS Summit into a whole new world, and with that comes exciting opportunities with what we can offer you. We will go beyond simply moving sessions online to fully embracing everything a virtual environment can offer. Find out more at PASS. org/summit

Thank you to our Global Sponsors and Supporters

This event was sponsored by Microsoft Learn more about SQL Server 2019 today: -Get free training: aka. ms/sqlworkshops -Download the SQL 19 e. Book: aka. ms/sql 19_ebook

Options to Refresh our Tabular Models Gaston Cruz - MVP Data Platform Solution Principal Slalom, USA www. gastoncruz. com /gastoncruz @gastonfcruz gastoncruz@outlook. com

Agenda ● Architectures ● Process ● Azure Logic Apps ● Azure Data Factory ● Summary

Architectures The main purpose of this scenario is to orchestrate and process objects within an instance of AAS with Logic Apps and Azure Data Factory 4 Azure Data Factory App Registration 1 Svc Principal 3 Web Activity Azure Logic Apps Http Action 1 Deploy an App Registration (application services principal) in Azure 2 Grant Permissions to our App for Model Processing in Azure Analysis Services 3 Deploy a Logic App that can read/write within REST API of Azure Analysis Services to process models 4 Create an Azure Data Factory pipeline with a Web Activity that will trigger the execution of the Logic App 2 Azure Analysis Services Rest API

App Registration Go to Azure portal and search for App Registrations. On the overview click App Registrations followed by New Application registration

App Registration In Settings of the new App Registration click Required Permissions and Select an API, type “Azure Analysis Services”. In Permissions select “Read and Write all Models”

App Registration Back in the Settings of the App Registration, now click Keys. Enter a Key description, choose the duration. A key will be created, and we need to copy this key to paste in a following step

App Registration Back in the Settings of the App Registration, now click Keys. Enter a Key description, choose the duration. A key will be created, and we need to copy this key to paste in a following step

Access and Permissions - AAS • Connect to your AAS server with SQL Server Management Studio. To process models using Rest API, the App Registered in before steps needs Server Administrator permissions. • Open AAS properties, Security and then Add a manual entry using App ID and AAD ID (tenant). Use following syntax: app: <app guid>@<tenant guid>

Implementing our Logic Apps • Create a new Logic App from the Azure Portal Marketplace and click Edit • Pick the “When a HTTP request is received” as a trigger from the list of commonly used triggers • Click “+New step”, “Add an action” and choose the “HTTP – HTTP” action. • Now we have all the components needed to be triggered from ADF and to communicate with AAS Rest API.

Configuring Logic Apps Configure now the HTTP action (from Microsoft Official docs API: https: //docs. microsoft. com/en-us/azure/analysis-services-async-refresh ) Method: POST URI: https: //<region>. as azure. windows. net/servers/<servername>/models/<modelname>refreshes Body: From the REST API documentation under POST/Refreshes with this sample we are refreshing the whole database (model) by a process type Full. It is possible to refresh only specific tables or partitions (see the image on how to refresh a table) { } “Type” : “Full”, “Commit. Mode” : “transactional”, “Max. Parallelism” : 2, “Retry. Count” : 2, “Objects” : [] Authentication: Active Directory Oauth Tenant: Use the AAD ID from the App Registration screen Audience: https: //*. as azure. windows. net Client ID: Use the App Registration App ID from that screen Credential Type: Secret: Use the App Registration Key from that screen

First testing for our Logic Apps • Run the Logic Apps from the Designer • Open the HTTP Action to see the REST API response code and msg • After the Logic Apps run and is ready to get executed from Azure Data Factory – Open the “When a HTTP request is received” trigger and take the HTTP POST URL

Implementing Azure Data Factory instance • Create a new Azure Data Factory instance from Azure Portal • Define Name, Version, Subscription, Location and of course you can attach that instance to a GIT Repository (more later)

Azure Data Factory – Pipeline Design • Go to Author & Monitor in the Azure Data Factory instance • In the visual pane, create a new pipeline and drag & drop a Web activity box • Configure the Web Activity: • URL: use the HTTP POST URL of the Logic App • Method: POST • Body (mandatory): { “message” : “test” } • Execute a Test Run and check

Implementing Triggers in Azure Data Factory • Go to Triggers (bottom left) • Define the Name, Description for the trigger • Setup a starting date • Recurrence (Every X minutes / hours / days / weeks / months) • Setup End Date

"Improve the Process" @gastonfcruz

Thank you

Thank You to our Global SQLSaturday Sponsors

Own your career with Interactive learning built by community and guided by data experts. 1 Attend an event Get involved. Get ahead. In-person 2 Join a Community 3 Explore More Newsletters, Recorded Training, Giving Back . org Online