Azure-docs: Explain how to deploy automatically, eg from VSTS

[simplesi]

SSAS automated deployments are typically done using Analysis Services Deployment Wizard (https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/running-the-analysis-services-deployment-wizard) - but apparently that doesn't work with Azure Analysis Services.

Even if it does work, the binary appears to be missing from the VSTS Hosted build agents:

[error]'"C:Program Files (x86)Microsoft SQL Server130ToolsBinnManagementStudioMicrosoft.AnalysisServices.Deployment.exe"' is not recognized as an internal or external command,operable program or batch file.

Any production use of AAS should be using well managed automated deployments - not from Visual Studio directly. Please provide clear advice on how to do this - no approach I've tried seems to work (eg AAS extension in VSTS is broken).

See related guidance out there on the web, but which doesn't address (a) AAS (b) VSTS
http://byobi.com/2017/11/methods-for-deploying-to-azure-analysis-services/#comment-22782

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 2bde37fb-b2e9-6ec2-7fc0-2846d1a00e1d
• Version Independent ID: b410c903-4141-96f2-0076-63c1b6a40e86
• Content: Deploy to Azure Analysis Services by using SSDT
• Content Source: articles/analysis-services/analysis-services-deploy.md
• Service: analysis-services
• GitHub Login: @Minewiskan
• Microsoft Alias: owend

[amanarneja]

@simplesi Thanks for the feedback. I have assigned this issue to the author to take a look and investigate updating the document with clearer guidance.

[amanarneja]

@simplesi can you help us break down your issue further. From what i gather there are 2 parts to it

1. You are looking for prescriptive guidance to build a CI/CD pipeline to AAS. Is that correct ?

2. You tried to follow some steps with VSTS hosted agent but ran into issues. Can you help us understand more on what were the exact steps you tried and failed?

[simplesi]

Sure. 1+2 are essentially solved the same way though - provide guidance on
how to deploy to AAS from a command line, because the existing documented
approaches don't seem to work.

I've tried:

1) Analysis Services deployment wizard.
https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/running-the-analysis-services-deployment-wizard
Note: the documentation says AAS isn't supported, but this is the normal
way do accomplish this task, so I thought perhaps it hadn't been confirmed.
I would use VSTS secure files to store authentication info.

Problem 1: authenticate against AAS. Solved by editing the XML to add user
ID and password to connection string directly. Hosted builds don't run on
domain-joined machines so this is required.

Problem 2: change connection details (incl passwords) for data source
connections in the model. Eg Dev/UAT/prod. Something is broken here - even
running the wizard in answer mode, testing connections with the new
credentials then immediately running it to use those answers gave
authentication failures for the data source connections.

2) VSTS Azure Analysis Services deployment extension, which essentially
uses powershell. Problem 1: only supports the file format for 1400 models.
There is no upgrade path from 1200 models (using 'provider' data sources,
to 'structured' data sources using a 'type' attribute and M code for the
query). It looks like I'd have to fully rewrite my model in 1400
compatibility model, which will take days.

Problem 2: extension only supports altering one data source connection

Problem 3: I'm not sure what it does to existing role members, which I need
to retain.

Solution: deploy, then manually edit the data source connection via SSMS,
then process the model manually. But this breaks processing/refreshes in
the meantime.

3) Visual studio directly, as a last resort.
Problem 1: there is no support (in vs2015 at least) for different
deployment configurations against different targets, eg Dev/UAT/prod. The
deployment target server is essentially a project level parameter, so it's
too easy to accidentally deploy to where you last deployed (eg prod) rather
than where you intend.
Problem 2: all members of roles not defined in VSTS are removed, so all
user security is essentially dropped if you're securing users using azure
ad.

I'm sure I could spend a week writing my own powershell module to solve the
above, but it doesn't seem all that unreasonable for a modern cloud product
to have a modern way to deploy to it in an automated CI/CD fashion.

Simon Phillips

On 27 Mar 2018 02:40, "Aman Arneja" [e-mail] wrote:

@simplesi https://github.com/simplesi can you help us break down your
issue further. From what i gather there are 2 parts to it

1.

You are looking for prescriptive guidance to build a CI/CD pipeline to
AAS. Is that correct ?
2.

You tried to follow some steps with VSTS hosted agent but ran into
issues. Can you help us understand more on what were the exact steps you
tried and failed?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/MicrosoftDocs/azure-docs/issues/6226#issuecomment-376367273,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAXdLvPcV2nN_kUh1RUXdwfKlcPtVCWbks5tiZiEgaJpZM4S5ps9
.

[amanarneja]

@simplesi Thanks for getting back to me. Will take a deeper look and get back to you.

[artur-tolstenco]

@simplesi did you managed to deploy on AAS the model using the DeploymentWizard from command line? what should be put as user and password in the connection string in the deployment targets? I mean what are the corect tokens. Is this corect:

Data Source=asazure://westeurope.asazure.windows.net/XXXX;Timeout=0;User ID=domainusername;Password=pwd123

for me it does not work. When run in silent mode i got the error:
Error loading Model.asdatabase: No parameterless constructor defined for this object.

[artur-tolstenco]

adding that if I delete the .configsettings and .assecurityinformation files from the folder that contains the model the Analysis Services Deployment in silent works well. But... it would publish a model that can't connect to the data source - we need a way to put the credentials in the deployment.

any help appreciated

[d2town]

Any progress on this? I'm struggling with the same issue. We have solutions we can not operationalize until we figure it out.

[Minewiskan]

@amanarneja

please-close

This is outside the scope of GitIssues for docs. If there is still an issue here, please open support case,

[reubster]

I don't agree that this should be closed. It's a standard requirement to be able to deploy from the command line.

There is a lack of documentation on this, as well as a shortfall of tools. If there is an MS recommended pattern for this, it should be documented in the relevant section above.

If there's not a pattern.. there needs to be.

[denverrenfro]

I agree with reubster, this issue does not seem to be resolved.
Has anyone from Microsoft answered the question in another place?

[winthropgutmann-ef]

Any update on this?

[KranthiPakala-MSFT]

@winthropgutmann-ef We have reopened the issue and assigned to the content author. They will evaluate and provide an update as appropriate.

@Minewiskan Could you please have a look at this? - Thanks!

[Minewiskan]

in-progress

[hakoneriksson]

Hi, we are deploying 1400 models with DevOps build and release pipelines and powershell to change settings. You need to use Microsoft.AnalysisServices.Deployment.exe in order to generate the xmla scripts to deploy to server. We are using an app registration in Azure with
Azure Analysis Services API delegated permission https://*.asazure.windows.net/Model.ReadWrite.All
to be able to deploy the .xmla script to the server. The app registrations also needs to be part of the Analysis Services Admins in Azure.

Build stage

• MSbuild task builds the .sln project file resulting in 3 files. (.asdatabase, .deploymentoptions & .deploymenttargets)
• Powershell script for changing deployment options with 2 injected environment variables for role deployment and partition deployment(see deploymentoptions.ps1)
• Copy model files to drop locaction Contents should be set as follows:

*?(.asdatabase|.deploymentoptions|.deploymenttargets)

• Publish build artifacts from drop location

RoleDeployment we have set to "DeployRolesRetainMembers" <- does not overwrite rolemembers!
PartitionDeployment we have set to "RetainPartitions". Use "DeployPartitions" if you do not use any external partition management, or you want to push changes to partitions.
Documentation

