What's New (Analysis Services)
SQL Server 2012 adds new capability for decision support and data analysis in the enterprise. Use the information in this topic to learn about the new Analysis Services features added in this release.
Important
If you are upgrading from a previous release or comparing this release of Analysis Services against previous versions, be sure to read the backward compatibility section to learn about breaking changes or behavior changes introduced in this release. For more information, see Analysis Services Backward Compatibility.
In this Article
SQL Server 2012 Service Pack 1 (SP1)
SQL Server 2012 What’s New by Feature
SQL Server 2012 Service Pack 1 (SP1)
PowerPivot in Excel:
PowerPivot in Microsoft Excel 2013 supports deeper integration with Excel and your data exploration workflows. For more information on what is new in PowerPivot, see What’s new in PowerPivot in Excel 2013 (https://office.microsoft.com/en-us/excel-help/whats-new-in-powerpivot-in-excel-2013-HA102893837.aspx?CTT=1).
PowerPivot for SharePoint:
A new architecture for SQL Server 2012 SP1 PowerPivot that supports a PowerPivot server outside a SharePoint 2013 farm. The new architecture leverages Excel Services for querying, loading, refreshing, and saving data. The PowerPivot server can still be installed on a server that also hosts SharePoint servers but it is not required. The new architecture is available when you deploy a new PowerPivot server with the slipstream version of SP1. The new architecture and is not available when you deploy the patch version of SP1 onto an existing PowerPivot. For more information, see the following:
The “SQL Server 2012 SP1 Full Installation” section of Overview of SQL Server Servicing Installation.
Install SQL Server BI Features with SharePoint 2013 (SQL Server 2012 SP1).
spPowerpivot.msi:
A Windows Installer package (spPowerpivot.msi) that enhances the PowerPivot for SharePoint experience with additional features such as PowerPivot Gallery, schedule data refresh, and management dashboard. The .msi deploys Analysis Services client libraries, PowerPivot for SharePoint 2013 Configuration, and copies PowerPivot for SharePoint 2013 installation files to SharePoint servers. For more information see Install or Uninstall the PowerPivot for SharePoint Add-in.
Version Compatibility for Tabular models
SQL Server 2012 SP1 introduces new features for Analysis Services running in Tabular mode, including optimized storage for measures and KPIs, extended data categorizations, extended characters, hierarchy annotation, and improved support when importing from Data Market data feeds. In some cases, Tabular model projects being deployed may not be compatible with an Analysis Services deployment server instance. With SP1 applied, you can specify Compatibility Level when creating new Tabular model projects, when upgrading existing Tabular model projects, when upgrading existing deployed Tabular model databases, or when importing PowerPivot workbooks. For more information, see Compatibility Level (SSAS Tabular SP1).
Import from PowerPivot in Excel 2013
You can now import PowerPivot in Excel 2013 workbooks into new Tabular model projects created in SQL Server Data Tools or directly in SQL Server Management Tools.
Note
For a summary of installing SQL Server 2012 SP1 and SQL Server BI features, see Upgrade BI Features to SQL Server 2012 SP1.
SQL Server 2012 What’s New by Feature
Feature Area |
Feature |
|---|---|
Server Instance and Server Monitoring |
Server Modes for Analysis Services Instances: Multidimensional, Tabular, and SharePoint xVelocity In-Memory Analytics Engine (VertiPaq) for Tabular Model Databases Schema Rowsets for Analysis Services in Tabular Mode Event Tracing Infrastructure |
Tabular Modeling |
Tabular Projects in SQL Server Data Tools Tabular Database Administration in SQL Server Management Studio Tabular Model Designer Diagram View Partitions in Tabular Models Security Roles in Tabular Models Row Level Security in Tabular Models Key Performance Indicators in Tabular Models Hierarchies in Tabular Models Large Tables in Tabular Models Images in Tabular Models DirectQuery Mode in Tabular Model Databases Memory Paging in Tabular Models DAX Functions in this Release |
Multidimensional Modeling |
Removal of the 4 Gigabyte Limit on String Storage for MOLAP Engine Resource Usage Reporting for Multidimensional Databases Trace Events for Lock Usage and Contention in Multidimensional Databases DistinctCount Performance Improvement in ROLAP Processing |
PowerPivot for Excel |
PowerPivot for Excel (SQL Server 2012) DAX Functions in this Release |
PowerPivot for SharePoint |
PowerPivot Configuration Tool PowerShell for PowerPivot for SharePoint BI Semantic Model Connection Files in SharePoint PowerPivot for SharePoint Configuration Settings and Server Health Rules PowerPivot Workbook Auto-upgrade to Enable Data Refresh |
Programmability |
DAX Functions in this Release PowerShell for AMO PowerShell for PowerPivot for SharePoint AMO and XMLA Extensions to Support Tabular Modeling CSDL Extensions to Support Tabular Modeling |
Design Tools |
SQL Server Data Tools (SSDT) integration with Visual Studio |
AMO and XMLA Extensions to Support Tabular Modeling
Both multidimensional models and tabular models are based on a unified BI semantic modeling schema (BISM), which is a superset of the schema provided in previous releases as the Unified Dimensional Model (UDM). Therefore, you can work with both types of models by using common APIs (AMO and XMLA), and connect to servers and instances running in either tabular or multidimensional mode by using both ADOMD.NET and OLEDB.
In addition to extending AMO and XMLA to support tabular models, this release includes new properties, methods, and objects to support the requirements, including properties for enhanced reporting and aggregation in reports.
For more information, see Tabular Model Programming. You can also download the AMO samples for tabular modeling from CodePlex.
BI Semantic Model Connection files in SharePoint
You can access a tabular model from within SharePoint using a new type of connection object called a BI semantic connection. A BI semantic connection provides an HTTP endpoint to Analysis Services databases that are deployed on a standalone server, or to an Excel workbook containing PowerPivot data in a SharePoint farm that includes PowerPivot for SharePoint. A BI semantic connection file is a SharePoint content item that specifies the server location of a model database, similar to how Office Data Connection (.odc) files store connection information to external data. Within a SharePoint environment, a BI semantic connection is a supported data source for Power View reports that you design and use in SharePoint. You can also use a BI semantic connection as a data source for PivotTables in Excel. For more information, see PowerPivot BI Semantic Model Connection (.bism).
Conceptual Schema Definition Language (CSDL) Extensions for Tabular Modeling
The Conceptual Schema Definition Language (CSDL) is used to represent a tabular model in response to a query from a client that can consume the schema and use that representation to create visualizations, such as Power View. The bi: extensions to CSDL provided in this release include entities, relationships and data types for tabular models, as well as properties to enhance visualization and model navigation in reports. To learn more about CSDL, see CSDL Annotations for Business Intelligence (CSDLBI).
DAX Functions in this Release
This release introduces new statistical functions, table functions, search functions, Row Level Security functions and more. More than Thirty new functions have been added in all.
DAX is used in both PowerPivot workbooks and tabular projects that you build in SQL Server Data Tools. For more information about which functions are new, see What’s New (PowerPivot for Excel). To review the DAX reference documentation in Books Online, see DAX Function Reference.
DirectQuery Mode in Tabular Models
DirectQuery mode is a deployment option for tabular projects that lets users and reporting clients retrieve data directly from a SQL Server data source. By using DirectQuery mode, you avoid the maintenance overhead of using cached data for a model, and leverage the sophisticated query processing of the underlying database system. This feature also lets you create models and build reports for large data sets that cannot reside in memory. DirectQuery supports a hybrid deployment mode that can use either the cache or the relational source. For more information, see DirectQuery Mode (SSAS Tabular).
DistinctCount Performance Improvement in ROLAP Processing
Queries that include DistinctCount in a ROLAP process can run faster, assuming certain criteria are met. Performance is improved because more of the operation has been offloaded to the relational database engine, where COUNT(DISTINCT column) is used to return unique non-null values, eliminating the need for more expensive ordering operations that degrade query performance.
This optimization is off by default because the query results produced by the new and older algorithms are not always identical. Transact-SQL counting can vary for NULL values and different collations. If you want to use the optimization, set the OLAP\ProcessPlan\EnableRolapDistinctCountOnDataSource property to 1.
This optimization works only if the following restrictions are met:
There is one partition per measure group. Multiple partitions make it impossible to correctly execute the query and merge the results from different partitions.
There are no arbitrary shapes in the queries, and no queries below the grain. If Analysis Services cannot formulate a SQL query that is reasonably efficient, it will fall back to the default query pattern.
The relational database engine must be SQL Server 2005 or later, Parallel Data Warehouse, or Teradata.
Event Tracing Infrastructure
The event tracing infrastructure is extended to support the new SQL Server Extended Events Framework. For more information, see Use SQL Server Extended Events (XEvents) to Monitor Analysis Services.
Hierarchies in Tabular Models
Hierarchies are metadata that define relationships between two or more columns in a table. Hierarchies can appear separate from other columns in a reporting client field list, making them easier for client users to navigate and include in a report. For more information, see Hierarchies (SSAS Tabular).
Images in Tabular Models
Images and similar data types, all under Binary Large Objects (BLOB) data types, are now supported in tabular projects. When creating a tabular project that includes images, the source column needs to be of binary or large binary data type.
Key Performance Indicators in Tabular Models
Key Performance Indicators (KPIs) are used to gauge performance of a value, defined by a Base measure, against a Target value, also defined by a measure or by an absolute value. For more information, see KPIs (SSAS Tabular).
Large Tables in Tabular Models
This release removes the limit of 2 billion rows per table. Tables have no limit in the number of rows they contain. Note that this limit is lifted for tables but not columns. Each column is limited to a maximum of 2 billion distinct values.
Memory Paging in Tabular Models
Memory paging allows models to be larger than the physical memory of the server. Memory paging is enabled through the VertiPaqPagingPolicy server property. For more information, see Memory Properties.
Partitions in Tabular Models
Partitions divide tables into logical partition objects. Each partition can then be processed independent of other partitions. Partitions can be defined for a project during model authoring in SQL Server Data Tools (SSDT), or for deployed models by using SQL Server Management Studio. For more information, see Partitions (SSAS Tabular).
Perspectives in Tabular Models
Perspectives define viewable subsets of a model that provide focused, business-specific, or application-specific viewpoints of the model. For more information, see Perspectives (SSAS Tabular).
PowerPivot Configuration Tool
A new tool is available to configure an installation of PowerPivot for SharePoint. The tool scans the system to determine whether SharePoint or PowerPivot software is already configured, and provides necessary actions used to deploy an operational server. This tool replaces the New Server installation option in SQL Server Setup that was previously used to install and configure software. In this release, installation and configuration are decoupled, with all configuration steps occurring post-installation using the PowerPivot Configuration Tool, PowerShell, or Central Administration. For more information, see PowerPivot Configuration Tools.
PowerPivot for Excel in SQL Server 2012
This release introduces a SQL Server 2012 version of PowerPivot for Excel. You can use this version of the add-in to author and publish PowerPivot workbooks from Excel 2010 to SQL Server 2012 PowerPivot for SharePoint.
For more information, see PowerPivot for Excel Installation and What’s New (PowerPivot for Excel).
PowerPivot for SharePoint Configuration Settings and Server Health Rules
This release improves the administration and tuning of a PowerPivot for SharePoint deployment by adding more configuration settings and health rules that help you detect and fix problems before they occur. New configuration properties give you more control over disk space consumption, caching, and data refresh activity. For data refresh, you can specify whether to deactivate data refresh for workbooks that no one is using or that repeatedly fail to refresh.
For more information, see Configure Disk Space Usage (PowerPivot for SharePoint), Enable and Configure PowerPivot Data Refresh (Analysis Services) and Configure Server Health Rules (PowerPivot for SharePoint).
PowerPivot Workbook Auto-upgrade to Enable Data Refresh
You can configure an automatic upgrade feature on a PowerPivot for SharePoint instance that upgrades SQL Server 2008 R2 workbooks to the latest version, thereby enabling data refresh for those workbooks on a SQL Server 2012 PowerPivot for SharePoint server. For more information, see Upgrade PowerPivot for Excel.
PowerShell for AMO
This release introduces PowerShell cmdlets for AMO. This feature gives you command-line connectivity, navigation, and discovery of Analysis Services databases that run on a Multidimensional or Tabular mode server. For more information, see Analysis Services PowerShell.
PowerShell for PowerPivot for SharePoint
This release introduces PowerShell cmdlets for configuring PowerPivot for SharePoint. Using a combination of SharePoint PowerShell cmdlets and the new PowerPivot cmdlets, you can fully configure a PowerPivot for SharePoint installation through PowerShell script. For more information, see PowerPivot Reference for SharePoint PowerShell.
Removal of the 4 Gigabyte Limit on String Storage for MOLAP Engine
This release introduces a new option that removes a physical constraint on string store file size. In previous releases, a string storage file could grow to a maximum of 4 gigabytes, after which the following error would occur, indicating that the maximum file limit was reached: “File system error: a FileStore error from WriteFile occurred”. This release removes the maximum file size limit, allowing files to grow as needed. Note that upper limits still apply, but they are based on the number of strings rather than the size of the physical file. For more information, see Configure String Storage for Dimensions and Partitions.
Resource Usage Reporting for Multidimensional Databases
You can now collect metrics about resource usage at the command level when processing queries. Resource Usage is a new event class that can also be used as an additional column on the Command End event or Query End event. In the TextData column for this event, you can capture the number of reads or writes, reads or writes as measured in kilobytes, CPU time in milliseconds, rows scanned, and rows returned. For more information, see Query Processing Events Data Columns.
Row Level Security in Tabular Models
Row Level Security restricts data access by user identity, using filters at the row level. Filters are implemented through roles. For more information see Roles (SSAS Tabular).
Schema Rowsets for Analysis Services in Tabular Mode
New schema rowsets and DMV query interfaces have been added in this release to support the xVelocity engine and tabular models. You can use DMVs to discover tabular model objects and their properties. DMVs contain information that you can use to plan ahead and deduce the performance characteristics of objects you have created. For example, you can use the DISCOVER_CALC_DEPENDENCY rowset to trace dependencies in columns, measures, and formulas, making it easier to troubleshoot complex formulas. Other rowsets help you find the columns used in perspectives, or determine data types and storage characteristics. For more information about new rowsets that support tabular mode, see Tabular Model Data Access. To learn more about DMV query interfaces, see Use Dynamic Management Views (DMVs) to Monitor Analysis Services.
Security Roles in Tabular Models
Roles define member permissions for a model. Each role contains members, by Windows username or by Windows group, and permissions (read, process, administrator). Members of the role can perform actions on the model as defined by the role permission. Roles defined with read permissions can also provide additional security at the row-level by using row-level filters. For more information, see Roles (SSAS Tabular).
Server Modes for Analysis Services Instances: Multidimensional, Tabular, and SharePoint
This release adds a server mode concept to an Analysis Services installation. An instance is always installed in one of three modes that determines the memory management and storage engines used to query and process data. Server modes include Multidimensional and Data Mining, SharePoint, and Tabular. For more information, see Determine the Server Mode of an Analysis Services Instance
SQL Server Data Tools (SSDT) integration with Visual Studio
Tabular projects and Multidimensional projects are created in SQL Server Data Tools. This release updates SQL Server Data Tools to run in the Visual Studio shell. The shell includes several IDE enhancements including improved readability and support for multiple monitors.
Tabular Database Administration in SQL Server Management Studio (SSMS)
This release adds administrative support for tabular model databases that you deploy to a standalone Analysis Services server. You can configure role-based security, use backup and restore commands, attach and detach commands, create partitions, and set server properties. For more information, see Tabular Model Databases (SSAS Tabular).
Tabular Model Designer Diagram View
This release introduces the Tabular Model Designer Diagram View. The diagram view displays tables, with relationships between them, in a graphical format. Columns, measures, hierarchies, and KPIs can be filtered, and you can choose to view the model using a defined perspective. For more information, see Tabular Model Designer (SSAS Tabular).
Tabular Projects in SQL Server Data Tools (SSDT)
The Tabular Model Designer is now integrated with SQL Server Data Tools (SSDT). For more information about authoring tabular projects by using SQL Server Data Tools (SSDT), see Tabular Model Solutions (SSAS Tabular)
Also included with this release is the Tabular Modeling Adventure Works Tutorial. This tutorial guides BI software professionals through creating a new tabular model project in SQL Server Data Tools (SSDT), importing data from the AdventureWorksDW2012 sample database, adding relationships, calculations, perspectives, roles, and hierarchies, and then deploying the model. For more information, see Tabular Modeling (Adventure Works Tutorial).
Trace Events for Lock Contention and Usage in Multidimensional Databases
This release includes new events to help you troubleshoot lock-related query or processing problems. Locks Acquired, Locks Released, and Locks Waiting are new trace events that complement existing lock events, Deadlock and LockTimeOut. For more information, see Lock Events Data Columns and the chapter on locking in the SQL Server 2008 R2 Analysis Services Operations Guide.
xVelocity In-Memory Analytics Engine (VertiPaq) for Tabular Model Databases
xVelocity in-memory analytics engine (VertiPaq) is an Analysis Services engine that services tabular model databases. The xVelocity engine uses in-memory storage and performs calculations that aggregate and manipulate data at the time it is requested. In contrast with the previous release, where the xVelocity engine was only available (as VertiPaq engine) via PowerPivot for SharePoint, you can now use the xVelocity engine on a standalone Analysis Services instance with no dependency on SharePoint.
To use the xVelocity engine, you install Analysis Services in a new Tabular server mode that lets you run tabular model databases on that instance. For more information, see Install Analysis Services in Tabular Mode.