Generative BI Agentic Assistant

This hands-on lab guides you through building an intelligent assistant that converts natural language into Structured Query Language (SQL) queries using database schemas. You'll create a powerful agentic chatbot that provides precise data insights on the basis of autonomous reasoning.

Use Case

Build a sophisticated Enterprise Sales Advisor chatbot that analyzes ticket sales data for various events and performances.

Prerequisites

• Access to the Redshift catalog for enterprise sales data.

• Basic familiarity with the platform interface.

Step 1: Catalog Creation

1. Navigate to Catalog Schema in menu bar on the left side.

2. Click on Add new in the top corner on the right side.

3. Configure the catalog:

   1. Name: "Enterprise-Sale-Data".

   2. Connector type: Redshift.

   3. Enter the required credentials provided separately.

4. Save the catalog.

5. Open the saved catalog.

6. Browse and select all the tables and click on Import schema on the right side.

7. Within Catalog explorer, generate business metadata for each table:

   1. Select each table individually.

   2. Enable checkboxes to generate metadata.

   3. Click Generate Metadata on the right side.

8. Publish the catalog.

Refer the video tutorial for creating catalog.

https://www.loom.com/share/55028e48089d4aa3bb6a1b3581d3051a?sid=7af1d731-ea84-4f52-8516-6c5ded5a26b1

Step 2: Create an Agent Prompt

1. Navigate to Prompt Playground on the left side.

2. Click on Add new in top corner on the right side.

3. Click on Prompt templates on the right side.

4. Select the Enterprise Sales Agent-Redshift template.

5. Increase the Max State Updates from 3 to 20 for complex reasoning.

6. Scroll down to Agent input box to add the following test query : "What is the total sales amount for each seller?"

7. Ensure that you scroll up to rename your prompt before saving.

8. You will be directed to the Prompt Playground and select your prompt:

   1. Click on the Tools tab to configure the following tools:

      1. Catalog Tool

         1. Click on Add new in tools.

         2. Enter name as “Enterprise Sales Catalog”.

         3. Enter the Description as “The catalog tool uses natural language questions to search relevant tables and columns to create a SQL query”.

         4. Select Type: Catalog.

         5. Select the catalog created in Step 1 from the dropdown menu.

         6. Save the tool.

      2. Database Tool

         1. Click Add new in tools.

         2. Enter name as “Sales Performance Datawarehouse”.

         3. Enter the Description as “This tool lets you run SQL queries on a specified database and table, then retrieve the results.”.

         4. Select Type: Database.

         5. Select Connector: Redshift.

         6. Enter the Redshift Datawarehouse credentials provided.

         7. Save the tool.

Test & Compare :

1. Click on Test and compare tab.

2. Select different models [Hint: Compare Claude Sonnet 3.7, Claude Sonnet 3.5 v2, Amazon Nova Pro].

3. Click on Test right below.

4. Compare the output of the selected Models.

5. Click on Select as best answer to select the best-performing model as the Primary Model.

6. Optionally Select as best answer to assign a Fallback Model.

7. Click Save prompt run in the right corner.

8. Save and publish the prompt.

Step 3: Create Agent 2.0 Recipe

1. Navigate to Recipe on the left side.

2. Click Add New in the top corner.

3. Configure recipe details:

   1. Name: "Enterprise Sales Advisor".

   2. Description: "Intelligent assistant for sales data analysis".

   3. Type: Agent 2.0

4. Set up the workflow :

   1. Drag and drop Chat node:

      1. In the Conversation history dropdown, select 4 messages.

      2. Enable Generate follow-up questions.

   2. Drag and drop Start node:

      1. Connect the Chat node to the Start node.

   3. Drag and drop Agent node:

      1. Select your created Agent prompt at step 2.

      2. Scroll down and Set the Messages dropdown to All Messages to maintain conversation context.

      3. Connect the Start node to the Agent node.

   4. Drag and drop End node:

      1. Connect the Agent node to the End node.

5. On the right side, set the Number of state updates to a higher value such as 75.

6. Click on the canvas, then select Save.

7. Select your recipe from the recipe table.

8. Ensure you click on Publish the recipe.

9. Click the Test button on the right side to verify functionality.

10. Test with sample questions:

    1. What is the average sales amount among top 10 sellers?

    2. What is the total sales amount for top 10 sellers?

    3. What is the total sales amount for each seller?

Refer the video to create and test recipe:

Step 4: Deploy Your Copilot

1. Click the Export button located at the top-right corner of the recipe interface.

2. Click Add new to create a deployment configuration.

3. Complete the export form:

   1. Name: "Enterprise Sales Assistant"

   2. Add Welcome message, as preferred.

   3. Description: "The Enterprise Sales Advisor Copilot is designed to help you analyze and optimize your sales data."

4. Add Seed Questions:

   1. What is the average sales amount among top 10 sellers?

   2. What is the total sales amount for the top 10 sellers?

   3. What is the total sales amount for each seller?

5. Add Error message, as preferred.

6. Enable positive (thumbs up) and negative (thumbs down) feedback.

7. Users can also add custom feedback.

8. Select Show traces.

9. Click on Save.

10. Your Copilot will appear in the Copilots section on the left side.

Step 5: Test Your Sales Advisor

1. Navigate to the Copilot on the left side and open your chatbot.

2. Ask sample questions:

   1. How much total revenue was generated from ticket sales for events in the "Shows" category by each seller?

   2. Which events have generated the most revenue, based on sales from associated listings?

   3. What is the total sales amount by each user, including their name and email?

Refer the video to test copilot:

All the best!