Tabular Database Deployment

Azure DevOps pipeline task that will deploy a Tabular Model to an existing Azure Analysis Service or Power BI Premium dataset.

YAML Snippet

- task: deploy-aas-db@1
  inputs:
    connectedServiceNameSelector: 'connectedServiceNameARM | connectedServiceNamePBI'
    connectedServiceNameARM: 'service connection to AAS' # connectedServiceNameSelector = 'connectedServiceNameARM'
    connectedServiceNamePBI: 'service connection to PBI' #  connectedServiceNameSelector = 'connectedServiceNamePBI'
    aasServer: 'asazure://westeurope.asazure.windows.net/fabrikam | powerbi://api.powerbi.com/v1.0/myorg/dataset'
    databaseName: 'database'
    loginType: 'inherit | spn | user'
    tenantId: 'tenantId'      # loginType = 'spn'
    appId: 'appId'            # loginType = 'spn'
    appKey: 'appKey'          # loginType = 'spn'
    adminName: "username"     # loginType = 'user'
    adminPassword: "password" # loginType = 'user'
    pathToModel: './model.bim'
    partitionDeployment: 'retainpartitions | deploypartitions'
    roleDeployment: 'deployrolesandmembers | deployrolesretainmembers | retainroles'
    connectionType: 'none | advanced | sql'
    datasources: | # connectionType = 'advanced'
      [
        {
          "name": "<datasetname>",
          "authenticationKind": "UsernamePassword",
          "connectionDetails": {
            "address": {
              "server": "<sqlserver>",
              "database": "<databasename>"
            }
          },
          "credential": {
            "Username": "<username>",
            "Password": "<password>"
          }
        }
      ]
    sourceSQLServer: 'SQLServer'  # connectionType = 'sql'
    sourceSQLDatabase: 'Database' # connectionType = 'sql'
    sourceSQLUsername: 'Username' # connectionType = 'sql'
    sourceSQLPassword: 'Password' # connectionType = 'sql'
    ipDetectionMethod: "autoDetect | ipAddressRange"
    startIpAddress: "10.0.0.1" # ipDetectionMethod = 'ipAddressRange' 
    endIpAddress: "10.0.0.1"   # ipDetectionMethod = 'ipAddressRange'
    deleteFirewallRule: "true | false"

Parameters

Azure Details:

connectedServiceNameSelector - Type of service connection to use
- connectedServiceNameARM: Use an Azure Resource Manager service connection
- connectedServiceNamePBI: Use a Power Platform service connection, to install: Power Platform Build Tools
connectedServiceNameARM - Which Azure RM service connection should be used to connect to the datafactory
connectedServiceNamePBI - Which Power Platform service connection should be used to connect to the datafactory

Analysis Service Details:

aasServer - The name of the Azure Analysis Service server or Power BI Premium connection
databaseName - The name of the Tabular database
loginType - Type of Azure Analysis Service login:
- inherit: inherit the service principal from the service connection
- spn: using a service principal
- user: using a named user

If loginType option is spn:

tenantId - Azure ID Tenant ID
appId - Application ID of the Service Principal
appKey - Key/secret of the Application ID

If loginType option is user:

adminName - The admin user use to connect to the Azure Analysis Service instance
adminPassword - The password of the admin user use to connect to the Azure Analysis Service instance

Deployment Details:

pathToModel - Location of the '.asdatabase'/'.bim' file
partitionDeployment - Determine how existing partitions are treated during deployment.
- deploypartitions: any existing partitions will be replaces
- retainpartitions: partitions of new tables will be deployed, but partitions for existing tables will be unaffected
roleDeployment - Determine how security roles and role members are treated during deployment.
- deployrolesandmembers: any existing roles and members will be replaced
- deployrolesretainmembers: roles will be deployed along with their members for new roles. Members for existing roles will be retained
- retainroles: the roles and members will not be deployed

Data Source Connection Details (only applicable for Azure Analysis service):

connectionType - Type of the data source configuration:
- none: no additional security configuration is needed
- advanced: addtional security configuration is provided in a JSON array.
- sql: configure the first datasource with the provided servername, databasename, username and password. Support also legacy datasource

If loginType option is advanced:

datasources: - See sample above on the format of the JSON and model definition in the .asdatabase/.bim file

    [
      {
        "name": "<DataSourceName>",
        "authenticationKind": "UsernamePassword",
        "connectionDetails": {
          "address": {
            "server": "<ServerName>",
            "database": "<DatabaseName>"
          }
        },
        "credential": {
          "Username": "<UserName>",
          "Password": "<Password>"
        }
      }
    ]

If loginType option is sql:

sourceSQLServer - The servername of the Azure SQL database server
sourceSQLDatabase - The database name
sourceSQLUsername - The username used for the connection by the model for trhe connection to the source database
sourceSQLPassword - The password for the given username

Firewall (only applicable for Azure Analysis service):

ipDetectionMethod - How to determine the IP address that needs to be added to the firewall to enable a connection
- autoDetect: adds the IP address of the agent to the firewall rules
- ipAddressRange: Manual provide the IP Address Range to be added to the firewall rules.
deleteFirewallRule - Delete the firewall rule at the end of the tasks

If ipDetectionMethod option is ipAddressRange:

startIpAddress - Start IP address of the range
endIpAddress - End IP address of the range.

Power BI Premium Data Source Connection Details

Setting datasource credentials via metadata is not possible for Power BI datasets, see: https://docs.microsoft.com/en-us/power-bi/admin/service-premium-connect-tools#setting-data-source-credentials To set Power BI datasource credentials either vai the UI or via the Power BI REST APIs.

Release notes

1.5.0

Rewritten to Powershell 5.1 (powershell.exe) and ADOMD + TOM
Support for Power BI Premium XMLA endpoints
Support for multiple datasources
Support for merging of roles + members and partitions

1.3.0

Support for legacy datasources

1.2.0

Add support for service principal deployments
Add support for adding firewall rules

1.1.2

Model files are readed with UTF8 encoding

1.1.0

New: AAS return messages (error/warning) are used for the tasks logging
Bugfix: Better logging when exceptions are thrown

1.0.0

Initial public release

Feedback

If you have any comment related to the documentation, like corrections, unclear features or missing documentation, feel free to leave feedback below via GitHub. Or correct it yourself and submit a PR; see CONTRIBUTING.md for more details. GitHub account required.

3 Open
3 Closed

0
Error messages when running a pipeline using Tabular Database Deployment task

Hope this is the right forum.
I have created a pipeline in Devops using the Tabular Database Deployment task. I have then set up the credentials that are needed and then ran my pipeline. Every time that I run my pipeline I get the following error message:
The JSON DDL request failed with the following error: Failed to execute a JSON script. Error returned: Either the 'app:***@XXXXXXXXX' user does not have permission to alter the 'database_name' object, or the object does not exist.

The connection of Azure Ad TenentID, Application ID, Application Key are correctly configured with the service principal. The azure subscription not owned by me.

So given this context and the error message do you have any idea why this issue could be occuring.
Thanks for any help in advance!

#226 opened 2 months ago by GabrielWatson1

0
Unsupported tabular model property : relatedColumnDetails

The deployment task (liprec.vsts-release-aas.deploy-aas-db.deploy-aas-db@1) doesn't seem to support this column Tabular Model Property : relatedColumnDetails. This property is used when defining field parameters as illustrated in the following code :
{ "name": "Parameter", "lineageTag": "641b243a-276a-451e-87a0-2264a20473f0", "columns": [ { "type": "calculatedTableColumn", "name": "Parameter", "dataType": "string", "isNameInferred": false, "isDataTypeInferred": true, "sourceColumn": "[Value1]", "sortByColumn": "Parameter Order", "lineageTag": "d12fbfab-140b-4ad6-ac10-7c40f88239fa", "summarizeBy": "none", "relatedColumnDetails": { "groupByColumns": [ { "groupingColumn": "Parameter Fields" } ] }...

The task outputs the following error in an Azure DevOps pipeline when trying to deploy the .bim file to a Power BI Premium dataset :
[error]Not a valid model file. (Exception calling "DeserializeDatabase" with "1" argument(s): "Unrecognized JSON property: relatedColumnDetails. Check path 'model.tables[8].columns[0].relatedColumnDetails', line 1, position 48847.")

#200 opened on Aug 30, 2023 by Berbaris

2
Add more flexibility for managing roles

Would be great if we could have an option to either overwrite or not roles and members for each model

#111 opened on Jun 29, 2020 by ChristinaHolmes

Open a new issue on GitHub

deploy-aas-db@1 task unable to configure advanced datasources

I'm wondering if the task already supports deploying the SSAS model with connectionType 'advanced' enabled.

I want to do this, because, for example, I want to be able to change the name of the datasource accross environments (see screenshot).

The code I tried was the following (with variables defined in my Azure DevOps project):

   - task: deploy-aas-db@1
      displayName: 'Deploy ${{ parameters.datamart }} model'
      inputs:
        connectedServiceNameARM: 'Data ARM'
        aasServer: 'asazure://northeurope.asazure.windows.net/mandmtabular01${{ parameters.env }}'
        databaseName: ${{ parameters.datamart }}
        loginType: inherit
        pathToModel: Tabular/${{ parameters.datamart }}/Tabular/Model.bim
        partitionDeployment: deploypartitions
        roleDeployment: retainroles
        connectionType: advanced
        datasources: | # connectionType = 'advanced'
          [
          {
              "name": "SQL/BI${{ parameters.env }};${{ parameters.datamart }}", 
              "authenticationKind": "UsernamePassword",
              "connectionDetails": {
                  "address": {
                      "server": "BI${{ parameters.env }}",
                      "database": "${{ parameters.datamart }}"
                  }
              },
              "credential": {
              "Username": "$(SqlUsername)",
              "Password": "$(SqlPassword)"
              }
          }
          ]

Unfortunatly, this task doesn't reconfigure anything on the SSAS server

#188 opened on Nov 17, 2022 by jkockx

2
Property name: EncryptConnection. The exception was raised by the IDbConnection interface
Start issue here
I am trying to deploy Model.bim using the Azure Analysis Services Deploy task.
- task: liprec.vsts-release-aas.deploy-aas-db.deploy-aas-db@1
  displayName: 'Deploy model to asazure://.asazure.windows.net/'
  inputs:
  ConnectedServiceNameARM: Development
  aasServer: ''
  modelName: 'Digital_DEVOPS'
  loginType: spn
  tenantId: ''
  appId: '**'
  appKey: '$(spkey)'
  pathToModel: 'Model.bim'
  connectionType: sql
  sourceSQLServer: ***.database.windows.net
  sourceSQLDatabase: ***
  sourceSQLUsername: '$(userid)'
  sourceSQLPassword: '$(passwordid)'
  Gets the below Error while performing "Process Database" as full.
Failed to save modifications to the server. Error returned: 'The given credential contains a property with a null value. Data source kind: SQL. Property name: EncryptConnection. The exception was raised by the IDbConnection interface.
Snippet from the Database script
"credential": {
"AuthenticationKind": "OAuth2",
"kind": "SQL",
"path": "***.database.windows.net",
"Username": "xyz",
"EncryptConnection": null
}
#108 opened on Jun 18, 2020 by tushmesh

1
Extent 'Analysis Service name' parameter

The parameter Analysis Service name is expected in the format: asazure://westeurope.asazure.windows.net/fabrikam.

Documentation is not inline with the help text from the extension.

#19 opened on Oct 29, 2018 by liprec