From Question to Dashboard: AI-Powered Data Analysis with Oracle APEX
Business Intelligence and reporting solutions have become a standard component of modern business applications. However, creating new reports and analyses often remains a time-consuming process. Business users typically need to navigate through existing dashboards, request new reports from the IT department, or rely on technical expertise to extract the information they need.
The rise of generative AI offers a new approach to data analysis.
What if users could simply describe the information they are looking for in natural language, and the application could automatically generate insights, recommendations, and visualizations?
A proof-of-concept application built with Oracle APEX demonstrates how Large Language Models (LLMs) can be integrated into Oracle-based solutions to transform natural language requests into dynamic dashboards.
The Concept
The goal of the application is straightforward:
Users enter a question in natural language, such as:
- “Show me the top 5 products by revenue”
- “Which region generates the highest revenue?”
- “Analyze the sales data and identify important trends”
- “Show me the most important insights from the sales dataset”
Instead of selecting predefined reports, users simply describe what they want to analyze.
The AI model then evaluates the request and generates:
- Business insights
- Actionable recommendations
- Relevant visualizations
The result is a dashboard that adapts dynamically to the user’s question.
Unlike traditional dashboards, there are no predefined reports. The entire analysis is created at runtime.
Technology Stack
The solution was implemented entirely using Oracle technologies and standard REST integration capabilities.
The following components were used:
- Oracle Database 26ai Free
- Oracle APEX 26.1
- Chart.js
- OpenAI API integration via APEX / PL/SQL
One of the most interesting aspects of the architecture is its simplicity.
No middleware or additional application server is required. Oracle APEX acts as the central orchestration layer between the AI model, the database, and the user interface.
The application communicates directly with the AI service and processes the returned results within Oracle APEX.
Structured Responses Instead of Free Text
Large Language Models naturally generate free-form text. While this is ideal for conversations, business applications require predictable and structured output.
For this reason, the model is instructed to return only valid JSON.
Every response follows a predefined structure:
“summary”: [],
“recommendations”: [],
“widgets”: []
}
This allows Oracle APEX to process the response automatically and transform it into dashboard components without additional interpretation.
Using structured JSON provides several advantages:
- Predictable responses
- Easy parsing within Oracle APEX
- Dynamic UI generation
- Reduced implementation complexity
Rather than interpreting free text, the application can directly consume the returned data structure and render the appropriate components.
Prompt Engineering: The Key to Reliable Results
One of the most important lessons learned during development was that the quality of the generated dashboard depends heavily on the quality of the prompt.
The AI model does not simply receive the user’s question. It also receives detailed instructions about:
- Available tables
- Available columns
- Allowed SQL syntax
- Supported chart types
- Required JSON structure
- Minimum number of insights and recommendations
The prompt contains rules such as:
Never invent columns.
Generate ONLY SELECT statements.
SQL must return exactly two columns:
label
value
The available columns are explicitly defined:
ORDER_DATE
CUSTOMER_NAME
REGION
REVENUE
PRODUCT_NAME
This information provides the model with the necessary context to generate meaningful SQL statements while preventing invalid responses.
Without these constraints, AI models may:
- Invent table names
- Reference non-existent columns
- Generate invalid SQL
- Produce inconsistent output formats
Prompt engineering therefore acts as both a quality assurance mechanism and a security layer.
It ensures that all generated content remains compatible with the underlying Oracle database structure and can be processed safely by the application.
From AI Response to Interactive Charts
Once the AI model has generated the widget definitions, Oracle APEX executes the corresponding SQL statements and retrieves the data required for visualization.
A key design decision was to standardize all generated SQL statements so that they always return exactly two columns:
- label
- value
This generic structure makes the frontend completely independent of the underlying business data.
Regardless of whether the query analyzes products, regions, customers, or time-based trends, the returned data can always be processed using the same rendering logic.
The application uses Chart.js, a popular open-source JavaScript charting library, to render the visualizations.
Each generated widget contains:
- A title
- A chart type
- A SQL statement
For example:
“title”: “Revenue by Region”,
“type”: “bar_chart”,
“sql”: “SELECT …”
}
After the SQL statement has been executed, the result is transformed into a Chart.js dataset.
A simplified version of the rendering logic looks as follows:
This approach allows entirely different visualizations to be created dynamically without requiring changes to the frontend code.
The frontend only receives:
- The chart type
- Labels
- Values
Chart.js then handles the rendering process.
More information about Chart.js can be found at:
Business Insights and Recommendations
The generated dashboard does more than display charts.
The AI model also produces business-oriented insights that help users understand the data.
Examples include:
- A small number of products generate the majority of revenue.
- Certain regions significantly outperform others.
- Revenue growth is concentrated within specific product categories.
In addition to insights, the model generates recommendations such as:
- Focus marketing efforts on top-performing products.
- Investigate high-growth regions in greater detail.
- Align inventory levels with demand patterns.
This transforms the dashboard from a reporting tool into an analytical assistant.
Instead of simply presenting data, the application provides guidance and highlights areas that may require further attention.
Dynamic Dashboard Creation in Oracle APEX
The true strength of the solution lies in its flexibility.
Every new question can produce:
- Different charts
- Different SQL statements
- Different insights
- Different recommendations
The dashboard is no longer a static collection of reports.
Instead, Oracle APEX becomes a platform that dynamically assembles analytical content based on natural language requests.
The generated dashboard adapts to the user’s business question and presents the most relevant information without requiring predefined report definitions.
This creates a highly flexible self-service analytics experience while keeping the implementation relatively simple.
Conclusion
This proof of concept demonstrates how Oracle APEX, Oracle Database, generative AI, and modern JavaScript visualization libraries can be combined to create a new approach to self-service analytics.
Users simply describe what they want to analyze in natural language. The application automatically generates:
- Structured JSON
- SQL queries
- Business insights
- Recommendations
- Interactive visualizations
One of the key findings of the project is that the real value does not lie solely in connecting an AI model to an application.
The combination of prompt engineering, structured response design, AI-generated SQL statements, and flexible chart rendering enables a completely new way of interacting with business data.
With Oracle APEX acting as the orchestration layer, organizations can build intelligent, data-driven applications that provide meaningful insights while significantly reducing the effort required to create traditional reports and dashboards.
