Classic Models

Workshop - Classic Models

Welcome to the Classic Models Schema creation workshop. In this hands-on guided demonstration, you will learn to build a complete Mondrian schema from the ground up using Schema Workbench. Unlike the SteelWheels overview where you examined an existing schema, this workshop will walk you through the entire process of creating a new multidimensional schema, from establishing database connections to publishing your completed schema to the Pentaho BA Server.

The Classic Models schema represents a typical sales and order management scenario, providing you with practical experience in the fundamental skills required for dimensional modeling and OLAP cube design. By the end of this workshop, you will have created a fully functional schema ready for business intelligence reporting and analysis.

In this guided demonstration, you will:

• Create a new schema - Learn the foundational steps to initialize a new Mondrian schema, including naming conventions and initial schema setup in Schema Workbench

• Add a cube to a schema - Understand the role of cubes in multidimensional modeling and configure the ClassicModelsOrders cube as the analytical structure for your data

• Add the fact table to a cube - Identify and configure the ORDERFACT table as your central fact table, which contains the measurable business events and foreign keys to dimension tables

• Add measures to a cube - Define quantitative metrics such as Sales that will be aggregated and analyzed, including proper configuration of:

  • Column mappings from the fact table

  • Aggregation functions (sum, count, avg, min, max)

  • Data types and format strings for proper display

  • Measure visibility and naming

• Add basic dimensions to a cube - Build the dimensional framework for analysis by creating two complete dimensions:

  • CUSTOMERS Dimension: Including Territory and Customer Name levels with proper hierarchy configuration

  • PRODUCTS Dimension: Including Product Line and Vendor levels

  • Learn to configure foreign keys, primary keys, hierarchies, and level properties

• Publish the schema to the Pentaho BA Server - Complete the development lifecycle by deploying your schema to the BA Server, making it available as a data source for Analyzer reports and interactive analysis

Workflow to define a Schema

---

1. 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 that the Pentaho Server is up and running:

Follow the guide below to understand how Classic Models Schema is defined:

1. JDBC Connection

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.

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

Restart the Pentaho Metadata Editor, and you will see your database in the Connection Type list.

JDBC Connection - mysql:sampledata

---

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

JDBC Connection

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

Field	Value
Connection name	hsqldb:sampledata
Connection type	Hypersonic
Host Name	localhost
Database Name	sampledata
Port Number	9001
Username	pentaho_admin
Password	password

1. Click Test.

JDBC Connection - hsqldb:sampledata

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

2. Schema / Cube

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.

Creating a Schema

---

1. To create a new schema, from the menu choose File > New > Schema. Alternatively, from the toolbar, click the New button, and click Schema.

1. To name the schema, in the left pane, click: Schema.

2. In the name field, replace the existing value by typing: Classic Models, and then press Enter.

Schema - ClassicModels

1. To add a cube, on the toolbar, click Add cube.

2. In the name field, replace the existing value by typing: ClassicModelsOrders.

1. To save the schema, from the menu select File > Save As:

3. FACT

FACT Table

The fact table holds the columns from which measures are calculated and contains references to the dimension tables.

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.

Fact Table

---

1. To add the ORDERFACT table, in the left pane, right-click Basic Orders, and click Add Table.

Add table

1. Click in the Value for name, select PUBLIC > ORDERFACT.

Set ORDERFACT table

1. Save the Model.

4. Dimensions & Hierarchies

Dimensions & Hierarchies

The ClassicModelsOrders cube contains two standard dimensions that enable multidimensional analysis of sales data. 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).

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.

Dimensions

---

1. To add a dimension, in the left pane, right-click ClassicModelsOrders Cube, and click Add Dimension.

Add Dimension

1. To create the CUSTOMERS dimension, type or choose:

Attribute	Value
name	CUSTOMERS
foreign key	CUSTOMERNUMBER
type	StandardDimension

CUSTOMER Dimension

1. To view the Hierarchy, in the left pane, expand CUSTOMERS, and then Click: New Hierarchy 0.

Add table

1. To add the CUSTOMER_W_TER table, right-click New Hierarchy 0, and Click: Add Table.

2. Click in the Value for name, select PUBLIC > CUSTOMER_W_TER, and press Tab.

Select Dimension table

1. To name the hierarchy and set the primary key, click New Hierarchy 0.

CUSTOMER Hiearachy

1. To create the Customer hierarchy, type or choose:

Attribute	Value
name	Customers
hasAll	enable
allMemberName	All Customers
primaryKey	CUSTOMERNUMBER
visible	enable

---

Level

Levels in the ClassicModelsOrders 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.

1. To add a level, in the left pane, right-click the Customer Hierarchy, and select Add Level.

x

x

x

x