In today's data-driven business environment, data analysis has become a key part of business decision-making. The Metad Analytics Cloud provides you with an intelligent data query experience, through its powerful AI copilot function based on ChatGPT, you can more efficiently query, optimize and interpret data. This article will introduce in detail how to turn on and configure the AI copilot in the Metad Analytics Cloud, and how to use the AI copilot in the Query Lab to improve the efficiency of data query.
Query Lab provides the function of flexibly operating the data source entity (physical table view or multi-dimensional data set) by using SQL query statement, which helps users in their daily data operation and maintenance work. The query lab is built in the semantic model workspace and operates and queries data through the data sources connected by the semantic model. If the user's data source is public network accessible, you can create a data source in the metad analytics cloud to connect query. If the user's data source is deployed in the private network, you can use the desktop agent to connect and query.
Next, this article will introduce how to turn on and configure AI copilot and use it to assist in querying the data sql in the laboratory, optimizing and explaining.
Turn on and configure AI copilot
The AI Copilot feature of the Metad Analytics Cloud can be enabled and configured in the website settings page, and the corresponding openai key or other AI service key can be configured.
The key of the AI service obtained by the user through the third-party service provider can also be configured in the AI Copilot, and the OpenAI api service can be called by configuring the API host address.
AI intelligence in Query Lab
Now, let's take a look at how AI copilot is used in the query lab to assist in writing, optimizing, and explaining queries. The AI copilot in the query lab contains a series of AI SQL use functions based on ChatGPT, mainly including natural language to SQL, SQL explain, SQL optimization, and SQL conversion. With these AI functions, natural language can be converted into native query SQL, not just SQL query pseudocode; SQL can be explained into natural language to help users understand complex SQL; comprehensive optimization suggestions can be provided for slow SQL to improve query efficiency; SQL language of different database types can be converted to reduce database migration difficulty and so on.
In the query lab, each query interface is a session of the AI copilot. When the query is saved, the current session records will be saved, which is convenient for users to continue using or query the history.
You can drag many of the tables in the database tables area into the table structure area in query lab you need for the query as the context information of the AI copilot session, so that the AI copilot can make intelligent suggestions based on the context information.
There are three ways to use AI copilot in the query lab:
- Open the AI copilot interface of the semantic model, and when at query lab interface, the AI chat session will switch to the query lab mode. Users can make inquiries based on the current context, and the AI copilot will give the corresponding SQL statement according to the context information (includes tables information).
- Use the AI menu in the query editor. Right-click to open the menu in the editor. You will see the Optimize, Natural language to SQL and Explain three AI menu items. Users can optimize, convert and explain all or selected SQL statements in the current editor.
- Click the AI pair programmer🧑💻 button in the editor toolbar to open the input box, enter any questions or suggestions, and the AI copilot will give the corresponding SQL statement or optimization result based on the context information, and output the result directly to the editor.
1: Natural language to SQL
In the query lab, you can start writing your query, either in SQL or in natural language.
- Input Example: associate adv_reseller and adv_sales and adv_date tables and query the reseller with the highest sales in FY2020 (excluding '[Not Applicable]')
- Output Example：
SELECT reseller.reseller_id, SUM(sales.sales_amount) AS total_sales
FROM adv_sales AS sales
JOIN adv_reseller AS reseller ON sales.reseller_key = reseller.reseller_key
JOIN adv_date AS date ON sales.order_date_key = date.date_key
WHERE reseller.reseller_id != '[Not Applicable]' AND date.fiscal_year = 'FY2020'
GROUP BY reseller.reseller_id
ORDER BY total_sales DESC
LIMIT 1;
In view of some details of the information, the user can adjust the results of the statement, or refine the question asked, in order to obtain more accurate results.
2: SQL optimization
Enter the SQL statement in the editor or select the existing statement, click the Optimize in the editor right-click menu, and the AI Copilot will give the corresponding SQL statement optimization suggestions based on the context information and replace the original statement.
AI copilot will provide query optimization suggestions based on best practices to ensure the best performance of the query.
3: SQL explain
Enter SQL statement in the editor or select the existing statement, click on the right-click menu of the editor Explain, AI copilot will give the corresponding SQL statement explanation based on the context information and insert it as a comment above the original statement.
4: Free Ask
Open the AI pair programmer 🧑💻 button in the query editor toolbar to enter any questions or suggestions in the input box, and the AI copilot will give the corresponding SQL statement or optimization result according to the context information, and output the result directly to the editor.
AI copilot chatbot
The AI copilot sidebar dialog of the laboratory is in chatbot based on ChatGPT, which can intelligently answer the questions raised by the user based on the current session context. Compared with the AI menus in the editor, the chatbot dialog can use the continuous chat method to continuously ask and answer a question for a question. In order to better understand the user's intentions.
As shown in the figure below, if you are not satisfied with the answer of the AI copilot, you can continue to ask questions, add a description of the question and supplement to improve the question.
To verify whether the answer of AI copilot is correct, you can copy the SQL statement to the editor to execute to see the correctness of the result.
Click on a question in the chatbot dialog to re-edit, and then resubmit the question. You can also delete some of the dialog content, and then continue the dialog content again.
When the query is saved, the chat record of the AI copilot will be saved for the user to continue to use or query the historical chat.
Actual example of online data analysis
To better demonstrate the function of AI copilot, let's use an online data analysis instance to illustrate.
Online data analysis can be implemented through the WASM semantic model. For more details about WASM model, please refer to Analyze with WASM database.
In this article we will use the Global YouTube Statistics 2023 (A collection of YouTube giants) dataset for demonstration.
Example: Most popular videos channel
Ask：global_youtube_statistics please give analysis advice for this table:
Answer：
To provide analysis advice for the "global_youtube_statistics" table, we would need to understand the specific analysis objectives and the questions you want to answer using the data in the table. However, based on the table information provided, here are some possible analysis directions and corresponding SQL queries you can consider:
Get the top-ranked YouTubers by subscribers:
SELECT "Youtuber", "subscribers"
FROM "global_youtube_statistics"
ORDER BY "subscribers" DESC
LIMIT 10;
Find the most popular video categories based on the number of video views:
SELECT "category", SUM("video views") AS "total_views"
FROM "global_youtube_statistics"
GROUP BY "category"
ORDER BY "total_views" DESC;
Calculate the average subscribers and video views for each channel type:
SELECT "channel_type", AVG("subscribers") AS "avg_subscribers", AVG("video views") AS "avg_views"
FROM "global_youtube_statistics"
GROUP BY "channel_type";
Determine the countries with the highest and lowest average monthly earnings:
SELECT "Country", AVG("lowest_monthly_earnings") AS "avg_lowest_earnings", AVG("highest_monthly_earnings") AS "avg_highest_earnings"
FROM "global_youtube_statistics"
GROUP BY "Country"
ORDER BY "avg_lowest_earnings" ASC;
These are just a few examples of analysis queries based on the available table columns. Please provide more specific analysis goals or questions if you need assistance with additional SQL queries.
Continue to Ask：With the "video views" field and "video_views_for_the_last_30_days" field, you can analyze the changes in video views and the latest 30 days of video views to find popular videos and channels:
Answer：
To analyze the changes in video views and find popular videos and channels based on the "video views" and "video_views_for_the_last_30_days" fields in the "global_youtube_statistics" table, you can use the following SQL query:
SELECT "Title", "Youtuber", "subscribers", "video views", "video_views_for_the_last_30_days"
FROM "global_youtube_statistics"
ORDER BY ("video_views_for_the_last_30_days" - "video views") DESC
LIMIT 10;
This query will retrieve the top 10 videos or channels with the largest increase in video views over the last 30 days compared to their total video views. By examining these results, you can identify popular videos or channels that have gained significant traction recently. The query includes the "Title", "Youtuber", "subscribers", "video views", and "video_views_for_the_last_30_days" columns to provide a comprehensive view of the videos and channels.
Feel free to adjust the query based on your specific requirements or add any additional filters or criteria to further refine the analysis.
Executing the SQL query statement above will result in the following:
| Title | Youtuber | video views | video_views_for_the_last_30_days |
|---|---|---|---|
| Music | Music | 0 | NaN |
| Gaming | Gaming | 0 | NaN |
| brightside | BRIGHT SIDE | 10708532224 | NaN |
| Luis Arturo Villar Sudek | Luisito Comunica | 8670473216 | NaN |
| News | News | 0 | NaN |
...
Continue to Ask：Exclude records where video_views_for_the_last_30_days is equal 'NaN'
Answer：
To exclude records where the "video_views_for_the_last_30_days" is equal to 'NaN', you can add a WHERE clause to the SQL query. Here's the modified query:
SELECT "Title", "Youtuber", "subscribers", "video views", "video_views_for_the_last_30_days"
FROM "global_youtube_statistics"
WHERE "video_views_for_the_last_30_days" != 'NaN'
ORDER BY ("video_views_for_the_last_30_days" - "video views") DESC
LIMIT 10;
By adding the WHERE clause with the condition "video_views_for_the_last_30_days" != 'NaN', you ensure that only records with valid values for the "video_views_for_the_last_30_days" field are selected in the result set. This will exclude any records where the value is 'NaN'.
Feel free to adjust the query to suit your specific requirements or add any additional filters or criteria as needed.
Executing the SQL query statement above will result in the following:
| Title | Youtuber | video views | video_views_for_the_last_30_days |
|---|---|---|---|
| Happy Lives | Happy Lives | 2634 | 6589000192 |
| Dan-Sa / Daniel Saboya | Dan-Sa / Daniel Saboya | 2908120832 | 6147999744 |
| ��������� KIMPRO | ��������� KIMPRO | 19206701056 | 3404000000 |
| DaFuq!?Boom! | DaFuq!?Boom! | 7906181632 | 2304000000 |
| Calon Sarjana | Calon Sarjana | 10664585 | 2292000000 |
| T-Series | T-Series | 228000006144 | 2257999872 |
...
We see that although it is sorted in descending order by the video_views_for_the_last_30_days field, the value of the video views field in the first record in the result is very small, so we continue
Continue to Ask：To find the most popular video channels, we should consider the correlation of the values of the "video views" field and the "video_views_for_the_last_30_days" field:
Answer：
To find the most popular video channels by considering the correlation between the values of the "video views" field and the "video_views_for_the_last_30_days" field, you can use the following SQL query:
SELECT "Title", "Youtuber", "video views", "video_views_for_the_last_30_days"
FROM "global_youtube_statistics"
WHERE NOT("video_views_for_the_last_30_days" = 'NaN')
ORDER BY ("video views" * "video_views_for_the_last_30_days") DESC
LIMIT 10;
The query includes the "Title", "Youtuber", "subscribers", "video views", and "video_views_for_the_last_30_days" columns to provide a comprehensive view of the popular video channels.
Feel free to adjust the query based on your specific requirements or add any additional filters or criteria to further refine the analysis.
Executing the SQL query statement above will result in the following:
| Title | Youtuber | video views | video_views_for_the_last_30_days |
|---|---|---|---|
| T-Series | T-Series | 228000006144 | 2257999872 |
| Cocomelon - Nursery Rhymes | Cocomelon - Nursery Rhymes | 164000006144 | 1975000064 |
| SET India | SET India | 147999997952 | 1824000000 |
| Sony SAB | Sony SAB | 101000003584 | 1656999936 |
| Zee TV | Zee TV | 73139052544 | 1707000064 |
| Colors TV | Colors TV | 61510905856 | 1188000000 |
Then we see that the result obtained in this way will be more reasonable.
During this process, the AI copilot provides suggestions to help you optimize your query and explains the results so that you can better understand the meaning of the sales data. You can also edit the query statement in the query editor, use the AI menu to optimize, explain, and transform the query statement to better understand the meaning of the query statement. Click Save to record the entire query process for use next time or query history.
Summary
Through a simple online data example, you can see how AI copilot provides intelligent support for you in the query laboratory, so that you can analyze data faster and more accurately. In addition to online data analysis, you can also connect to public data sources or connect to intranet data sources through desktop agents to intelligently query and analyze data in the database.
In the following articles, we will take a deeper look at the AI copilot function of the metad analytics cloud, including more advanced functions and use cases. Please look forward to it!