Publish-Cube allows you to deploy a tabular or multidimensional cube to an Analysis Services instance either on-premise or in Azure. Behind the scenes it uses the Analysis Services Deployment Utility in silent mode. Publish-Cube simplifies the use of Analysis Services Deployment Utility by automatically updating the various config files that the Deployment Utility uses to deploy the cube.
When you perform a build of a Visual Studio cube project, it creates an AsDatabase file which defines the entire model such as dimensions, attributes and measures associated with the cube. Publish-Cube can be used in CI senarios as part of the pipeline so that you can populate the cube with data and run tests against the cube using DAX or MDX as part of the pipeline.
To automate the build and deployment of tabular cube in Azure DevOps, you can use MsBuild to create AsDatabase from your Visual Studio solution. You can then add a PowerShell task which uses Publish-Cube to invoke Analysis Services Deployment Utility to deploy each AsDatabase. For Multidimensional models you will have to use DevEnv.com (Visual Studio) to generate the AsDatabase file.
Publish-Cube can also be used to automate the deployment of cubes as part of a server deployment using tools such as Octopus Deploy or Azure DevOps Release Manager.
Install from PowerShell gallery using:
Install-Module -Name DeployCubeThe following pre-requisites need to be installed for Publish-Cube to work properly.
Microsoft.AnalysisServices.Deployment.exeMicrosoft.AnalysisServices.Deployment.exe is known as the Analysis Services Deployment Utility which is installed with SQL Server Managment Studio (SSMS).
The module also requires the Microsoft SQL Server PowerShell module SqlServer which is installed automatically.
Custom install directories for Microsoft.AnalysisServices.Deployment.exe are now supported. Please set the environment variable CustomAsDwInstallLocation prior to running any functions.
In order to successfully deploy a tabular cube, the process running Publish-Cube needs to run under a service account that has admin privileges on your target Azure Analysis Services or SSAS instance. If you wish to use a specific Windows account, all of the relevant functions have UserID / Password parameters.
Full documentation for all the functions in this module are provided below. Here's a quick start guide.
Publish-Cube -AsDatabasePath "C:\Dev\YourCube\bin\Debug\YourCube.asdatabase" -Server "YourCubeServer"Where -AsDatabasePath is the path to your tabular or multidimensional model, and -Server is the name of the target server (including instance and port if required). The above is the minimum set of parameters that can be used with Publish-Cube.
Normally, the database will be named the same as your AsDatabase file (i.e. YourCube in the example above). However, by adding the -CubeDatabase parameter, you can change the name of your deployed cube to be anything you like.
Publish-Cube -AsDatabasePath "C:\Dev\YourCube\bin\Debug\YourCube.asdatabase" -Server "YourCubeServer" -CubeDatabase "YourNewCubeName"As part of the deployment you can specify a processing option. Valid processing options are: ProcessFull, ProcessDefault and DoNotProcess. However, it is strongly recommended that you use default "DoNotProcess" option as the connection to your source database may not be correct and need adjustment post-deployment.
Publish-Cube -AsDatabasePath "C:\Dev\YourCube\bin\Debug\YourCube.asdatabase" -Server "YourCubeServer" -ProcessingOption "DoNotProcess"Finnally, if there are multiple versions of the Analysis Services Deployment Utility (Microsoft.AnalysisServices.Deployment.exe) are installed on your build agent, you can specify which version should be used with the -PreferredVersion option.
Publish-Cube -AsDatabasePath "C:\Dev\YourCube\bin\Debug\YourCube.asdatabase" -Server "YourCubeServer" -PreferredVersion latestThe following is a list of commands provided by this module once you have installed the package (see Installation above)
| Function | Description |
|---|---|
| Find-AnalysisServicesDeploymentExeLocations | Lists all locations of Microsoft.AnalysisServices.Deployment.exe on the host |
| Get-AnalysisServicesDeploymentExePath | Returns the path of a specific version of Microsoft.AnalysisServices.Deployment.exe |
| Get-CubeDatabaseCompatibilityLevel | Returns the CompatibilityLevel of a deployed cube database |
| Get-ServerMode | Returns the mode of the server: Tabular or Multidimensional |
| Get-SqlAsPath | Returns the path to a specific cube database SQLSERVER:\SQLAS\YourServer\DEFAULT\Databases\YourCubeDatabase |
| Get-ModuleByName | Loads the named PowerShell module, installing it if required |
| Get-SqlConnectionString | Helper function to create valid SQL Server database connection strings |
| Get-SsasProcessingMessages | Examines the XML returned by the Invoke-AsCmd function to find errors. Writes error message if errors are found |
| Invoke-ProcessTabularCubeDatabase | Processes an SSAS database on a SQL Server SSAS instance |
| Invoke-ExternalCommand | Helper function to run command-line programs |
| Ping-SsasDatabase | Returns true if the specified SSAS database exists on the server |
| Ping-SsasServer | Returns true if the specified SSAS server exists |
| Publish-Cube | Publish-Cube deploys a tabular or multidimentional cube to a SQL Server Analysis Services instance |
| Select-AnalysisServicesDeploymentExeVersion | Finds a specific version of the Microsoft.AnalysisServices.Deployment.exe if more than one present on the host |
| Unpublish-Cube | Drops a Tabular or Multidimensional cube from the specified server |
| Update-AnalysisServicesConfig | Updates the various config files (listed below) which are needed to deploy the cube |
| Update-TabularCubeDataSource | Updates the cube's connection to the source SQL database. |
For CI senarios, Publish-Cube has been packaged as an extension for Azure Pipelines available from the marketplace here: Deployment tools for SSAS Tabular Cube Models
An example tabular model is provided as a Visual Studio solution alongside a SQL database which acts as the cubes source. The DACPAC is provided in the .\example folder. You can use this to test that deployments work correctly. Note that the SSDT Visual Studio solution is configured to deploy to SQL Server 2016. Open the Visual Studio solution and change the target version and rebuild the solution if you have a different version of SQL Server installed.
If you are facing problems in making this PowerShell module work, please report any problems on DeployCube GitHub Project Page.