Using XMLA in Pentaho Reporting

Anyone who has ever actually attempted it may have noticed that Pentaho Report Designer does not natively accept XMLA as a datasource.  It is still possible, and this little guide will step you through how to do this.  We used MSSQL 2008R2 with the standard Adventure Works example data loaded.  We used the Pentaho BI Suite EE 3.8, but the directions should remain the same for CE and more recent releases.

UPDATE: As Thomas Morgner pointed out, you don’t need to use PDI to bridge the gap between PRD and XMLA, you can use OLAP4J.  The PDI method can be generalized for almost any type of datasource and perform elaborate preprocessing as needed, so keep it in mind anyways.

Set up MS OLAP XMLA Server as SOAP Web Service

1. First, copy all of the contents of the C:Program FilesMicrosoft SQL ServerMSAS10_50.MSSQLSERVEROLAPbinisapi directory into the C:inetpubwwwrootolap directory.  Create this if necessary.
2. Open the Server Manager by right clicking on Start->Computer and selecting Manage.  On the far left of this window, there should be the Console Tree. This can be opened by clicking on the gray button near the top of the window if it is not there.
3. Expand Roles by clicking on the plus sign next to it.  Below it should now be the Web Server (IIS) option.  If it is not there, it can be added by right clicking on roles and selecting Add Roles, and then clicking on the Web Server (IIS) checkbox when it shows up.
4. Either way, expand the Web Server (IIS) option and click on the Internet Information Services option below it.  There will now be a second selection tree next to it labeled Connections.
5. Expand the option that matches the name of the server, right click on Application Pools option below it, and select the add application pools option.  Set the name in the window that comes up to OLAP, and make the Managed Pipeline mode Classic.
   
6. Back on the Connections tree, expand the sites option. Next, expand the Default Web Sites Option below it, and then click on the OLAP option.  Of the icons that appear in the middle, double click on the Handler Mappings option.
7. Right click on any of the entries in the list and select the Add Script option.  In the window that pops up: under Request Path enter *.dll, under Executable enter C:inetpubwwwrootolapmsmdpump.dll, and under Name enter OLAP.
8. Back in the Connections Tree, click back on OLAP to return to the previous screen of icons.  This time double click on the Authentication option, and make sure that the only entry in the list that is marked as enabled is the first one, Anonymous Authentication.  This will allow access to the connection without having to add extra authentication data to the query (it still requires authentication to access the server data).
9. Once all this is completed, it can be checked for response by going to the address of the dll, such as http://localhost/olap/msmdpump.dll which will give a SOAP response error.
10. It can also be tested through the SQL Management Studio by entering the address as the server name when connecting to a server.  All of the same options will be available as if accessing the server directly.

Connecting Microsoft OLAP to Pentaho Data Integration (KETTLE)

1. Open Pentaho Data Integration (PDI).
2. Go to File –> New Transformation
3. Click on the “Design” Tab if not currently selected.
4. Now look for the OLAP step.
   1. One way to do this is to browse under the folder “Input” on the left and find the “OLAP Input” or alternatively click in the search box at the top and type in “OLAP Input”
   2. Drag the step to the right and side onto the canvas.
5. Double click on the OLAP Input Step.
6. FIll in the below.  We have created anonymous login for simplification of this POC.  We recommend that set up the security appropriately in production.  We’ve also included a sample MDX query that should return 50 lines for testing purposes.MDX Query:

Configure OLAP input:

1. Now do a search for “Text file output” on the left or browse for it under the output folder.
2. Drag it onto the the canvas on the right.
3. Click on your newly named OLAP Input called MSOLAP now and hold down the shift key.
4. Drag an arrow while still holding left click and shift and connect it to the Text File output step and release the Shift and left click button.
5. Double click the “Text file output” and set it to a local file system like: C:UsersAdministratorDocumentsMSOLAP Output
6. Go to File –> Save As and save your transformation as MSOLAP.  Click the green “Run” button that looks like a play button.
7. You should have a successfully exported data from MSOLAP to a text file (you’ll know if you don’t see any error messages and it says Finished! at the bottom).

Using Pentaho Report Designer (PRD) to report from Microsoft OLAP

1. Open Pentaho Report Designer
2. Click on the “Data” tab on the right.
3. Right click on “Data Sets” on the right.
4. Go to Pentaho Data Integration.
5. Browse for MSOLAP.ktr and click Open.
6. In the name field, call it “MSOLAP.”
7. Select the MSOLAP step.
8. Click Preview to verify that it will work.
9. Click OK.  You should see three columns under Data sets under MS OLAP as a data source.  They are: Column0, Column1, [Internet Sales Amount].
10. Drag each column on the “Details” band of the report.
11. Now go back to Pentaho Data Integration and open MSOLAP.ktr from the last section.
12. Delete the text output step.  Find the dummy step on the left and drag it to the canvas and connect them with a hop as shown in the picture:
13. Go back to PRD.  Click on the preview button.  It looks like a giant eyeball in the top left hand side.
14. If you see no errors and about 2 pages of detail data (and 50 lines of data), you have successfully queried Microsoft OLAP from Pentaho report designer.
15. If you’d like to publish this to the BI Server, make sure that the PDI transformation file (.ktr) is accessible by the BI Server to use in the transformation whether by file system or stored in the PDI repository.