Data Analytics Workflow Using the Ocient MCP Server

This tutorial shows how to incorporate the MCP Server into an end-to-end data analytics workflow, from loading data to analysis and reporting. In this workflow, you converse with an AI agent or AI assistant in natural language, and the agent uses the MCP tools to inspect metadata and execute SQL statements against the Ocient System on your behalf. The example workflow loads a data set of taxi trips from an S3 bucket, analyzes the trips, and produces a summary report.

Prerequisites

An Ocient MCP Server connected to an Ocient System. For setup steps, see Ocient MCP Server.
An MCP client, such as an AI assistant or an AI-powered code editor, that you configure to use the server.
A database user with privileges to create tables, create data pipelines, and create MCP tools in the target schema.
Source data in a supported data pipeline source, such as an S3 bucket.

Step 1: Verify the Connection.

Ask the agent a simple metadata question.

Prompt

What schemas exist in my Ocient database?

The agent invokes the show_schemas tool and lists the user-defined schemas that your privileges allow you to see. A successful response confirms that the MCP client, the server, and the database connection all work.

Step 2: Explore the Schema.

Before the agent writes SQL statements, it grounds itself in your actual metadata. Ask about the tables and columns you plan to analyze.

Prompt

What tables are in the analytics schema, and what columns does the trips table have?

The agent invokes the show_tables and show_columns system tools to retrieve the table list and the column names, data types, and nullable properties. Because the agent reads the real metadata instead of guessing, its generated SQL statements reference correct object names and handle data types correctly. The agent can also query the information_schema views with the execute_statement tool to confirm additional type details.

Step 3: Load the Data.

Describe the data you want to load and where it lives.

Prompt

Load the CSV files in s3://example-bucket/taxi-trips/ into a new table named
analytics.trips. The files have a header row.

The agent inspects a sample of the source data, generates a CREATE TABLE SQL statement with appropriate data types, and generates a CREATE PIPELINE SQL statement to load the files. The agent executes both statements with the execute_statement tool, and then starts the load.

SQL

START PIPELINE trips_pipeline;

You can ask the agent to monitor the load progress. The agent queries the pipeline status views, such as information_schema.pipeline_status, and reports errors from the sys.pipeline_errors system catalog table if any records fail. For details about data pipelines, see Load Data.

Step 4: Analyze the Data.

Ask analytical questions in natural language.

Prompt

What are the 10 busiest pickup hours by total trips, and what is the average
fare for each of those hours?

The agent generates and executes a SQL query.

SQL

SELECT HOUR(pickup_timestamp) AS pickup_hour,
       COUNT(*) AS total_trips,
       AVG(fare_amount) AS average_fare
FROM analytics.trips
GROUP BY pickup_hour
ORDER BY total_trips DESC
LIMIT 10;

If a query fails, the execute_statement tool returns the Ocient error message as a descriptive tool error, and the agent corrects the query and retries. You can iterate conversationally, for example, ask the agent to filter by date range, join reference tables, or apply machine learning model functions from Machine Learning in Ocient.

Step 5: Encapsulate the Analysis as an MCP Tool.

After you settle on an analysis that you want to reuse, ask the agent to wrap it in a user-defined MCP tool.

Prompt

Create an MCP tool named analytics.busiest_hours that takes a start date and an
end date and returns the busiest pickup hours with average fares.

The agent executes a CREATE MCP TOOL SQL statement that stores the parameterized query as a metadata object in the database. From this point on, the MCP server automatically discovers the new tool and exposes it as analytics_busiest_hours, and any teammate or AI agent connected to the same Ocient System can invoke it by name, without knowing the underlying query logic. For an overview, see User-Defined MCP Tools. For the complete CREATE MCP TOOL syntax, see Model Context Protocol Tools.

Step 6: Generate a Report.

Ask the agent to summarize the results.

Prompt

Summarize the trip analysis as a short report with a table of the busiest hours
and three key findings.

The agent invokes the analysis tools, collects the result sets, and composes the report in your conversation or in a file, depending on your MCP client. Because the tools are stored in the database, you can repeat this workflow on a schedule or from a custom agent. For details, see Build an AI Agent with the Ocient MCP Server.

Ocient MCP Server Model Context Protocol Tools Load Data Machine Learning in Ocient

​Prerequisites

​Step 1: Verify the Connection.

​Step 2: Explore the Schema.

​Step 3: Load the Data.

​Step 4: Analyze the Data.

​Step 5: Encapsulate the Analysis as an MCP Tool.

​Step 6: Generate a Report.

​Related Links

Prerequisites

Step 1: Verify the Connection.

Step 2: Explore the Schema.

Step 3: Load the Data.

Step 4: Analyze the Data.

Step 5: Encapsulate the Analysis as an MCP Tool.

Step 6: Generate a Report.

Related Links