Release 2.5.3: Connect Excel and other ODBO / XML/A clients with instantOLAP

Today we released our next version of instantOLAP, including the first beta version our new ODBO / XML/A / MDX connector which opens the world of other OLAP clients to instantOLAP servers. With the new connector, you can query the instantOLAP server with Microsoft Excel or any other reporting client that supports the Microsoft ODBO standard or the webservice-based XML/A protocol.

The beta version of the connector will be available for free as a part of the following releases unless it reaches the production standard in version 2.6.

You can download the new release 2.5.3 from our download page. Beside the connector, it contains a small number of bug fixes and performance-improvements.

[toc]

Connectivities

The instantOLAP XML/A connector supports XML/A 1.1, a web-service based query protocol which is supported by a large and increasing number of clients. The new XML/A server is part of the usual instantOLAP server and is reachable under „http://<server>:<port>/iolapWS/xmla„. It uses basic authorization (which must be supported by the client) and only operates with user accounts having the „iolapPowerUser“ role or upper (from version 2.6 a new and different role will be mandatory).

Windows clients using ODBO as their connection standard can connect to the instantOLAP XML/A connector using an ODBO provider which talks „XML/A“ to the server. There are currently two bridges available, the commercial O2X Provider from Simba Technologies and the Microsoft SQL Server 2008 provider from Microsoft.

The connector transforms the instantOLAP concepts into the XML/A world:

• instantOLAP models are visible as „catalogs“ for XML/A, schemes are not supported.
• Each dimension contains a single hierarchy with the same name as the dimension. Future versions of instantOLAP will support multiple hierarchies.
• Keys are visible as „members“ for XML/A
• Key-Attributes become properties in XML/A. The member properties MEMBER_UNIQUE_NAME, MEMBER_CAPTION, CHILDREN_CARDINALITY, PARENT_UNIQUE_NAME, LEVEL_NUMBER and UNIQUE_LEVEL_NAME are supported and delivered for each key, even if they are not defined in the model.
• All queries via XML/A use a single cube, which must be specified in the query. Future versions will allow to query all cubes or groups of cubes.

MDX support

Both XML/A and ODBO use MDX as their query languge. MDX was invented by Microsoft and is comparable to SQL but specialized for multidimensional queries. The beta version of the instantOLAP XML/A connector supports the core syntax of MDX and a number of operators and functions.

Supported MDX syntax in the beta version:

• SELECT statement (with an unlimited number of axes and dimension properties)
• WHERE clause
• WITH MEMBER clause
• WITH SET clause
• Operators: (), {}
• Functions

Supported functions in the beta version:

• AddCalculatedMembers
• Aggregate
• Ascendants
• Children
• CrossJoin
• Descendants
• Distinct
• DrilldownLevelBottom
• DrilldownLevel
• DrilldownLevelTop
• DrilldownMember
• Except
• Hierarchize
• Intersect
• Join
• Union
• VisualTotals

Example: How to set up an example connection to instantOLAP with Excel 2007

Before you can connect instantOLAP from Excel, you must install an XML/A compatible ODBO driver on your local workstation. In this example, we use the O2X driver from Simba. After you successfully installed the driver, you can start Excel and create your first pivot table:

1. Click into an empty cell of your worksheet and use the „Pivot Table“ button from the „Insert“ tab to create a new table at this location.

2. Now, Excel wants you to define the datasource for your pivot table. Because instantOLAP is an external data deliverer, you must choose the „External datasource“ option and use the button „Choose connection“ to continue.

3. The following dialog shows all existing datasources stored in your current workbook and on your computer. Because this is the first time you connect an instantOLAP model, you must use the „Search for other elements…“ button to create a new connection. After you successfully created an connection, it will be displayed here and you don’t have to redefine it.

4. This button opens the Explorer and shows the folder where all stored connections (ODC-files) are located. Now you can use the „New source…“ button to create a new ODC file (and datasource):

5. The new dialog wants you choose the type of the new datasource. It contains a list for all standard types and an element „Others“ for unknown types like instantOLAP. Choose this option and click the „Next >“ button.

6.  Now you can choose the driver for the new connection. Here you must choose and XML/A compatible driver like the SimbaO2X Provider or the Analysis Services Driver 9 or 10. Then click onto „Next >“ to open the next tabulator in the dialog.

7.  The next page wants you to input the URL of the server and the username and password for the connection.

The URL for an instantOLAP server is usually „http://<address>:<port>/iolapWS/xmla“. E.g. this example connects to an instantOLAP server running on the local machine and on port 8080. The username and password are the same as when you log into the instantOLAP Web Portal or into the Workbench, but you need at least to be a „Power user“ to connect to the XML/A server.

We also suggest to save the password and to select an initial Catalog for your connection (a catalog is the same as models in instantOLAP). After you successfully entered and tested you connection, you can use the „OK“ button to continue.

8. Now you can choose the cube of the model on which you want to execute your queries. Here, instantOLAP shows all cubes and an addition cube named „ALL“ which acts as a hypercube containing all other cubes (this is also the cube all instantOLAP reports use). The use the „Next >“ button to enter the next page.

9. Here you can edit the filename and display name of your new connection. Also, if you want to store the password in the connection, you must check the „Save password“ option here, again.

Now finish all dialogs. If everything went right, a new and empty Pivot table should be visible and you can add facts or dimensions to it by clicking them in the field list at the right border of your Excel sheet.

Release Notes

08.06.2009: Release 2.5.3

Server

• A new XMLA server is available as backend for XMLA and ODBO clients (e.g. Microsoft Excel)
• Supported MDX Functions: AddCalculatedMembers, Aggregate, Ascendants, Children, CrossJoin, Descendants, Distinct, DrilldownLevel, DrilldownLevelBottom, DrilldownLevelTop, DrilldownMember, Except, Hierarchize, Intersect, Join, VisualTotals
• Better performance for File-Caches
• Bugfix: The Omit-Factor of dimension mappings is no longer used for list reports

Web-Frontend

• Patterns (%USER%, %TITLE% and %NAME%) for mail topics introduced
• Bugfix: Problems with included images in PDF-mails solved

Workbench

• Other levels with the same name in the same dimension are no longer automatically renamed when a level is renamed
• Visible and Description-Properties introduced for Keys, Attributes and Cubes
• Bugfix: A missing java archive for Linux was added