Any suggestions? Export (0) Print
Expand All

What's New in Analysis Services

 

Applies To: SQL Server 2016 Preview

needhelp Need help?  MSDN forum  |  stackoverflow  |  Log an issue or suggestion at Microsoft Connect

SQL Server 2016 Analysis Services (SSAS) feature updates are released incrementally. [CTP 3.3] indicates updates released in SQL Server 2016 Community Technical Preview 3.3.

System_CAPS_noteNote

For CTP releases of Analysis Services, make sure that server, client tools (SSDT and SSMS), and data providers (especially AMO) are from the same CTP release. You can run Setup to upgrade server instances but earlier CTP-version client tools should be reinstalled. If you have a custom AMO-dependent application, you might need to install an updated version of AMO. For instructions and more information, see Install Analysis Services data providers (AMO, ADOMD.NET, MSOLAP).

It's now possible to upgrade an 1100 or 1103 Tabular model to 1200 when the model contains pasted tables. We recommend using SQL Server Data Tools to upgrade a model. In SSDT, set CompatibilityLevel to 1200 and then deploy to a SQL Server 2016 instance of Analysis Services. See Compatibility Level for Tabular models in Analysis Services for details.

In Management Studio, script is now enabled for database commands, including Create, Alter, Delete, Backup, Restore, Attach, Detach. Output is Tabular Model Scripting Language (TMSL) in JSON. See Tabular Model Scripting Language (TMSL) Reference for XMLA for more information.

SSIS Analysis Services Execute DDL Task now also accepts tabular model scripting language commands. Other SSIS-SSAS tasks will be supported in a future CTP to use the new tabular metadata (exceptions will be called out in the release notes).

SSAS PowerShell cmdlet Invoke-ASCmd now accepts tabular model scripting language commands. Other SSAS PowerShell cmdlets will be updated in a future CTP to use the new tabular metadata (exceptions will be called out in the release notes). See Analysis Services PowerShell Reference for details.

See DirectQuery Mode (SSAS Tabular) for more information.

  • Row level security (RLS) is now supported for DirectQuery models at compatibility level 1200. Previously, the presence of RLS prevented customers from running a Tabular model in DirectQuery mode.

  • Calculated columns are now supported for DirectQuery models at compatibility level 1200. Previously, the presence of calculated columns prevented customers from running a Tabular model in DirectQuery mode.

  • Performance optimization: redundant join elimination for VertiPaq and DirectQuery.

You can now store translated metadata in a Tabular 1200 model. Metadata in the model includes fields for Culture, translated captions, and translated descriptions. To add translations, use the Model > Translations command in SQL Server Data Tools. See Translations in Tabular models (Analysis Services) for details.

Scripts can be generated in SSMS to easily automate most of the administrative tasks performed in the tool. In CTP 3.2 you can now generate script in SSMS for tabular models at compatibility level 1200. Currently, the following tasks can be scripted: Process at any level, plus CREATE, ALTER, DELETE at the database level. Script is in TMSL (a JSON format).

Functionally, TMSL is equivalent to the XMLA ASSL extension that provides multidimensional object definitions, except that TMSL uses native descriptors like model, table, and relationship to describe tabular metadata. See Tabular Model Scripting Language (TMSL) Reference for XMLA for details about the schema.

To use this feature, you must download the version of SSMS that ships concurrently with CTP 3.2 and connect to a CTP 3.2 version of an Analysis Services in tabular mode.

A generated JSON-based script for a tabular model might look like the following:

{
  "create": {
    "database": {
      "name": "AdventureWorksTabular1200",
      "id": "AdventureWorksTabular1200",
      "compatibilityLevel": 1200,
      "readWriteMode": "readWrite",
      "model": {}
    }
  }
}

The payload is a JSON document that can be as minimal as the example shown above, or highly embellished with the full set of object definitions. Tabular Model Scripting Language (TMSL) Reference for XMLA describes the syntax.

At the database level, CREATE, ALTER, and DELETE commands will output TMSL script in the familiar XMLA window. Other commands, such as Process, can also be scripted in this release. Script support for many other actions is still pending and will be added in subsequent CTPs.

The following commands are currently available in TMSL script:

Scriptable commands

Description

create

Adds a database, connection, or partition. The ASSL equivalent is CREATE.

createOrReplace

Updates an existing object definition (database, connection, or partition) by overwriting a previous version. The ASSL equivalent is ALTER with AllowOverwrite set to true and ObjectDefinition to ExpandFull.

delete

Removes an object definition. ASSL equivalent is DELETE.

refresh

Processes the object. ASSL equivalent is PROCESS.

A calculated table is a model-only construction based on a DAX expression or query in SSDT. When deployed in a database, a calculated table is indistinguishable from regular tables.

There are several uses for calculated tables, including the creation of new tables to expose an existing table in a specific role. The classic example is a Date table that operates in multiple contexts (order date, ship date, and so forth). By creating a calculated table for a given role, you can now activate a table relationship to facilitate queries or data interaction using the calculated table. Another use for calculated tables is to combine parts of existing tables into an entirely new table that exists only in the model. See Create a Calculated Table (SSAS Tabular) to learn more.

DirectQuery is now available for tabular models that operate at the 1200 compatibility level. In SQL Server 2016, DirectQuery has different properties and capabilities for tabular 1200 models. Most noteworthy is the extra control over defining sample datasets used for model design and testing. See DirectQuery Mode (SSAS Tabular) to learn more.

System_CAPS_noteNote

Now that DirectQuery is turned on for tabular 1200 models, you might want to upgrade an existing 1103 model to 1200 to use the new features. A direct upgrade is not supporteds, but the workaround is easy. Simply open the model in SSDT, set the DirectQuery Mode property to Off, set Compatibility Level to 1200, and then turn DirectQuery back on again.

CTP 3.2 enables bi-directional cross filters by default for tabular models at the 1200 compatibility level in SSDT. Filters are only auto-generated when the direction can be established with a high degree of certainty. If there is ambiguity in the form of multiple query paths across table relationships, a filter won't be created automatically. See Bi-directional cross filters for tabular models in SQL Server 2016 Analysis Services for details.

System_CAPS_noteNote

Be sure to upgrade the SSAS tabular engine if you upgrade to CTP 3.1 tools. Schema changes to the tabular object model require that both engine and tools are from the same build.

This release adds three new capabilities for tabular model design in the version of SSDT for Visual Studio 2015 that ships concurrently with CTP 3.1:

  • Upgrade a Tabular 1100 or 1103 model to 1200

    Object metadata is modified to the new JSON format, which will require the JSON editor (see steps below) to see.

    After upgrading, the model can be deployed only on a SQL Server 2016 instance of Analysis Services.

    In Solution Explorer, right-click Model.bim > Properties > Compatibility Level. Change the compatibility level from 1100 or 1103 to 1200.

  • Roles in SSDT

    You can now define roles for Tabular models at the 1200 compatibility level in the Tabular model designer in SSDT.

  • JSON editor for BIM files

    The Code View in Visual Studio 2015 now renders the BIM in JSON format for Tabular models at the 1200 compatibility level. The version of Visual Studio determines whether the BIM file is rendered in JSON via the built-in JSON Editor, or as simple text.

    To use the JSON editor, with the ability to expand and collapse sections of the model, you will need the CTP 3.1 version of SQL Server Data Tools plus the Visual Studio 2015 (any edition, including the free Community edition). For all other versions of SSDT or Visual Studio, the BIM file is rendered in JSON as simple text.

    1. Install the free Visual Studio 2015 Community or a higher edition to get the latest JSON editor.

    2. Install the version of SSDT for Visual Studio 2015 that ships concurrently with CTP 3.1 to get the latest updates to the Analysis Services project templates, the Tabular editor, and the Analysis Management Objects (AMO) data provider.

    3. Start Visual Studio 2015 Community edition and open, upgrade, or create a Tabular model at the 1200 compatibility level.

      By default, Visual Studio 2015 uses 1103 as the default compatibility level so make point of switching to 1200.

    4. For a workspace server, specify a SQL Server 2016 Analysis Services instance in Tabular mode. You must use a CTP 3.1 version of the server if you are using CTP 3.1 versions of the tools.

    5. In Solution Explorer, right-click Model.bim to select View Code.

    At a minimum, an empty model will contain the following JSON. For more information, see Tabular Model Scripting Language (TMSL) Reference for XMLA.

    {
      "name": "SemanticModel",
      "id": "SemanticModel",
      "compatibilityLevel": 1200,
      "readWriteMode": "readWrite",
      "model": {}
    }
    
    System_CAPS_importantImportant

    Avoid editing the JSON directly. Doing so can corrupt the model.

You can now use the version of SSMS that ships concurrently with CTP 3.1 to create, process, and merge partitions for Tabular models at the 1200 compatibility level.

Data sources supported for Tabular 110x models in DirectQuery mode now include Oracle, Teradata and Microsoft Analytics Platform (formerly known as Parallel Data Warehouse).

Starting in CTP 3.0, you no longer need two versions of SSDT for building relational and BI projects. SQL Server Data Tools for Visual Studio 2015 adds project templates for Analysis Services solutions, including Analysis Services Tabular Projects used for building models at the 1200 compatibility level. Other Analysis Services project templates for multidimensional and data mining solutions are also included, but at the same functional level (1100 or 1103) as in previous releases.

Updates to the formula bar help you write formulas with more ease by differentiating functions, fields and measures using syntax coloring, it provides intelligent function and field suggestions and tells you if parts of your DAX expression are wrong using error 'squiggles'. It also allows you to use multiple lines (Alt + Enter) and indentation (Tab). The formula bar now also allows you to write comments as part of your measures, just type “//” and everything after these characters on the same line will be considered a comment.

For Tabular 1200 models, metadata operations in SSDT ware much faster. By way of comparison, on the same hardware, creating a relationship on a model set to the SQL Server 2014 compatibility level (1103) with 23 tables takes 3 seconds, whereas the same relationship on a model created set to the SQL Server 2016 compatibility level (1200) takes just under a second.

With formula fixup on a Tabular 1200 model, SSDT will automatically update any measures that is referencing a column or table that was renamed.

You can now save incomplete DAX measures directly in a Tabular 1200 model project and pick it up again when you are ready to continue.

To support multiple environments, like Test and Pre-production environments, Visual Studio allows developers to create multiple project configurations using the configuration manager. Multidimensional models already leverage this but Tabular models do not. Starting in CTP 3.0 you can now use the Configuration manager to deploy to different servers.

In SQL Server 2016, an Analysis Services instance in Tabular server mode can run Tabular models at any compatibility level (1100, 1103, 1200). In CTP 3.0 and later, SQL Server Management Studio is updated to display properties and provide database model administration for Tabular models at the 1200 compatibility level.

Starting in CTP 3.0, Analysis Services Management Objects (AMO) has been re-factored to include a second assembly, Microsoft.AnalysisServices.Core.dll. The new assembly separates out common classes like Server, Database, and Role that have broad application in Analysis Services, irrespective of server mode.

Previously, these classes were part of the original Microsoft.AnalysisServices assembly. Moving them to a new assembly paves the way for future extensions to AMO, with clear division between generic and context-specific APIs.

Existing applications are unaffected by the new assemblies. However, should you choose to rebuild applications using the new AMO assembly for any reason, be sure to add a reference to Microsoft.AnalysisServices.Core.

Similarly, PowerShell scripts that load and call into AMO must now load Microsoft.AnalysisServices.Core.dll. Please update any scripts prior to pointing them to a CTP 3.0 version of SQL Server 2016 Preview Analysis Services.

System_CAPS_noteNote

In CTP 3.0, AMO cannot be used to author new tabular 1200 models. In a subsequent CTP, AMO will be extended with a tabular object model that can be used for that purpose.

Database Consistency Checker (DBCC) runs internally to detect potential data corruption issues on database load, but can also be run on demand if you suspect problems in your data or model. DBCC runs different checks depending on whether the model is tabular or multidimensional. See Database Consistency Checker (DBCC) for Analysis Services tabular and multidimensional databases for details.

CTP 2.3 adds a graphical user interface to SQL Server Management Studio to configure and manage Analysis Services Extended Events. You can set up live data streams to monitor server activity in real time, keep session data loaded in memory for faster analysis, or save data streams to a file for offline analysis. For more information, see Monitor Analysis Services with SQL Server Extended Events and Using extended events with Analysis Services (Guy in a Cube blog post and video).

SQL Server 2016 CTP 2.3 includes new includes support for variables in DAX. Variables can now store the result of an expression as a named variable, which can then be passed as an argument to other measure expressions. Once resultant values have been calculated for a variable expression, those values do not change, even if the variable is referenced in another expression. For more information, see VAR Function.

For SQL Server 2016 CTP 2.3, DirectQuery now generates simpler queries that provide better performance.

SQL Server 2016 Analysis Services (SSAS) includes new parallel processing functionality for tables with two or more partitions, increasing processing performance. There are no configuration settings for this feature. For more information about configuring partitions and processing tables, see Tabular Model Partitions (SSAS Tabular).

Analysis Services administrators can now use SQL Server Management Studio to configure computer accounts to be members of the Analysis Services administrators group. In the Select Users or Groups dialog, set the Locations for the computers domain and then add the Computers object type. For more information, see Configure Analysis Services Server Administrators.

Installation of Power Pivot for SharePoint is simplified. You select Analysis Services from the standard Instance Feature list and then from a single setup page you select the Analysis Services server mode and configure administrators.

For more information, see Install Analysis Services in Power Pivot Mode.

The following elements have been added to the TProperty complex type defined in the [MS-CSDLBI] 2.0 schema:

Element

Definition

DefaultValue

A property that specifies the value used when evaluating the query. The DefaultValue property is optional, but it is automatically selected if the values from the member cannot be aggregated.

Statistics

A set of statistics from the underlying data that is associated with the column. These statistics are defined by the TPropertyStatistics complex type and are provided only if they are not computationally expensive to generate, as described in section 2.1.13.5 of the Conceptual Schema Definition File Format with Business Intelligence Annotations document.

needhelp Need help?  MSDN forum  |  stackoverflow  |  Log an issue or suggestion at Microsoft Connect

Show:
© 2016 Microsoft