How deploy a cube (XMLA format) on an Azure SSAS

Question

Good afternoon everybody,

First I wish you the best for 2021 and even 2022 Here is my problem, I'm waiting our technical team grant me access to the Azure Devops platform (this can take a while), but in the meanwhile I would like to have a temporary solution allowing me to deploy an xmla (Json format) directly on an existing SSAS instance in order to create a cube using powershell.

I've read some documentation and it seems the best solution is to use the Invoke-ASCmd (correct me if I'm wrong); and I don't think it's possible to do that through REST API

Idea is to create a new cube in an automated way

Here is my code

$myfile = "c:\temp\test2.xmla"
$mybody=Get-Content -Path c:\temp\test2.xmla
$myaas="asazure://northeurope.asazure.windows.net/pocenvironment"
$myaasname="pocenvironment"
$mytenant = "1234"
$myclientID = "ABCD";
$mykeyID = "theKeyID";
$myauthority = "https://login.windows.net/" + $mytenant + "/oauth2/authorize";

$TokenRequestParams = @{
    Method = 'POST'
    Uri    = "$myauthority"
    Body   = @{
        grant_type = "urn:ietf:params:oauth:grant-type:device_code"
        code       = $DeviceCodeRequest.device_code
        client_id  = $myclientID
    }
}
$TokenRequest = Invoke-RestMethod @TokenRequestParams

$myconnectstr="Provider=MSOLAP;User ID=;Password="+$TokenRequest+";Data Source="+$myaas+";Persist Security Info=True;Impersonation Level=Impersonate"

Invoke-ASCmd -InputFile $myfile -ConnectionString $myconnectstr -Server $myaas

and the XMLA is something like this

{
  "create": {
    "database": {
      "name": "TESTME",
      "compatibilityLevel": 1500,
      "model": {
        "culture": "en-GB",
        "dataSources": [
          
        ],
        "tables": [
           
        ],
        "relationships": [
          
        ],
        "roles": [
          {
            "name": "Admin",
            "modelPermission": "administrator",
            "members": [
              {
                "memberName": "[email protected]",
                "identityProvider": "AzureAD"
              }
                     ]
          },
          {
            "name": "Reader",
            "modelPermission": "read",
            "members": [
              
            ]
          }
        ],
        "annotations": [
          {
            "name": "ClientCompatibilityLevel",
            "value": "400"
          }
        ]
      }
    }
  }
}

But I receive the following error message (tried with the powershell ise x86 and 64bits)

<b>Invoke-ASCmd : Object reference not set to an instance of an object.
At line:24 char:1
+ Invoke-ASCmd -InputFile $myfile -ConnectionString $myconnectstr -Serv ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Invoke-ASCmd], NullReferenceE 
   xception
    + FullyQualifiedErrorId : System.NullReferenceException,Microsoft.Analysis 
   Services.PowerShell.Cmdlets.ExecuteScriptCommand
</b>

Bhushan Bhushan · Accepted Answer · 2021-01-07T12:56:11

I have been trying something similar to "createoreplace" multiple tables on Azure AAS Tabular Model, in my case $connectionstring was a problem, if you have not already tried, try authenticating first with your id .. Thanks

How deploy a cube (XMLA format) on an Azure SSAS

2 Answers