PowerShell Commands for SSAS Tabular Mode

By: Daniel Calbimonte | Comments | Related: > Analysis Services Administration

Problem

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.

Solution

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

SQL Server 2012 or 2014 (I used SQL 2014 for this tip)
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).
You have to install a Tabular Instance. For more information about Tabular installations, refer to this link.
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.

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.

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

Right click on the AdventureWorks Tabular Model and select deploy.

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

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.

The database name is AdventureWorks Tabular Model SQL 2012.

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

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.

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.

ls

PowerShell Command to Process a Tabular Table

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

 Invoke-ProcessDimension

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.

You will receive this message while processing.

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

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

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.

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"

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.

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

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.

To run the PowerShell script run this command:

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

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

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

Verify the Last Processed date:

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

You will receive the following message if everything is OK.

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

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"

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

Verify that the paladin\SSAS Users group was created.

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:

About the author

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

PowerShell Commands for SQL Server Analysis Services Tabular Mode