As we all know, Microsoft Excel is one of the most popular tools to analyze your data. When using the Microsoft Business Intelligence (BI) stack and SQL Server Analysis Services (SSAS) in particular, you can create powerful reports when connecting Microsoft Excel to a SSAS cube.
Because I’m also a great fan of open source, I was looking for a similar solution with the Pentaho community edition 5.0.1. So far I was only able to find some commercial solutions until now. Today I want to share a great tool I discovered just recently to connect Microsoft Excel with the Mondrian OLAP server: XMLA Connect
Installation
The first step is to install the XMLA Connect on your Windows machine. Therefor you have to download the latest file via sourcefore.net. When you have downloaded the file, you can just double click the executable and an installation wizard guides you through the process. I assume that you have the Pentaho BI server CE 5.0.1 installed and configured for XMLA (a default installation should be enough).
Connection
Next, we are going to make a connection to a Mondrian cube. Because I have a default Pentaho installation, we are going to connect to the SteelWheels cube. The steps to connect to a Mondrian cube are quite simple. You can also find the steps on the wiki page of the tool at sourceforce.net. So, to make a connection, do the following:
1. Open a new Microsoft Excel Workbook
2. Go to the ‘Data’ tab, and in the Ribbon select ‘From Other Sources’ and then ‘From Data Connection Wizard’.
3. In the ‘Data Connection Wizard’ screen you have to select ‘Other/Advanced’ and Press the ‘Next’ Button.
4. Next, in the ‘Data Link Properties’ Select the option ‘XMLA Data Source’ and click on the ‘Next’ button. You are now in the ‘Connection’ tab of the ‘Data Link Properties’. Here you have to enter your information. In my case, it looks like the screen below.
5. After your parameters are correct, you can press the ‘OK’ button. The next screen you get is to select the cube you want to connect to. Then click the ‘Next’ button and after that you can click on the ‘Finish’ button to save the connection and to close the wizard. Now, you can choose to create a pivot table and query the data.
I first tried to install it on Windows 7 Pro, but here I got a message that it could not be installed. It was because the machine was a 32 bits machine. So next step, I used a Windows 8.1 64 bits machine with Excel 2013. Here it all worked.
According to the description on sourcefource.com, the connector is still in Beta. I’ve discovered that in my case it is not possible to select a catalog. you have to enter it by hand, and then click on the ‘Advanced’ tab. Then you can go back to the ‘Connection’ tab and you can use the ‘Test Connection’ button to validate your connection parameters.
Conclusion
With the XML Connector it is possible to connect to a Mondrian cube in Excel. Although the still in beta, I think the XML Connector is a nice alternative for commercial solutions. It is free, licenced under the GNU General Public License version 3.0 (GPLv3). At the time of this writing, it is not possible to connect to a Mondrian cube with XMLA Connect and Excel PowerPivot and PowerView. It would certainly be a nice feature if this is possible. I’m looking forward to a final release.
No comments:
Post a Comment