The rise of large-language models, such as GPT-3, has been a significant development in the field of natural language processing (NLP). These models are trained on massive amounts of text data and can understand and generate human-like language with a high degree of accuracy, as well as source selections from a vast repository of factual information . This has led to a wide range of potential applications in the analytics space.

At Sisense, we noticed this, and found that we could build an application to leverage GPT-3 to let businesspeople easily ask questions about relatively modest datasets (about the size of the CSVs business users often email to each other) without any formal training in analytics. We were able to build this really quickly, like in a day or two.

Further, we realized that a lot of business questions actually require some convenient information that isn’t likely to be in the supplied data, and that GPT-3 could fetch it for us without a lot of trouble.

For example, if we wanted to know whether ad campaigns on LinkedIn or Reddit were more effective based on the industry vertical of the prospects we were looking for, and we had a bunch of historical sales opportunity records and ad campaign records, but *not* the industry label of the prospects, we could ask GPT-3 to go fill in that data for us, and it would do so, really quite reliably. We could also have asked our Sales Ops team to do this work, and waited a week for the team to make time to reconcile the labels used by Crunchbase and ZoomInfo and Hoovers.

Other not-impossible-just-time-consuming-or-expensive-to-generate labels : sentiment of support emails, competitors mentioned in free-text fields in account records, revenue of companies by name (not always reliable), etc, etc.

Making use of GPT-3 is pretty straightforward on a one-off basis. However, leveraging the power of large-language models for business intelligence at scale requires a highly extensible analytics platform in order to make the source data easily accessible, and to integrate the augmented results back into the flow of analysis.

How do we know that? Because our AI team built an integrated system that does just that. Let’s walk through how we took advantage of GPT-3 using Sisense (and how you can, too!)

Getting Started

Let’s start with stating the problem we want to solve: “How do we equip business users to augment their data using GPT-3, and explore the results, from within a dashboard environment?”

As defined, this is a pretty big challenge. We can break it down into a few pieces to make it easier to tackle:

We can assume that our business user has identified some data she wants to analyze using a BI interface – for example, a filtered list of accounts – so we’ll need a mechanism to identify the relevant data, funnel it to GPT-3, and ask questions about it
Our business user will need some way to take any augmented data supplied by GPT-3 and push it back into the database where the original data is stored, so that it can be used in queries and charting.
We can also assume that our business user doesn’t have the capability – or the desire! – to extend or update BI semantic models by hand, so we’ll need some way to incorporate the augmented data back into the semantic model of the BI environment – basically, set up the table relationships between the original data and the augmented data in the model – automatically.

Let’s start with step one. First, we’ll need a mechanism to call GPT-3 programmatically. Fortunately, that’s pretty easy to do, since OpenAI makes a convenient Python package available, and Sisense has a Jupyter kernel with a Python runtime built into its data modeling infrastructure. So we can write our interface between Sisense and GPT-3 in a python notebook, and we’ll have access to all the Sisense data services, as well as the responses from GPT-3.

Let’s make sure we have the OpenAI package available:

And then sketch out the basics of asking GPT-3 a question:

There’s more to it, of course – JSON formatting, parameter passing, result caching, HTML formatting of results, and of course we’ll need our OpenAI key – but this is the core of the operation.

Now that we’ve got a basic mechanism for passing questions to GPT-3, we’ll need a mechanism for a Sisense user to enter a prompt. For that, we’ll turn to Sisense’s mechanism for providing custom interactivity, Sisense BloX.

BloX allows an interactive dashboard widget to be defined in JSON, making it really simple to create controls and readouts that respond to user input. We can define a control surface for taking user input as a question for GPT-3 like this:

BloX has a straightforward model which allows us to define user interface components in JSON. We’ll start with a body tag:

And we’ll define a Container to hold a free text input to allow the user to enter a question:

Using a ColumnSet for layout, we’ll define an ActionSet to create a ‘Search’ button that we’ll use to submit a user’s question:

Finally, we’ll define a Container in which we’ll render whatever output we get back from GPT-3:

So far, we’ve just defined a UI; our component doesn’t have any logic to it. Let’s add an Action to our ActionSet which will execute on the Sisense instance a Notebook with the python we defined earlier for interacting with GPT-3:

BloX actions are written in javascript, just like any Sisense widget or dashboard customization (so note that we’ve shifted from Python to Javascript for this part of our reference implementation). First, we do a little bookkeeping. (Prism is the class used to access all Sisense componentry):

Now we’ll write a function that will direct Sisense to execute our Python code, which we’ll name callNoteBook.

We want Sisense to execute our Notebook in the instance’s python compute environment. As an analytics engine, Sisense expects requests to be queries against data models, but it also has a robust facility for running Python or R code after a query has executed (usually to transform the query result).

