Classic Models
Workshop - Classic Models
While examining existing schemas helps you understand OLAP concepts, the true mastery of dimensional modeling comes from building your own Mondrian schemas from the ground up. Creating a multidimensional cube requires understanding how to connect relational tables, define hierarchies that mirror business thinking, configure measures with proper aggregation rules, and publish schemas to production environments - skills that transform you from a passive consumer of analytics to an active architect of business intelligence solutions.
In this hands-on workshop, you'll construct a complete Mondrian schema called ClassicModelsOrders using Semantic Model Editor, experiencing every step of the development lifecycle from initial setup through production deployment. Unlike the SteelWheels overview where you explored a pre-built schema, this workshop guides you through creating each component yourself - establishing database connections, adding a fact table, building dimensions with hierarchies and levels, defining measures, and ultimately publishing your schema, where it becomes available for Analyzer reports and interactive analysis.
What You'll Accomplish:
Create a new Mondrian schema from scratch and establish proper naming conventions
Configure JDBC connections in both Schema Workbench and Pentaho Server for consistent data access
Add the ClassicModelsOrders cube as the central analytical structure
Configure the ORDERFACT fact table as the source of measurable business events
Define the Sales measure with sum aggregation and currency formatting
Build the CUSTOMERS dimension with a two-level hierarchy (Territory → Customer Name)
Create the PRODUCTS dimension with Product Line and Vendor levels
Configure foreign keys, primary keys, and level properties for proper query generation
Understand dimension types, hierarchy settings, and level uniqueness properties
Validate your schema structure and review the generated XML
Publish your completed schema to Pentaho BA Server
Test your schema by creating an Analysis Report in Pentaho Analyzer
By the end of this workshop, you'll have hands-on experience with every essential component of Mondrian schema development. You'll understand how fact tables connect to dimensions through foreign keys, how hierarchies organize data from general to specific, and how measures define what gets calculated and aggregated.
Most importantly, you'll complete the full development lifecycle - from initial design through testing and deployment - validating that your schema works correctly by creating actual analysis reports that leverage your multidimensional model. This practical experience prepares you to design and build production-ready OLAP cubes for real-world business intelligence requirements.
Prerequisites: Semantic Model Editor and Pentaho Server installed and configured; Access to SampleData database; Basic understanding of dimensional modeling concepts, fact tables, and dimension tables
Estimated Time: 35 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 Classic Models Schema is defined:
JDBC Connection
A JDBC (Java Database Connectivity) connection in Schema Workbench establishes the link between the tool and your source database, enabling you to access physical tables and columns needed to build Mondrian schemas.
If you're using the Pentaho Lab then the driver has already been copied to the /lib directory.
Copy the JDBC driver for your database into:
/opt/pentaho/server/pentaho-server/tomcat/lib directory.
Restart the Pentaho Server.
Select the Pentaho User Console version:
Log into the Pentaho Server Console (as Admin) > Data Connections.
Click: + Add connection.
Click: Hypersonic - Connect
In the Database Connection dialog, type or choose the following:
Connection name
hsqldb_sampledata
Connection type
Hypersonic
Host Name
localhost
Database Name
sampledata
Port Number
9001
Username
pentaho_admin
Password
password
Scroll down & Click: Test connection
Click: Connect.
The connection is added to the list.
Log into the Pentaho Server Console (as Admin) > Manage Data Sources.
From the drop-down co wheel, select: New Connection.
In the Database Connection dialog, type or choose the following:
Connection name
hsqldb_sampledata
Connection type
Hypersonic
Host Name
localhost
Database Name
sampledata
Port Number
9001
Username
pentaho_admin
Password
password
Test the connection.
Click OK.
Classic Models Schema
The ClassicModelsOrders cube is a foundational multidimensional structure built in Schema Workbench that demonstrates core OLAP design principles using sales order data from the SampleData database.
At its center is the ORDERFACT fact table, which contains transactional sales data and serves as the source for quantitative measures such as Sales (aggregated as a sum of TOTALPRICE with currency formatting).
The cube includes two primary dimensions: the CUSTOMERS dimension, which provides a hierarchical view from Territory down to individual Customer Names using the CUSTOMER_W_TER table, and the PRODUCTS dimension, which organizes product data from Product Line through Vendor using the PRODUCTS table.
Select: Semantic Model Editor.
Click: + Add New Model & enter the following details:
Model Name
Classic Models
Data Connection
hsqldb_sampledata
Click: Create Model.
Drag & drop the following table onto the Canvas.
ORDERFACT
The FACT table. Steel WheelsInc. Sales data from FY2003_2005.
Drag & drop the ORDERFACT table onto the 'Use as Fact table' option.
Cube Element
A cube, defined by a Cube XML element, is the context for a report or interactive analysis session. It represents a collection of events, describing the occurrences of a business process over the lifetime of the data mart.
Cubes tend to be a complete set of Dimensions, Hierarchies, Levels and Measures for doing an analysis on the set of events. For instance, if you’re interested in sales by customer, you might want to look at sales amounts (Measures) by customer (Dimension) geography (Level).
A cube collects these things into one place, ready for analysis and querying.
Select: Model Structue tab to define the 'Cube 1'.
Enter the following details:
Mandatory
Cube name
Classic Models
Optional Information
Default Measure
Cache
true
Visible
true
Enabled
true
Describe Cube
Caption
Description
Steel Wheels Inc.Inc. Sales FY2003-2005
Click: Apply.
If you click on the 3 dots, you're able to:
Delete the cube
Add a Measure - i.e. Sales, Revenue, Quantity Sold, etc..
Add Degenerate Dimension - i.e. Order Status (metrics data thats residing in the FACT table).
Click: Save.
Click: Advanced tab to view XML.
Measures
Measures in the ClassicModelsOrders cube are the quantitative, numeric facts that business users analyze and aggregate across different dimensional perspectives. The primary measure in this schema is Sales, which represents the total revenue from order transactions and is defined by mapping to the TOTALPRICE column in the ORDERFACT fact table. This measure uses the sum aggregator, meaning that sales values are added together when aggregated across any dimension - whether viewing total sales by Territory, Product Line, or any combination of dimensions.
The measure is configured with a formatString of $#,###.00, ensuring that sales figures display as properly formatted currency with dollar signs, thousand separators, and two decimal places for cents, making reports immediately readable and professional for business users.
The Numeric datatype ensures mathematical operations and aggregations are performed correctly. Measures are essential because they provide the "what we're measuring" in analytics - while dimensions answer "how we're grouping the data" (by customer, by product), measures answer "what values are we calculating and comparing" (sales revenue, quantities, counts), enabling users to track performance, identify trends, and make informed business decisions based on concrete numbers rather than subjective assessments.
In the Model Canvas, Click: 3 dots > Add a Measure.
Select metric type:
Select: 'Add Simple Measure' & Confirm.
Enter the following details:
Mandatory Information
Measure Name
Sales
Source Column
TOTALPRICE
Default Aggregation
Sum
Optional Information
Format
#,###.00
Visible
true
Data Type
Numeric
Describe Measure
Caption
Description
Steel Wheels Inc. Sales FY2003-2005
Click: Apply & Save.
Add a 'Quantity' measure based on QUANTITYORDERED with aggregation=COUNT.
You will need the measure for the Calculated Measure.
Calculated Measure
Calculated measures are derived metrics defined within the OLAP semantic layer that combine, transform, or aggregate base measures to create business-critical analytics. Unlike base measures that directly represent data stored in source tables, calculated measures use MDX (Multidimensional Expressions) formulas to perform operations ranging from simple arithmetic - such as dividing Sales by Quantity to determine average unit price - to sophisticated analytical functions like identifying top-performing customer segments through ranking and aggregation.
By defining these calculations once within the Mondrian schema rather than recreating them in individual reports, organizations ensure consistent metric definitions across all analyses, eliminate calculation errors from redundant implementations, and embed business logic directly into the data model where it can be centrally maintained and automatically available to all report developers and end users.
In the Model canvas, Click: (top right corner) Add a Measure.
Select: 'Add Calculated Measure'.
Click: Confirm.
Enter the following details:
Mandatory Information
Measure Name
Unit Price
Formula
[Measures].[Sales] /
[Measures].[Quantity]
Optional Information
Format
$#,###.00
Visible
true
Parent
Describe Measure
Caption
Description
Unit Cost
Click: Apply & Save.
x
x
x
x
x
Dimensions & Hierarchies
The Classic Models cube contains two standard dimensions that enable multidimensional analysis of sales data.
Open: Classic Models
Dimensions & Hierarchies
Dimension & Hierarchy
The CUSTOMERS dimension, linked to the fact table via the CUSTOMERNUMBER foreign key, provides a customer-focused analytical perspective through the "Customers" hierarchy, which includes two levels:
Territory (the top level, representing geographic sales regions with unique members) and
Customer Name (the detail level, displaying individual customer names).
To create the CUSTOMERS dimension, drag & drop: CUSTOMERS_W_TER table onto the 'Use as Dimension' option for Classic Models.
Configure the CUSTOMERS dimension with the following details:
Dimension Name
Name
CUSTOMERS
Connect to the Fact Table
Column
CUSTOMERNUMBER
Dimension Type
Type
StandardDimension
Optional Information
Visible
true
Usage Prefix
Describe Dimension
Caption
Description
Geographical Customer data
Click: Apply & Save.
Customers Hierarchy
To configure the Hierachy & Add the levels, Click on the 3 dots next to the Hierachy 1 panel.
Enter the following details:
Hierarchy Name
Name
Customers (lowercase)
Mandatory Data
Source Table
CUSTOMER_W_TER
Hierarchy Key
CUTOMERNUMBER
Has All
true
Optional Information
Visible
true
All Member Name
All Products
All Member Caption
All Level Name
Default Member
Member Reader Class
Origin
Display Folder
Unique Key Level Name
Level 1
Describe Hierarchy
Caption
Description
Customers Hierachy
Click: Apply & Save.
Don't worry about the Level1 error message. .! Let's now Add a Level to resolve the issue.
Levels
Levels
Levels in the Classic Models schema serve as the building blocks of hierarchical navigation, enabling users to analyze sales data at different levels of granularity and detail. They organize information from broad categories to specific details, allowing business users to start with high-level summaries (such as total sales by Territory or Product Line) and progressively drill down into finer details (individual Customer Names or specific Vendors) to investigate patterns, identify trends, and answer detailed business questions.
Levels also define the aggregation behavior for measures, determining how sales figures are summarized and rolled up across the hierarchy - for instance, customer-level sales automatically aggregate to the Territory level, providing meaningful subtotals at each tier.
This hierarchical structure mirrors natural business thinking, where executives might analyze performance by region while sales managers focus on individual customer performance, making OLAP cubes intuitive and aligned with real-world decision-making processes.
To configure the Territory Level, Click the 3 dots in the Level 1 panel.
Enter the following details:
Level Name
Name
Territory
Level Source
Source Table
CUSTOMER_W_TER
Column
TERRITORY
Relevant Information
Name Column
Ordinal Column
Optional Information
Visible
true
Aproximate Row Count
Null Parent Value
Key Column Type
Internal Type
Unique Members
true
Level Type
Regular
Hide Member If
Never
Formatter Class
Caption Column
Parent Column
Describe Level
Caption
Description
Customer geo.
Click: Apply & Save.
Its descision time ..
You could add a level: Customer Name .. however, this is really a Property of CUSTOMERS. So a more meaningful model would reflect the MARKETS - geo - that Steel Wheels Inc. operate in.
The MARKETS dimension would then enable the user to drill-down a geo hierarchy:
Markets
Territory
Country
State
City
and the CUSTOMERS dimension:
Customers
Customer Name
First Name
Last Name
Contact Name
eMail
Phone Number
To add Customer Name as a Level, highlight Customers Hierarchy > Add New Level.
Highlight the Level1 and enter the following details:
Level Name
Name
Customer Name
Level Source
Source Table
CUSTOMER_W_TER
Column
CUSTOMERNAME
Relevant Information
Name Column
Ordinal Column
Optional Information
Visible
true
Aproximate Row Count
Null Parent Value
Key Column Type
Internal Type
Unique Members
true
Level Type
Regular
Hide Member If
Never
Formatter Class
Caption Column
Parent Column
Describe Level
Caption
Description
Wholesale Buyer Name
Click: Apply & Save.
Classic Models Test
Create an Analyzer report based on the Classic Models: Classic Models data source.
Dimension & Hiearachy
The PRODUCTS dimension, connected through the PRODUCTCODE foreign key, organizes product information through the "Products" hierarchy with two levels:
Product Line (the top level, categorizing products into distinct product families with unique members such as Classic Cars, Motorcycles, and Planes) and
Vendor (the detail level, identifying the specific manufacturer or supplier of each product). Both dimensions follow a hierarchical structure from general to specific, allowing users to drill down from high-level categories (Territory or Product Line) to granular details (Customer Name or Vendor), with each level defined by specific database columns, data types (String), and uniqueness properties that determine aggregation behavior and query performance.
To create the PRODUCTS dimension, drag & drop: PRODUCTS table onto the 'Use as Dimension' option for Classic Models.
Configure the PRODUCTS dimension with the following details:
Dimension Name
Name
PRODUCTS
Connect to the Fact Table
Column
PRODUCTCODE
Dimension Type
Type
StandardDimension
Optional Information
Visible
true
Usage Prefix
Describe Dimension
Caption
Description
Steel Wheels Inc. Products
Click: Apply & Save.
Products Hierarchy
To configure the Hierachy & Add the levels, Click on the 3 dots next to the Hierachy 1 panel.
Enter the following details:
Hierarchy Name
Name
Products (lowercase)
Mandatory Data
Source Table
PRODUCTS
Hierarchy Key
PRODUCTCODE
Has All
true
Optional Information
Visible
true
All Member Name
All Customers
All Member Caption
All Level Name
Default Member
Member Reader Class
Origin
Display Folder
Unique Key Level Name
Level 1
Describe Hierarchy
Caption
Description
Customers Hierachy
Click: Apply & Save.
Don't worry about the Level1 error message. .! Let's now Add a Level to resolve the issue.
Levels
Levels
Levels in the Classic Models schema serve as the building blocks of hierarchical navigation, enabling users to analyze sales data at different levels of granularity and detail. They organize information from broad categories to specific details, allowing business users to start with high-level summaries (such as total sales by Territory or Product Line) and progressively drill down into finer details (individual Customer Names or specific Vendors) to investigate patterns, identify trends, and answer detailed business questions.
Levels also define the aggregation behavior for measures, determining how sales figures are summarized and rolled up across the hierarchy - for instance, customer-level sales automatically aggregate to the Territory level, providing meaningful subtotals at each tier.
This hierarchical structure mirrors natural business thinking, where executives might analyze performance by region while sales managers focus on individual customer performance, making OLAP cubes intuitive and aligned with real-world decision-making processes.
To configure the Territory Level, Click the 3 dots in the Level 1 panel.
Enter the following details:
Level Name
Name
Territory
Level Source
Source Table
CUSTOMER_W_TER
Column
TERRITORY
Relevant Information
Name Column
Ordinal Column
Optional Information
Visible
true
Aproximate Row Count
Null Parent Value
Key Column Type
Internal Type
Unique Members
true
Level Type
Regular
Hide Member If
Never
Formatter Class
Caption Column
Parent Column
Describe Level
Caption
Description
Customer geo.
Click: Apply & Save.
Its descision time ..
You could add a level: Customer Name .. however, this is really a Property of CUSTOMERS. So a more meaningful model would reflect the MARKETS - geo - that Steel Wheels Inc. operate in.
The MARKETS dimension would then enable the user to drill-down a geo hierarchy:
Markets
Territory
Country
State
City
and the CUSTOMERS dimension:
Customers
Customer Name
First Name
Last Name
Contact Name
eMail
Phone Number
To add Customer Name as a Level, highlight Customers Hierarchy > Add New Level.
Highlight the Level1 and enter the following details:
Level Name
Name
Customer Name
Level Source
Source Table
CUSTOMER_W_TER
Column
CUSTOMERNAME
Relevant Information
Name Column
Ordinal Column
Optional Information
Visible
true
Aproximate Row Count
Null Parent Value
Key Column Type
Internal Type
Unique Members
true
Level Type
Regular
Hide Member If
Never
Formatter Class
Caption Column
Parent Column
Describe Level
Caption
Description
Wholesale Buyer Name
Click: Apply & Save.
Classic Models Test
Create an Analyzer report based on the Classic Models: Classic Models data source.
Last updated
Was this helpful?