Exploring LLM‑Based Generative Business Intelligence (GenBI): Architecture, Implementation, and Lessons Learned

The rapid adoption of large‑language‑model (LLM) generative AI has extended into the database domain, giving rise to the concepts of DB‑for‑AI and AI‑for‑DB. This article focuses on the latter, specifically Generative Business Intelligence (GenBI), which enables self‑serving analytics through natural‑language interfaces.

Why GenBI? Traditional BI teams are overloaded with both scheduled reporting and ad‑hoc exploratory queries, slowing insight delivery. Text‑to‑SQL (NL2SQL) offers a way to let users describe questions in natural language and obtain SQL queries automatically, dramatically improving speed‑to‑insights.

Text‑to‑SQL advances. Early NL2SQL models relied on pattern matching and performed poorly. Modern LLM‑based in‑context learning now dominates benchmark leaderboards such as Spider and BIRD, with solutions like XiYan‑SQL, CHASE‑SQL, PET‑SQL, DIN‑SQL, and CHESS achieving top results. This progress fuels the GenBI movement.

LLM‑based GenBI Agent architecture. The core is an autonomous agent that receives a natural‑language question, performs reasoning and planning, and decomposes the task into sub‑tasks. It leverages schema linking (metadata) and domain knowledge via Retrieval‑Augmented Generation (RAG) to construct prompts for the LLM, which acts as the reasoning engine. The agent can invoke external tools (e.g., Lambda functions) to execute generated SQL against a query engine such as Amazon Athena, iterating until a final answer is produced.

Practical implementation on AWS Bedrock. The article details a concrete stack: Agent: AWS Bedrock Agent configured with a system prompt that positions the model as a SQL data engineer. LLM: Anthropic Claude 3.5 Sonnet, selected via Bedrock’s model catalog. RAG: Bedrock Knowledge Base storing table schemas, descriptions, DDL, and sample data in S3, indexed for vector search. Tool: An AWS Lambda function exposing an OpenAPI endpoint that executes SQL on Amazon Athena. Chat UI: A lightweight Streamlit interface for user interaction.

Configuration snippets are provided. The system prompt and objective are expressed as:

Role: You are a SQL data engineer doing data analysis by running SQL queries against Amazon Athena.
Objective: Generate SQL queries based on the provided schema and user request. Execute the generated SQL queries and return the SQL execution result against Amazon Athena.
Steps:
1. Query Decomposition and Understanding:
   - Analyze the user’s request to understand the main objective.
   - Use one SQL query to solve the request at your best. If you think the problem is too complex, you can break down request into multiple queries that can each address a part of the user's request, using the schema provided.
2. SQL Query Creation:
   - For the SQL query, use the relevant tables and fields from the provided schema.
   - Construct SQL queries that are precise and tailored to retrieve the exact data required by the user’s request.
3. Query Execution and Response Presentation:
   - Execute the SQL queries against the Amazon Athena database.
   - Return all the results exactly as they are fetched from the Amazon Athena.

The OpenAPI definition for the Lambda tool is:

{
  "openapi": "3.0.0",
  "info": {
    "title": "SQL Command Execution API",
    "version": "1.0.0",
    "description": "API for executing SQL commands on a shipment records database via an AWS Lambda function."
  },
  "paths": {
    "/executeSql": {
      "post": {
        "summary": "Execute an SQL command",
        "description": "Executes a provided SQL command on the shipment records database via an AWS Lambda function.",
        "operationId": "executeSqlCommand",
        "requestBody": {
          "description": "SQL command to be executed",
          "required": true,
          "content": {
            "application/json": {
              "schema": {
                "type": "object",
                "properties": {
                  "sqlCommand": {
                    "type": "string",
                    "description": "The SQL command to be executed."
                  }
                },
                "required": ["sqlCommand"]
              }
            }
          }
        },
        "responses": {
          "200": {
            "description": "The result of the SQL command execution.",
            "content": {
              "application/json": {
                "schema": {
                  "type": "object",
                  "properties": {
                    "status": {"type": "string", "description": "The status of the command execution."},
                    "data": {"type": "object", "description": "The data returned from the SQL command execution, structure depends on the SQL command executed."}
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

After configuring the knowledge base, syncing metadata, and defining advanced prompts (including guidelines for tool usage, thinking tags, and answer formatting), the agent can be deployed directly from the Bedrock console or accessed via the AWS client API.

Lessons learned. LLMs generate highly capable SQL, including complex joins and CTEs, but occasional execution errors and hallucinations persist. Controlling temperature, top‑k, and top‑p improves stability. Providing traceability of reasoning, using self‑reflection, and fine‑tuning on domain‑specific query/result pairs are essential for production reliability.

Conclusion. LLM‑driven GenBI is in a rapid growth phase, with both industry and academia investing heavily. Advances in reasoning, coding, and context windows will make SQL generation more accurate, while post‑training alignment techniques will unlock deeper, vertical‑specific data‑conversation capabilities.

References include papers and blog posts on Redshift ML, self‑driving databases, XiYan‑SQL, CHASE‑SQL, PET‑SQL, DIN‑SQL, CHESS, Databricks Genie, Snowflake Cortex Analyst, AWS QuickSight Generative BI, Amazon Q, and ReAct.