> For the complete documentation index, see [llms.txt](https://academy.pentaho.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://academy.pentaho.com/solution-analyst-path-sandbox/speciality-courses/analytics-performance.md).

# Analytics Performance

Coming soon — Make the analytics layer fast at scale: find slow queries in Mondrian logs, tune and prime the cache, design aggregate tables and indexes, and prove the gain on a slow cube.

{% hint style="warning" icon="eye" %}
**SANDBOX — draft page.** **🚧 In development — coming soon.** This Speciality is still being written. The outline below comes from the draft course `analyst-perf-specialty` in Pentaho Content Manager: module and lesson names, kinds and estimated times are taken from its lab manifests, and may change before release. The labs have not yet been tested on a lab server. Items marked REPLACE-ME need an owner's confirmation.
{% endhint %}

Make the analytics layer fast at scale: find slow queries with Mondrian SQL and MDX logging, tune and prime the Mondrian cache, design aggregate tables and indexes, and prove the gain on a slow cube.

{% columns %}
{% column %}

<p align="center"><i class="fa-file-lines" style="color:green;">:file-lines:</i><br><strong>12 lessons</strong><br><em>across 5 modules</em></p>
{% endcolumn %}

{% column %}

<p align="center"><i class="fa-flask" style="color:orange;">:flask:</i><br><strong>7 workshops</strong><br><em>hands-on labs</em></p>
{% endcolumn %}

{% column %}

<p align="center"><i class="fa-clock" style="color:cyan;">:clock:</i><br><strong>~6h 40m</strong><br><em>estimated</em></p>
{% endcolumn %}
{% endcolumns %}

{% hint style="success" icon="cloud-arrow-up" %}
Every workshop will be hands-on, on a Windows lab machine with the Lab Guide embedded. REPLACE-ME — confirm the hosted lab for this Speciality.
{% endhint %}

***

<h3 align="center">Before you start</h3>

{% columns %}
{% column %}
{% hint style="success" icon="circle-check" %}
**What you'll need**

Schema Workbench, or experience writing Mondrian schemas, and comfort with SQL. Editing server configuration files is part of the course.
{% endhint %}
{% endcolumn %}

{% column %}
{% hint style="info" icon="tag" %}
**Built against**

Pentaho Enterprise Edition **11.0.0.0-232**, the build every course on the Academy runs, on a Windows lab machine.
{% endhint %}
{% endcolumn %}
{% endcolumns %}

***

{% hint style="info" icon="trophy" %}

#### What you'll build

A deliberately slow Steel Wheels cube on a fact table of 599,200 rows, made fast step by step: Mondrian SQL and MDX logs to find where the time goes, a tuned and primed Mondrian cache, aggregate tables and indexes — with cold and warm timings before and after each change. The capstone does it again on its own cube and proves the gain.
{% endhint %}

***

<h3 align="center">Course outline</h3>

<p align="center"><i class="fa-file-lines" style="color:green;">:file-lines:</i> reading · <i class="fa-flask" style="color:orange;">:flask:</i> hands-on workshop</p>

{% hint style="info" icon="circle-info" %}
This is the draft outline. Times are the lab guides' own estimates, and the course is not yet open for enrolment.
{% endhint %}

<table data-search="false"><thead><tr><th width="382">Module</th><th width="115" align="center">Reading</th><th width="132" align="center">Workshops</th><th width="100" align="right">Time</th></tr></thead><tbody><tr><td>Getting Started</td><td align="center">2</td><td align="center">—</td><td align="right">~30m</td></tr><tr><td>Finding Slow Queries</td><td align="center">1</td><td align="center">2</td><td align="right">~1h 15m</td></tr><tr><td>Caching</td><td align="center">1</td><td align="center">2</td><td align="right">~1h 20m</td></tr><tr><td>Aggregates and the Database</td><td align="center">1</td><td align="center">2</td><td align="right">~1h 35m</td></tr><tr><td>Capstone Project</td><td align="center">—</td><td align="center">1</td><td align="right">~2h</td></tr><tr><td><strong>Total</strong></td><td align="center"><strong>5</strong></td><td align="center"><strong>7</strong></td><td align="right"><strong>~6h 40m</strong></td></tr></tbody></table>

{% stepper %}
{% step %}

### <i class="fa-rocket" style="color:blue;">:rocket:</i> Getting Started

`2 readings · ~30m`

How to get the most out of this guide, and where the time goes when an Analyzer report is slow.

<figure><img src="https://1021209504-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fu2m9COjKaS0E9nT7B9bQ%2Fuploads%2FKZf0dC2Bp3dl7NyvhHVy%2Frequest-path.svg?alt=media" alt="The six stages of one Analyzer request: Analyzer writes one MDX query; Mondrian parses and plans; Mondrian looks in its cache; the database runs a GROUP BY over the fact table for each missing segment; Mondrian evaluates the cells; Analyzer renders them. Beside each stage, the log that times it. Only the database stage grows with the fact table."><figcaption><p>The request path: six stages, the log that times each one, and the one stage that grows with the fact table</p></figcaption></figure>

<details>

<summary>See the full breakdown</summary>

<table data-search="false"><thead><tr><th width="79"></th><th>Lesson</th><th width="90">Time</th></tr></thead><tbody><tr><td><i class="fa-file-lines" style="color:green;">:file-lines:</i></td><td>Before You Start</td><td>~15m</td></tr><tr><td><i class="fa-file-lines" style="color:green;">:file-lines:</i></td><td>Where Analytics Time Goes</td><td>~15m</td></tr></tbody></table>

</details>
{% endstep %}

{% step %}

### <i class="fa-magnifying-glass-chart" style="color:blue;">:magnifying-glass-chart:</i> Finding Slow Queries

`1 reading · 2 workshops · ~1h 15m`

Turn on Mondrian's SQL and MDX logging and profile a slow Analyzer report down to the query that costs the most.

<figure><img src="https://1021209504-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fu2m9COjKaS0E9nT7B9bQ%2Fuploads%2F835Wcj9nNdolHBLDmTu0%2Flog-mark-to-fix.svg?alt=media" alt="Six rows from the mark in the logs to its cause and fix. A long exec with few rows: no aggregate table. Filtered as slow as unfiltered: missing indexes. SQL on every run: a cold cache, so turn it on and prime it. Thousands of members first: a high-cardinality level. The NonEmptyCrossJoin warning: a contains filter; use includes. Times that grow: too little memory, check -Xmx."><figcaption><p>From the mark in the logs to the fix, and the lesson that makes each fix</p></figcaption></figure>

<details>

<summary>See the full breakdown</summary>

<table data-search="false"><thead><tr><th width="79"></th><th>Lesson</th><th width="90">Time</th></tr></thead><tbody><tr><td><i class="fa-file-lines" style="color:green;">:file-lines:</i></td><td>Overview of Finding Slow Queries</td><td>~15m</td></tr><tr><td><i class="fa-flask" style="color:orange;">:flask:</i></td><td>Mondrian SQL and MDX Logging</td><td>~25m</td></tr><tr><td><i class="fa-flask" style="color:orange;">:flask:</i></td><td>Profile a Slow Analyzer Report</td><td>~35m</td></tr></tbody></table>

</details>
{% endstep %}

{% step %}

### <i class="fa-memory" style="color:blue;">:memory:</i> Caching

`1 reading · 2 workshops · ~1h 20m`

How the Mondrian cache works, how to size and tune it, and how to flush and prime it after a data load.

<figure><img src="https://1021209504-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fu2m9COjKaS0E9nT7B9bQ%2Fuploads%2FXCh97xjSTTuPLZ4FOVE2%2Fsegment-lookup.svg?alt=media" alt="Mondrian&#x27;s search for a segment: parse the query, then the local cache if enabled, then the external segment cache if configured, and only then SQL, a Segment.load against the fact table. With EnableInMemoryRollup on, finer segments are rolled up in memory. Afterwards a weak reference is kept, which the JVM reclaims when short of heap."><figcaption><p>How a query finds a segment: memory first, SQL last, and a weak reference afterwards</p></figcaption></figure>

<figure><img src="https://1021209504-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fu2m9COjKaS0E9nT7B9bQ%2Fuploads%2FNFBYwQ91VYNAw7p79BVP%2Fstale-flush-prime.svg?alt=media" alt="Without a flush, the load adds late 2005 orders to ORDERFACT_BIG but the segment cache still holds the old cells, so the report shows yesterday&#x27;s figures. With a flush (the Mondrian Schema Cache refresh or a script) and a scheduled run of Slow-Sales-by-Line that takes the cold run, the first user opens the report warm with today&#x27;s figures."><figcaption><p>Stale cache, flush and prime: what the workshop shows, with and without a flush after the load</p></figcaption></figure>

<details>

<summary>See the full breakdown</summary>

<table data-search="false"><thead><tr><th width="79"></th><th>Lesson</th><th width="90">Time</th></tr></thead><tbody><tr><td><i class="fa-file-lines" style="color:green;">:file-lines:</i></td><td>Overview of Mondrian Caching</td><td>~15m</td></tr><tr><td><i class="fa-flask" style="color:orange;">:flask:</i></td><td>Tune the Mondrian Cache</td><td>~30m</td></tr><tr><td><i class="fa-flask" style="color:orange;">:flask:</i></td><td>Flush and Prime the Cache</td><td>~35m</td></tr></tbody></table>

</details>
{% endstep %}

{% step %}

### <i class="fa-database" style="color:blue;">:database:</i> Aggregates and the Database

`1 reading · 2 workshops · ~1h 35m`

Design aggregate tables for the queries users actually run, and index the star schema for Mondrian's SQL.

<figure><img src="https://1021209504-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fu2m9COjKaS0E9nT7B9bQ%2Fuploads%2Fk7JXm4MivJrWrVNVKldU%2Fagg-collapsed-kept-lost.svg?alt=media" alt="ORDERFACT_BIG, 599,200 rows, beside its two aggregates. PERF_AGG_TERR_LINE_YEAR, at most 84 rows, holds collapsed TERRITORY, PRODUCTLINE and YEAR_ID and needs no join. AGG_CUST_QTR_ORDERFACT_BIG keeps CUSTOMERNUMBER, joining CUSTOMER_W_TER, collapses YEAR_ID and QTR_NAME, and loses Product and Order Status. Each FACT_COUNT adds up to 599,200."><figcaption><p>Collapsed, kept and lost: the treatment of each dimension, in the columns of the two aggregate tables</p></figcaption></figure>

<details>

<summary>See the full breakdown</summary>

<table data-search="false"><thead><tr><th width="79"></th><th>Lesson</th><th width="90">Time</th></tr></thead><tbody><tr><td><i class="fa-file-lines" style="color:green;">:file-lines:</i></td><td>Overview of Aggregates and the Database</td><td>~15m</td></tr><tr><td><i class="fa-flask" style="color:orange;">:flask:</i></td><td>Design Aggregate Tables</td><td>~45m</td></tr><tr><td><i class="fa-flask" style="color:orange;">:flask:</i></td><td>Indexes and the Star Schema</td><td>~35m</td></tr></tbody></table>

</details>
{% endstep %}

{% step %}

### <i class="fa-trophy" style="color:blue;">:trophy:</i> Capstone Project

`1 workshop · ~2h`

Take a slow cube, find what makes it slow, and make it fast — with the before and after timings to prove it.

<details>

<summary>See the full breakdown</summary>

<table data-search="false"><thead><tr><th width="79"></th><th>Lesson</th><th width="90">Time</th></tr></thead><tbody><tr><td><i class="fa-flask" style="color:orange;">:flask:</i></td><td>Capstone: Make a Slow Cube Fast</td><td>~2h</td></tr></tbody></table>

</details>
{% endstep %}
{% endstepper %}

***

{% hint style="info" icon="award" %}
**Optional, not part of the path**

Speciality courses are open to any learner who wants to go further. They are not among the five mandatory courses of the Certified Solution Analyst path. The course ends in a capstone project. REPLACE-ME — confirm whether it carries its own exam and course accreditation.
{% endhint %}

***

<p align="center"><a href="https://academy.pentaho.com/solution-analyst-path-sandbox/speciality-courses.md" class="button secondary" data-icon="arrow-left">Back to Speciality Courses</a></p>


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://academy.pentaho.com/solution-analyst-path-sandbox/speciality-courses/analytics-performance.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
