PowerShell Commands for SQL Server Analysis Services Tabular Mode

By:   |   Comments   |   Related: > Analysis Services Administration


Tabular Mode for Analysis Services was introduced in SQL Server 2012 and PowerShell for Analysis Services is also supported in SQL 2012 and later versions. In this tip, we will look at how to automate some tasks for Tabular Databases using PowerShell.


Below the are the requirements to get started with this tip:

  1. SQL Server 2012 or 2014 (I used SQL 2014 for this tip)
  2. Download the Adventure Works Tabular Model SQL Server 2012 sample (I could not find a Tabular sample for SQL Server 2014, but that may change in the future).
  3. You have to install a Tabular Instance. For more information about Tabular installations, refer to this link.
  4. SSDT for Business Intelligence should be installed.

Install SQL Server 2012 AdventureWorks Tabular Model Sample Database

In order to start, download the Tabular Model SQL Server 2012 sample listed in the requirements.

Double click on the AdventureWorks Tabular Model SQL 2012 project.

Tabular project

Make sure that the Instance name is specified. To do this, go to the Solution Explorer, select the AdventureWorks Tabular Model and right click on Properties.

Tabular properties

Make sure to specify the correct instance name in the Server Properties in the Deployment Server section.

Instance information

Right click on the AdventureWorks Tabular Model and select deploy.

Deploy tabular project

If everything is OK, you will get a Success Window with the number of rows transferred as shown below.

Deploy success message

Verify Installation of Sample AdventureWorks Database

To verify that the Database was deployed, you can open SQL Server Management Studio (SSMS) and connect to the Tabular Instance.

Connect to Tabular model

The database name is AdventureWorks Tabular Model SQL 2012.

Tabular database

If you expand the objects, you will be able to see the connections, tables and roles.

Tabular database expanded

Using PowerShell for Tabular Database Tasks

Now we will look at some things we can do with PowerShell. Open PowerShell for SQL Server as shown below, by running sqlps in the Windows Run application.

Open sql powershell

Viewing Tables for a Tabular Database Using PowerShell

In PowerShell, the structure and commands for tabular databases are very similar to those used for multidimensional databases (a non tabular database). In a Tabular Database, you have the database, the tables and roles. In PowerShell, the tables are stored in the dimension section. In order to see the tabular tables, move to the dimensions using the following PowerShell command:

 cd sqlas\infra4\instancia2\database\adventureworks tabular model sql 2012\dimensions

Where "sqlas" refers to SQL Server Analysis Services, "infra4\instancia2" is the instance name and "adventureworks tabular model sql 2012" is the name of the database shown above.

Get a list of all Dimensions for a Tabular Database Using PowerShell

Run the ls command to see all the dimensions and compare with the tables listed above. As you can see, you have the tables of the tabular model in this path.


Integration Service Project

show tabular tables

PowerShell Command to Process a Tabular Table

To process a tabular table, we are going to use the Invoke-ProcessDimension.


process a dimension

You will be prompted for the Name of the Dimension which will be Promotion and the name of the Database. You will also be asked the ProcessType which can be a Process Full, which processes everything. For more information about process types, refer to this link.

Second way to process a dimension

You will receive this message while processing.

Processing in progress

Once done, you can verify the properties of the promotion table for the last processed option using SSMS.

Table properties

Verify Dimension Last Processed Date Using PowerShell

If you try to verify the last processed date, you will not be able to see the dimension date change with this command:

 gci | select name,lastprocessed

Last processed information

To see the new Process date, you will need to close and open PowerShell and you will be able to see the LastProcessed date has been updated.

Last processed date updated

PowerShell Command to Process a Tabular Table Without Prompts

You can also run the command to process without prompting for information. The following sample processes the Currency Table with a ProcessFull.

 Invoke-ProcessDimension -name "Currency" -database "Adventueworks Tabular
Model SQL 2012" -ProcessType "ProcessFull"

process another dimension

Create a Script from SSMS to Process a Database with PowerShell

You can also create a script to process the Tabular Database. To do this in SSMS, right click on the Database and select the Process Database option.

Process Database

In the Process Database Window, select the Script Action to New Query Window option and close the window.

Script action

You will have a script similar to this one below. Save the script as runscript.xmla. In this example, we will save the script in the c:\tabular folder.

xmla process script

To run the PowerShell script run this command:

 Invoke-ASCmd - InputFile:"c:\tabular\runscript.xmla"

Run a script

You will get an XML message like this one indicating that everything is OK.

Process results

To verify that everything worked, in SSMS right click on the database and select properties.

Database properties

Verify the Last Processed date:

Last processed information

Backup a Tabular Database with PowerShell

In this example, we will backup a tabular database to the backup.abf file in the c:\tabular\ folder and compress it:

 Backup-ASDatabase c:\tabular\backup.abf "Adventueworks Tabular
Model SQL 2012" -ApplyCompression

backup tabular database

You will receive the following message if everything is OK.

Integration Service Project

You can also verify that the backup was created in the c:\tabular folder.

backup tabular file

PowerShell Command to Add Members to Tabular Roles

You can also add members to Tabular Roles. This sample shows how to add the paladin\SSAS Users group to the Admins Role:

 Add-RoleMember -MemberNane "paladin\SSAS Users" -database "Adventure works Tabular
Model SQL 2012" -rolename "Admins"

Integration Service Project

To verify that the group was added to the role, in SSMS right click on the Admins role.

Tabular roles

Verify that the paladin\SSAS Users group was created.

TAbular role members

As you can see, there many commands that can be used in PowerShell to automate administrative Tabular Database tasks.

Next Steps

For more information, refer to the following links:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms