Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast






Learn more about SQL Server tools








Learn more about SQL Server tools


   Got a SQL tip?
            We want to know!

Using Excel to interact with a SSAS cube

MSSQLTips author Daniel Calbimonte By:   |   Read Comments (5)   |   Related Tips: > Analysis Services Tutorials
Problem

Sometimes we need to access a SSAS cube using Excel. In this tip we will show how to:

  1. Access SSAS with Excel
  2. Add Dimensions to a cube in Excel
  3. Work with SSAS KPIs in Excel
  4. Add SSAS Named Sets in Excel
  5. See the MDX Query that Excel creates
  6. Create new calculations using Excel
  7. Find Multidimensional Members
Solution

Requirements

In order to start we are going to use the AdventureworksDW 2012 Database and the AdventureWorks 2012 Project that can be downloaded here: http://msftdbprodsamples.codeplex.com/releases/view/55330

We will also use the OLAPExtension for Excel for this demo: http://olappivottableextend.codeplex.com/

I used Excel 2010 and SQL Server 2012 for this demo, but most of the features displayed apply to Excel 2007 and SQL Server 2005/2008/2008 R2 as well.

Access SSAS using Excel

In order to connect from Excel to SSAS in Excel go to File > Data and select From Other Sources. After that, select the From Analysis Services option. What we are doing is connecting to a Multidimensional SSAS Database using Excel.

connect from Excel to SSAS

Enter the name of the server and the authentication where the Analysis Server is installed.

Connect to Database Server

Select the database, in this case we are using Adventure Works.

Select the Database

Finally, press Finish.

Save Data Connection

Select the Import Data option and import to an existing worksheet:

Impoet Data

This sample shows the sales statistics of the fictional company AdventureWorks. Let's click the Internet Average Sales Amount and the Internet Average Unit Price of the company in Excel.

click the Internet Average Sales Amount and the Internet Average Unit Price of the company in Excel.

If you are familiar with the Business Intelligence Development Studio (BIDS) or the SQL Server Data Tools (SSDT), the results are the same when you drag and drop the Measures to the Cube Browser:

the Business Intelligence Development Studio (BIDS) or the SQL Server Data Tools (SSDT)

Analyze SSAS using Excel directly from SQL Server Data Tools (SSDT)

In SQL Server Data Tools (SSDT), you also have an icon to open your SSAS project directly in Excel without the need to execute steps 1 - 5. Click on the Excel button as shown below

you have also a nice icon to open your SSAS project directly in Excel

and select the Adventure Works cube.

Select the Adventure Works cube

You will get a Microsoft Excel Security Notice, so press Enable to allow access.

There is a Microsoft Excel Security Notice

Add Dimensions to a Cube in Excel

In Excel, if you want to see the Internet Average Sales per country, go to Customer > Location and drag the country to the Row Label.

Adding dimensions to a Cube in Excel

In SSDT, to do the same, go to Customer > Customer Geography and drag and drop the country dimension in the Browse tab.

In SSDT, to do the same

Work with SSAS KPIs in Excel

In Excel there is a KPI to display the Goals of the company and the current Values in the company. There is also a Status and a Trend icon. Check the options shown below to see these.

Working with SSAS KPIs en Excel

You will see in Excel, that the revenue is $80,450,596 and the Goal was $154,088,000, so the Channel Revenue Status is red because the goal was not met. Also, we can se that the Trend is positive by the direction of the arrow.

You will see in Excel

In SSDT, in the KPIs Tab, you can see the same options.

In SSDT, in the KPIs Tab, you can watch the same options

Add SSAS Named Sets in Excel

In In the PivotTable Tools go to Fields, Items, & Sets. With named sets you can create filters by row, column or create your own MDX to create filters.

Add SSAS Named Sets in Excel

If you create, for example, a Set Based on Row Item you can add or remove rows and columns using MDX internally.

create a Set Based on Row Item

You can also press the Edit MDX button to edit or customize your set of data.

You can also press the Edit MDX

See the MDX Query that Excel Creates

Let's be honest, nobody really likes to write MDX. Is there a way to see the MDX query that Excel created?

The answer is yes. You need to add the Excel Extension mentioned in the requirements at the beginning of the article (or here). This Extension gives you a new option in Excel to see the MDX. The option is OLAP PivotTable Extensions as shown below.

See the MDX query made in Excel

You can see the MDX Query and verify the query generated.

You can see the MDX Query and verify the query generated.

How to Create Calculations Using Excel

Let say that we have the Internet Average Sales in dollars, but we want to calculate the Average Sales in Euros with the current value. Is there a way to create a SSAS calculation in Excel?

Yes, you can copy measures from the MDX tab with Ctrl+C

How to create Calculations using Excel

And create a Calculation with the OLAP PivotTable Extension. You just need to add the name and the calculation. In this example I am transforming dollars to Euros from the value 1.2694 Dollars = 1 Euro. Finally add this to the Pivot Table and you will have the calculation in Excel.

And create a Calculation with the OLAP PivotTable Extension

Find Multidimensional Members

Finally, you can find members and folders of the multidimensional database. For example, if we go to the search Tab and find France, it finds the folder where it is stored.

Find Multidimensional members.
Next Steps

It this demo we learned how to connect to SSAS with Excel and create filters, named sets, and work with KPIs. You can find even more information and OLAP extensions in the links below:



Last Update: 2/27/2013


About the author
MSSQLTips author Daniel Calbimonte
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Monday, May 13, 2013 - 8:19:24 AM - Bhargav Read The Tip

Hi Daniel,

I'm new to BI. I have a report and when you click on a particular field it takes you to drill down report. Recently the user wanted to see an extra field in the ddrill down report, so i went into the ACTIONS and added that field. What i couldn't get is i can see the data for the newly added field under the browser tab but in excel the filed is not showing any values other than the blanks. As in this article i do not have the excel option in my SSAS. I tried both in BIDS and in SSMS but i don't seen  excel icon to analayze the data, please need help.

Thanks


Wednesday, May 15, 2013 - 10:29:26 AM - Daniel Calbimonte Read The Tip

Hello,

Did you read this link ?

http://msdn.microsoft.com/en-us/library/ms174548(v=sql.90).aspx


Monday, May 20, 2013 - 8:38:20 AM - Bhargav Read The Tip

Thanks , for some reason i deleted the dimension and recreated the dimension and added it to the actions and now i can see the data in the excel as well. Strange how MS Tools work sometimes.


Tuesday, March 18, 2014 - 2:57:36 PM - Terence Read The Tip

Is it possible to share only the "view" of the excel report without the SSAS connection?  

In other word, I want to email out an excel report with two columns (Internet Average Sales amount and Internet Average Unit Price) in it to other people but I don't want recepicients to have connection to the SSAS or have a office cube data file sitting somewhere on their local machine.

Is there anything in excel or add on to accomplish this?

Thank you.

 


Wednesday, April 09, 2014 - 4:59:37 PM - Rui Quintino Read The Tip

Hi Daniel,

Very good overview on Excel, OLAP & Analysis Services. Also a big fan of Olap Pivot table extensions :) recomend that!

Have to mention our addon for Excel, Analysis Services/PowerPivot: SmartPivot. Lots of extra features, including full instant search, saved filters,faster table reports & several others.

You can check my blog here for more info rquintino.wordpress.com/

Or the product page here www.devscope.net/products/SmartPivot . 

(disclaimer: yes, I work for DevScope very closely to SmartPivot team ;) )

Kind regards,

 

Rui



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.