Universal Service Queries for Serverboards

  Visit at GitHub

Serverboards 18.04 will introduce our take into Business Intelligence by using Universal Service Queries. USQ allows us to write SQL queries that access remote servers and services seamlessly, mixing them in the queries and allowing to tap into the intelligence hidden in their connections.

Why several sources of information

Any organization uses many sources of information. From external services like Google Analytics, to internal ones as Excel files, including CRMs and ERPs.

But each of these service is a different island with its own information, and there is no easy way to make them talk each other and more importantly to discover the connections between the data.

Even if an organization is still stuck into the 90’s with spreadsheets everywhere or even a master excel with all the organization intelligence, it’s difficult to tap into the data connections.

Moreover even if you are totally into the late 10’s, using everything from Google Analytics, Instagram, Facebook, Mailchimp and whatnot, each one of them is its own owner of information, and tracking properly a campaign that may span several services is normally quite difficult.

But, what it we can see all data in the same format and work over this data?

This is where Serverboards’ Universal Service Queries come. Using our technology we make everything a remote relational database table. If you are not into databases, you can think of it as many excel spreadsheets, with a header and row data.

Data manipulation with SQL

Structure Query Language (SQL) is a programming language for data extraction an relation navigation. Its a industry embraces technology that runs on almost everything data related. There is huge amounts of knowledge and although it has its quirks, they are perfectly known.

For USQ we choose to use SQL as the data manipulation language for all these reasons. Its not the easiest way to manipulate the data, but indeed its one of the best known and powerful.

In the future we want to add a graphical editor for the queries, so non technical people can create their own queries and tap into this data stream.

For now you can use USQ for nice looking dashboards that grab the data directly from the sources, and to create triggers for your rules and alarms. In the future this simple idea of direct access to data using SQL can be extrapolated to many more applications.

Data warehouse or not?

Standard approach to Business Intelligence passes through a Data Warehouse that periodically grabs changed data from the source systems and dumps it into a local database, which then is queried when appropriate. Also typical slow queries are precalculated so that the data is faster to access.

We do not follow (yet) this approach as we do not currently need it. We use similar approach per plugin to keep a cache of the data so we do not pound on the remote server and to improve response time.

This allows us to only request the data when is currently used, and to have a more dynamic environment where real time information can be obtained.

It also allows for dynamic tables, that return different data depending on the query itself. For example it can be used to extract localized data, but only when passed a specific language code.

But in the future we may add a Data Warehouse layer to allow faster queries and offline use of remote services. Stay tuned!

Technical details

To be able to have USQ we developed an SQL engine that do not rely on local storage, but on diverse “extractors” that know how to extract the proper information from the original source.

This allows to decouple the data extraction and the manipulation.

Data extraction requires only two functions, schema and extractor which provide first the shape of the data, and then the data itself. The extractor receives some constraints on the data, as a date range.

A very simple extractor in python is:

def schema(config, table=None):
  if not table:
    return ["random"]
  return {
    "columns": ["random"]
  }

def extractor(config, table, quals, columns):
  min = int(get_quals(quals, "random", ">", "0"))
  max = int(get_quals(quals, "random", "<", "100"))

  n = random.randint(min, max)

  return {
    "columns": ["random"],
    "rows": [[n]]
  }

def get_qual(quals, op, column, default=None):
    for q in quals:
        if q[0] == column and q[1] == op:
            return q[2]
    return default

It has some quirks, as not knowing the type of the quals (int? string? just cast it), but its as simple as it gets, and allows very powerful expressions to be made in a language that shines for queries: SQL.

Even if you don’t know how to program in Python, sure you can see the beauty of simplifying the creation of extractors, so Serverboards does not impose any unnecessary difficulty creating new ones for any other data source.