Using Blueprint Validation and Confluence Fields Together

Confluence Fields plugin for Atlassian JIRA provides reporting via builtin Mondrian server. All Confluence custom fields you define are reported in MS Excel pivot using XMLA datasource plugin as described in Using Mondrian with Confluence Fields.

However default XMLA schema is very basic: Confluence field-derived OLAP dimensions are flat, no hierarchies are defined. That makes sense as Confluence fields only keep Confluence page's id and title, no other information from Confluence is stored. But what if you need a more complex reporting? Suppose that you have a 'Clients' custom field that sources its values from a Client blueprint in your Confluence server. The blueprint has a field named 'Country' that you want to use to group your customers for JIRA reporting. Let's keep the blueprint very simple:

images/wiki/download/attachments/51151335/Screenshot_2020-12-22_at_12.37.29.png
To get the Country information in MS Excel pivot I do the following:

  • Install the Blueprint Validation plugin for Confluence. The plugin is used to parse my Confluence pages to JSON and to check them against JSON schema to make sure that the data entered is valid

  • Develop and deploy a custom Confluence server plugin that publishes JSON data to a JIRA server

  • Develop and deploy a custom JIRA server plugin that consumes the JSON data and saves it to the JIRA database

  • Develop a Mondrian schema that defines OLAP dimensions with hierarchies that use the data saved

As a result I get an Excel pivot that features the required data grouping:

Confluence Part

In order to have my Confluence blueprint parsed and validated I have to follow some formatting rules as described in Blueprint Validation Markup. Take a look at an example:

company.xml
<ac:layout><ac:layout-section ac:type="single">
<ac:layout-cell>
<table class="wrapped">
<colgroup> <col/> <col/> </colgroup>
<tbody>
<tr>
<th>Account manager</th>
<td class="dsattr-manager dsvalidate-manager">
</td>
</tr>
<tr>
<th>Country</th>
<td class="dsattr-country dsvalidate-country">
</td>
</tr>
<tr>
<th>Address</th>
<td class="dsattr-address">
<ac:placeholder>Mailing address</ac:placeholder>
</td>
</tr>
</tbody>
</table>
...
</ac:layout-section>
</ac:layout>

This is a standard Confluence blueprint markup with some notable modifications, namely <TD>-tag class attributes: dsattr-manager, dsattr-country, etc. These define JSON attributes (note the data object in the screenshot below):

images/wiki/download/attachments/51151335/Screenshot_2020-12-22_at_12.51.44.png To get the data published to my JIRA server I created a custom Confluence plugin that handles DataValidateEvent. Let's take a closer look at the plugin's dataObjectValidateEvent method:

@EventListener
public void dataObjectValidateEvent(DataValidateEvent event) {
if (!templateKey.equals(event.getTemplateKey())) {
LOGGER.debug(String.format("DataValidateEvent for template=%s; handler is %s, aborting", event.getTemplateKey(), templateKey));
return;
} else {
LOGGER.debug(String.format("DataValidateEvent for template=%s; processing...", event.getTemplateKey()));
}

First I need to make sure that Confluence page matches my blueprint. Blueprint Validation plugin publishes DataValidateEvent to all consumers without any filtering, so I implement this filtering myself.

The event object contains JSON data serialised to string, so I parse it and add some extra data for JIRA server:

JsonNode node = mapper.readTree(event.getData());
if (!node.isObject()) {
throw new RuntimeException("Unexpected data object");
}
ObjectNode obj = (ObjectNode)node;
obj.put("id", page.getId());
obj.put("title", page.getTitle());

I use Atlassian ApplicationLink interface to communicate to JIRA:

ApplicationLink jira = appLinkService.getPrimaryApplicationLink(JiraApplicationType.class);
if (jira == null) {
LOGGER.warn("No JIRA application link configured");
event.setValid(false);
event.addMessage("No JIRA application link configured");
return;
}
...
ApplicationLinkRequestFactory reqFactory = jira.createAuthenticatedRequestFactory();
reqFactory
.createRequest(Request.MethodType.POST, endpoint)
.addHeader("content-type", "application/json; charset=UTF-8")
.setRequestBody(mapper.writerWithDefaultPrettyPrinter().writeValueAsString(obj))
.executeAndReturn((com.atlassian.sal.api.net.Response response) -> ...

Finally, I need to guarantee that the data is actually received by JIRA. If any error happens the plugin must inform a user, so I wrap my code in try-catch...

try {
...
} catch (Throwable ex) {
event.setValid(false);
event.addMessage("Failed to push changes to JIRA server: " + ex.getMessage());
LOGGER.warn("Failed to push changes to JIRA server", ex);
}

...and deny page save if it cannot be published to JIRA:

images/wiki/download/attachments/51151335/Screenshot_2020-12-22_at_13.15.08.png

JIRA Part

On the JIRA server side I need to develop an endpoint that consumes the client data and stores it to a database table. I define the table using Atlassian AO:

@Preload
public interface Company extends RawEntity<Long> {
public static String TABLE = "AO_2CAE32_COMPANY";
 
@NotNull
@PrimaryKey(value = "ID")
Long getID();
 
@StringLength(255)
String getTitle();
void setTitle(String title);
 
@Indexed
@StringLength(255)
String getManager();
void setManager(String category);
 
@Indexed
@StringLength(2)
String getCountry();
void setCountry(String country);
}

The save method is very simple:

@Override
public void save(ObjectNode company) {
ao.executeInTransaction(() -> {
Long id = Util.getLong(company, "id");
Company c = ao.get(Company.class, id);
if (c == null) {
c = ao.create(Company.class, new DBParam("ID", id));
}
c.setTitle(Util.getText(company, "title"));
c.setCountry(Util.getText(company, "country"));
c.setManager(Util.getText(company, "manager", "fullName"));
c.save();
return null;
});
}

I built and deployed the plugin to my JIRA server then tested to make sure that the Company table is actually populated. As Atlassian AO adds some prefixes to custom tables, take a note of the actual name of the Company table – in case of blueprint-validation-demo-companion plugin it is called AO_2CAE32_COMPANY.

Now that data is piped from Confluence to JIRA let's get to Mondrian schema.

Mondrian Part

Confluence Fields plugin uses Mondrian v4 schema format. You can download the default schema file as described in the documentation and use it as a sample.

Make sure that WORKLOG query contains required Confluence custom fields. My JIRA's Clients custom field is cf_11100 so the query definition looks as follows:

<Query alias="WORKLOG">
<ExpressionView>
<SQL dialect="postgresql"><![CDATA[SELECT
jiraissue.ID,
concat(project.pkey, '-', jiraissue.issuenum) as ISSUEKEY,
jiraissue.PROJECT as PROJECTID,
COALESCE(jiraissue.issuetype, '-1') as TYPEID,
app_user.lower_user_name as AUTHOR,
DATE(worklog.CREATED) as CREATED,
DATE(worklog.STARTDATE) as STARTED,
cube."TIME_WORKED" / 3600 as TIMEWORKED,
cube.cf_11100_id
FROM
jiraissue
INNER JOIN project ON jiraissue.PROJECT = project.ID
INNER JOIN worklog ON jiraissue.ID = worklog.issueid
INNER JOIN "AO_9417FD_WORKLOG_CUBE" cube ON jiraissue.ID = cube."ISSUE_ID" and worklog.id = cube."LOG_ID"
INNER JOIN app_user ON app_user.user_key = worklog.author]]></SQL>
</ExpressionView>
</Query>

I defined CLIENTS query and made sure it contains all data needed (including the Country column):

<Query alias="CLIENTS" keyColumn="id">
<ExpressionView>
<SQL dialect="postgresql"><![CDATA[SELECT
"ID" as id,
"TITLE" as title,
"COUNTRY" as country
FROM "AO_2CAE32_COMPANY"]]></SQL>
</ExpressionView>
</Query>

The matching dimension has the Country column in a hierarchy:

<Dimension name="Clients" table="CLIENTS" key="Client">
<Attributes>
<Attribute name="Client" keyColumn="id" nameColumn="title" hasHierarchy="false" />
<Attribute name="Country" keyColumn="country" hasHierarchy="false" />
</Attributes>
<Hierarchies>
<Hierarchy name="Client" allMemberName="All Clients">
<Level attribute="Client" />
</Hierarchy>
<Hierarchy name="Country > Client" allMemberName="All Clients">
<Level attribute="Country" />
<Level attribute="Client" />
</Hierarchy>
</Hierarchies>
</Dimension>

Finally I reference Clients dimension in a Worklog cube:

<Cube name="Worklog">
<Dimensions>
<Dimension source="Issue" />
<Dimension source="Project" />
<Dimension source="Type" />
<Dimension source="User" name="Worker" />
<Dimension source="LogDate" name="Created" />
<Dimension source="LogDate" name="Start" />
<Dimension source="Clients" />
<Dimension source="Contracts" />
<Dimension source="Products" />
</Dimensions>
<MeasureGroups>
<MeasureGroup table="WORKLOG">
<Measures>
<Measure aggregator="count" column="id" name="Worklogs count" />
<Measure aggregator="sum" column="timeworked" name="Time Spent" />
</Measures>
<DimensionLinks>
<ForeignKeyLink dimension="Issue" foreignKeyColumn="id" />
<ForeignKeyLink dimension="Project" foreignKeyColumn="projectid" />
<ForeignKeyLink dimension="Type" foreignKeyColumn="typeid" />
<ForeignKeyLink dimension="Worker" foreignKeyColumn="author" />
<ForeignKeyLink dimension="Created" foreignKeyColumn="created" />
<ForeignKeyLink dimension="Start" foreignKeyColumn="started" />
<ForeignKeyLink dimension="Clients" foreignKeyColumn="cf_11100_id" />
</DimensionLinks>
</MeasureGroup>
</MeasureGroups>
</Cube>

To test this schema install Tomcat and get your external Mondrian server running as described in the documentation, then use Excel to do your JIRA OLAP.

The demo projects referenced in this blog have other templates defined, namely Contracts and Products. Feel free to extend the projects to your needs, you can create very complex JIRA reports using the technics described above.

There is a DEMO Mondrian server that you can connect to test JIRA OLAP in live:

Endpoint

https://demo.mesilat.com/mondrian/xmla

Username

xmla

Password

xmlaxmla