SteelWheels

Overview of a Mondian Schema v3 ..

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

6KB

SteelWheels.xml

Open

Start Schema Workbench:

Windows (PowerShell):

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

Linux:

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

Ensure Pentaho Server is running:

Ensure that the Pentaho Server is up and running (automatically started in Pentaho Lab):

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

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

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.

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

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.

Restart the Pentaho Schema Workbench, to register the driver.

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

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

Field

Value

Connection name

hsqldb_sampledata (you cannot use reserved charaters in the connection name)

Connection type

Hypersonic

Host Name

localhost

Database Name

sampledata

Port Number

9001

Username

pentaho_admin

Password

password

Click Test.

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

JDBC Explorer

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

To view the physical tables, expand PUBLIC.
To view the columns in the CUSTOMER_W_TER table, expand CUSTOMER_W_TER.

To close JDBC Explorer, in the top-right corner of the JDBC Explorer window, click the X icon.

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.

It features five well-designed dimensions:

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

Customers dimension with customer-level details and six member properties providing rich customer attributes;

Products dimension for product categorization and analysis;

Time dimension enabling temporal analysis across years, quarters, and months;

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

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.

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

Select: SteelWheels.xml.
Click: Open.
To view the schema, in the left pane, expand Sales_2003_2005.

Notice the fact table, dimensions, and measures.

To view the fact table, in the left pane, click Table: ORDERFACT.
In the left pane, expand Markets.

The Markets dimension consists of a hierarchy with four levels with the CUSTOMER_W_TER table.

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

In the left pane, expand Customers.

The Customers dimension consists of a hierarchy with one level (Customer) and six-member properties.

View the Product Dimension
Expand the Time Dimension.

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

In the left pane, expand Order Status.

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.

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.