The lack of interaction of BI reports

Here’s a familiar scenario. You spent a whole week building a dashboard, and when you finally completed it and presented it to the users, they went, 'Good, can you add a download to Excel button?'. “Sure, will do.” you said. 

A few weeks past, you opened the dashboard project and checked the user sessions. No one really used the dashboard anymore. 

Maybe your dashboard didn’t provide enough valuable insight. However, in other cases, it could simply be due to the fact that the user wanted to know a very specific number on their own. For example, you build a fancy dashboard to monitor the entire store's business, and the user just wants to know how the sales of oranges were in November, or to check the inventory of chocolate in the past seven days. 

Instead of surrendering and giving out the raw data, there's another choice: Build a chatbot inside the dashboard that can answer these detailed user questions. The chatbot generates real-time calculations in the database based on the user's question and provides them with accurate results. The chatbot is integrated inside the dashboard, no more filters and formulas in Excel are needed. 

Benefits of integrating chatbot into a BI dashboard

1.Help your users quickly get the answer they want: 

It would be a waste of time for users to download the excel file and do filters, sum on their own. For questions like "inventory of chocolate in the past seven days", it can be generated by AI in several seconds.

2.Help you understand users by knowing what question they ask the chatbot

What people do in the excel file will be completely invisible for you. But if you can aggregate what they ask the chatbot, then you can learn and analyze the questions. For instance, if you find that many people across various departments are searching for sales data of product XYZ from the past month, you can add this part as a new section in your dashboard. This is extremely helpful because you can now gather input and iterate on products independently, instead of being taught what to do.

3. Help you retain users in the dashboard you built

With this powerful chatbot as an assistant, the value of the dashboard itself actually increases. People is likely to spend more time on your dashboard, pay greater attention to the key metrics you've built, and may even think more deeply about those metrics. Hence, it is beneficial for both the user and you, ultimately benefiting the entire business.

How it works

You might question the accuracy of AI, but once you understand how it works, you'll find it quite simple and reliable. When a user enters a question, you are generating a summary of the table schema and using it together with the user question as a prompt towards the gpt3.5-turbo API endpoint. The ChatGPT API understands the user's question and the data, then returns a SQL query. The database subsequently runs the query and calculates the result that the user is seeking. The calculation is performed by the SQL query in the database, ensuring accuracy of math, while the AI is only for understanding user questions and generating the corresponding machine language.

We have also made it simple by integrating ChatGPT API as a plugin in the product. Install the plugin, then the ChatGPT API would work like a custom function that you can use in interaction. You don't have to have a OpenAI account or API key for that.

The custom function takes data schema, and user prompt as inputs. The data sources are the data you've used in the dashboard, which will be used to answer user questions. The function will extract the table schema, metadata, and sample data for the AI model to understand the data. The prompt is the question the user asks. Use interaction to pass user input as the prompt to the function.

You can build an interactive component to trigger the custom function action with proper input, ChatGPT will generate a query based on the table information and user input, and execute the SQL calculation in the database.




How to integrate a Chatbot to dashboard

Acho is a free BI tool to build a dashboard with. And it features a ChatGPT Plugin that helps you easily integrate your dashboard with AI. Now, let me show you how to build a dashboard with a ChatGPT chatbot from scratch.

  1. Connect dataThe first step is to connect data. Ideally, a dashboard should build on real-time data sources like a production database, database replication, or API data pipeline. While you can use static data like CSV or Excel files to develop, keep in mind that this data will not automatically update.
  1. Build your dashboard
    After the data is connected, you can begin building the dashboard. Consider which KPIs and charts to display, aligning them with your dashboard's goal. Several designed templates are provided for your reference or direct use.
    Click the table to build a chart with it. For calculated fields, create a metrics or query from the table first, then build the visualizations on top of that.
Create a metric with a data source

3. Configure the ChatGPT custom functionNext, we'll work on interaction, where we make ChatGPT understand user's question and data.Hint: You don't have to have OpenAI account/API key for this, the OpenAI usage will reflect on your Acho usage.

4. Add a "Chatbot" template element to your dashboard
Now, it's time to build the Chatbot element front-end. I'll illustrate using our Dashboard with Chatbot template. We save the conversation in the an app data array, and update it each time. This template is readily available. In the Chatbot template, we use a List element to display the conversation, with each item in the response conversation array appearing as a dialogue bubble. In the bubble, ${$item.content} access the conversation content. You can reverse-engineer it to see the detail of how we built it, or copy it directly to your dashboard and tune it.

Install the ChatGPT API in our Plugin Page to activate it. Then, you can use the custom function in the Interaction. In the ChatGPT API collection, there are 5 useful APIs helping you build the Chatbot. The API token here is your Acho API token, where you can find at Profile - Account:

Each time a user is enter the dashboard, we run "New Conversation" to create a new conversation, and save the conversation id in the app data. We also generate table schema using the "Generate context with tableNode" and save it as well, replace the table node to your own table.

Next, whenever user enter a question, we trigger the "Reply Conversation" action, here we set the message to the user question and table metadata we saved in app data, so that ChatGPT can understand them and give answers accordingly. Then, we parse the SQL from the result conversation and run it in a Query using a "Set SQL parameter" action. 

Again, you can replace the datasource and API token to make our template work, or you can reverse-engineering our template and build your own.

Test and share

Click Preview to test whether the chatbot functions correctly. If it works as expected, proceed to Publish and invite your users to access the dashboard. Your users will receive an email containing a magic link. Through this link, they will be able to view the dashboard.

Challenges:

  1. Data Credibility and Accuracy: People trust what they see more than anything. AI, unfortunately,  might seem like a black box to most people. However, there's no need to worry. Since the SQL query is visible out there, users can read the SQL to verify whether they are getting the correct answer.
  2. Stability: Sometimes AI doesn't generate the correct query, especially when the schema is complex or the question is opaque. Like with any dashboard, training your users is essential after deployment. Encourage users to phrase their questions more, and AI would reiterate the query and provide the correct answer.

Having questions? Find our in our discord channel , or contact us directly