In this case we’re just interested in running some Python code and we don’t need to fetch anything from a data model, so we’ll construct a no-op query against a trivial data source called AITransformation which we created specifically for this purpose. (Fetching data from a model before we ask GPT a question will be useful later, though!)

JAQL is the definition language Sisense Fusion uses for queries.

The transformData tag tells Sisense that we want to execute some code after our (empty) query returns, the code we want executed, and how we want it executed.

customCode is how we reference the Sisense infrastructure for running arbitrary code in a python kernel – in this case, we’ll use it to execute a Jupyter Notebook (DataSearch1.ipynb) we’ve defined that contains the implementation of our ask_ai() function, and all its associated control logic.

Here we’ll pass the user’s question to the notebook using the data in the POST request to the kernel.

We’ll add the widget ID so that our Notebook can provide dynamic logging that our BloX widget can display.

We’ll package up our request for execution:

Now we’ll set up how we want to handle displaying the result to the user. We want this UI to be interactive and not leave the user hanging while GPT-3 works its magic, so we’ll design for the widget to update the user with what’s happening as we process the question. We can update the output area with some text to confirm that execution has started:

Next we’ll want to handle any responses we get through the Notebook from GPT-3, and update the widget UI accordingly:

And we’ll use a setInterval to periodically check for updates:

To deploy our component in the system, we’ll create a new BloX widget in a Sisense Fusion dashboard – we could then embed the dashboard, or just the widget itself, anywhere our users need it to be, or we can use it in the Sisense IDE – and we’ll insert the JSON and javascript we built above into the editor, which gets us to this basic user interface for interrogating GPT-3:

We’ve built a decidedly stripped-down look and feel – we’re basically at the breadboarding stage here. Users can type in a question in the ‘Your Message’ field, and use the yellow ‘Search’ button to send the query toff to GPT-3. Responses get rendered below, as an HTML table (how this particular magic happens, we’ll cover below).

Great! We’ve established that we can communicate with a Large-language Model via API using the python kernel in Sisense Fusion, take user input interactively, and present returned data back to the user in a user interface. While it doesn’t seem like a lot, we’ve gotten remarkably far in a short time – from here, we can rapidly build up the mechanisms to incorporate existing BI data into the user’s questions, write results back to a database, and incorporate those results into the BI system.

Let’s say we want to use GPT-3 to augment some data we already have in a database – for example, to identify which of our customers were acquired in the last four years. Instead of manually supplying a long string of names, could we simply reference an existing list somewhere? Our question might be phrased like this:

“Which of the following companies got acquired between 2018 to 2022 [Dashboard “Customer Base” widget Customers column name]”

In order to do this, we’ll need three new capabilities: a parser for a user’s question that can determine the data to which the user is referring, a mechanism to fetch that data from the BI system, and one to tell Sisense to pass that data along with our question in a format GPT-3 can handle.

The first one could be tricky – but wait! We have a highly capable large-language model at our disposal that we can leverage to parse whatever text the user supplies and from it determine what the user is referencing.

Since we have our handy ask_ai function, we can use it to pass the question to GPT-3, and ask it to extract the dashboard, widget and column entities from the sentence. But for GPT-3 to have a fair shot at understanding what we’re looking for, we’ll need to give it some examples first.

Let’s define a helper class to keep the training logic together:

First we’ll give GPT-3 some examples of the question format we’re anticipating.

We’re also going to need to concatenate these training questions we’ve defined with the actual question we’re asking GPT-3 to parse. We can put that into another utility function:

And we will use the next function to generate the question for GPT-3:

Once we receive a response from GPT-3, we will have to parse it and extract the relevant entities. We can extract generic entities like this:

And we can assemble these tools to extract the dashboard, widget and column names:

Now that we’ve articulated how we’ll present the user’s question for parsing, let’s do it!

The next component we need to build is a mechanism to take the entities we’ve extracted from the user’s question, and map it to the data the user is referring to from Sisense. We’ll use the Sisense API to do this. The utility class AIUtils contains a Sisense connection attribute.

SisenseAPI is a utility class we’ll use in order to get access to different Sisense Fusion components, such as the data source schema, widgets and dashboards, as well as the query interface. For convenience, we’ll define a helper function to handle formatting requests to Sisense REST APIs:

In order to translate from the dashboard, widget and column names provided by the user to the actual entities managed by Sisense, we’ll need the corresponding IDs for each. Let’s start with the dashboard name we extracted earlier, and use the Dashboard API to get to the underlying ID of the object:

We’ll do the same for the widget element, using the widgets API:

A quick iteration to find the widget ID we’re looking for:

With the information about the dashboard and widget in hand, we should be able to assemble a query to fetch the data represented in the widget (so that we can get the column entries we’re looking for). To do this, we’ll need to build a JAQL representation of the widget query.

First, we create a basic JAQL element, which we can later enrich with the widget information:

Here we’ll extract all the relevant info regarding the requested widget (and dashboard)

Next, we enrich the widget JAQL with widget data and widget filters (which are defined in the widget level).

We’ll also need to apply any dashboard filters (which are defined in the dashboard level and can be seen within Sisense UI in the right hand rail)

Now we can complete get_widget_jaql by delivering our fully-expanded widget JAQL:

What does having the fully-expanded JAQL of a widget get us? The ability to run it, and get data back, of course!

We can extend the SisenseAPI class with a new method, run_jaql:

And now that we’ve taught SisenseAPI to call the datasources API, we can use it to run our query and get the data back:

Here’s how we’ll make use of the routines we’ve just assembled:

Finally, we can lean on all of this to pass fully expanded questions to GPT-3. We’ll actually paginate and batch questions, as well as reintegrate the responses, so that we don’t overwhelm the relatively (by data standards) modest length limits of questions that GPT-3 can handle:

Here’s how we’ll issue the batched calls:

Stitching the responses back together into a dataframe is straightforward:

But what do we do with this table, currently sitting in memory in our python kernel? It’s not connected to anything yet, so we can’t query it or leverage any of our BI infrastructure to engage with it.

The most useful thing for us to do would be to add it to our data model. We could push it all the way back to an origin database with a create table statement that we send off to a database, but for now we can simply encode our data in a custom import statement of a new table in our model.

get_import_query isn’t anything magical or mysterious – it translates a table into a sql expression that recreates the table. (In the reference architecture we’ve published, you’ll see that we’ve implemented it for both Redshift and Snowflake syntax)

But let’s take a closer look at create_table_with_query:

This function uses the Sisense datamodel API to add a new table to an existing data model (that’s why the call to utils.delete_table() almost immediately beforehand – don’t want to create a table that already exists! – and once that new table (with our import query that recreates the data we received in the dataframe) is in the model, it’s fully part of the BI system.

Well, not quite – for the new table to be query-able, we need to publish the model – publishing is a thing because we want to be able to edit models without having in-process changes affect other users – these APIs are used by the Sisense IDE, and other apps, where live-editing of production can be bad. Fortunately, there’s an API for publishing an updated model:

As our very last step, we want to update our BloX widget with the result supplied by GPT-3 so that the user has a visual response, not just a hidden-away new table! So we’ll convert our dataframe to HTML:

In order to both render the result of our GPT-3 question, we’ll actually generate two HTML strings that represent our result. The first will be the result which the BloX widget will display (including any logging information we produced):

And the second will be used by the BloX widget script to generate a new visualization on the dashboard that includes the augmented data:

But how do we get this HTML table back to the user? We’re still in our python context – far away from the end user’s client. Remember when we were defining our BloX action, relatively early in this post?

We said we’d use this code to update the BloX widget with whatever our Python notebook sent back. Now that we’re sending back properly-formatted HTML, our BloX widget is going to make a new TableWidget out of it, and display that to the user.

Finally, we can grab the embed code for this widget from Sisense:

And our augmented analytics mini-app (with a few tantalizing spoilers for our next post) can now live anywhere on the web!

Wow! With just a little javascript, and a bit more python code, we’ve connected two very powerful systems – an embedded analytics platform with access to cloud data warehouses and semantic modeling machinery, and a generative AI capable of highly flexible human interaction, and we’ve built a tool that can fetch augmented data for users with little or no upfront training! It’s worth taking a pause here, and an inventory of what we used to do this:

Analytics Platform with access to data and semantic models (Sisense)
Large Language Model with a REST API for asking questions (GPT-3)
Low-code interactive UI toolkit with Javascript actions (Sisense BloX)
Javascript frontend runtime and API (Sisense)
Server-side Python compute environment, with network and storage access, invoked from front-end javascript (Sisense Custom Code Transforms)
APIs for interrogating semantic models and dashboards (Sisense)
APIs for running new data queries against existing semantic models and database connections, without defining them up front (Sisense)
APIs for modifying existing data models on the fly, and pushing updated models into production dynamically
Bi-directional, client-side to server-side data passing

This is already a huge blog post, so we’ll pause here. In our next technical deep-dive post, we’ll complete the story, by adding mechanisms (using Sisense APIs) to insert the augmented data tables we got from GPT-3 back into our database, add the augmented table to our semantic model, ask GPT-3 what the best join path into our existing model would be for the new augmented table, use that updated model to drive end-user slicing and dicing, and leverage GPT-3 to summarize individual charts or whole dashboards for us. Or just jump ahead and take a look at the completed code, read more about LLMs and augmented analytics, or grab an installable version of the reference architecture.

How to make BI and analytics adoption simple with large language models like GPT-3 and ChatGPT

Get the latest in analytics right in your inbox.