Standard Model
Apply your schema creation skills ..
Workshop - Miniature Model
Guided demonstrations provide valuable instruction, but true competency comes from independent practice where you apply learned concepts without step-by-step handholding. The transition from following instructions to creating schemas autonomously is critical for developing the confidence and problem-solving skills needed to tackle real-world dimensional modeling challenges. This workshop represents that crucial bridge—an opportunity to prove you've internalized the fundamentals while working with increased complexity.
In this hands-on exercise, you'll independently build the Miniature Models schema, demonstrating your mastery of Mondrian schema development by creating a more sophisticated analytical model than the Classic Models example. Working at your own pace with structured instructions but without detailed guidance, you'll construct a Sales_FY2003_2005 cube featuring three-level hierarchies in both dimensions—adding an intermediate tier that mirrors the multi-level drill-down paths commonly found in production business intelligence environments where users navigate from continental regions through countries to specific customers, or from product categories through vendors to individual SKUs.
What You'll Accomplish:
Create the Miniature Models schema with proper naming and initial configuration
Build the Sales_FY2003_2005 cube targeting fiscal year 2003-2005 sales analysis
Configure the ORDERFACT fact table as your source of transactional measures
Define the Sales measure with sum aggregation and currency formatting
Create the Quantity Ordered measure with distinct-count aggregation
Build a three-level CUSTOMERS dimension hierarchy (Territory → Country → Customer Name)
Construct a three-level PRODUCTS dimension hierarchy (Line → Vendor → Product Name)
Configure foreign keys, primary keys, and all required level properties independently
Understand the difference between two-level and three-level dimensional navigation
Publish your completed schema to Pentaho BA Server
Validate your work by creating an Analysis Report in Pentaho Analyzer
By the end of this workshop, you'll have demonstrated your ability to independently create production-quality Mondrian schemas without guided assistance. The three-level hierarchies provide deeper analytical capabilities than the Classic Models example, enabling users to drill through intermediate categories that often prove essential in real-world reporting scenarios. Successfully completing this exercise proves you've moved beyond rote instruction-following to genuine schema design competency—you understand not just how to build cubes, but why each component matters and how they work together to enable multidimensional analysis. This independent success prepares you to tackle custom schema requirements for actual business intelligence projects with confidence.
Prerequisites: Completion of Classic Models workshop; Schema Workbench and Pentaho Server installed and configured; Access to SampleData database; Solid understanding of cubes, dimensions, hierarchies, levels, and measures
Estimated Time: 75 minutes
Start Schema Workbench:
Ensure Pentaho Server is running:
Ensure that the Pentaho Server is up and running (automatically started in Pentaho Lab):
Data Source (Reference)
To create a new connection, from the menu select Options > Connection.
Enter the following details and Test the connection:
Connection name
hsqldb_sampledata
Connection type
Hypersonic
Host Name
localhost
Database Name
sampledata
Port Number
9001
Username
pentaho_admin
Password
password
Create Schema & add Cube
From the menu choose File > New > Schema. Alternatively, from the toolbar, click the New button, and click Schema.
In the name field, replace the existing value by typing: Miniature Models, and then press Tab. Important: Remember to press Tab after entering a new value.
On the schema toolbar, click Add cube.
In the name field, replace the existing value by typing: Sales_FY2003_2005, and then press Tab.
To save the schema, from the menu select File > Save.
In the Save dialog, type MinitiatureModels.xml, and then click Save.
Add ORDERFACT Table
To add the ORDERFACT table, in the left pane, right-click Exercise Cube, and click Add Table.
Click in the Value for schema, select PUBLIC, and press Tab.
Click in the Value for name, select ORDERFACT, and press Tab.
To save the schema, on the toolbar, click Save.
Customers & Products Dimensions, Hierarchies & Levels
To add a CUSTOMERS dimension, in the left pane, right-click Exercise Cube, and click Add Dimension.
To create the CUSTOMERS dimension, type or choose:
name
CUSTOMERS
foreignKey
CUSTOMERNUMBER
To add the CUSTOMER_W_TER table, right-click New Hierarchy 0, and click Add Table.
Click in the Value for schema, select PUBLIC and press Tab.
Click in the Value for name, select CUSTOMER_W_TER, and press Tab.
To name the hierarchy and set the primary key, click New Hierarchy 0.
To define the Customers hierarchy, type or choose:
name
Customers
allMemberName
All Customers
primaryKey
CUSTOMERNUMBERS
To add a level, in the left pane, right-click the Customers hierarchy, and select Add Level.
To create the Territory level, type or choose:
name
Territory
column
TERRITORY
type
String
uniqueMembers
Selected
levelType
Regular
hideMemberIf
Never
To add another level, in the left pane, right-click the Customers (hierarchy), and select Add Level.
To create the Country level, type or choose:
name
Country
column
COUNTRY
type
String
levelType
Regular
hideMemberIf
Never
To add another level, in the left pane, right-click the Customers (hierarchy), and select Add Level.
To create the Customer level, type or choose:
name
Customer Name
column
CUSTOMERNAME
type
String
levelType
Regular
hideMemberIf
Never
To save the schema, on the toolbar, click Save.
To add another dimension, in the left pane, right-click Sales cube, and click Add Dimension.
To create the PRODUCTS dimension, type or choose:
name
PRODUCTS
foreignKey
PRODUCTCODE
To view the hierarchy, in the left pane, expand PRODUCTS, and then click New Hierarchy 0.
To add the PRODUCTS table, right-click New Hierarchy 0, and click Add Table.
Click in the value for schema, select PUBLIC, and press Tab.
Click in the Value for name, select PRODUCTS, and press Tab.
To name the hierarchy and set the primary key, click New Hierarchy 0.
To define the Product hierarchy, type or choose:
name
Products
allMemberName
All Products
primaryKey
PRODUCTCODE
To add a level, in the left pane, right-click the Products (hierarchy), and select Add Level.
To create the Line level, type or choose:
name
Line
column
PRODUCTLINE
type
String
uniqueMembers
Selected
levelType
Regular
hideMemberIf
Never
To add another level, in the left pane, right-click the Products (hierarchy), and select Add Level.
To create the Vendor level, type or choose:
name
Vendor
column
PRODUCTVENDOR
type
String
levelType
Regular
hideMemberIf
Never
To add another level, in the left pane, right-click the Products (hierarchy), and select Add Level.
To create the Product Name level, type or choose:
name
Product Name
column
PRODUCTNAME
type
String
levelType
Regular
hideMenberIf
Never
To save the schema, on the toolbar, click Save.
Add Sales Measure
To add a measure for Sales, in the left pane, right-click Sales cube, and click Add Measure.
To create the Sales measure, type or choose:
name
Sales
aggregator
sum
column
TOTALPRICE
formatString
$#,###.00
datatype
Numeric
Repeat to add Quantity measure.
name
Quantity Ordered
aggregator
distinct-count
column
QUANTITYORDERED
formatString
#
datatype
Integer
To save the schema, on the toolbar, click Save as Miniature Models - original.xml.
Publish the Schema
To publish the schema, from the menu, select File > Publish.
To publish the schema:
User field, type: admin.
Password field, type: password.
Click: Publish.
To dismiss the Schema dialog, click OK.
Create a Report in Analyzer
Return to the User Console.
From the User Console Home Perspective, click Create New > Analysis Report.
In the Select Data Source dialog, click Miniature Models: Sales_FY2003_2005.
Drag Sales to the Measure drop zone.
Drag Territory and Line to the Rows drop zone.
Minimize the User Console and return to Schema Workbench.
Last updated
Was this helpful?