Forem: Hannsta

Weekend Project - Building an NLQ App with ChatGPT

Hannsta — Tue, 03 Sep 2024 11:59:38 +0000

Part 1: Generating the Data Model

It’s a common refrain - “Just ask your data a question in natural language!”.

Having spent most of my career in the space, I can't help but think... it looks so easy…

So, my goal over the next few posts is to see how far I can get using open-source software and AI. My starter goal is the table stakes:

When a user asks a simple “What” question, my app will return a result set with an answer.

My flex goals are a bit more ambitious

The data setup should not involve any manual user interventions. All GPT.
The result should be flexible and dynamic so the user can interact with it to ask subsequent questions.
The user should be able to ask “Why’ questions.

I have broken this up into a few distinct steps. The first two are covered in this article. The third will be available shortly after.

Step 1: Model Suggestion
Step 2: Model Creation
Step 3: Querying the Model

Why Not Just Have GPT Generate SQL?

Let's start addressing the obvious, it might seem like a good idea to have GPT generate SQL directly from user queries. And maybe in a year or so this will be the correct way, but for now I have found it a bit lacking. As you scale up in complexity both in the number of tables and types of calculations that need to be performed, GPT starts to forget things. In an effort to solve a complex series of joins, it may forget the correct columns you need for your visualizations.

By placing a metadata layer between ChatGPT and the SQL generation, we can simplify the job for GPT. Instead of asking GPT to generate the entire SQL query from scratch, we ask it to navigate a predefined structure. This not only improves the accuracy of the queries but also makes the system more secure. For example, implementing Row-Level Security (RLS) is much easier when you have a metadata layer controlling what data is accessible. This layer acts as a buffer, ensuring that the generated queries adhere to the rules and structures defined by the model.

Establishing the Data Model

For this example I am using RestBI, but most BI tools will support this in one form or another. In this context, a Model is the metadata layer, a code representation of your data structure —think of it like LookML for those familiar with Looker. It defines the tables, columns, joins, and other elements needed to build queries. The model abstracts away the complexity of the underlying database, providing a consistent interface for querying data. For GPT, the end result is a nice flat table structure.

One advantage of using Models here, is that they provide a set of related fields. So if a user has subsequent questions, or wants to know "why", we can let them pivot around the initial visualization, within context of the Model, without having to go all the way back to the database every time.

Step 1: Model Suggestion

Getting GPT to Propose a Starting Point

Running against a sample or test database should be pretty easy, but let’s think ahead. Most databases at any size of company have 1000s of tables. Most of these tables will be built for purpose and irrelevant to any given user’s question.

To simplify the starting point, I wanted the first step to be a list of potential Models that exist within the database. An auto-discoverer. Break out the Models by use case, tell me what tables it needs, and why.

The Prompt

The idea is this: by feeding it metadata about the database, GPT can suggest different combinations of tables that might be useful for answering specific types of questions. In this case I have obtained the metadata through the RestBI /metadata endpoint.

I then ask GPT to suggest a series of Models as a list of PossibleModel Type:

const ListPossibleModels = async (tables: Table[], userSuggestions: string | null = null) => {
    let prompt = `
    The following is a list of all tables in the database, and the columns within those tables in JSON format:
    ${
        tables.map((table) => {
            return JSON.stringify({
                table: table.name,
                columns: table.columns.map((column) => column.name)
            })
        }).join(', ')
    }
    A Model is a collection of tables and columns that can be used to create queries. Please analyze the tables
    and columns above and suggest what models could be created from them. This could be one or several models, based on the dataset.
    A model typically represents a set of business questions, and tables that can be properly joined together to answer those questions.
    type PossibleModel = {
        name: string,
        tables: string[],
        reason: string
    }
    Please provide your suggestions in JSON format as a list of PossibleModel. Do not provide any other words or text in the response.
    `;
    let response = await Chat(prompt);
    return JSON.parse(response);
}

I love Typescript, and am using mostly NextJS on this project. By providing ChatGPT the types itself, I can ensure its output matches with my own structure without any fancy manipulations.

How did it do?

As goes the trend with GPT, at first I was really happy with my results! It looked spot on… It did a good job of breaking out tables by use case, describing why, and selecting the most relevant tables for each model.

