SQL Server Analysis Services Reporting with Excel



By:
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 multidimensional data source. We will connect to the cube we just created using Excel and develop a very simple report using the cube data.

Explanation

Open Microsoft Excel and select the “Data” tab from the menu ribbon. Click on “From Other Sources” and select “From Analysis Services” option as shown in the below screenshot.

Select the SQL Server Analysis Services data source from Microsoft Excel

In the next step specify the SSAS server name and logon credentials. If you have all of the SSAS components installed on your local machine, you can also use “localhost” as the server name. If you were able to successfully connect to the specified SSAS instance with the logon credentials specified, in the next step you should be able to select the SSAS “AdventureWorks” database and find the Adventure Works Cube. Select the Adventure Works Cube and proceed to the next step.

Select the SSAS Database and Table in Microsoft Excel

In the next step, specify the name of the connection file to save. This file will be saved as an .ODC file and you can reuse this connection file when you want to use the same connection in other workbooks. After saving the file, you will be prompted with the option to select the kind of report you want to create. We will go with the default option and select “PivotTable Report”.

Select the Report Type in Microsoft Excel

After selecting “PivotTable Report”, a designer will open with options to select dimension, attributes and measures to populate your pivot table. Select the values as shown in the below screenshot. We have selected Internet Sales Amount measure and Product Categories hierarchy from the Product dimension.

Select the PivotTable Fields in Microsoft Excel
Additional Information
  • Consider selecting more data elements from the cube, format the report and make it look more professional.

Last Update: 4/18/2017






Comments For This Article





download


get free sql tips
agree to terms


Learn more about SQL Server tools