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




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools