Click on it to export the current JQL into the Analytics Client console (Saiku Community Edition) 

Working with Measures and Dimensions: the Pivot Table

Select the Jira Core cube:

This will load the Cube with its Measures and Dimensions:

Expand the Issue dimension and click on the Key to add as Row:

The click on the Issue Count measure. The pivot table will count the issues for each dimension. This is a quite obvius measure: you got 1 issue per key!

We will neasure something more useful now: issue status vs issue type. Revove the key dimension and add the Status as rows and the Type as columns:

In the eaxmple above, there are 8 stories taht are already done. But what are those stories? Click on the Drill action from the top bar and then click on the cell to see its detailed data:

This will bring up a dialog. Expand the Issue dimension and select the Key option:

Click on the OK button:

The issue key for the eight done stories are listed!

MDX behind the scene

Easy no? But what is it happed behind the scene? You have been building a MDX query behind the scene! Click on the Show MDX button:

The MDX performed behind the scne is displayed. Copy it into the mememory for later use:

WITH
SET [~ROWS] AS
    {[Issue].[Key].[Key].Members}
SELECT
NON EMPTY {[Measures].[M01- Issue count]} ON COLUMNS,
NON EMPTY [~ROWS] ON ROWS
FROM [JIRA Core]
WHERE CrossJoin({[Type].[Type].[Story]}, {[Status].[Status].[Done]})

The good news are that you can build awesome complex MDX queries in a visual way (with darg and drop) without any knowled about the MDX language!

Save the scheme (catalog)

Actually the cube is volatile and it will be lost if the console is abnadoned. You can save it for later use. Click on the Save button and provide the Demo name for the Catalog (a set of cubes grouped in a Mondrian schema):

Clcik on the Save button to close the dialog. The catalog is now saved in the Jira database!.

Listing saved schemes

Click on the Manage cubes button to list the saved catalogs in Jira:

A new page listing all the saved schemas (catalogs) is displayed. Click on the MDX Console button:

The MDX console and the XML/A protocol

The MDX console is opened. Type the 

SHOW CATALOGS; 

instructions to list all the saved catalogs:

Type:

USE [Demo] ;

to load the cubes in the Demo catalog.

Now, copy&paste the MDX query that you built in previous steps:

The eight closed stories in the JQL are listed! You got the same results than from the Analytics client (Saiku). You have seen the XMLA protocol in action. It is possinle to connect to Jira to list the saved catalos and perfom MDX queires on their cubes. The MDX consle above is an example of a XMLA client tool. But you can use any third party compatible tool. You might want to use the Arquery's XMLConnector for Excel and connect to Jira from Excel to perform analytics (read the details in this dedicated tutorial).

Close the MDX Console anc click on the Download link to see the Mondrian's XML schema file:

Open the Demo.xml file with some text viewer. You are seeing the scheme built by the Analytics Server app. You can edit and modify it to meet your particular requriments. This requires some adavance knowledge about the Mondrian 4 sheme specification. Then upload the modifications to replace the existing scheme with your custom version. You can add new dimensions and measures, rename them (to localize captions, etc).  If you watch the Demo.xml file contents in detail you will see a lot of SQL queries to define the physical schema. All of those SQL queries are built with the SQL for Jira Driver app available on the Marketplace. And a free edition of the Driver runtime is included in the Analytis server app to resolve the SQL queries performed by the Mondrian engine. In this way, Mondrian schemes become independent of the Jira native database. Just the same Mondrian schema works with H2, Oracle, MS SQL Server,  MySQL and PostgreSQL with no changes.

What if you want to modify all the schemes to support a new measure and dimension? Must you edit and modify them one by one? The answer is not. You can modify the template in Jira, so all the new catalogs will use your custom template with your modifications.

Open a Scheme (with no state)

But let us please continue with this example. Click on the Analytics link to load the scheme in the Analytics Client (Saiku) again:

Note that the Saiku's Cubes section lists all the Mondrian schemes (AKA MDX Catalogs) names (Demo) along with the Jira user name (admin in this example):

Open a Scheme (with a saved state)

But wait, a MDX querie showing eight done stories was already performed. Is that MDX query gone? Do you have to repeat all the steps every time to restore a previous MDX query? The answer is no. You can load a previously saved MDX queeri in Saiku again by clicking on the Load button:

Double click on the Demo catalog to load it again into Saiku by restoring its previous saved state:

The eight done stores are now listed again!.

This tutorial is ended up. What did you learn?

• Load JQL queries into the Saiku console for analytics
• Work with dimension and measures to analyze data
• Drill down data to see details
• Save schemes (catalogs) in Jira
• Open catalogs via XMLA (with the console)
• Modify catalogs to meet your requirements
• Load catalogs in Saiku and restory previous saved state