Browse an SSAS Cube using Excel – Automatic Setup done by SQL Server Data Tools
Hi All,
Most of us; who are working on SQL BI solutions are aware of connecting SSAS Cube using Excel (as client) for browsing. Because we know that how much the clients/financial users likes to work with Excel 🙂
In SQL 2012 RC0, there is one handy-dandy quick-link which enables us to make this whole functionality ready-made in few seconds. Believe it or not, try the following:
- Open “SQL Server Data Tools” (This is new name assigned to Visual Studio IDE/BIDS in SQL Server 2012 RC0)
- Use MultiDimensionalProject & create a cube with any method you want.
- Process & Deploy it.
So far these are the intial setup steps for the trick I wanted to show you.
Now open the cube browser (Cube’s browser tab). You will see an excel icon on the top. When you hover the mouse, you will see “Analyze in Excel”. That’s it. Click on it and you will see the magic. 🙂
Note: You will also see this option if you deploy the cube to SSAS server and browse it from SQL Server Management Studio.
Once the excel is open, you will a security notice for data connection to SSAS Cube from Excel.
You enable it and Ta-da. Pivot table is ready for use.
Note regarding that security notice:
Once you click Enable button and you can browse the SSAS Cube in Excel. But when the next time excel opens; you will see the same security warning for enabling connection to SSAS Cube.
You can get rid of that warning just by selecting “Enable all Data Connections (not recommended)” under External Content category in Trust Center. I will post another article for this security settings in Excel and their pros and cons later.
Thanks, Khilit
That’s nice but still does not substitute the old browser for me. I know that Microsoft is really pushing Excel which is not a problem at all in itself but in order for this button to work you need Excel installed on the server. Which is not the usual case. Especially in a PROD environment. But you still end up needing to analyse data on a PROD server and it was a lot easier using SSMS and the cube browser than having Excel installed on some server that has a live connection to PROD.
We usually have Office on our dev boxes/servers but not in UAT/PROD and they can easily be totally separated.
Yes martinisti, you are right. This article only applies to/valid for development environment.
Once we had requirements to develop excel reports from SSAS cube. And every time we develop something in SSAS, we want to test in excel because final reports are to be developed in excel. So, rather than opening excel file -> creating connection -> drag-drop fields etc. ultimately it’s gonna take time.
But in this case, you click on this button (Analyze in Excel). Everything will be ready for you. Once you complete testing it, just delete the excel file. When you want to re-test the cube… just click the button again 🙂
Thanks for nice reply, keep it up.
Tc, khilit