Building AI-Driven Business Intelligence with OpenAI

In all the systems we use, the analytical charts on the dashboard always appear first.

We develop many analytical charts for different users to quickly analyze their daily work. A better approach is to create a chart system that can be configured with data. We call it BI, which is commonly seen in tools like Power BI, Tableau, and others.

Using BI tools, we configure charts one by one by setting up options such as tables, fields, relationships, aggregations, and filters. This process is often very complex and not easy to understand.

I often wonder if there is a way to automatically generate charts just by telling a system what data I want.

ChatGPT makes this possible.

We just need to tell ChatGPT the structure and relationships of the tables, and then have it return the SQL based on our description to achieve this amazing feature.

Yes, it's that simple! Let's try it.

We will use the database of WordPress as an example. First, we need to convert the WordPress database DDL into simpler information, keeping only the necessary details:

Prompt:

Copy

Format 1:

{tablename} has {field1}, {field2}...

{tablename2} has {field1}, {field2}...

{tablename} and {tablename2} are related by field2

Please convert the following DDL SQL to format 1:

CREATE TABLE wp_commentmeta (

meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,

comment_id bigint(20) unsigned NOT NULL DEFAULT '0',

meta_key varchar(255) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,

meta_value longtext COLLATE utf8mb4_unicode_520_ci,

PRIMARY KEY (meta_id),

KEY comment_id (comment_id),

KEY meta_key (meta_key(191))

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE wp_comments (

comment_ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,

comment_post_ID bigint(20) unsigned NOT NULL DEFAULT '0',

comment_author tinytext COLLATE utf8mb4_unicode_520_ci NOT NULL,

comment_author_email varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',

comment_author_url varchar(200) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',

...

Chat on the above prompt, reply as below:

Prompt:

Copy

wp_commentmeta has meta_id, comment_id, meta_key, meta_value

wp_comments has comment_ID, comment_post_ID, comment_author, comment_author_email, comment_author_url, comment_author_IP, comment_date, comment_date_gmt, comment_content, comment_karma, comment_approved, comment_agent, comment_type, comment_parent, user_id

wp_commentmeta and wp_comments are related by comment_id

wp_links has link_id, link_url, link_name, link_image, link_target, link_description, link_visible, link_owner, link_rating, link_updated, link_rel, link_notes, link_rss

wp_options has option_id, option_name, option_value, autoload

wp_postmeta has meta_id, post_id, meta_key, meta_value

wp_posts has ID, post_author, post_date, post_date_gmt, post_content, post_title, post_excerpt, post_status, comment_status, ping_status, post_password, post_name, to_ping, pinged, post_modified, post_modified_gmt, post_content_filtered, post_parent, guid, menu_order, post_type, post_mime_type, comment_count

wp_postmeta and wp_posts are related by post_id

wp_term_relationships has object_id, term_taxonomy_id, term_order

wp_term_taxonomy has term_taxonomy_id, term_id, taxonomy, description, parent, count

wp_termmeta has meta_id, term_id, meta_key, meta_value

wp_terms has term_id, name, slug, term_group

wp_usermeta has umeta_id, user_id, meta_key, meta_value

wp_users has ID, user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name

Alright, next we will use the following prompt to generate an executable SQL:

Prompt:

Copy

Format 1:

{tablename} has {field1}, {field2}...

{tablename2} has {field1}, {field2}...

{tablename} and {tablename2} are related by field2

Tables:

wp_commentmeta has meta_id, comment_id, meta_key, meta_value

wp_commentmeta and wp_comments are related by comment_id

wp_links has link_id, link_url, link_name, link_image, link_target, link_description, link_visible, link_owner, link_rating, link_updated, link_rel, link_notes, link_rss

wp_options has option_id, option_name, option_value, autoload

wp_postmeta has meta_id, post_id, meta_key, meta_value

wp_postmeta and wp_posts are related by post_id

wp_term_relationships has object_id, term_taxonomy_id, term_order

wp_term_taxonomy has term_taxonomy_id, term_id, taxonomy, description, parent, count

wp_termmeta has meta_id, term_id, meta_key, meta_value

wp_terms has term_id, name, slug, term_group

wp_usermeta has umeta_id, user_id, meta_key, meta_value

wp_users has ID, user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name

The SQL for "Top 10 most commented posts in last 30 days" is(only SQL, no any notes):

Chat on the above prompt, and reply as below:

Prompt:

Copy

SELECT

wp_posts.ID,

wp_posts.post_title,

COUNT(wp_comments.comment_ID) AS comment_count

FROM

wp_posts

INNER JOIN wp_comments ON wp_posts.ID = wp_comments.comment_post_ID

WHERE

wp_posts.post_status = 'publish'

AND wp_comments.comment_approved = 1

AND wp_comments.comment_date_gmt > DATE_SUB(NOW(), INTERVAL 30 DAY)

GROUP BY

wp_posts.ID

ORDER BY

comment_count DESC

LIMIT

10;

This is just a simple demo and we haven't provided enough information. ChatGPT generated the above SQL using online resources. In normal situations, we would need to provide additional information.

Here we are just presenting an idea, and you will need to continuously optimize it.

Enjoy your journey of optimizing your prompt!

ProgrammingBI

Last updated 1 years ago