Analyze in Excel (Browser Tab, Cube Designer) (Analysis Services - Multidimensional Data)
Applies To: SQL Server 2016 Preview
Analyze in Excel provides the cube developer with a way to quickly review how a project would look to the end user. The Analyze in Excel feature opens Microsoft Excel, creates a data source connection to the workspace database, and automatically adds a PivotTable to the worksheet. This feature replaces the Office Web Control that provided an embedded PivotTable in the Browser tab in previous releases.
To view cube data:
In SQL Server Data Tools, in Solution Explorer, double-click a cube to open it in Cube Designer.
Click the Browser tab.
Click Reconnect to validate the connection.
Click the Excel icon in the menu bar.
When asked to enable data connections, click Enable. Excel opens using the current data connection, adding a PivotTable to the worksheet so that you can begin browsing your data.
You can now build a PivotTable interactively by dragging measures from the fact table to the Values area, and dimension attributes to the Row and Column areas. If you have hierarchies, add them to Rows or Column areas. You can roll up or drill down the hierarchy to browse fact data at different levels.
Objects and data are viewed within the context of the effective user or role and perspective. When using Excel, the credentials of the current user, not the credentials specified in the Impersonation Information page, are used to connect to the data source when a query is executed.
To use the Analyze in Excel feature, Excel must be installed on the same computer as SQL Server Data Tools (SSDT). If Excel is not installed on the same computer, you can use Excel on another computer and connect to the cube as a data source. You can then manually add a PivotTable to the worksheet. Model objects (tables, columns, measures, and KPIs) are included as fields in the PivotTable field list.
For more information about the Analyze in Excel feature, see these resources:
Browser (Cube Designer) (Analysis Services - Multidimensional Data)
Toolbar (Browser Tab, Cube Designer) (Analysis Services - Multidimensional Data)
Metadata (Browser Tab, Cube Designer) (Analysis Services - Multidimensional Data)
Query and Filter (Browser Tab, Cube Designer) (Analysis Services - Multidimensional Data)