Going deep dive on OAC MCP Server (Unlocking Oracle Analytics Cloud with AI)

When I first tried the Oracle Analytics AI Assistant, I was genuinely surprised by what it could do. Most queries ran very well—even in Slovenian, a language not officially supported in Oracle Analytics ☹️—and I was able to truly talk to my data.

However, when I tried to push the AI Assistant toward more advanced analytics, such as applying functions like TRENDLINE or CLUSTERS—both of which are one-click advanced analytics features—it simply didn’t understand what I was asking.

When the Oracle Analytics Cloud MCP Server became available, I decided to see whether it could handle these advanced analytics functions. And it did.

TRENDLINE

First, I asked the MCP Server to analyze sales revenue for the last two years of available data. Since my dataset is fairly old, I wanted to avoid any misunderstandings, so I explicitly specified that the analysis should focus on the last two years of available data.

So, here’s how it all went:

Click here to open: Customer profitability analysis

As you can see, the analysis produced is already quite comprehensive and deep, but our focus is on displaying the last year of sales revenue by month on the chart:

This chart is our baseline for calculating a trend line, so we can request to add a trend line to this chart.

MCP Server thought a bit, prepared a plan, and produced a result that was very likely correct. However, there was no evidence that Oracle Analytics’ TRENDLINE function was actually used—it wasn’t. The trend was calculated outside of Oracle Analytics.

What changed? I added a more explicit instruction: “Add a trend line to this chart, use Oracle Analytics to calculate.”

Now we can see in the console output that MCP Server generated logical SQL using TRENDLINE.

{
  `query`: `SELECT 
    MONTH(XSA('ziga.vaupot@smartq.si'.'SAMPLE_SALES_DATA')."SAMPLE_SALES_DATA"."Time Bill Dt") AS llm_0,
    SUM(XSA('ziga.vaupot@smartq.si'.'SAMPLE_SALES_DATA')."SAMPLE_SALES_DATA"."Revenue") AS llm_1,
    TRENDLINE(SUM(XSA('ziga.vaupot@smartq.si'.'SAMPLE_SALES_DATA')."SAMPLE_SALES_DATA"."Revenue"), (MONTH(XSA('ziga.vaupot@smartq.si'.'SAMPLE_SALES_DATA')."SAMPLE_SALES_DATA"."Time Bill Dt")) BY (), 'LINEAR', 'VALUE') AS llm_2
FROM XSA('ziga.vaupot@smartq.si'.'SAMPLE_SALES_DATA')
WHERE YEAR(XSA('ziga.vaupot@smartq.si'.'SAMPLE_SALES_DATA')."SAMPLE_SALES_DATA"."Time Bill Dt") = 2013
GROUP BY MONTH(XSA('ziga.vaupot@smartq.si'.'SAMPLE_SALES_DATA')."SAMPLE_SALES_DATA"."Time Bill Dt")
ORDER BY llm_0
FETCH FIRST 12 ROWS ONLY`
}

From the MCP Server output, we can see that it understands Oracle Analytics functions and knows exactly how to use the TRENDLINE function:

Based on the results, our monthly sales chart now includes a linear trend line:

To confirm that MCP Server really understands what we’re asking for, the next prompt can be: “Apply a polynomial model instead of linear.” MCP Server then executes:

The first step of the process generates a logical SQL query:

{
  `query`: `SELECT 
    MONTH(XSA('ziga.vaupot@smartq.si'.'SAMPLE_SALES_DATA')."SAMPLE_SALES_DATA"."Time Bill Dt") AS llm_0,
    SUM(XSA('ziga.vaupot@smartq.si'.'SAMPLE_SALES_DATA')."SAMPLE_SALES_DATA"."Revenue") AS llm_1,
    TRENDLINE(SUM(XSA('ziga.vaupot@smartq.si'.'SAMPLE_SALES_DATA')."SAMPLE_SALES_DATA"."Revenue"), (MONTH(XSA('ziga.vaupot@smartq.si'.'SAMPLE_SALES_DATA')."SAMPLE_SALES_DATA"."Time Bill Dt")) BY (), 'POLYNOMIAL', 'VALUE') AS llm_2
FROM XSA('ziga.vaupot@smartq.si'.'SAMPLE_SALES_DATA')
WHERE YEAR(XSA('ziga.vaupot@smartq.si'.'SAMPLE_SALES_DATA')."SAMPLE_SALES_DATA"."Time Bill Dt") = 2013
GROUP BY MONTH(XSA('ziga.vaupot@smartq.si'.'SAMPLE_SALES_DATA')."SAMPLE_SALES_DATA"."Time Bill Dt")
ORDER BY llm_0
FETCH FIRST 12 ROWS ONLY`
}

We can see that the POLYNOMIAL model has been applied:

TRENDLINE(
  SUM(XSA('ziga.vaupot@smartq.si'.'SAMPLE_SALES_DATA')."SAMPLE_SALES_DATA"."Revenue"),
  (MONTH(XSA('ziga.vaupot@smartq.si'.'SAMPLE_SALES_DATA')."SAMPLE_SALES_DATA"."Time Bill Dt")) BY (),
  'POLYNOMIAL',
  'VALUE'
) AS llm_2

And the final output shows sales by month with a polynomial trend line, which fits the data much better than the linear model:

Based on this, we can confidently conclude that the OAC MCP Server is more capable than the OAC AI Assistant for advanced analytics tasks.

CLUSTERS

With CLUSTERS, I ran into a few issues—specifically, clustering wasn’t applied on XSA (Extended Subject Area) datasets (for example, my uploaded Sales Data Excel file). So I switched to the OA BOOTCAMP semantic model and retried, essentially trying to force clustering on a subject area instead.

My request was simple: segment customers into 5 clusters.

In the first step, MCP Server responded with:

I'll cluster customers based on their purchasing behavior - Revenue, Units, and Profit. Let me build the query.

The available measures for clustering:

- Revenue
- Units
- Discount Value
- Profit
- Total Costs

And customer attributes:

- Customer Nr.
- Customer
- Age
- Credit Rate

Let me create a clustering query that segments customers into 5 clusters based on Revenue, Units, and Profit.

Based on this, we can see that MCP Server correctly identified that it should cluster customers into five segments using purchasing behavior (Revenue, Units, and Profit). Here’s the query it generated to retrieve and cluster the data:

{
  `query`: `SELECT 
    "OA Boocamp Subject Area"."D Customers"."Customer" AS llm_0,
    "OA Boocamp Subject Area"."Revenue Metrics"."Revenue" AS llm_1,
    "OA Boocamp Subject Area"."Revenue Metrics"."Units" AS llm_2,
    "OA Boocamp Subject Area"."Profit Metrics"."Profit" AS llm_3,
    CLUSTER(("OA Boocamp Subject Area"."D Customers"."Customer"), 
            ("OA Boocamp Subject Area"."Revenue Metrics"."Revenue", 
             "OA Boocamp Subject Area"."Revenue Metrics"."Units",
             "OA Boocamp Subject Area"."Profit Metrics"."Profit"), 
            'ClusterName', 'algorithm=k-means;numClusters=5') AS llm_4
FROM "OA Boocamp Subject Area"
ORDER BY llm_4, llm_1 DESC
FETCH FIRST 500 ROWS ONLY`,
  `maxRows`: 500
}

Notice the CLUSTER function with algorithm=k-means and numClusters=5. MCP Server also ran an additional query to summarize each cluster’s characteristics.

Here’s the customer segmentation into 5 clusters based on Revenue, Units, and Profit:

Cluster	Customers	Total Revenue	Total Units	Total Profit	Profile
Cluster 5	3,316	$65.97M	896K	$13.21M	🌟 Premium Customers — High volume, highly profitable
Cluster 3	3,778	$40.57M	605K	$7.23M	💼 Core Business — Largest segment, solid profitability
Cluster 4	783	$29.58M	369K	$2.86M	📈 Growth Potential — High revenue per customer, moderate profit
Cluster 2	1,545	$24.53M	345K	-$4.69M	⚠️ At Risk — Unprofitable, needs cost review
Cluster 1	578	$12.35M	158K	-$7.29M	🔴 Loss Leaders — Smallest & most unprofitable segment

Finally, I requested a visualization of the results:

Key takeaways

Natural language works—but precision matters. Oracle Analytics AI Assistant handles general analysis well, but struggles when advanced analytics functions (like TRENDLINE and CLUSTER) are requested implicitly rather than explicitly.
MCP Server understands OAC semantics. Unlike the AI Assistant, the Oracle Analytics Cloud MCP Server can correctly generate and execute OAC-specific functions in logical SQL when given clear instructions.
Advanced analytics require explicit intent. Phrases like use Oracle Analytics to calculate make a crucial difference—prompt clarity determines whether calculations are executed inside OAC or externally.
TRENDLINE support goes beyond basics. MCP Server not only applies linear trendlines, but also understands and correctly executes alternative models such as polynomial, using native OAC functions.
CLUSTER works reliably on semantic models. Clustering on XSA (uploaded datasets) may be limited, but MCP Server performs well when working against a proper OAC semantic model (e.g. OA Bootcamp).
End-to-end analytical reasoning is visible. MCP Server exposes its thought process through generated logical SQL, making it easier to validate correctness and build trust in the results.
MCP Server is a step forward for power users. For advanced analytics scenarios, MCP Server is clearly more capable than the current OAC AI Assistant and better suited for analysts who need precise, reproducible results.

Žiga Vaupot's Blog

Search This Blog