On further inspection I started to realize some flaws:

The models were typically pretty small and use case focused. There were 155 tables across 15 schemas in my database. Most of the suggested models only used 4 or 5. I know I can safely join up to 20 of these into one.
It was only picking the most obviously named tables. My database is old, like back when people were proud of their efficiency in obfuscating table names, old. Any table like this was ignored. Junk in .. Junk out .. not really a groundbreaking learning, but yea, can be a problem.

Next Steps: Refining the Suggestions

As an immediate improvement, I introduced a user feedback mechanism. A text input box in my UI allows the user to add some additional context to the process :

${ userSuggestions ? `Here are an additional set of instructions to consider: ${userSuggestions}` : ''}

Now I could say “Please generate a model that is all encompassing of sales, product, categories, and customers”.

A few ideas for the future:

When the user submits feedback, include the last suggestions. This way the user can say things like “use the first model you generated, but include these 3 tables as well”
Start with the question. Maybe I am thinking about this wrong. Maybe I need to take the user’s question, and work backwards into the model needed to answer it.

Criticisms aside, I’m happy enough with this to move onto model creation.

Step 2: Model Generation

Moving from Suggestion to Creation

This one took me a few iterations to get right, and I know there is still a lot more to be done. But if I had to grade the final result, I would give this a solid B. Not quite ready for production, but it definitely passed expectations and made me eager to keep going.

The Model object

First, let's talk about what a data model actually is. In the context of business intelligence, a data model is essentially a blueprint that defines how data is organized, what tables exist, how those relate to each other, and what columns are exposed within them.

Importantly, it defines how SQL should be generated. In tools like Looker, this is known as LookML, but the concept is similar across most BI platforms.

In our case, we are using RestBI, and the Model is represented in JSON format. This is nice because of the simplistic and controlled structure and ChatGPT tends to do a good job of generating JSON.

Given a Model and a Query, RestBI can generate a result set for us.

A case for sending Types over Examples

Everyone knows to build examples into their prompt right? Well, in the past I have found that examples tend to be both helpful and confusing GPT.

Helpful because it clearly outlines what format the result should take.
Confusing, because it often seeds a thought in GPT that may not be relevant. If my example is a sales example, but now I am querying supply chain data, GPT will tend to include sales content more than I want because the example suggests that.

Since RestBI is JSON based and created in Typescript, there are thorough type definitions for what a model is. I decided to pass in a minified version of the type definitions instead of an example model.

const modelTypes = ‘export enum DatabaseType{POSTGRES='POSTGRES',MYSQL='MYSQL',ORACLE='ORACLE',SQL_SERVER='SQL_SERVER',SQLITE='SQLITE',SNOWFLAKE='SNOWFLAKE'}export enum ColumnDataType{STRING='STRING',NUMBER='NUMBER',DATE='DATE',BOOLEAN='BOOLEAN',JSON='JSON'}export enum ColumnType{MEASURE='MEASURE',DIMENSION='DIMENSION'}export type Connection={id:string,name:string,host:string,port:number,user:string,password:string,database:string,type:DatabaseType,schema?:string,warehouse?:string,role?:string}export type Table={id:string,dbName:string,name:string,schema?:string,alias?:string,validated?:boolean,columns:Column[]}export type Column={id:string,dbName:string,name:string,alias?:string,type?:ColumnType,validated?:boolean,dataType?:ColumnDataType,aggregationType?:string}export type JoinClause={column1:string,column2:string,operator:string}export type Join={id:string,validated?:boolean,table1:string,table2:string,clauses:JoinClause[],joinType?:string}export type Model={id:string,name:string,displayName?:string,connection:Connection,tables:Table[],joins:Join[],formulas:Formula[],filters:Filter[]}export type Formula={id:string,name:string,expression:string}export type Filter={id:string,name:string,expression:string}’

First Attempt: One-Shot Generation

My first approach was straightforward: to have GPT generate the entire model in one go. The prompt was similar to what I used for the suggestions, but with the expectation that it would output a fully-formed model ready to use.

To narrow the scope of the process down to what was relevant I took the user’s suggested Model as a basis, and only considered tables from the suggestion.

Here’s the initial approach:

export const CreateModel = async (tables: Table[], possibleModel: PossibleModel, userSuggestions: string) => {
    const relevantTables = tables.filter((table) => possibleModel.tables.includes(table.name) || possibleModel.tables.includes(table.dbName));
    let prompt = `
    The following is a list of all tables in the database, and the columns within those tables in JSON format:
    ${
        relevantTables.map((table) => {
            return JSON.stringify({
                table: table.name,
                schema: table.schema,
                columns: table.columns.map((column) => column.name)
            })
        }).join(', ')
    }
    Please create a valid Model based on the tables and columns above. The model should be named ${possibleModel.name}.
    ${ userSuggestions ? `Here are an additional set of instructions to consider: ${userSuggestions}` : ''}
    Here are the appropriate type definitions for creating the model:
   ${ modelTypes }
    Do not include a connection object in the model. Include tables, joins, formulas, and filters as needed. Include schema in tables if available. Be sure to limit columns to those that are necessary for the model.
    Please provide your suggestions in JSON format as a Model. Do not provide any other words or text in the response.
    `;
    let response = await Chat(prompt);
    return JSON.parse(response);
}

At first again, I was very encouraged. The first few models GPT generated were valid! Real models that could instantly be used! But as I explored more a few problems started to appear:

Not every model was valid. Sometimes it would confuse column names, sometimes it would mix up schemas, sometimes it would forget joins.
Another problem was that the models tended to be incomplete. While my suggestion had 7 tables, the resulting model only had 2 or 3.

Second Attempt: Validation and Iteration

To address the validity shortcomings of the one-shot approach, I introduced a validation pass. After generating the model, automatically validate it against the metadata to ensure everything was accurate and complete. If the model didn’t pass validation, I send a nearly identical prompt back to GPT with the added “hey here were the errors please fix them”

I implemented this with the RestBI \validate endpoint.

Simplified, here’s how that process looks:

client.validateModel(model).then((result: ValidationResult) => {
    let errors =  generateErrorList(result.model.tables);
    if (errors.length > 0) {
        setValidated(false);
        fixModel(model, errors.join(', '));
    }
}

It was an easy adjustment, and it seems to work pretty well. Most models are properly validated after only 1 loop. I'll likely keep this going forward.

Third Attempt: Table-by-Table Generation

I mentioned one of the main struggles in the initial approach was that it would generate incomplete models, with only a fraction of the tables I was interested in.

This time, I went through each of the tables listed in the "suggested" model. Instead of prompting GPT to create a Model type, I prompted it instead to generate a Table, allowing it to focus on picking good columns, and generating aliases:

export const CreateTable = async (tables: Table[], possibleTable: string, userSuggestions: string) => {
    const relevantTable = tables.find((table) => table.name === possibleTable || table.dbName === possibleTable);
    let prompt = ` 
    The following is the definition of the table ${relevantTable.name}. 
    ${JSON.stringify(relevantTable)}
    Please create a valid Table based on the tables and columns above. The table should be named ${possibleTable}.
    ${ userSuggestions ? `Here are an additional set of instructions to consider: ${userSuggestions}` : ''}
    Here are the appropriate type definitions for creating the table:
    export enum ColumnDataType{STRING='STRING',NUMBER='NUMBER',DATE='DATE',BOOLEAN='BOOLEAN',JSON='JSON'}export enum ColumnType{MEASURE='MEASURE',DIMENSION='DIMENSION'}export type Column={id:string,dbName:string,name:string,alias?:string,type?:ColumnType,validated?:boolean,dataType?:ColumnDataType,aggregationType?:string}export type Table={id:string,dbName:string,name:string,schema?:string,alias?:string,validated?:boolean,columns:Column[]}
    Include columns that most people would find useful, ensure their display name is human readable. Include schema if available.
    Please provide your suggestions in JSON format as a Table. Do not provide any other words or text in the response.
    `;
    let response = await Chat(prompt);
    return JSON.parse(response);
}
};

Once this was done, I added the resulting tables to a new Model manually:

for (let tableName of suggestedModel.tables) {
  let table = await CreateTable(metadata, tableName, suggestion);
  model.tables.push(table);
}

