{"id":996,"date":"2021-06-26T12:11:43","date_gmt":"2021-06-26T10:11:43","guid":{"rendered":"https:\/\/www.flip-design.de\/?p=996"},"modified":"2021-06-26T12:11:43","modified_gmt":"2021-06-26T10:11:43","slug":"process-sql-analysis-services-cubes-via-the-azure-data-factory","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=996","title":{"rendered":"Process SQL Analysis Services Cubes via the Azure Data Factory"},"content":{"rendered":"\n

If you work with Analysis Services on Azure, it is another way to process a cube. On premises you use mostly the SQL Server Agent to do this, but this option is not given on Azure. There you can use Azure Automation, PowerShell, or a Data Factory (ADF). This post is dedicated to the ADF, because mostly you need to process a table, partition, or the whole database after you made a data intake.

First we need to grant permissions to the SSAS Cube from the ADF. So, we need to create a managed identity. To get the needed data, open the properties of the ADF at the Azure Portal.

With the Tenant and the ADF ID, we can build the credential-<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

Next, connect to the AS instance via the Management Studio and go the server administrators and add this user. Via the Azure portal it is not possible, but after that, you can view the result.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n

To check the result, if a refresh has worked, I have added a View to my data source and imported the data inside the cube.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

You should format the column as a date type and format the column as a G, so you also get the time inside the output.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

Okay, the infrastructure is ready! Now, we can add a new pipeline inside our ADF with a web activity.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n
\nhttps:\/\/eastus.asazure.windows.net\/servers\/blog001\/models\/blog\/refreshes\n<\/div><\/figure>\n\n\n\n

This is the schema: https:\/\/<region>.asazure.windows.net\/servers\/<servername>\/models\/<modelname>\/refreshes<\/a>
<\/strong>For the fifth point add following URL for the authentication. https:\/\/*.asazure.windows.net<\/a>

The following XMLA code will initiate a full process
{<\/p>\n\n\n\n

    „Type“: „Full“,<\/p>\n\n\n\n

    „CommitMode“: „transactional“,<\/p>\n\n\n\n

    „MaxParallelism“: 2,<\/p>\n\n\n\n

    „RetryCount“: 2,<\/p>\n\n\n\n

    „Objects“: []<\/p>\n\n\n\n

}
After running the ADF pipeline you should get a full refresh of the cube:<\/strong><\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n



<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"

If you work with Analysis Services on Azure, it is another way to process a cube. On premises you use mostly the SQL Server Agent to do this, but this option is not given on Azure. There you can use … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/996"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=996"}],"version-history":[{"count":1,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/996\/revisions"}],"predecessor-version":[{"id":1004,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/996\/revisions\/1004"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=996"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=996"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=996"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}