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.
![Tabular project](/tipimages2/3415_1.jpg)
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](/tipimages2/3415_2.jpg)
Make sure to specify the correct instance name in the Server Properties in the Deployment Server section.
![Instance information](/tipimages2/3415_3.jpg)
Right click on the AdventureWorks Tabular Model and select deploy.
![Deploy tabular project](/tipimages2/3415_4.jpg)
If everything is OK, you will get a Success Window with the number of rows transferred as shown below.
![Deploy success message](/tipimages2/3415_5.jpg)
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](/tipimages2/3415_6.jpg)
The database name is AdventureWorks Tabular Model SQL 2012.
![Tabular database](/tipimages2/3415_7.jpg)
If you expand the objects, you will be able to see the connections, tables and roles.
![Tabular database expanded](/tipimages2/3415_8.jpg)
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](/tipimages2/3415_9.jpg)
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](/tipimages2/3415_10.jpg)
![show tabular tables](/tipimages2/3415_11.jpg)
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](/tipimages2/3415_12.jpg)
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](/tipimages2/3415_13.jpg)
You will receive this message while processing.
![Processing in progress](/tipimages2/3415_14.jpg)
Once done, you can verify the properties of the promotion table for the last processed option using SSMS.
![Table properties](/tipimages2/3415_15.jpg)
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](/tipimages2/3415_16.jpg)
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](/tipimages2/3415_17.jpg)
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](/tipimages2/3415_18.jpg)
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](/tipimages2/3415_19.jpg)
In the Process Database Window, select the Script Action to New Query Window option and close the window.
![Script action](/tipimages2/3415_20.jpg)
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](/tipimages2/3415_21.jpg)
To run the PowerShell script run this command:
Invoke-ASCmd - InputFile:"c:\tabular\runscript.xmla"
![Run a script](/tipimages2/3415_22.jpg)
You will get an XML message like this one indicating that everything is OK.
![Process results](/tipimages2/3415_23.jpg)
To verify that everything worked, in SSMS right click on the database and select properties.
![Database properties](/tipimages2/3415_24.jpg)
Verify the Last Processed date:
![Last processed information](/tipimages2/3415_25.jpg)
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](/tipimages2/3415_26.jpg)
You will receive the following message if everything is OK.
![Integration Service Project](/tipimages2/3415_27.jpg)
You can also verify that the backup was created in the c:\tabular folder.
![backup tabular file](/tipimages2/3415_28.jpg)
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](/tipimages2/3415_29.jpg)
To verify that the group was added to the role, in SSMS right click on the Admins role.
![Tabular roles](/tipimages2/3415_30.jpg)
Verify that the paladin\SSAS Users group was created.
![TAbular role members](/tipimages2/3415_31.jpg)
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:
- http://msdn.microsoft.com/en-us/library/hh758425.aspx
- http://blogs.msdn.com/b/cathyk/archive/2011/08/25/managing-tabular-models-using-powershell.aspx
- SQL Server Business Intelligence Tips
About the author
![MSSQLTips author Daniel Calbimonte](/images/DanielCalbimonte.jpg)
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips