Behavior Changes to Analysis Services Features in SQL Server 2016
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
This topic describes behavior changes in Analysis Services, Power Pivot for SharePoint, and Data Mining. Behavior changes affect how features work or interact in SQL Server 2012 as compared to earlier versions of SQL Server.
Currently, this topic contains only the behavior changes for Power Pivot for SharePoint. Other Analysis Services features do not have behavior change announcements at this time.
In this Topic:
This section documents the breaking changes reported for SQL Server 2016 Analysis Services (SSAS) features in SQL Server 2012 SP1.
SQL Server 2008 R2 Power Pivot workbooks will not silently upgrade and refresh the models when they are used in SQL Server 2012 SP1 Power Pivot for SharePoint 2013. Therefore Scheduled data refreshes will not work for SQL Server 2008 R2 Power Pivot workbooks.
The 2008 R2 workbooks will open in Power Pivot for SharePoint 2013, however scheduled refreshes will not work. If you review the refresh history you will see an error message similar to the following:
“The workbook contains an unsupported Power Pivot model. The Power Pivot model in the workbook is in the SQL Server 2008 R2 Power Pivot for Excel 2010 format. Supported Power Pivot models are the following:
How to upgrade a workbook: The scheduled refreshes will not work until you upgrade the workbook to a 2012 workbook. To upgrade the workbook and model it contains, complete one of the following:
For more information on workbook upgrade, see Upgrade Workbooks and Scheduled Data Refresh (SharePoint 2013).
Behavior change in DAX ALL Function.
Prior to SQL Server 2012 SP1, if you specify a [Date] column in Mark as Date Table, for use in time-intelligence, and that [Date] column is passed as an argument to the ALL function, in-turn, passed as a filter to a CALCULATE function, all filters for all columns in the table are ignored, regardless of any slicer on the date column.
= CALCULATE (<expression>, ALL (DateTable[Date]))
Prior to SQL Server 2012 SP1, all filters are ignored for all columns of DateTable, regardless of the [Date] column passed as an argument to ALL.
In SQL Server 2012 SP1 and in Power Pivot in Excel 2013, the behavior will ignore filters only for the specified column passed as an argument to ALL.
To work around the new behavior, in effect ignore all columns as a filter for the entire table, you can exclude [Date] column from the argument, for example,
=CALCULATE (<expression>, ALL(DateTable))
This will yield the same result as the behavior prior to SQL Server 2012 SP1.
This section documents the behavioral changes reported for SQL Server 2016 Analysis Services (SSAS) features in SQL Server 2012.
Analysis Services, Multidimensional Mode
Cube browser in Management Studio and Cube Designer has been removed
The cube browser control that let you drag and drop fields onto a PivotTable structure in Management Studio or in Cube Designer has been removed from the product. The control was an Office Web Control (OWC) component. OWC was deprecated by Office and is no longer available.
Power Pivot for SharePoint
Higher permission requirements for using a Power Pivot workbook as an external data source
An Excel workbook can render Power Pivot data that is embedded within the same workbook or in an external workbook. In the previous release, permission requirements were the same regardless of whether the Power Pivot data was embedded or external. If you had View Only permissions on a Power Pivot workbook, you could get full access to all of the Power Pivot data in the workbook for both embedded and external connections.
In this release, permission requirements have changed for Excel workbooks that render Power Pivot data from an external file. In this release, you must have Read permissions (or more specifically, the Open Items permission) to connect to an external Power Pivot workbook from a client application. The additional permissions specify that a user has download rights to view the source data embedded in the workbook. The additional permissions reflect the fact that model data is wholly available to the client application or workbook that links to it, resulting in a better alignment between permission requirements and the actual data connection behavior.
To continue using a Power Pivot workbook as an external data source, you must increase SharePoint permissions for users who connect to external Power Pivot data. Until you change the permissions, users will get the following error if they try to access Power Pivot workbooks in a data source connection: “Power Pivot Web service returned an error (Access denied. The document you requested does not exist or you do not have permission to open the file.)”
The following steps instruct you to break permission inheritance at the library level and increase user permissions from View Only to Read for specific documents in this library. Before you proceed, carefully review existing permissions and documents and verify that these steps are appropriate for your site.
Alternatively, you can create a folder in the library, move all affected documents to that folder, and set unique permissions on the folder.
If your workbooks are stored in Power Pivot Gallery, breaking permission inheritance on a workbook will disrupt thumbnail image generation for that workbook if it is configured for data refresh. To simultaneously allow access to both workbooks and preview images in the gallery, consider granting to specific users Read permissions at the library level, for all documents in the library.
You must be a site owner to change permissions.
How to increase permissions to Read permission level for individual workbooks
Click the down arrow to open the menu for an individual document.
Click Manage Permissions.
By default, a library inherits permissions. To change the permissions of individual workbooks in this library, click Stop Inheriting Permissions.
Select the checkbox by user or group names that require additional permissions on Power Pivot workbooks. Additional permissions will allow these users to link to the embedded Power Pivot data and use that data as an external data source in other documents.
Click Edit User Permissions.
Choose Read permissions, and then click OK.
Power Pivot Gallery: New rules for snapshot generation for some Power Pivot workbooks
This release introduces new requirements for generating snapshot images in Power Pivot Gallery, eliminating a potential source of information disclosure (namely, showing a snapshot of data from a data source that you do not have permission to view). These requirements apply only to Power Pivot workbooks that connect to external data sources each time you view the workbook. If you only use workbooks that visualize embedded Power Pivot data, you will see no change in how snapshots are generated in Power Pivot Gallery.
For a workbook that refreshes its data each time it is opened, the new requirements for snapshot generation are as follows:
Power Pivot workbooks that are used as external data sources by other workbooks or reports must be in the same library as the workbooks that consume the data. For example, if you have sales-data.xlsx that provides data to sales-report.xlsx, both workbooks must be in the gallery in order for snapshot images to appear.
Workbooks that are used together must inherit permissions from a common parent (that is, the Power Pivot Gallery). In our example, both sales-data.xlsx and sales-report.xlsx must inherit from Power Pivot Gallery.
If a workbook fails to meet any of the above criteria, the following locked icon will appear instead of the thumbnail image you expect:
New default setting for load balancing requests changed from Round-Robin to Health-Based
A Power Pivot service application has default settings that determine how requests for Power Pivot data are distributed across multiple Power Pivot for SharePoint servers in a farm. In the previous release, the default setting was Round Robin, where requests were distributed sequentially among the available servers. In this release, the default is now Health Based. The Power Pivot service application uses server health statistics, such as available memory or CPU, to determine which server instance gets the xt request.
If you upgraded your server from the previous release, the Power Pivot service application retains the previous default setting (Round Robin). To use the Health Based allocation method setting, you must modify the configuration settings. For more information, see Create and Configure a Power Pivot Service Application in Central Administration.