Oops! Something went wrong while submitting the form.
Share this article
This article is part of THE FOLLOWING SERIES:
This process requires few steps and provides immense benefit. With this connection, you can use any number of PivotTables in Excel to "drag-and-drop" build a report. You can also easily create dashboards, KPIs, or use PowerQuery to use Excel for strategic reporting and analysis.
Major Considerations
You must have the SSAS server name hosting the database and Cubes you would like to access.
The login used for Windows must be granted security permissions to read the SSAS database on the SSAS server.
You must be using a modern desktop version of Excel, newer than or equal to 2016. If you use older versions, you may be requred to install additional Microsoft features to connect to SSAS.
Note. The steps in this article may vary in their order or description.
Steps
Open a new Excel document. You can start with either a new or existing document.
Note. Save this document and use it as an analysis tool. The Cube connection is active when this file is open and the Cube is accessible.
Select the Data tab.
Select the From Other Sources menu.
Select the From Analysis Services option.
Enter the SSAS server name.
Select Use Windows Authentication.
Click Next.
Select SSAS_Analytics.
Select Insights.
Click Next.
Click Finish.
Click Yes.
Note. This pop-up window is similar to a warning when saving a Microsoft Word file with the same name as an existing file, except this file contains Cube connection details. It's technical, but safe to click Yes.
Select cell B2.
Click OK.
Choose PivotChart for table+chart.
Choose Power View to load and analyze data using Excel data models (e.g., PowerBI).
Choose another location for your item.
Select the Properties button to apply advanced settings to the PivotTable.
Click on the gear icon menu.
Select Collapse All.
Expand+drag items to the right.
Measures: define numerical values like worked hours, fees, collected, etc. These are organized into "folders."
Dimensions: define text/data values to silce/filter the "measures," such as period, data, employee, office, etc.
Drop+reorder items in squares.
Filters: defines the dimensions to use for selecting specific people, places, dates, etc.
Values: defines the list of items being measured on those dimensions.
Congratulations! Now you can connect an Excel workbook to a SQL Server Analytics Server database and Cube to "drag-and-drop" build reports, create dashboards and KPIs, and use PowerQuery with Excel for strategic reporting and analysis.
Don't miss any update on this topic.
Sign up for our newsletter to receive a personalized collection of our most recent articles, blog posts, videos, and more, curated specifically for you based on your interests.
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
