AI Generated Summary

Learn how to add AI-Generated Summaries useing Codatum's Text Panel feature and Vertex AI.

The text summary accompanying the dashboard helps users understand the data. However, summaries written by humans cannot reflect user actions such as parameter specifications.

This article introduces how to use Vertex AI via BigQuery to generate summaries based on data specified by users using parameters. This can also be achieved by using AI functions that support connections other than BigQuery.

Codatum has a Text Panel for displaying text in Markdown notation, so here we will use the Text Panel to display the chart.

How to Set up

• Setting up Vertex AI

• Model creation

• Transferring aggregated data to AI models

• Select the Text Panel chart and display the data

Following the above steps, you can add summaries that change each time the user modifies the parameters and executes the query.

Implementation Example

• Use the Chicago Taxi Trip public data from BigQuery

• Generate a summary each time the data target is changed via parameters, and create a summary corresponding to the displayed data

Model Creation

• This task can also be performed using the BigQuery query editor

CREATE OR REPLACE MODEL `codatum-example.example.vertex_ai`
REMOTE WITH CONNECTION `us.vertex_ai_test`
OPTIONS(
  ENDPOINT = 'gemini-2.0-flash'
)

After completing the above query, the vertex_ai model will be added to the example dataset in BigQuery.

Data Aggregation

• Aggregate the number of rides, ride time, ride distance, and ride fare by company and payment method on a monthly basis

  • Set company name and payment method to be changeable in parameters

SELECT
  DATE_TRUNC(trip_start_timestamp, month) as month,
  company,
  payment_type,
  COUNT(distinct unique_key) as trip_count,
  SUM(trip_seconds) / 60 as total_trip_min,
  AVG(trip_seconds) / 60 as average_trip_min,
  MAX(trip_seconds) / 60 as max_trip_min,
  MIN(trip_seconds) / 60 as min_trip_min,
  SUM(trip_miles) as total_trip_miles,
  AVG(trip_miles) as average_trip_miles,
  MAX(trip_miles) as max_trip_miles,
  MIN(trip_miles) as min_trip_miles,
  SUM(fare) as total_fare,
  AVG(fare) as average_fare,
  MAX(fare) as max_fare,
  MIN(fare) as min_fare
FROM
  `bigquery-public-data.chicago_taxi_trips.taxi_trips`
WHERE trip_start_timestamp >= '2017-01-01' AND trip_start_timestamp < '2018-01-01' -- Data from 2017
--outlier exclusion
AND trip_seconds != 0 AND fare != 0 AND trip_miles != 0
AND trip_seconds < 21600
AND CASE {Company} WHEN '' THEN TRUE ELSE company = {Company} END
AND CASE {PaymentType} WHEN '' THEN TRUE ELSE payment_type = {PaymentType} END
GROUP BY DATE_TRUNC(trip_start_timestamp, month), company, payment_type
ORDER BY month

Transferring data to AI models

Codatum allows you to reference the results of other SQL blocks, so you can manage SQL blocks that aggregate data and SQL blocks that transfer data separately.

• Convert aggregated data to JSON format

SELECT
  TO_JSON_STRING(STRUCT(
  average_fare,average_trip_miles,average_trip_min,company,max_fare,max_trip_miles,max_trip_min,min_fare,min_trip_miles,min_trip_min,month,payment_type,total_fare,total_trip_miles,total_trip_min,trip_count
  )) as input_text
FROM
  Monthly_basis_summaries

• Combine JSON data for each row into a single JSON object

SELECT
  CONCAT('[', STRING_AGG(input_text, ','), ']') AS all_input_texts
FROM
  JSON_formatted_summaries

• Pass the combined JSON data to the AI model

  • Combine the prompt and JSON data and pass them to the created model

SELECT 
  JSON_VALUE(ml_generate_text_result.candidates[0].content.parts[0].text) as output,
FROM ML.GENERATE_TEXT(
  MODEL `codatum-example.example.vertex_ai`,
  (
    SELECT
      CONCAT(
        'The following data is a summary of monthly taxi ride data by company and payment type.',
        'Summarize the trends by payment method and month, and create a report in Markdown format.',
        'After explaining the overview of the data, such as the total number of rides and the month with the most rides, explain the distinctive items in separate sections.'
        'There is no need to enclose the entire Markdown.',
        'Please use various Markdown elements.',
        'Please ensure that the generated summary is complete. No further editing of the summary will be required. However, additional analysis suggestions are possible.',
        'Since code execution is not possible within the summary, please do not embed any code.'
        'Payment methods for the taxi ride data in question is ', CASE WHEN {PaymentType} = '' THEN 'All' ELSE {PaymentType} END,', ',
        'company is ', CASE WHEN {Company} = '' THEN 'all' ELSE {Company} END, '. Input data：',
        all_input_texts
      ) as prompt,
      *,
    FROM All_JSON_formatted_summaries
  ),
    STRUCT(
      0.3 AS temperature,
      1000 AS max_output_tokens)
)

• The stability of the summary varies depending on the content of the prompt given to the model, so please try different approaches.

Adding a Text Panel chart

• Select Text Panel from the chart type selection and add it

  • The text panel displays the value in the first row, so if there are multiple text data, only one will be displayed

Notes

• If Markdown is enclosed, it cannot be expanded properly in the text panel, so please ensure that the AI output does not enclose Markdown