Business Intelligence with Generative AI
TLDR; I built a chat bot that performs business intelligence tasks for you.
In the modern global economy, it’s increasingly important to make decisions based on data rather than subjective judgments such as intuition, guesswork, or experience. However, analyzing large amounts of data can be complex and requires the expertise of specialists to deliver a meaningful result that can be used to make decisions. Fortunately, recent developments in Generative AI research are creating new opportunities for analyzing and interpreting large amounts of data. These opportunities can simplify business analytics and provide decision-makers with faster access to relevant information, without requiring specialists to extract and process it beforehand. I explored how easy it is to build a prototype of a chat bot that performs business intelligence tasks for you on a dataset it is completely unknown to.
What are Large Language Models?
Large Language Models (LLMs) are Generative AI systems that specialize in natural language processing (NLP). The Transformer architecture behind these models was developed by Google scientists in 2017 and has since been used for the Google Translate service. This architecture enables the model to consider other parts of sentences or words when analyzing individual words to generate an answer. With the availability of larger chat datasets and advances in computing power, companies like OpenAI were able to develop models such as GPT (Generative Pre-trained Transformer) which could follow instructions and answer questions on a large dataset of information. End-user products like ChatGPT or Google Gemini were developed from these models and made available to the public.
To understand some limitations, let’s take a closer look at the terms “context window“ and “token”. An LLM divides all input or output into tokens, which are usually four-character word fragments. However, due to the implementation of an LLM, there is a limitation (context window) that restricts the maximum number of tokens that can be processed. Therefore, it’s important to avoid generating excessively large input. Using more tokens increases the need for processing power and therefore the cost of using services such as OpenAI or AWS Bedrock.
Let’s build a Chat Bot
To simplify the process of extracting information from a large data set, a chat functionality is implemented in Streamlit that allows a decision maker to query data from a data warehouse and allow the LLM to analyze, interpret, and visualize it. The first step involves the LLM receiving a question in natural language. Based on the question, the LLM determines if it is related to our data in our databases. If it is, it requests a list of all databases, including their descriptions. Based on this information, the LLM will determine which database to select and request its SQL schema. With this schema, the LLM can construct a SQL query related to the user’s question, which is then executed against the data warehouse to retrieve the necessary data. Once the LLM receives the data in CSV format, it analyzes and interprets it. The analysis is followed by displaying a diagram or map of the data, which also provides a visual representation for the user.
Let’s tell the LLM what its job is!
To let the LLM know what to do and how to behave, we give it what is called a system message. This message includes both rules to follow and instructions for the steps to be completed.
As a professional data analyst, you can access our databases through the provided functions to assist managers in accessing and analysing data
Follow these rules:
- Do not request all items from a table
- Limit SQL statements to 100 rows
- When constructing SQL statements, it is important to group the data in a useful manner
- Do not include any external links or images
- If you show an diagram, this is shown above and you don't have to include it yourself
Follow these steps:
- Check if data analysis is requested, proceed, otherwise reject
- Get available databases
- Choose database and get database schema
- Build query on the users request and execute it
- If useful, show the user a chart or map
- Thoroughly analyse the data in a professional manner and provide essential insights and recommendationsThis simple message, explains the role of the LLM and its rules, such as never requesting all the data from a table, or the exact steps to retrieve, analyze, and visualize data. This is the first message sent to the LLM before any user interaction.
But how does the LLM access the data warehouse?
To give the LLM the functionality to access our data warehouse, OpenAI’s GPT-4 functions are used. The LLM is basically given a list of functions, their description, and the parameters it can set. If the LLM decides that it needs a function to answer the user’s questions, it outputs a function call message, which we then parse on our client and call the function and return it’s output to the LLM.
tools = [
{
"type": "function",
"function": {
"name": "getDatabaseSchema",
"description": "Retrieves the schema for a database",
"parameters": {
"type": "object",
"properties": {
"name": {
"type": "string",
"description": "Name of the database you want to receive the schema for",
},
},
"required": ["name"],
},
}
}
]This is a snippet of one of the functions, also called ‘tools’. It describes a function called “getDatabaseSchema” with the parameter “name”. The function’s schema can be easily described in OpenAPI format. This information, along with that of all the other functions, is supplied to the LLM with every ‘chatComplete’ call. This ensures that the LLM always knows what capabilities it has and can access. If the LLM needs to retrieve a specific database schema, it can be easily implemented in Python. To simplify this example, we have stored the complete schemas of our databases in a directory called ‘schema’, which can be easily read by the Python function.
def getDatabaseSchema(args):
path = 'schemas/{}.sql'.format(args["name"])
if os.path.isfile(path):
f = open(path, "r")
return f.read()
else:
return "Error: not found"Adding comments with further description on an attribute can help the LLM create more precise SQL statements, especially if the context of the database is not widely known or completely new. However, in this example, it should be easy to understand for the LLM even without comments.
CREATE TABLE "example-dataset".customers (
id char(32) NOT NULL,
zip_code_prefix varchar(5) NOT NULL,
city varchar NOT NULL,
state char(2) NOT NULL
);
COMMENT ON COLUMN "example-dataset".customers.id IS E'ID of the customer';
COMMENT ON COLUMN "example-dataset".customers.zip_code_prefix IS E'Prefix of the customers ZIP Code';
COMMENT ON COLUMN "example-dataset".customers.city IS E'City where the customer lives';
COMMENT ON COLUMN "example-dataset".customers.state IS E'State where the customers lives';After receiving the SQL schema, the LLM can create a SQL statement and execute it against the data warehouse. To enable this, a “executeQuery” function has been implemented, which uses the AWS Redshift connector to access our data warehouse. The LLM will receive the requested rows from the data warehouse in CSV format and can further analyze and visualize it for the user.
Let’s visualize the data
Since the chat functionality is implemented with a simple Streamlit component that just displays the messages between the user and the LLM, we can also easily use Streamlit’s chart or map component. To display a chart, we created a new function called ‘chart’ that takes the chart type and data in CSV format as parameters. This function then writes the parameters and data back into the message stack. This allows the LLM to remember that it has displayed a chart and can also modify it later if requested. The frontend then reads the chart data from the message stack and parses the CSV into a data frame and presents it to the user. The same implementation is used for maps.
Get Simon Wakenhut’s stories in your inbox
Join Medium for free to get updates from this writer.
As this approach is not very efficient in token usage or flexibility, I also tested letting the LLM generate the code to display a chart. The results were quite mixed. Although it worked a few times, the LLM failed many times to create valid code for the given context. This approach also poses a significant security risk, as we can never be sure what code the LLM will generate. Therefore, it should never be implemented without a human checking the code before execution, especially if attacks like prompt injection still exist. To save on token usage, the chart message can be excluded from the messages sent to the LLM, but that results in not having the possibility to let the LLM change it afterwards.
Let’s test the Chat Bot
To test the chat bot, I used a public dataset of real data from a Brazilian e-commerce company. The dataset includes over 100,000 orders from multiple marketplaces between 2016 and 2018. The data is anonymized, so no personal information such as names or exact locations of customers or sellers is available. Only the categories of the sold products are visible.
So let’s ask the chat bot something about our dataset.
An example of a query is the number of orders per month within a year. The LLM made the right decision to display this data in the form of a chart. The analysis of the data is also very interesting especially in November, which is the month with the most orders. The LLM sees that “Black Friday” and “Cyber Monday” cause a high number of orders. This knowledge was not obtained from the data set, but from the knowledge it gained from the training of websites. In addition to the analysis, the LLM recommends linking the stock level with the sales history and increasing the stock level at the end of the year.
The implemented map function can also be used by the LLM. In this example, the user asked where the orders come from grouped by state. However, this is a tricky question. Without the “grouped by state”, this question can be interpreted in different ways (grouped by state, country, city, ZIP code, …). Therefore, asking the LLM to simply show where the orders come from would result in heavily varying results. This response also includes an analysis of the LLM for the displayed data, such as a possible explanation for why the majority of orders originate from São Paulo.
The question about the sales per month in 2017 also shows that the user gets a useful chart back. But maybe more importantly, a list of insights and recommendations which could be important to optimise operations.
It is worth noting that the tests showed that the LLM is able to recover from errors. If the SQL statement lacks a LIMIT statement to limit the number of rows returned from the data warehouse, the “executeQuery” function returns an error message. If the function fails to generate a SQL statement with a LIMIT, it reads the returned error, generates a corrected SQL statement and re-executes it. This demonstrates LLM’s ability to recover from errors, but also highlights its occasional failure to follow the rules. Therefore, it is still crucial to include appropriate checks in our code.
To sum up …
LLMs are great at generating simple queries from natural language questions and then retrieving and analysing data. But such an implementation needs to be extensively tested on more complex scenarios. This example just showed how easy it is to implement for a proof of concept.
A production implementation requires many more checks to verify that an answer is plausible. Such control mechanisms can be used to check if the data received from the data warehouse is the same as the LLM displays in text or a visualization such as a chart. In this state, it is a really great tool for users with experience in querying data warehouses. The user only has to manual verify that the query and the answer are plausible. Also similar to humans, if there is a question we don’t quite understand or not everything is specified so the answer is up to interpretation, we could instruct the LLM to ask the user until a question is clear without the LLM interpreting the questions differently each time.
In short, the integration of an LLM into tools to minimize manual work is already possible and is getting better everyday. Now is the time to become familiar with this technology and explore how it can be used.
Links
- Source code https://github.com/simwak/business-intelligence-chat-bot
- “Attention is all you need” — The paper which (probably) started the LLM journey https://arxiv.org/pdf/1706.03762.pdf
- GPT-4 Function calling https://platform.openai.com/docs/guides/function-calling
- Streamlit charts https://docs.streamlit.io/library/api-reference/charts
- Brazilian E-Commerce Public Dataset by Olist https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce
