> 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/preparing-data-for-analytics.md).

# Preparing Data for Analytics

Coming soon — Use Pentaho Data Integration to build the star schemas cubes sit on: a date dimension, slowly changing dimensions, fact and summary tables, and a scheduled cube refresh.

{% 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-dataprep-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 %}

Use Pentaho Data Integration to build the star schemas and summary tables that cubes and domains sit on: a date dimension, dimensions with slowly changing history, fact and summary tables, and a scheduled load that refreshes the cube.

{% columns %}
{% column %}

<p align="center"><i class="fa-file-lines" style="color:green;">:file-lines:</i><br><strong>11 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>~7h 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**

PDI Developer, Level 1 — Practitioner on the PDI Developer path, or day-to-day work in Spoon, and Schema Workbench for the cube the mart feeds.
{% 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 sales mart built with Pentaho Data Integration — a generated date dimension, dimensions with slowly changing history, a fact table loaded with surrogate keys and summary tables for aggregates — loaded by a job scheduled on the Pentaho Server that refreshes the cube when it finishes.
{% endhint %}

<figure><img src="https://1021209504-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fu2m9COjKaS0E9nT7B9bQ%2Fuploads%2FrzyZiiNckcDImpjNJ9d5%2Fsales-mart-star.svg?alt=media" alt="The Sales Mart star schema. fact_sales, one row per order line (2,996 in the Steel Wheels data), keyed on order_number and order_line_number, joins to dim_date by date_key, to dim_customer (one row per customer version) by customer_key, and to dim_product by product_key. dim_date has one row per day, 1,096 rows keyed yyyymmdd."><figcaption><p>The Sales Mart: one fact table at the order line grain, three dimensions, one surrogate key each</p></figcaption></figure>

***

<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>Modeling the Star</td><td align="center">1</td><td align="center">2</td><td align="right">~1h 30m</td></tr><tr><td>Loading Facts</td><td align="center">1</td><td align="center">2</td><td align="right">~1h 25m</td></tr><tr><td>Automate and Publish</td><td align="center">—</td><td align="center">2</td><td align="right">~1h 15m</td></tr><tr><td>Capstone Project</td><td align="center">—</td><td align="center">1</td><td align="right">~3h</td></tr><tr><td><strong>Total</strong></td><td align="center"><strong>4</strong></td><td align="center"><strong>7</strong></td><td align="right"><strong>~7h 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 the route from source data to a star schema an analyst can model.

<figure><img src="https://1021209504-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fu2m9COjKaS0E9nT7B9bQ%2Fuploads%2FTIeGWCPHTBzyJw1BEDzZ%2Forder-system-to-cube.svg?alt=media" alt="The route from order system to cube. CUSTOMERS, ORDERS, ORDERDETAILS and PRODUCTS are extracted to files; PDI transforms the dimensions, then the facts (dimensions before facts, every time), and loads the star. Group by summarizes it into agg_ tables; the Mondrian schema sales_mart.xml maps both to the Sales Mart cube that Analyzer queries."><figcaption><p>From the order system to the cube: extract, transform dimensions before facts, load, summarize, then the cube</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>From Source Data to a Star Schema</td><td>~15m</td></tr></tbody></table>

</details>
{% endstep %}

{% step %}

### <i class="fa-star" style="color:blue;">:star:</i> Modeling the Star

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

Dimensional modelling in practice: a date dimension, then dimensions that keep their history.

<figure><img src="https://1021209504-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fu2m9COjKaS0E9nT7B9bQ%2Fuploads%2F4dDFqh6WZdGv87d0R3lh%2Fscd-euro-plus-moves.svg?alt=media" alt="Type 2 beside type 1. Euro+ Shopping Channel, customer 141, has two dim_customer rows: key 11, Madrid, closed at 2005-05-15, and key 123, Barcelona, from 2005-05-15. Order 10417 of 13 May finds key 11; order 10424 of 31 May finds key 123. La Rochelle Gifts, key 4, has its credit_limit overwritten from 118200.00 to 125000.00, with no new key."><figcaption><p>One extract, two kinds of change: Euro+ gets a new version (type 2), La Rochelle Gifts an overwrite (type 1)</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 Dimensional Modeling</td><td>~15m</td></tr><tr><td><i class="fa-flask" style="color:orange;">:flask:</i></td><td>Build a Date Dimension</td><td>~30m</td></tr><tr><td><i class="fa-flask" style="color:orange;">:flask:</i></td><td>Load Dimensions with Slowly Changing History</td><td>~45m</td></tr></tbody></table>

</details>
{% endstep %}

{% step %}

### <i class="fa-table-cells" style="color:blue;">:table-cells:</i> Loading Facts

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

Load the fact table against its dimensions, then the summary tables that aggregate tables and dashboards read.

<figure><img src="https://1021209504-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fu2m9COjKaS0E9nT7B9bQ%2Fuploads%2FuPAJlTvUzDXyO6SYDWVT%2Ffact-load-one-line.svg?alt=media" alt="The first order line through tr_fact_sales.ktr: order 10100, line 1, 2003-01-06, customer 363, product S24_3969, 49 at 34.47. Lookups add date_key 20030106, customer_key 86 (the version valid on the order date) and product_key 80; a lookup that finds nothing returns key 0, the unknown member. calc-sales_amount adds 1689.03 and iu-fact_sales inserts or updates the line."><figcaption><p>One order line through the fact load: three lookups, a calculation, and a write that is safe to repeat</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 Loading Facts</td><td>~15m</td></tr><tr><td><i class="fa-flask" style="color:orange;">:flask:</i></td><td>Load the Fact Table</td><td>~40m</td></tr><tr><td><i class="fa-flask" style="color:orange;">:flask:</i></td><td>Summary Tables for Aggregates</td><td>~30m</td></tr></tbody></table>

</details>
{% endstep %}

{% step %}

### <i class="fa-calendar-check" style="color:blue;">:calendar-check:</i> Automate and Publish

`2 workshops · ~1h 15m`

Schedule the load on the Pentaho Server, and refresh the cube once new data arrives.

<figure><img src="https://1021209504-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fu2m9COjKaS0E9nT7B9bQ%2Fuploads%2F4Xq8WFzjGgggBjagxtDS%2Fload-job-refresh.svg?alt=media" alt="The job jb_load_sales_mart. Start runs tr_dim_customer, tr_dim_product, tr_fact_sales and tr_agg_sales, then the HTTP entry refresh-mondrian-cache, which flushes the Mondrian cache, then Success. Each transformation entry and the HTTP entry has a hop to Abort job, followed when its result is false."><figcaption><p>The load job with refresh-mondrian-cache between tr_agg_sales and Success</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-flask" style="color:orange;">:flask:</i></td><td>Schedule the Load on the Pentaho Server</td><td>~35m</td></tr><tr><td><i class="fa-flask" style="color:orange;">:flask:</i></td><td>Refresh the Cube After a Load</td><td>~40m</td></tr></tbody></table>

</details>
{% endstep %}

{% step %}

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

`1 workshop · ~3h`

Build a sales mart from raw orders, schedule its load, and point a cube at it.

<figure><img src="https://1021209504-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fu2m9COjKaS0E9nT7B9bQ%2Fuploads%2FY5oLKvjtUuCXz7vnmpYX%2Fcapstone-sales-rep-mart.svg?alt=media" alt="The capstone&#x27;s target. rep_fact_order_lines, one row per order line (2,996), joins to rep_dim_date by a yyyymmdd date key, to rep_dim_customer and rep_dim_product, and to rep_dim_sales_rep by the version valid on the order date; Gerard Hernandez moves from Paris to London on 15 May 2005. An agg_ table is rebuilt each load; a nightly job ends by refreshing the cube."><figcaption><p>The rep_ star, its aggregate, and the nightly job that ends by refreshing the cube</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-flask" style="color:orange;">:flask:</i></td><td>Capstone: A Mart for the Sales Cube</td><td>~3h</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/preparing-data-for-analytics.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.
