Skip to content

Publish / Deploy a Tabular or Multidimensional Cube to SSAS or AAS

License

Notifications You must be signed in to change notification settings

DrJohnT/DeployCube

Repository files navigation

PowerShell Gallery Version Build status

DeployCube

Deploy a SSAS Tabular or Multidimensional cube using the Analysis Services Deployment Utility

Overview

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.

Installation

Install from PowerShell gallery using:

Install-Module -Name DeployCube

Pre-requisites

The following pre-requisites need to be installed for Publish-Cube to work properly.

Microsoft.AnalysisServices.Deployment.exe

Microsoft.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.

Admin privileges required for deployment

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.

Getting Started

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 latest

List of commands

The 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.

Azure DevOps Agent

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

Example Tabular Model

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.

Issue Reporting

If you are facing problems in making this PowerShell module work, please report any problems on DeployCube GitHub Project Page.