Deployment stage
For each environment you want to deploy the model to, you need to fetch the existing roles and partitions for that model, since the deployment tool will generate the complete .xmla script that overwrites the existing model.
We do this with Microsoft.AnalysisServices.Deployment.exe and the required dlls from SQL Server Management Studio. (see aasdeploy.zip)
_Please don`t ask me how I brute forced the dlls needed :(_
You can find the dlls in the installation folder for SSMS if you want to use clean dlls and executable.
(C:Program Files (x86)Microsoft SQL Server Management Studio 18Common7IDE)

We also update the credential towards our data warehouse component, since this is different across environments. This could be changed to Oauth if you need. We are only using a standard SQL user to read data when processing the model.
Then we deploy within a DevOps Azure powershell task and specifically the "Invoke-ASCmd" command for the .xmla script
Everything here is located in the deployment script (deploy-azure-powershell.ps1)

• Copy Microsoft.AnalysisServices.Deployment.exe and dlls into the agent. we fetch this from a git repo and extract the zip.
• modify deployment targets file (see deploymenttargets.ps1) with environment variables for Server, Database, and service principal/pw to connect to aas server to get the existing roles and partitions
• deploy with Azure powershell (see deploy-azure-powershell.ps1) and supply variables for Service principal, file locations, your datasource connection info, and analysis services server adress: (asazure://westeurope.asazure.windows.net/yourservernamehere)

Notes:
_deploy-azure-powershell.ps1_: We are including the database name in the credential path, due to how we have defined our connection object in the model. The standard generated path property does not include database.
$path = $DwhServerAddress + ";" + $DwhDatabaseName

aasdeploy.zip
scripts.zip

Please feel free to contact me for further details if needed.

[christianwade]

Option 1 (AS deployment wizard) should work.

We are not aware of any documentation saying it's not supported. If you can point us to this, we can get it corrected.

You should use service principal for this.

Currently you do need to reset the credentials for data sources. This can be done securely using the AAS scripting/programmability APIs

[reubster]

@christianwade ... I've been looking at this for quite a while, and been in touch with Microsoft professional services in the UK.. and we can't find a way to securely update the data source credentials using AAS scripting.

[christianwade]

You can use TOM ( https://docs.microsoft.com/en-us/bi-reference/tom/introduction-to-the-tabular-object-model-tom-in-analysis-services-amo )

or TMSL (https://docs.microsoft.com/en-us/bi-reference/tmsl/tabular-model-scripting-language-tmsl-reference )

If you script out the model in SSMS you can see the properties you need to set.

If a provider data source you can set the connection string (https://docs.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.tabular.providerdatasource.connectionstring )

If a structured data source, you can set the credential (https://docs.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.tabular.structureddatasource.credential )

[reubster]

Hi @christianwade ,

Thanks for the response. I think my question was unclear. I'm currently using TMSL with the invoke-ascmd in Azure DevOps. However this exposes the TMSL createorreplace command in plain text.

I think what you're suggesting is that I could write a .net framework app to execute the TMSL instead.

That's perfectly fair, if that's the recommendation... if a little onerous.

What I really need is support for managed identity authentication in the AAS to Azure Sql data connector. I'm guessing this is on the roadmap. In the meantime I need to use sql authentication to connect to the datasource... hence the question about securely executing TMSL.

Cheers,
Reub

[fmms]

@reubster :
I am just building the TMSL as part of DevOps pipeline dynamically and injecting there the authentication information from secure variables.

@hakoneriksson
instead of trying to install parts of SSMS on the build agents I am using Tabular Editor. That is a nice 2MB concise download that i do during the build. See https://github.com/otykier/TabularEditor.
The author spoke at SQLbits about integration with DevOps. See https://sqlbits.com/Sessions/Event18/Analysis_Services_DevOps_using_Tabular_Editor.

[reubster]

@fmms

"I am just building the TMSL as part of DevOps pipeline dynamically and injecting there the authentication information from secure variables."

My TMSL powershell has

$AlterDataOpsDatasourceCommand = @"

{
    "createOrReplace": {
      "object": {
        "database": "$AnalysisServicesDatabaseName",
        "dataSource": "SQL/Db"
      },
      "dataSource": {
        "type": "structured",
        "name": "SQL/Db",
        "description": "Db database",        
        "connectionDetails": {
          "protocol": "tds",
          "address": {
            "server": "$DbServerName",
            "database": "$DbDatabaseName"
          },
          "authentication": null,
          "query": null
        },
        "credential": {
          "AuthenticationKind": "UsernamePassword",
          "Username": "$AASAutomationUsername",
          "Password": "$AASAutomationPassword", 
          "EncryptConnection": false,
          "PrivacySetting": "Organizational"
        }
      }
    }
  }

"@    

# Connect to Azure as the service credential 
Login-AzureRmAccount -ServicePrincipal -Credential $Credential -SubscriptionName $subscriptionName -TenantId $AADDirectoryID

# Connect to AAS instance 
Add-AzureAnalysisServicesAccount -RolloutEnvironment $Region -ServicePrincipal -Credential $Credential -TenantId $AADDirectoryID;

# Execute the TMSL commands 
Invoke-ASCmd -Server $AnalysisServicesServerName -Database $AnalysisServicesDatabaseName -Query $AlterDwDatasourceCommand;

In the script, $AASAutomationUsername, $AASAutomationPassword need to be expanded plain text, even if they're originally secure variables.

TMSL doesn't support secure strings in the command, as far as I can tell.

?

[hakoneriksson]

@reubster :
I am just building the TMSL as part of DevOps pipeline dynamically and injecting there the authentication information from secure variables.

@hakoneriksson
instead of trying to install parts of SSMS on the build agents I am using Tabular Editor. That is a nice 2MB concise download that i do during the build. See https://github.com/otykier/TabularEditor.
The author spoke at SQLbits about integration with DevOps. See https://sqlbits.com/Sessions/Event18/Analysis_Services_DevOps_using_Tabular_Editor.

This looks promising. Everytime we merge features is a pain. Thanks!
https://tabulareditor.github.io/blog.html

[reubster]

"instead of trying to install parts of SSMS on the build agents I am using Tabular Editor. That is a nice 2MB concise download that i do during the build. See https://github.com/otykier/TabularEditor."

Yes, me too. It's so much better.

"The author spoke at SQLbits about integration with DevOps."

I went to that too! Thanks for posting the link. I'd been wanting the slides, but had forgotten.

[Minewiskan]

reassign:kfollis

[Minewiskan]

reassign:minewiskan

[Minewiskan]

please-close