Since I had my start in the TRIRIGA world over 10 years ago, I've had clients asking me to perform complex data manipulations. It started out as Crystal Reports, then everything was migrated to BIRT (and I still have nightmares about getNativeValue issues and layers of grids!). These tools were quite static whereas we were given specs and had to build out the report for our end users.

Next in my journey, I started looking into Tableau. My coworker, Fred Chun led our BI efforts and made us see how powerful dedicated BI tools can be. I actually built a TRIRIGA to Tableau integration using the Tableau Web Data Connector and a custom Java servlet. This allowed users to add TRIRIGA queries to their Tableau dashboards.

These days, if a client doesn't want to use BIRT, they have few options. Kurve is one of the leading TRIRIGA reporting tools alongside dedicated BI platforms like Power BI, Tableau and QlikView. Each tool has its own upsides and downsides in terms of complexity, data security and user-friendliness.

In my last post, I showed how you can run a small language model using WASM. Let's take it a step further and run an entire SQL server, editor and data analytics library with WASM. DuckDB is a lightweight data analytics database that has a WASM port. This instance runs completely in memory and performs quite well! I also looked at alternatives such as Postgres which has a WASM port that supports saving data locally too.

The goal of this application is to give power users additional control over their data. They can use it to simply join TRIRIGA datasets with generally better performance or incorporate external data sources. Using Perspective, I can display the query results in a data table and allow the user to export the dataset for future use.

For a quick demo of the application, take a look at this video:

In the above video, I took a few mocked TRIRIGA data sources and added my own badge swipe data. The first query matched the badged employees to my local data and the second result showed me employees that badged into buildings where they do not have a primary seat assignment. Such a simple case would be quite difficult to build in TRIRIGA whereas SQL gives us the capabilities in one query! 

During development of this tool, I did encounter some issues with the TRIRIGA API. Using my SDK, I utilized the newest platform APIs for retrieving data. The issue with this approach is that IBM provides all data as strings, therefore certain fields required manual conversion. The docs give us this example response:

{
	"module_id":8,
	"data":
	[
		{
			"0-RecordInformation-triSpecClassCL":"Utility Meter",
			"0-RecordInformation-triBrandCL":"",
			"0-RecordInformation-triIdTX":"EQ-1000329",
			"1-RecordInformation-triNameTX":"Building1 Meeting Room 1",
			"has_access":"true",
			"0-RecordInformation-triSerialNumTX":"",
			"record_id":"128600829",
			"0-RecordInformation-triImageIM":"",
			"bo_id":"10008338",
			"1-RecordInformation-triParentBuildingTX":"Building1",
			"gui_id":"10012038",
			"0-RecordInformation-triNameTX":"BuildingEquipment1",
			"0-RecordInformation-triBarCodeEntryTX":"",
			"0-RecordInformation-triUserMessageFlagTX":"",
			"0-RecordInformation-triFormLabelSY":"Building Equipment",
			"1-RecordInformation-triParentFloorTX":"Building1Floor1"
		},
		{
			"0-RecordInformation-triSpecClassCL":"Utility Meter",
			"0-RecordInformation-triBrandCL":"",
			"0-RecordInformation-triIdTX":"EQ-1000329",
			"1-RecordInformation-triNameTX":"Building1 Meeting Room 2",
			"has_access":"true",
			"0-RecordInformation-triSerialNumTX":"",
			"record_id":"128600830",
			"0-RecordInformation-triImageIM":"",
			"bo_id":"10008338",
			"1-RecordInformation-triParentBuildingTX":"Building1",
			"gui_id":"10012038",
			"0-RecordInformation-triNameTX":"BuildingEquipment2",
			"0-RecordInformation-triBarCodeEntryTX":"",
			"0-RecordInformation-triUserMessageFlagTX":"",
			"0-RecordInformation-triFormLabelSY":"Building Equipment",
			"1-RecordInformation-triParentFloorTX":"Building1Floor2"
		}
	],
	"response_id": 1643138269175,
	"result_has_next":false,
	"result_page_number":1,
	"result_total_size":2,
	"result_count":2,
	"result_has_previous":false,
	"result_total_pages":1
}

If casting fails for any field, then the original string value is used. To remain consistent with how DuckDB identifies field types, I've also added logic to convert numeric text fields as numbers (e.g. control numbers that are mapped to ID fields).

Another issue was with the property names. Dashes are not great for column names and I did not want the columns to start with a number, therefore using a simple transformation script, the properties were modified. This will allow end users to simply type in their column names rather than enclosing them in double quotes.

I've moved all data processing logic into a web worker to keep the UI responsive while data is being retrieved. This was probably not necessary, but I also used this project to learn new methods!

Future Plans

This initial release should be functional enough, but it can definitely be improved:

First, the tool should save queries and data sources in local memory. Similar to the stack tool, we can utilize local storage to save the config and re-load the data when the user opens the page. I don't envision saving the actual results in memory since we have a direct link to TRIRIGA and users can always re-upload their local files.

Next, sharing results from TRIRIGA queries should be possible. Perspective has configuration parameters which can be encoded in the URL. The goal here would be to eventually embed the data tables or charts directly in TRIRIGA.

Finally, it would be great to add interactivity to the data tables. I can expose the Spec IDs and generate links which allow users to open records directly from the application. Again, this should not be difficult to complete.

Show Me The Code!

Sure! You can find the entire code repository covered in this post over here. Feel free to contribute or open issues and enhancement requests. All feedback is welcome!