Integrating and extending BI tools

[note : if you are only interested in the technical part, skip ahead]

Motivation

BI evolved dramatically in the last years, or better, what we do with data and how we do it saw emerging trends that transformed the role and usage of BI tools in our projects.

New tools emerged in the landscape, at first glance more powerful, probably capable of delivering “nicer” reports and analyses.

But.

The eternal and in my opinion never met promise of the “self service BI” is still behind the marketing campaigns of many vendors and drives the development of the tools.

This has been a constant since the 90’s and I am wondering if we should ponder on our expectations.

The assumption was that the IT departments would provide feature-rich off the shelf tools to the business community, maybe connect it to an RDBMS, a DWH at best and call it a day: job done.

Did it ever work for you?

I did have a few extremely active communities who accepted the challenge and got organized gaining the needed skills, some even built communities of practices around those.

My hat is off for those precious and rare cases, but in average it did not happen.

The “self service”? promise worked when the tasks at hand were trivial: a table, a couple of filters, maybe two groups and associated rollups.

But dealing with data and intelligence, in most cases, requires a lot more, this is why there are specialists to help with that, even letting aside data science for a moment.

For this reason I would like to present a fictional scenario which involves data, information, intelligence and subsequent action.

The scenario

You are a fictional school with students who go through a learning program, every period they are graded on different courses and eventually they proceed to the next period or retake the current one.

To make "progress/retake" decision a number of stakeholders meet at the end of the period and gather information about each student using an extremely rich report which provides data about their grades on each course.

Users can drill down to single assessments if needed, check the absences of the student, eventual remarks from the teachers, averages from previous periods etc.

Stakeholders like to have an overview of the student information, but also be able to quickly jump to details if they need.

Since the analytical tool allows them to gather the needed information and transform that into intelligence, the can decide if a grade should be moderated, capped, if mitigating circumstances should be applied. They can act and record their decisions.

WriteBack

In our fictional scenario, we could imagine the class stakeholders navigating reports and analyses, but where are they going to record their decisions (for the sake of simplicity let’s imagine they are writing down grades only)?

If your guess was an excel spreadsheet, you are probably right. Yes, they could eventually enter the decisions in the transactional system directly, but it is usually impractical because you have to select the student, select the course, enter the grade and click save. With excel you write a number in a cell and you are done, much faster.

Then somehow whatever is in excel has to go into the system, so there is a post-decision phase.

Can we do better?

Reports are usually not created to write data back to a system or db, but this functionality is around since many years, often used in budgeting tools, plugged on top of OLAP cubes.

Then just select a BI tool that supports the writeback functionality!?

It would be nice if it was that simple.

Let’s? describe this potential solution:?

Your BI tool now connects to the db and can pull out reports, link them with drill throughs and provide all the intelligence your stakeholders need.

Hopefully you have an SSO based authorization for the report system and your app and reports are also embedded in the transactional application as well. Now, how do we manage the security for the writeback?

You should not write directly to the DB but eventually leverage an existing API provided by your application and find a way to authenticate your requests against it.

Probably doable, depending on the flexibility of the tool you chose.

An alternative solution

What if, instead of using the perfect tool for that specific situation (which might or might not exist), we use a tool that does not natively support any of that, but can be easily extended and integrated to do so?

See, this is one case, which might or not apply to you, but over the years many cases will appear as new requirements emerge for your evolving project. Some of these requirements might not be met by the solution you picked to solve your previous problem.

A flexible (flexible, NOT agile!) tool will always be a flexible tool.?

Flexibility comes with a price: you need to have some skills, namely coding probably. You do not need to be a champion because the technologies we use nowadays are usually simple, a junior developer would be more than enough if supported by a decent architect who guides him/her with the big picture.

The problem I see is that even developers are becoming a rare commodity in IT departments as the “self service / tool does everything for us” mentality reduced the (need for?) technical skills there.

We proudly call it “standardization”, we select a technology (usually the one that comes from the vendor with the shiniest marketing presentation) and no matter what we need to do, we use that one because we are not able to use anything else.

I don’t know, but I feel that when standardization is fueled by ignorance, the resulting standards might be sub-par… maybe it’s just me.

The technical part

Many different tools could be used, probably, for this solution.

?I chose Eclipse BIRT because it is open source (commercial options available) BI tool which allows many options for integration, customization and extension.

BIRT is Java based, has a viewer that can be hosted under an app server such as Tomcat and offers already a decent set of query and visualization capabilities out of the box.

You can probably replicate the same solution with other tools, maybe closer to the technology stack you use, if they are flexible enough.

?First thing first, let's create an oversimplified (demo friendly) data model in mySQL?

After populating 3 students and a few courses, this very simple query allows us to query the marksheet :?

select s.*,c.*,m.mark?
from student s
?join? course c
left outer join marksheet m
on m.studentId = s.studentId
and c.courseId = m.courseId
where s.studentid = ?        

In BIRT we create a new report, create a DataSource that points to the mySQL database and a DataSet based on the query above.

We also need a Report Parameter which we will call studentId and we will bind to the query parameter seen above.

