Excel 2007/2010

The XMLA interface allows you to access icCube through Excel 2007/2010. For example, the following section describes how to create a pivot table using Excel 2007. Note that the connection string generated is:

Provider=MSOLAP.4;Persist Security Info=False;Location=http://localhost:8282/icCube/xmla;Initial Catalog=Sales (Excel)
    

Due to several issues with the OLE DB provider (i.e., lack of prompting user credentials when not using Windows Integrated Authentication), it is strongly recommended to download the latest Microsoft SQL Server 2008 Analysis Services OLE DB Provider as described here.

Once configured, Excel is going to connect to the icCube HTTP server. The actual authentication mechanism (if not anonymous) that is used will require specific icCube configuration (i.e., HTTP servlet filters). Please consult our forum for more details and up-to-date information about that.

Anonymous Authentication

When anonymous logon is activated (default in icCube.xml) it is taking precedence over for example the username/password as defined in Excel (same for ADOMD.NET). Therefore, to connect with a username/password you must de-activate the anonymous logon within the "HTTP Basic Authentication" filter configuration within the icCube.xml file.

Pivot Table Creation Walkthrough

So let's create a pivot table...

Select 'Choose Connection'.

Click 'Browse for more'.

Click 'New Source'.

From here you have two options :

1. Either Select an MS Analysis Driver.

Set server location (http://localhost:8282/icCube/xmla) corresponding to the icCube XMLA interface (HTTP location as defined in icCube.xml and available in the monitoring WEB interface).

You can now jump directly to 'Then select the schema and cube bound to the data source'.

2. Or Select 'Other/Advanced'.

Select an OBDO driver.

Then setup the parameters of the data source starting with its location (http://localhost:8282/icCube/xmla) corresponding to the icCube XMLA interface (HTTP location as defined in icCube.xml and available in the monitoring WEB interface).

Then select the schema and cube bound to the data source.

Then save and open the pivot table.