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.