Developing BI dashboards using AI services

The AI in Reporting feature simplifies SQL query generation for reports using AI models. Report requirements developed in excel format can be input as CSV, and AI service can generate SQL queries and other reporting artifacts. This reduces manual coding and creates efficient, standardised reporting queries.

The tool used for this is Cursor which internally uses other AI services. You can download Cursor.

The source code used in this tool is available here avni-ai-experiment (private repository as the CSV files used in the context may contain customer specific information). This repository will become a public repository soon.

Generate aggregate query

When to use

Excel or spreadsheet contain the requirements for the report all present in a single sheet. This is the input used for generating the SQL. If you do not have this file then the steps below are not recommended as it will not be productive approach.

Setup

Open avni-ai-experiment in Cursor.
Download the requirement sheet as a CSV file. Copy its contents and put them in any file under bi-reporting-spike/dataset/workspace folder. Let's say - requirement.csv. An example is present in workspace folder by name example.csv.
Create one file which contains all the table definition in the bi-reporting-spike/dataset/workspace folder. Let's say - table-def.sql. An example is present in workspace folder by name example-jnpct-def.sql. This was generated from IntelliJ (select schema and generate).

Chat

Open chat window in Cursor.
Prompt to forget everything (line 1 of aggregate-query-prompt.txt)
Follow the steps in https://github.com/avniproject/avni-ai-experiment/blob/main/bi-reporting-spike/dataset/workspace/aggregate-query-prompt.md