A little bit of formatting, images etc and we get in few minutes a simple marksheet report.

Nothing too fancy, in a real life scenario you would probably have way more information, nicer formatting etc.

At this point we want to be able to integrate the report with a transactional web application, which I do not have, so I will create a mock application, imagine the (static) html code here is generated by your app in the backend

I inserted an iframe to embed the report we created, the application at this point looks like this:?

and the architecture we created:

A frontend Web server serves the static html code that mocks the web application and the BIRT report viewer is installed on a Tomcat application server, potentially the same that in a real life scenario would host the web application backend if such application was created in Java.

Since our report is embedded in the web application, we are using the html format to render it (BIRT allows to render in many different formats and even to create your own emitters should you need to).?

We want the report to look like the application and we want it to be interactive, for this reason I am adding to the HTML code of the report the same CSS I am using for the application (Bootstrap 5 in the case of this demo) and also JQuery.

BIRT has many different hooks for event handlers, which normally are executed in the backend at various point of the report lifecycle, meaning in the various steps in which the report is generated and rendered.

These event handlers can be written either in java or javascript (uses Rhino, they run on the server).

One specific event is used to add client side code, we will use this one to include css and , files we want in the rendered html.

Since now we added libraries, style and the eventual needed initializations, we can use them to provide some interactivity to the report and make it look like our web application.

We want to add a mouse-hover effect to the lines with the student courses so that, when we hover the mouse over one of the lines, it gets highlighted in orange

We want to identify the table rows that contain student data since in a real report we might have multiple tables and one? simple way is to add an html element in the rows? with an attribute such as dataType="course".

At this point with a little bit of JQuery magic, we can add the mouse-hover effect

?

This js code can be inserted into an html element we place at the bottom of the page, so we are sure that when it is executed, the rest of the report has been already rendered and therefore the DOM has already the element we want to interact with.

In our mock web app now we add a click event handler that allows us to call the report passing the correct studentId parameter

Since I used the same stylesheet and tried to have similar styles, we can hardly see where the web app ends and where the report begins, from a user point of view they are one single component.

We also want to add an event that, if we double click on a row, in the report, in the Mark column an edit box appears and we can enter a value, later on we will need a way to post it to the RDBMS, the so called "write-back".

This creates an input field in the last td of the tr element and sets the initial value to the mark the student currently has for the specific course.

When the user leaves the edit box (i.e. presses "enter") the on change event is fired, so we call a "postValue" function .

But, HOW is postValue capable of writing back the value to the DB?

Obviously our architecture at this point is missing a couple of components: An API to post the grades and some security.

The backend

Your application normally already has a proper API to write data into its database, this API uses a jwt token or something similar to check if the call is legitimate and therefore should be considered as authorized.

As I do not have a real backend application in this demo I will simulate this with a servlet that generates a token (since it is just a demo, it will not? require username and password).

An easy way to secure api servlets is to use a Filter servlet, you probably already have some mechanism implemented in your application so, you will use that one, eventually.

but since also the BIRT viewer is a servlet, then we can secure it with the exact same filter

This goes in the web.xml file of my API app?

and this for the BIRT viewer app

Now, let's add the token management in the mock html app and see what happens when the token is passed correctly and when it is not!

Here we are retrieving the token and passing it correctly to the report viewer

We can see that the report displays correctly without any issues, but if we alter the token value..,

Let's now create the setMark API

It expects sid, cid and mark (sid being studentId and cid being courseId), with those then attempts an upsert in mySQL.

Here, in your real application you probably have some additional security such as role filtering etc.

Now our architecture diagram looks like this:?

We need to link the BIRT report to the setMark api, the token will be passed from the web app to the report via a report parameter (conveniently called "token") and from the report to the API.

token and studentId are set as global variables? during the report rendering, taking the values from the report parameters.

With those we perform an Ajax call to the API.

Usually I like to give the users the "excel vibe" allowing them to post values without the need of confirming with a a "save" button and notifying them when an asynchronous operation is ongoing making the field they modified orange during the operation and then red or temporarily green when such operation is failed or successful.

This allows them to quickly modify as many values as they need regardless of the responsivity of the backend application. Typically they will never see the orange phase, unless the API is rather slow.

And that's it!

We integrated a report with a web application, we linked them to the same security and allowed some interactive behavior in the report itself which, with the help of an API was able to send data back to the transactional database.

Conclusions

This was a fun experiment for a rainy Saturday, but you can easily see how, implementing tools that are designed to be open to extension, customization and integration, you can deliver solutions that are closer to the business functions needs.

How to implement those solution should be up to you, not the tool, we could have easily swapped the servlets for serverless APIs, we could have used JS, PHP… and with BIRT we just scratched the surface, did not even have to code java event handlers or our own emitters.

I hope you can see that in a scenario where reports might be more complex that the one we saw here, might be linked together with drill throughs etc., it could be beneficial to build? a solution in this way rather then coding complex logic and visualization in your application itself.

There is the ever lasting discussion between the "make or buy" and in the last decades the "buy" always wins.

In my opinion we buy products, we make solutions.