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

 

New features for SQL Server Analysis Services Tabular Databases in PowerShell


By:   |   Last Updated: 2016-10-12   |   Comments (1)   |   Related Tips: > Analysis Services Administration

Problem

I would like to know if there are new PowerShell features in SQL Server 2016 related to Tabular Databases.

Solution

There are new PowerShell features for Tabular Databases in SQL Server 2016 and in this tip we will cover the following:

  • How to use the Invoke-ProcessASDatabase cmdlet
  • How to execute TMSL with Invoke-ASCmd
  • How to automate Tabular Database tasks with PowerShell using SQL Server Agent
  • How to use Invoke-ProcessTable

Requirements

  • SQL Server 2016
  • SSDT installed

How to use the Invoke-ProcessASDatabase cmdlet

We will start with the Invoke-ProcessASDatabase cmdlet. This cmdlet is for Tabular and Multidimensional Databases. We will see how to use it in Tabular Databases. 

Start PowerShell.

Starting PowerShell

In PowerShell, go to sqlserver and change directories to the tabular databases:

sqlserver:
cd '\sqlas\YourInstanceName\tabular\databases'

The following command will list the name of the Tabular databases, the compatibility level and the last time the database was processed:

ls | select name, compatibilitylevel, lastprocessed

showing compatibility level in PowerShell

If the compatibility level is 1200 there is a new parameter we can use named RefreshType. This is used in Tabular databases to show the Process Type for this new level. Some possible values for this parameter are:

  • Full
  • ClearValues
  • Automatic
  • Defragment

Use the following command to process the Tabular Database:

Invoke-ProcessASDatabase "TabularProject7" -RefreshType "Full""

This is the result if everything is OK:

Processing Tabular Databases in PowerShell

How to execute TMSL with Invoke-ASCmd

Invoke-ASCmd now supports the Tabular Model Scripting Language (TMSL). This is a new language introduced and supported in SQL Server 2016. It is simpler than XMLA (the old DDL language used) and it is based on JSON.

We will show how to execute a JSON file using Invoke-ASCmd.

First, we will create a TMSL script named sample.json in the c:\sql folder:

{
"alter": {   
    "object": {   
      "database": "TabularProject7"  
    },   
    "database": {   
      "name": "MSSQLTIPS",   
    }   
  }
 }

The script changes the tabular database name from TabularProject7 to MSSQLTIPS. With TMSL, you can process, backup, alter, create and drop Tabular Objects.

In order to execute the JSON script, run the following commands in PowerShell:

Invoke-ASCmd –InputFile "C:\sql\sample.json" -Server "YourInstanceName\TABULAR" 

Invoke-ASCmd is used to run a command for Analysis Services and InputFile is used to specify the JSON script and Server is used to specify the instance.

The results displayed will be similar to this:

running TMSL scripts

If everything is OK, the Tabular Database name will be changed to MSSQLTIPS:

list of Tabular Databases

How to automate a Tabular Database task with PowerShell using SQL Server Agent

We will use SQL Server Agent to automate tasks in PowerShell for Tabular Databases. In this example, we will create a database backup in SQL Server Agent using PowerShell.

In SQL Server Management Studio (SSMS), select the database to backup, right click and select Back Up...:

Backup Tabular Databases

Unselect the Encrypt backup file option to simplify the backup process (encrypting the backup is a best practice, but it is not part of this tip):

Backup properties

Press Script to generate a script and select Script Action to New Query Window:

Generate scripts

The script generated will be in JSON format:

{
  "backup": {
    "database": "MSSQLTIPS",
    "file": "MSSQLTIPS.abf",
    "allowOverwrite": false,
    "applyCompression": true
  }
}

We will modify the path to store in our SQL folder. By default, the tabular backups are stored in a path similar to this: C:\Program Files\Microsoft SQL Server\MSAS13.TABULAR\OLAP\Backup, but we changed to c:\\sql. Notice the use of double backslashes.

Save the script in a file named backupTabular.json:

{
  "backup": {
    "database": "MSSQLTIPS",
    "file": "c:\\sql\\MSSQLTIPS.abf",
    "allowOverwrite": false,
    "applyCompression": true
  }
}

In SSMS, connect to your SQL Server instance and under SQL Server Agent > Jobs, right click and select New Job...:

Creating jobs

Specify a name for the job and optionally enter a description:

Job properties

Go to Steps and press New... to create a new step:

Job steps

You could run a TMSL script using the SQL Server Analysis Services Command in SQL Server 2016, which is a new feature:

step properties

However, in this tip we will run PowerShell. Select PowerShell as the Type and enter the following commands in the Command section of the interface:

sqlserver:
Invoke-ASCmd –InputFile "C:\sql\backupTabular.json" -Server "YourServer\YourInstance"  

The script will backup the tabular database using the backup script generated before.

PowerShell step

By default, the job will run with the SQL Server Agent account. This account may not have permission to create a file in the folder specified, so you may need to use a proxy account that has the necessary permissions. For more information about proxies and credentials refer to this tip about using proxies. To create a credential, in the Database Engine, go to Security > Credential and assign a Windows user with permissions to the folder where the backup will be created:

SQL Credential

To assign a credential to a proxy, go to SQL Server Agent > Proxies and assign the credential created above for the proxy:

SQL Proxy

Return to the SQL Server Agent Job and in the SQL Job Steps, in the Run as, use the proxy that you just created and save the job.

Using proxy in the step

To run the job, right click the job and select Start Job at Step...:

Start a job

If everything is OK, the job will create a backup named MSSQLTIPS.abf file in the specified folder:

Tabular backup

SQL Server Agent does not show the detail errors when running PowerShell scripts. If you have errors when running the job, use PowerShell to run the job to see if it works. If it works, but fails in SQL Server Agent the problem might be the job permissions.

How to use Invoke-ProcessTable

To process Tabular Tables, there is a new cmdlet named Invoke-ProcessTable. In the past, to process tables we used the Invoke-ProcessDimension. Now there is an exclusive cmdlet for Tabular Tables.

The following example shows how to process a Tabular Table:

Invoke-ProcessTable -TableName "DimCustomer" -Database "TabularProject8" -RefreshType "Full" 

Where DimCustomer is the Tabular Table and TabularProject8 is the database. We are doing a Process Full.

Conclusion

In this tip, we learned how to use the Invoke-ProcessASDatabase cmdlet, how to run TMSL using Invoke-ASCmd, how to automate Tabular Tasks using SQL Server Agent and PowerShell and how to process a Tabular table.

Next Steps


Last Updated: 2016-10-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.



    



Wednesday, July 25, 2018 - 9:34:58 AM - Eduardo Back To Top

Can i process many tables in the same script?
Like an array or something like that:

-TableName [table1, table2]


Learn more about SQL Server tools