Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

PowerShell Commands for SQL Server Analysis Services Tabular Mode


By:   |   Last Updated: 2014-12-12   |   Comments   |   Related Tips: > 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:

  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.

 ls

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.

 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:



Last Updated: 2014-12-12


next webcast button


next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools