BI ChatBot in Domo: Step-by-Step Guide

Jan 05, 2024

In the ever-evolving landscape of business intelligence (BI), the need for seamless interaction with data is paramount. Imagine a world where you could effortlessly pose natural language questions to your datasets and receive insightful answers in return. Welcome to the future of BI, where the power of conversational interfaces meets the robust capabilities of Domo.

This blog post serves as your comprehensive guide to implementing a BI ChatBot within the Domo platform, a revolutionary step towards making data exploration and analysis more intuitive and accessible than ever before.

Gone are the days of wrestling with complex queries or navigating through intricate dashboards. With the BI ChatBot in Domo, users can now simply articulate their questions in plain language and navigate through datasets with unprecedented ease.

Join us on this journey as we break down the process into manageable steps, allowing you to harness the full potential of BI ChatBot integration within the Domo ecosystem. Whether you're a seasoned data analyst or a business professional seeking data-driven insights, this guide will empower you to unlock the true value of your data through natural language interactions.

Get ready to elevate your BI experience and transform the way you interact with your datasets. Let's dive into the future of business intelligence with the implementation of a BI ChatBot in Domo.

 

Prerequisites:

  1. ChatGPT API Key: Prepare for the integration of natural language to SQL conversion by obtaining a ChatGPT API Key. This key will empower your system to seamlessly translate user queries in natural language into SQL commands.

  2. DOMO Access: Ensure that you have the necessary access rights to create a new application within the Domo platform. This step is crucial for configuring and deploying the BI ChatBot effectively within your Domo environment.

 

1: Integrate the HTML Easy Bricks App.

Begin the process by incorporating the HTML Easy Bricks App into your project. Navigate to the AppStore and add the HTML Easy Bricks to your collection. Save it to your dashboard for easy access.

Upon opening the App for the first time, it will have a default appearance. To enhance its visual appeal and functionality, customize it by incorporating the HTML and CSS code. This transformation will result in the refined look illustrated below.

 

Image 1: DOMO HTML Easy Brick UI

 

2: Map/Connect the Dataset to the Card.

In this phase, establish a connection between the dataset and the card where users will pose their inquiries. Refer to the image below, where the "Key" dataset is linked to "dataset0." Extend this mapping to accommodate up to three datasets.

If your project involves more datasets, consider using the DDX-TEN-DATASETS App instead of HTML Easy Bricks for a more scalable solution. This ensures seamless integration and accessibility for users interacting with various datasets within your Domo environment.

 

Image 2: Attach Dataset With Card

 

3: Execute the Query on the Dataset for Results.

In this phase, you'll implement the code to execute a query on the dataset, fetching the desired results. Before this, initiate a call to the ChatGPT API to dynamically generate an SQL query based on the user's natural language question.

It's essential to note that the below code is designed to only accept valid column names in the query, adhering strictly to MySQL syntax. To facilitate accurate query generation from ChatGPT, create a prompt that includes the dataset schema and provides clear guidance for obtaining precise SQL queries.

Here is a call to the ChatGPT API to get SQL Query.

VAR GPTKEY = 'key'

VAR Prompt = 'Write effective prompt'
$.ajax({
            url: 'https://api.openai.com/v1/chat/completions',
            headers: {
              'Authorization': 'Bearer ' + GPTKEY,
              'Content-Type': 'application/json'
            },
            method: 'POST',
            data: JSON.stringify({
              model: 'gpt-3.5-turbo',
              messages: Prompt,
              max_tokens: 100,
              temperature: 0.5,
              top_p: 1.0,
              frequency_penalty: 0.0,
              presence_penalty: 0.0
            }),
            success: function (response) {

                  //Write code to store the Query into the variable
           }
});

 

Refer to the code snippet below for executing the query on Domo and retrieving the results.

var domo = window.domo;
var datasets = window.datasets;

domo.post('/sql/v1/'+ 'dataset0', SQLQuery, {contentType: 'text/plain'}).then(function(data) {
  //Write your Java or JQuery code to print data.
});

 

The above code will accept the SQL queries generated by ChatGPT. It's important to highlight that, in the code, there is a hardcoded specification that every query will be applied to the dataset mapped as 'dataset0'. It's advisable to customize this part based on user selection.

The code is designed to accept datasets with names such as 'dataset0', 'dataset1', and so forth. Ensure that any modifications align with the chosen dataset for optimal functionality, you can also use the domo.get method to get data for more information visit here.

The outcome will be presented in JSON format, offering flexibility for further processing. You can seamlessly transfer this data to a table format and display or print it as needed.

 

Conclusion

Incorporating a BI ChatBot in Domo revolutionizes data interaction, seamlessly translating natural language queries into actionable insights. The guide's step-by-step approach simplifies integration, offering both analysts and business professionals an intuitive and accessible data exploration experience. As datasets effortlessly respond to user inquiries, this transformative synergy between ChatGPT and Domo reshapes how we extract value from data, heralding a future of conversational and insightful business intelligence. Dive into this dynamic integration to propel your decision-making processes into a new era of efficiency and accessibility.

Hardik Dangar

About the Author

Hardik Dangar

Project Lead in Magnusminds

Hardik is working as Project Lead of MSBI in INDIA. Hardik started his career working on SQL Server and MSBI. Hardik is having 5+ years of experience. In the starting of his career he was working on SQL Server, SSIS and SSRS. Hardik likes to explore technical things on SQL Server.