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:
