# SteelWheels

{% hint style="warning" %}

#### Workshop - Overview of SteelWheels Schema

While relational databases excel at storing transactional data, analyzing that data across multiple dimensions—such as sales by product, by region, by time period - requires a different approach. OLAP (Online Analytical Processing) enables multidimensional analysis by organizing data into cubes with dimensions, hierarchies, and measures. Pentaho's Schema Workbench allows you to create Mondrian schemas that map your relational database structures into powerful OLAP cubes, transforming row-and-column data into intuitive, business-friendly analytical models.

In this guided demonstration workshop, you'll explore the SteelWheels Mondrian schema - a comprehensive example built on the SampleData database that represents a typical sales analytics scenario. Using both JDBC Explorer and Schema Workbench, you'll examine how transactional sales data is transformed into a multidimensional cube with geographic hierarchies, customer dimensions, product categorizations, and time-based analysis. This hands-on exploration provides the foundation you need to understand dimensional modeling concepts before building your own schemas.

**What You'll Accomplish:**

* Configure JDBC connections in Schema Workbench to access relational databases
* Use JDBC Explorer to navigate physical database tables, columns, and relationships
* Open and examine an existing Mondrian schema (SteelWheels.xml)
* Explore the Sales\_2003\_2005 cube structure and its ORDERFACT fact table
* Examine the Markets dimension with its four-level geographic hierarchy
* Review the Customers dimension including member properties for rich attributes
* Understand the Products and Time dimensions for product and temporal analysis
* Identify degenerate dimensions like Order Status that exist within fact tables
* Explore measures including Quantity and Sales with their aggregators and format strings
* Review annotations that provide enhanced functionality and metadata

By the end of this workshop, you'll understand the essential components that comprise a Mondrian schema and how they work together to enable multidimensional analysis. You'll see how fact tables contain measurable transactions, how dimension tables provide context through hierarchies, and how these elements combine to create intuitive OLAP cubes. This conceptual foundation—gained through exploring a complete, real-world example—prepares you to design and build your own schemas that transform relational data into powerful analytical models for Pentaho Analyzer and other OLAP reporting tools.

**Prerequisites:** Schema Workbench installed and configured; Pentaho Server running with SampleData database accessible; Basic understanding of relational database concepts and dimensional modeling principles

**Estimated Time:** 45 minutes
{% endhint %}

<figure><img src="https://content.gitbook.com/content/VLCJbSi5xQoetAvNtGiu/blobs/rXi2PRogD20M1fCEJdLQ/image.png" alt=""><figcaption><p>SteelWheels Schema</p></figcaption></figure>

{% file src="<https://content.gitbook.com/content/VLCJbSi5xQoetAvNtGiu/blobs/VKIhESGCKLIT4ePomO1O/SteelWheels.xml>" %}

***

1. Start Schema Workbench:

{% hint style="info" %}

#### Windows (PowerShell):

```powershell
cd \
cd Pentaho/design-tools/schema-workbench/
./workbench.bat
```

{% endhint %}

{% hint style="info" %}

#### Linux:

```bash
cd
cd Pentaho/design-tools/schema-workbench/
./workbench.sh
```

{% endhint %}

2. Ensure Pentaho Server is running:

{% hint style="danger" %}
**Ensure that the Pentaho Server is up and running (automatically started in Pentaho Lab):**

```bash
cd
cd /opt/pentaho/server/pentaho-server
sudo ./start-pentaho.sh
```

{% endhint %}

Follow the guide below to understand how a **Schema** is defined:

{% tabs %}
{% tab title="1. JDBC Connection" %}
{% hint style="info" %}

#### JDBC Connection

Before creating any schema components, you must configure a database connection by selecting **Options > Connection** from the menu and providing essential connection parameters including the connection name, database type (such as Hypersonic, MySQL, Oracle, or PostgreSQL), access method (Native JDBC), host name, database name, port number, and authentication credentials.&#x20;

Schema Workbench supports a vast range of relational databases through JDBC drivers, allowing you to connect to most common database systems.
{% endhint %}

{% hint style="danger" %}
**If you're using the Pentaho Lab then the driver has already been copied to the /lib directory.**

