Browsing a Cube Using Excel

Overview

Once the cube is deployed and ready to host queries from the data store, client applications can start querying the cube. One of the most user friendly client tools for business users to query a cube is Microsoft Excel. It has a built-in interface and components to support GUI based connection, querying and formatting of data sourced from a cube. Business users can use the familiar interface of Excel and create ad-hoc pivot table reports by querying the cube without any detailed knowledge about querying a multi-dimensional data source. We will connect to the cube we just created using Excel and develop a very simple report using the cube data.

Analyse 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 Pivot Table to the worksheet. This feature replaces the Office Web Control that provided an embedded Pivot Table 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 Pivot Table to the worksheet so that you can begin browsing your data.
• You can now build a Pivot Table 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.

 Note:

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 Pivot Table to the worksheet. Model objects (tables, columns, measures, and KPIs) are included as fields in the Pivot Table field list.