Udostępnij za pośrednictwem

What is new in SQL Server Analysis Services 2016 RC0

  • Artykuł

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.

image001

Display folders show up in Excel’s field list:

image002

And in the Power BI desktop field list, when you use the February update:

image003

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:

cd SQLAS\

And then connect to the server:

cd [SERVERNAME]

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:

image004

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:

image005

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:

image006

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.

Download now!

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.

Comments

  • Anonymous
    March 09, 2016
    What are the chances we'll see the HideMemberIf for ragged hierarchies in Tabular added to SSAS 2016?
    • Anonymous
      March 25, 2016
      No this won't come at the SQL 16 timeframe.
  • Anonymous
    March 17, 2016
    Display folders: Great !
  • Anonymous
    March 21, 2016
    Lot of improvements and changes in SQL server 2016 over previous one.
  • Anonymous
    March 31, 2016
    Any possibility of seeing the functionality to create a DRILLTHROUGH ACTION for Tabular? It exists today for multidimensional, and I've found Open source software that already does this for tabular.. but I would rather use a solution provided by Microsoft as opposed to open source.
    • Anonymous
      November 30, 2016
      Hi Sherri, sorry for the delay in getting back to you :) We are actually working on this. Expect it soon!