XML/A (Excel client)

There are several ways to integrate Jira data with a third party Business Intelligence system.

One would be via some ETL tool (Extraction, Transformation & Loading). We recommend the SQL for Jira Driver for that as is allows to perform SQ+JQL queries remotely via standard JDBC over the HTTP protocol and in a secure and Jira permissions-compatible way. This method requires some synchronization process to keep data up to date.

As alternative, the XML/A protocol is an industry-standard that allows to connect two BI applications in client/server scenario. The app transforms Jira into a full featured BI server with full support for the XML/A protocol so you might want to to use the your favorite XML/A client tool to explore data. In fact this app deploys the award winning Saiku Analytics Community Edition web tool packed as Jira app  and uses it as BI client to connect to the BI Server (Mondrian).

The app starts a XML/A server on Jira listening to this URL:

We will use Excel as BI client example to connect to Jira via the XML/A protocol. 

You can watch a video showing this tutorial at 3 min 51 secs.

First of all, you have to create a Mondrian schema on Jira. Please follow the steps described in this section: Getting started (Tutorial).

Then download the free XMLA connector for Excel and install it. The connector installs a complete tutorial in Excel that you might want to watch (optional):

Create an empty Excel workbook and click on the Pivot Table icon:

This will bring up a dialog:

Then selct the Use an external data source option and click on the Choose connection... button:

Click on the Browse for More... button:

Click on the New Source... button:

Select Other/Advanced click on Next:

Select the XMLA Data Source option at the bottom of the list and click Next to select the Connection tab:

Fill out the Location with the following URL (replace the Jira BAse Url with yours value):

<Jira Base URL>/jira/plugins/servlet/marketexpert/mondrian/xmla/

and your Jira user's credentials (User name & Password). Then select a Catalog:

Click on th OK button and select the Issues cube:

Click on Next and check the Save password in file opton.

Click on Fnish and close the rest tof the dialogs. The Analyze tab is selected in Excel showing a Piot Table on the left and the cube Measures and Dimensions on the rigt:

Click on the Issue count option in the Values (Measures) box, This counts all the issues in the cube (JQL):

Now select the Priority, Staus and Type dimensions. This will group the data according the selected dimensions:

Select the data fro the table and add achart in the regular way: