STARLIMS + Google Data Connector = lazy lab data

Yup; almost a full year without a post: shame on me. But here I am, with something cool for you STARLIMS users/developers/adminstrators!

Recently, I was looking for a good reporting tool – thinking to replace SAP’s Crystal Reports – and somehow stumbled upon Google Data. Google Data is kind of a mix in between Microsoft Power BI, Tableau, QLik and any and all Data visualization/analytics/findagoodbuzzword tools.

… Except it is free. Kind of.

DISCLAIMER: This project is in progress and I do this on my free time at home. I do not want to release the google script code just yet, but I do want to share progress and see where it takes me. Please contact me directly if you wish to know more and want to use this. Right now, this is closer to prototype than anything else.

There are good tutorials on writing your own Data Connectors. Google is really working hard to get developers on board. Of course, since this is not a very well known tool yet, it is still quite tricky to get going, even when following the said tutorials.

But chances are that if you read this, you know me. And you know this is the kind of things that intrigues me. Challenge! New playground! Undiscovered lands! So here I am: I have given it a try, and I will share my work here. Freely. Eventually, who knows; maybe someone somewhere will use it!

About STARLIMS…

Given the nature of STARLIMS, you can kind of reverse-engineer the code so I will, aside from giving you here a (somewhat) working solution, explain what I have done and share the connector’s code so you can make it better.

To know more about STARLIMS, visit their web site: https://www.informatics.abbott/

To learn how to build your own Google Data Connector, head to https://developers.google.com/datastudio/connector/get-started

That’s where I started.

Google Data Connector

Now, what’s behind a Google Data Connector? Basically, there are 4 functions really:

  1. Authentication. For STARLIMS, I am using the Username and Password approach. I pass these in headers, so if your connection is secured (HTTPS) then you’re good. If you use normal STARLIMS authentication, then this will work for you. Where I work, we use AD, and it works fine too.
  2. getSchema. This is critical – we need to provide the connector, at the beginning, the full schema of the data. Given the size of STARLIMS, I am opting for either
    1. Predefined entities (Folders, Orders, Ordtask or COC)
    2. QBE Templates (code-based)

I think I can manage the schemas from these quite easily. I need to know field names, labels and types, whether they are measures or dimensions; that’s it.

3. getData. This is tricky. This will pass a sub-set of the required fields, and the data must match the subset of these fields. It can be done, but one needs to create the right SQL statements based on the schema.

4. getConfig. This ties everything together. The way I built it is to define the URL of your connection (aka STARLIMS URL) and whether you will use a specific QBE or a predefined entity. When you first create a connection, it will require username and password. I am not storing these, google is; it is up to you then to decide if you trust google or not with the data…

And that’s it, really! This is the kind of charts you can do in a few clicks:

Of course, these are very simple / silly examples. But it is to show that you can create viz easily, and good looking ones at that!

My idea / goal is to extend my connector and code so you could create dynamic dashboards on your STARLIMS Data using existing QBE templates you spent hours / days to optimize and clean. I think this will be definitively worth it.

You can get started here. I will update the STARLIMS Code and enhance the connector. For now, all I do is a simple query on FOLDERS and ORDTASK to kind of have a proof of concept; it actually works quite well!

Let’s see where this gets me in the future!

Cheers,

Michel

P.S. Remember: this is work in progress and not production-ready!