To create a JDBC connection you will need to copy the JDBC driver for your database into the PSW install directory ...\schema-workbench\lib.&#x20;

Restart the Pentaho Schema Workbench, to register the driver.
{% endhint %}

{% embed url="<https://www.loom.com/share/5d8aeb46632048b491c3a26eeaeb936f?hideEmbedTopBar=true&hide_owner=true&hide_share=true&hide_title=true>" %}
JDBC Connection - mysql:sampledata
{% endembed %}

1. To connect to the sampledata database, from the menu select Options > Connection.

<figure><img src="https://content.gitbook.com/content/VLCJbSi5xQoetAvNtGiu/blobs/uECDe9aEKgMl76x3Bl3r/image.png" alt=""><figcaption><p>JDBC Connection</p></figcaption></figure>

2. In the Database Connection dialog, type or choose the following:

<table><thead><tr><th width="228">Field</th><th>Value</th></tr></thead><tbody><tr><td>Connection name</td><td>hsqldb_sampledata (you cannot use reserved charaters in the connection name)</td></tr><tr><td>Connection type</td><td>Hypersonic</td></tr><tr><td>Host Name</td><td>localhost</td></tr><tr><td>Database Name</td><td>sampledata</td></tr><tr><td>Port Number</td><td>9001</td></tr><tr><td>Username</td><td>pentaho_admin</td></tr><tr><td>Password</td><td>password</td></tr></tbody></table>

4. Click Test.

<figure><img src="https://content.gitbook.com/content/VLCJbSi5xQoetAvNtGiu/blobs/pCgw9Cls7HlipJY1AyMO/image.png" alt=""><figcaption><p>JDBC Connection - hsqldb:sampledata</p></figcaption></figure>

4. Click OK to dismiss the Message Box dialog and click OK to close the Database Connection dialog.

***

{% hint style="info" %}

#### JDBC Explorer

{% endhint %}

1. To view the SampleData database in JDBC Explorer, from the menu select File > New > JDBC
   \
   Explorer.

<figure><img src="https://content.gitbook.com/content/VLCJbSi5xQoetAvNtGiu/blobs/ca49aTuClvMiREekN4Lf/image.png" alt=""><figcaption><p>JDBC Explorer</p></figcaption></figure>

2. To view the physical tables, expand PUBLIC.
3. To view the columns in the CUSTOMER\_W\_TER table, expand CUSTOMER\_W\_TER.

<figure><img src="https://content.gitbook.com/content/VLCJbSi5xQoetAvNtGiu/blobs/1VYfekGHh1IEsLpAuhdR/image.png" alt=""><figcaption><p>JDBC Explorer - sampledata</p></figcaption></figure>

4. To close JDBC Explorer, in the top-right corner of the JDBC Explorer window, click the X icon.
   {% endtab %}

{% tab title="2. SteelWheels Schema" %}
{% hint style="info" %}

#### Steel Wheels Schema

The **SteelWheels** schema is a comprehensive Mondrian analysis schema built on the SampleData database that demonstrates enterprise-level dimensional modeling for sales analytics. The schema centers around the **Sales\_2003\_2005** cube, which uses the **ORDERFACT** fact table containing transactional sales data spanning three years.&#x20;

It features five well-designed dimensions:&#x20;

**Markets** dimension with a four-level geographic hierarchy (Territory, Country, State/Province, City) for location-based analysis;&#x20;

**Customers** dimension with customer-level details and six member properties providing rich customer attributes;&#x20;

**Products** dimension for product categorization and analysis;&#x20;

**Time** dimension enabling temporal analysis across years, quarters, and months;&#x20;

**Order Status** dimension, which serves as an example of a degenerate dimension existing within the fact table without a separate dimension table.&#x20;

The schema includes multiple measures such as **Quantity** and **Sales** with appropriate aggregators and format strings, making it an ideal reference model for understanding how complex business requirements are translated into functional OLAP cubes that support interactive reporting and analysis in Pentaho Analyzer.
{% endhint %}

1. From the menu, select File > Open.
2. Navigate to: Workshop--Busines-Analytics\PSW\schemas\\.

<figure><img src="https://content.gitbook.com/content/VLCJbSi5xQoetAvNtGiu/blobs/OcdkwvYmmZVUNqwuNH1H/image.png" alt=""><figcaption><p>Steel Wheels Schema</p></figcaption></figure>