I then similarly created a single prompt to generate all of the joins.

Possible next steps

Expand the prompt to include other column attributes like default aggregations
Include a pass for formulas, default filters, etc
Some mechanism to alter the table once created simply, eg: add/remove columns or tables

How Did It Do?

The improvement in quality was immediately noticeable. I have all of the tables I wanted, and a good variety of columns. And of course, it passes validation.

Obviously, there is still a lot more to do here. But it feels good enough to start testing the Query functionality.

Conclusion

It's a work in progress but I feel pretty happy for only a few hours of time.

In the next post we provide an interface to allow true Natural Language Queries and use ChatGPT to translate it into something our Model recognizes, and can query.

Stay tuned, will publish the follow-up shortly! Please leave any thoughts you have in the comments, would love to hear from you.

How to implement Drill Anywhere in React/ChartJS/RestBI

Hannsta — Fri, 30 Aug 2024 13:09:29 +0000

It's inevitable that your end users will want more depth from whatever reporting you give them in a web application.

Most people solve this by increasing the sheer number of reports they offer. This is a mostly futile effort. The more irrelevant visualizations you show, the harder it is for the user to find the one that actually answers their question.

Given the choice I always opt for interactivity over breadth. Start simple and let users dive in as needed.

In this spirit, I wanted to write an article about my favorite interactivity mechanism - what is typically called “drill anywhere", with a focus on a newer tool RestBI that makes the data manipulation component easier.

What is Drill Anywhere

Drill Anywhere is a powerful feature found in many business intelligence tools today. It allows you to click on a specific point within a visualization, and drill into that data element. Unlike a drill-down that follows a set hierarchical path through the data, drill anywhere allows you to choose from a large list of other columns, and pick any to dive into.

When set up properly, this can be done an almost unlimited amount of times. Allowing users to start at a very high level aggregate, say “sales by region”, and work their way all the way down to the individual transaction.

How it works

With the proper data model this feature can be surprisingly easy to implement. In this case “proper” generally means a relational data model. One or two central fact tables, surrounded by a series of supporting dimensions. While it is possible to implement this on flat tables, or other structures, you will get the most flexibility out of your data going with this approach.

A visualization that enables drill anywhere typically starts with one Dimensional variable (a grouping column like “Region” or “Store” or “Product ID”), and one metric (an aggregated number like “total sales”, “average profit”)

In pseudo-SQL this chart might be fed by the following query:

SELECT region, sum(sales) FROM sales_fact JOIN region_dim
GROUP BY region

When a user clicks on the chart, they are clicking on a specific region. We then present them with a list of other Dimensional variables:

State, Store, Category, Sub-Category, Product, SKU, etc

Whatever the user selects becomes the new grouping variable. The old grouping variable is added to the where clause.

For example, if the users were to select the “east” region, and Store, the SQL would become:

SELECT store, sum(sales) FROM sales_fact JOIN region_dim, store_dim
WHERE region = ‘east’
GROUP BY store

Let’s see it in action.

Libraries We’re Using

For this example we are using few specific tools:

React: Front-end framework
Chart.js: Charting library
RestBI: A light BI / analytic query layer
Material UI: UI components (drill anywhere popup)

The code below is intended to highlight the core elements of enabling Drill Anywhere. The full code can be found here.

Setting up the Data Model in RestBI

Most charting examples assume you got the data easily, and kind of ignore it in the instructions. In this case the data is the whole point, so it's worth highlighting how we got here.

If you have relatively structured data, RestBI is a great way to simplify it for analytics. Rather than write custom SQL and worry about tables/joins, we can query the data with a simple syntax (an ORM for BI?, idk.).

To use RestBI we need to first define our data model. This acts kind of like the metadata layer that exists in many BI tools, except it is all JSON so it lives in our code.

Here is a simple example:

import { Connection, DatabaseType, Table, ColumnDataType, Join, Model, Formula, ColumnType } from "restbi-sdk";

// Define the PostgreSQL connection. 
const PostgresConnection: Connection = {
    id: '1',
    name: 'Postgres',
    host: 'host.docker.internal',
    port: 5433,
    user: 'postgres',
    password: 'test',
    database: 'adventureworks',
    type: DatabaseType.POSTGRES,
};

// Define the Tables with display names
const salesOrderHeaderTable: Table = {
    id: '1',
    dbName: 'SalesOrderHeader',
    schema: 'sales',
    name: 'Sales Order Header',
    columns: [
        { id: '1', dbName: 'SalesOrderID', dataType: ColumnDataType.NUMBER, name: 'Sales Order ID', type: ColumnType.DIMENSION },
        { id: '2', dbName: 'OrderDate', dataType: ColumnDataType.DATE, name: 'Order Date' },
        { id: '3', dbName: 'CustomerID', dataType: ColumnDataType.NUMBER, name: 'Customer ID' },
        { id: '4', dbName: 'TotalDue', dataType: ColumnDataType.NUMBER, name: 'Total Due' }
    ]
};
// Additional tables (Customer, Product SalesOrderDetail, etc.)
const productTable: Table = { /* ... */ };
const customerTable: Table = { /* ... */ };
const salesOrderDetailTable: Table = { /* ... */ };
const productCategoryTable: Table = { /* ... */ };
const productSubcategoryTable: Table = { /* ... */ };
const invoiceTable: Table = { /* ... */ };

// Define a simplified join
const updatedJoins: Join[] = [
    {
        id: '1',
        table1: 'SalesOrderHeader',
        table2: 'Customer',
        clauses: [{
            column1: 'CustomerID',
            column2: 'CustomerID',
            operator: '='
        }]
    },
    // Additional joins go here
];

// Define formulas
const formulas: Formula[] = [
    { id: "1", name: 'Formula Bikes', expression: "SUM(CASE WHEN {{Category Name}} = 'Bikes' THEN {{Total Due}} ELSE NULL END)" },
    { id: "2", name: 'Average Sales', expression: "AVG({{Total Due}})" },
    { id: "3", name: 'Year', expression: "YEAR({{Order Date}})" },
];

// Define the updated Model
export const AdventureWorksModel: Model = {
    id: '1',
    name: 'AdventureWorksModel',
    displayName: 'Adventure Works Model',
    connection: PostgresConnection,
    tables: [
        salesOrderHeaderTable,
        productTable,
        customerTable,
        /* ... additional tables */
    ],
    joins: updatedJoins,
    formulas: formulas,
    filters: []
};

Setting Up the Chart

For the chart, we used a simple react ChartJS bar chart. To obtain the dataset, we construct a RestBI query that refers to the currently selected Dimension column, Metric column, and any active Filters (this will be populated as we begin to drill). This is set to execute each time the user selects a new column, or drills:

import React, { useState, useEffect } from "react";
import { Bar } from "react-chartjs-2";
import { RestBIClient, SQLResult, Model, Query, inferColumnType } from "restbi-sdk";
import { FormControl, InputLabel, MenuItem, Select } from "@mui/
material";
import {AdventureWorksModel} from './models'

//RestBI client
const client = new RestBIClient();
//Use the data model generated previously
const dataModel = AdventureWorksModel;

const DrillAnywhereChart = ({ client, dataModel}) => {
  const [chartData, setChartData] = useState<SQLResult | null>(null);
  const [selectedDimension, setSelectedDimension] = useState<string>("");
  const [selectedMetric, setSelectedMetric] = useState<string>("");
  const [drillAnywherePopupVisible, setDrillAnywherePopupVisible] = useState<boolean>(false);
  const [drillAnywhereValue, setDrillAnywhereValue] = useState<string>("");
  const [filters, setFilters] = useState<QueryFilter[]>([]);


 //If either of the columns are updated, re-run the query
  useEffect(() => {
    const query: Query = {
      columns: [selectedDimension, selectedMetric], //initially provided by user updated by drill
      filters: filters,
      limit: 25, //for display purposes we use top 25 and set to sort descending
      sortBy: { name: selectedMetric, direction: "DESC" },
    };

    client.executeQuery(query, dataModel).then((data) => setChartData(data));
  }, [selectedDimension, selectedMetric]);

  //ChartJS Bar Chart config options
  const options = {
      onClick: handleChartClick
      // other chart  config options
  }

  //Format RestBI result to populate chart
  const data = chartData
    ? {
        labels: chartData.rows.map((item) => item[selectedDimension]),
        datasets: [
          {
            label: selectedMetric,
            data: chartData.rows.map((item) => item[selectedMetric]),
            backgroundColor: "#53D9D9",
          },
        ],
      }
    : { labels: [], datasets: [] };

  return <Bar options={options} data={data} />;
};

export default DrillAnywhere;

Note: to keep it dataset agnostic we have created two state variables for the selectedDimension and selectedMetric. To make this even more dynamic we can let the user specify these values

Let the User Pick the Starting Chart Columns

The code below highlights how we can create a simple Material UI select menu, with a list of all of the columns (in this case Dimensions) that feeds nicely into the query defined above.

<FormControl variant="outlined" style={{ minWidth: 200, marginRight: 20 }}>
  <InputLabel>Dimension</InputLabel>
  <Select
    value={selectedDimension}
    onChange={(e) => {
       //update the chart so the dimensional value is the column the user selected
       setSelectedDimension(e.target.value as string)
    }}
    label="Dimension"
  >
    {dataModel && dataModel.tables //loop through the tables in our Model
        .flatMap((table: Table) =>
        //for each column in the table, check if its a dimension and add to list
        table.columns.map((column: Column) => {
            if (inferColumnType(column) === ColumnType.DIMENSION) {
                return (<MenuItem key={column.name} value={column.name}>
                    {column.name}
                </MenuItem>);
            }
            return null;
        })
    )}
  </Select>
</FormControl>

Handle the Chart Click event

To allow the user to drill down, we need to handle click events on the chart. We need to figure out what value the user clicked on, then trigger the popup to prompt the user to select the next dimension.

const handleChartClick = (e, elements) => {
  if (elements.length > 0) {
    const elementIndex = elements[0].index;
    //get the appropriate element from the chartData
    const label = chartData?.rows[elementIndex][selectedDimension];
    //set the drill value to be the data point the user clicked on
    setDrillAnywhereValue(label);
    //open the popup
    setDrillAnywherePopupVisible(true);
  }
};

Drill Anywhere Popup Menu

To prompt the user with a list of available columns, we can leverage the RestBI model definitions. In this example we are using all columns that are typically classified as a "Dimension" and placing those in a material UI select menu.

<DrillAnywherePopup>
...
<FormControl variant="outlined" style={{ minWidth: 200, marginTop: 20 }}>
  <InputLabel>Drill-Down Dimension</InputLabel>
  <Select
    value=""
    onChange={(e) => drillAnywhere(e.target.value as string)}
    label="Drill-Down Dimension"
    displayEmpty
  >
    {dataModel && dataModel.tables //for each table in the Model
      .flatMap((table) =>
        //for each column in the table, check if its a dimension and add to list
        table.columns.map((column) => {
          if (column.columnType === "DIMENSION") {
            return (
              <MenuItem key={column.name} value={column.name}>
                {column.name}
              </MenuItem>
            );
          }
          return null;
        })
      )}
  </Select>
</FormControl>
</DrillAnywherePopup>

Drill Anywhere Function

The final step is the drill anywhere function itself. All we have to do here is add a new filter value to our filters array (the column and value the user clicked on, and update the selected Dimension to be whatever column the user chose from our column list.

const drillAnywhere = (newDimension: string) => {
  //create new QueryFilter
  const newFilter = {
      column: selectedDimension,
      operator: "=",
      value: drillAnywhereValue,
  };
  const updatedFilters = [...filters, newFilter];
  //update chart dimension to be whatever was clicked
  setSelectedDimension(newDimension);
  //update filters on data query
  setFilters(updatedFilters);
};

Wrap-Up

So yea, with a non-massive amount code we are able to take what was previously a singular flat chart, and turn it into something far more flexible.

Id imagine everyone here is pretty familiar with MaterialUI, React, and ChartJS. Definitely go check them out if not. If this is your first time reading about RestBI, I'd encourage you to check it out as well, here is a link to the full drill anywhere example application if you want to see this in action.

Hopefully someone out there found this useful. I'd love to hear any thoughts readers might have.