Steel Wheels
Walk-through of existing model ..
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
Identify degenerate dimensions like Order Status that exist within fact tables
Explore Sales measure 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.
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: 20 minutes
Ensure Pentaho Server is running:
Ensure that the Pentaho Server is up and running (automatically started in Pentaho Lab):
Follow the guide below to understand how a Schema is defined:
JDBC Connection
Before creating a schema, you must configure a database connection in the Managed Data Sources and provide 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.
Semantic Model Editor supports a vast range of relational databases through JDBC drivers, allowing you to connect to most common database systems.
You do not need to create the following Data Source.
This section is for reference only as you will be imported an existing SteelWheels model with the SampleData connection already defined.
To create a JDBC connection you will need to copy the JDBC database driver into:
/opt/pentaho/server/pentaho-server/tomcat/lib directory.
Restart the Pentaho Server, to register the driver.
Pentaho deploys with database drivers for: hsqldb & postgresql.
Log into Pentaho Server > Managed Data Sources.
From the drop-down menu, Select: New Connection.
In the Database Connection dialog, type or choose the following:
Connection name
SampleData (you cannot use reserved charaters in the connection name)
Connection type
hsqldb
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.
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.
Log into the Pentaho User Console (PUC).
Open the Model Editor by taking one of the following actions:
If you are using the Modern Design of PUC, in the menu on the left side of the page, click Model Editor.
If you are using the Classic Design of PUC, click File > Model Editor.
The Semantic Model Editor opens.
The Semantic Model Editor opens.
Click Import. The Please Select Data Source window opens.
Select: SteelWheels > Import
Notice the fact table, dimensions, and measures.
The JDBC data connection retrives the schema & tables.
To start defining the model you just darg & drop the required tables onto the canvas. You can also click on the 
to Preview the data.
The Canvas will display the name of the cube: Sales_2003_2005. This is editable.
When you place your cursor on the canvas further options enable you to add measures & degenerate dimensions along with, collapse all nodes, hide cube edges (relationships).
To view the XML schema, select Advanced tab.
To preview the Fact table data, click
Table: ORDERFACT.
Click on the 3 dots to edit the Fact Table - just the Alias!
In the Model Structure panel, select & expand CUSTOMERS.
The CUSTOMERS dimension consists of a hierarchy - Customers - with one Level - Customer - with the CUSTOMER_W_TER table.
The following actions are available:
Enable you to edit & apply the required settings at: Dimension Hierarchy Level
Preview the table data
Indicates the Key that is used to define the relationship - join - with the FACT table.
Place your cusor over the 'Relationship' to view the corresponding Key in the FACT table.
The Customer Level also has a number of Properties. Currently these are only displayed in the XML - </> Advanced tab.
The CUSTOMERS dimension consists of a hierarchy with one level (Customer) and six-member properties.
To view Annotations, again take a look at the Country Level under the MARKETS Dimension > Markets Hierarchy.
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.
Time dimensions based on: year/quarter/month/week/day/hours/minutes/seconds 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 Level Type attribute:
TimeYears
TimeQuarters
TimeMonths
TimeWeeks
TimeDays
Edit Years. Notice the Level Type has been set as TimeYears.
Expand the Degenerate Dimension - ORDERSTATUS.
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 reside in the fact table.
For example, the degenerate dimension - ORDERSTATUS - is possible as there are only a few values residing in the Order Status column in the ORDERFACT table.
Finally .. highlight the Sales measure.
Edit the measure to see how its configured.
Notice: Default Aggregation, Source Column, and Data Type.
Last updated
Was this helpful?