2. Select: SteelWheels.xml.&#x20;
3. Click: Open.
4. To view the schema, in the left pane, expand Sales\_2003\_2005.&#x20;

<figure><img src="https://content.gitbook.com/content/VLCJbSi5xQoetAvNtGiu/blobs/l7fidp2ULcyHh0nkiNLn/image.png" alt=""><figcaption></figcaption></figure>

{% hint style="info" %}
Notice the fact table, dimensions, and measures.
{% endhint %}

5. To view the fact table, in the left pane, click Table: ORDERFACT.
6. In the left pane, expand Markets.&#x20;

<figure><img src="https://content.gitbook.com/content/VLCJbSi5xQoetAvNtGiu/blobs/LhIhtjCQGplOeTcvNtdE/image.png" alt=""><figcaption><p>Dimensions</p></figcaption></figure>

{% hint style="info" %}
The Markets dimension consists of a hierarchy with four levels with the CUSTOMER\_W\_TER table.
{% endhint %}

7. To view the annotations for the Country level, in the left pane, expand Markets and click
   \
   Data.Role.

<figure><img src="https://content.gitbook.com/content/VLCJbSi5xQoetAvNtGiu/blobs/HFR2dCQGdgZBAiPw1u28/image.png" alt=""><figcaption><p>Annotations</p></figcaption></figure>

8. In the left pane, expand Customers.

<figure><img src="https://content.gitbook.com/content/VLCJbSi5xQoetAvNtGiu/blobs/uogIRyaLShl5WjeZLXUs/image.png" alt=""><figcaption><p>Dimensions &#x26; Members</p></figcaption></figure>

{% hint style="info" %}
The Customers dimension consists of a hierarchy with one level (Customer) and six-member
\
properties.
{% endhint %}

9. View the Product Dimension
10. Expand the Time Dimension.

{% hint style="info" %}
Date dimensions are among the most important dimensions of many Mondrian cubes. The usefulness of a cube often depends on the way the date dimension has been modelled. This section shows how to create a basic date dimension and how it can be augmented with properties to suit specific analysis needs.

Time dimensions based on: year/quarter/month/week/day are coded differently in the schema due to MDX time-related functions.

Time dimensions are identified with type=TimeDimension. The role of a level in a time dimension is indicated by the levelType attribute:

* TimeYears
* TimeQuarters
* TimeMonths
* TimeWeeks
* TimeDays
  {% endhint %}

<figure><img src="https://content.gitbook.com/content/VLCJbSi5xQoetAvNtGiu/blobs/OCLVPfEEiZ1xj5yrkKzb/image.png" alt=""><figcaption><p>TIME Dimension</p></figcaption></figure>

9. In the left pane, expand Order Status.

<figure><img src="https://content.gitbook.com/content/VLCJbSi5xQoetAvNtGiu/blobs/vj1OCryVjVl339Gqdgap/image.png" alt=""><figcaption><p>Degenerate Dimensions</p></figcaption></figure>

{% hint style="info" %}
Whereas a star dimension has one-dimension table, and a snowflake dimension has two or more, a degenerate dimension has none. All of the columns that describe the dimension live in the fact table.&#x20;

For example, a degenerate dimension could be created for Order Status because there are only a few values in the Order Status column. Creating a dimension table is unnecessary because it only has a few values, adds no additional information, and incurs the cost of an additional join.
{% endhint %}

11. To view the Quantity measure, in the left pane, click Quantity.

<figure><img src="https://content.gitbook.com/content/VLCJbSi5xQoetAvNtGiu/blobs/wrSnColxLrzOWr5olPJD/image.png" alt=""><figcaption><p>Measures</p></figcaption></figure>

{% hint style="info" %}
Notice the aggregator, column, and formatString.
{% endhint %}

12. (Optional) View the Sales measure.
13. To close the schema, in the top-right corner of the schema window, click the X icon.

{% file src="<https://content.gitbook.com/content/VLCJbSi5xQoetAvNtGiu/blobs/cmvqwBx4zOrrZFIO4mut/SteelWheels.xml>" %}
{% endtab %}
{% endtabs %}