With the release of SQL Server 2016 RC0, we’ve added more capability for modeling and managing Tabular models set at the SQL Server 2016 compatibility level (1200). RC0 brings us one step closer to general release by adding display folders, full-fledged PowerShell support for Tabular 1200 models and instances, SSIS administration of SSAS Tabular workloads, and a new Tabular Object Model namespace in AMO.
Support for Display folders in Analysis Services
Tables can contain hundreds of individual columns or measures. With SQL Server 2016 RC0, modelers can organize them into user-defined folders to view and manage these attributes. These folders are called display folders and are also supported by Excel and Power BI Desktop.
You can now set the display folder on any measure or column in SSDT, using the latest SQL Server Data Tools Preview in Visual Studio 2015 release that coincides with the RC0 drop.
Display folders show up in Excel’s field list:
And in the Power BI desktop field list, when you use the February update:
For this RC, there is a known issue that the display folder won’t show up immediately in Excel. Please see the release notes for more details and a workaround.
PowerShell support for SQL Server 2016 Tabular models
With RC0, you can now use all the Analysis Services PowerShell cmdlets against a Tabular model set to the SQL Server 2016 compatibility level (1200). We also introduced two new cmdlets: Invoke-ProcessASDatabase and Invoke-ProcessTable.
Let’s see how we can use these PowerShell improvements when connecting to a Tabular model. To start, I run PowerShell and then enter the following command that loads the SQL PowerShell environment:
“Import-Module sqlps -DisableNameChecking”
With this loaded, you can now connect to your SQL Server 2016 Analysis Services tabular instance. First, switch to the SQLAS provider:
And then connect to the server:
After connecting to the server, you can navigate Tabular objects. We will automatically detect if the Tabular model is at the 1200 compatibility level and then show a tabular representation:
Tabular objects can now be used in the many cmdlets available for SSAS. You can find the complete list here in the documentation.
SSIS support for SQL Server 2016 Tabular models
With RC0 you can now use all the SSIS task and destinations against a SQL Server 2016 Tabular model. SSIS tasks have been updated to represent tabular objects instead of multidimensional objects. For example, with the latest tools, if you want to select objects to process, the Processing Task will automatically detect if the model is a Tabular 1200 compatibility model and give us Tabular objects rather than measuregroups and dimensions:
The same goes for the Partition processing destination, as it now also shows Tabular objects and also supports pushing data into a partition.
The Dimension processing destination will not work for Tabular models with the SQL 2016 compatibility level as the Processing task or Partition task are sufficient to schedule tabular processing. The Execute DDL task was already updated in CTP 3.3 to receive TMSL scripts.
Tabular Object model for SQL Server 2016 Tabular models
The new Tabular Object Model (TOM) is part of Analysis Management Objects (AMO), the complete library of programmatically accessed objects that enables an application to manage a running instance of Microsoft SQL Server Analysis Services. With the Tabular Object Model you can now use a concepts familiar to the Tabular developer instead of using Multidimensional concepts. This allows for simpler and more readable code when developing against a Tabular 1200 model.
A high-level picture of the new TOM API looks like this:
Here is a small code snippet that shows how to refresh a single table:
public void RefreshTable()
var server = new Server();
server.Connect(ServerConnectionString); //Connect to the server.
Database Db = server.Databases[“TMDB”]; //Connect to the DB
Model m = Db.Model; //Get the model
m.Tables[“Sales”].RequestRefresh (RefreshType.Full); //Mark the Sales table to be refreshed.
m.SaveChanges(); //Commit the changes
You can find more information in the documentation. Content is mostly reference docs right now, with more details to come over the next several weeks. To download TOM you can download “SQL_AS_AMO.msi” from the SQL 2016 RC0 feature pack here.
To get started, download SQL Server 2016 RC0. The corresponding tools, SSDT February 2016 for Visual Studio 2015 are also available for download. If you already have an earlier version of SSDT, you can install the latest version over it.