Extended Model
Adding advanced model capabilities ..
Workshop - Building a Multidimensional Data Model
Creating basic cubes with simple dimensions establishes foundational skills, but production-grade OLAP schemas require advanced capabilities that transform analytical models from functional to powerful. Real-world business intelligence demands sophisticated dimensional features including shared dimensions for consistency across cubes, time intelligence for trend analysis, geographic annotations for mapping visualizations, member properties for rich context, and degenerate dimensions for transaction-level details - all working together to create analytical environments that answer complex business questions with ease and flexibility.
In this hands-on workshop, you'll enhance the Miniature Models schema by adding enterprise-level dimensional capabilities that mirror professional business intelligence implementations. Building upon your foundation of cubes, dimensions, and hierarchies, you'll implement shared TIME dimensions that ensure consistent temporal analysis across multiple fact tables, add geographic annotations that enable map-based visualizations, enrich dimensions with member properties that provide business users with filtering and contextual information, and create degenerate dimensions that capture transaction identifiers without the overhead of separate dimension tables.
What You'll Accomplish:
Create a four-level MARKETS dimension with Territory, Country, State/Province, and City hierarchies
Add geographic annotations (Data.Role, Geo.Role, Geo.RequiredParents) for map visualization support
Refactor the CUSTOMERS dimension and add member properties (Number, Contact Names, Phone, Address, Credit Limit)
Enhance the PRODUCTS dimension with member properties (Description, Code, Vendor)
Build a shared TIME dimension that can be reused across multiple cubes
Configure time-based levels (Years, Quarters, Months) with appropriate level types
Add AnalyzerDateFormat annotations for intelligent time-based calculations
Implement DimensionUsage to reference shared dimensions from within cubes
Create a degenerate dimension (ORDERSTATUS) using fact table columns without a separate dimension table
Add a Quantity measure to enable calculated measure creation
Publish and validate your enhanced schema in Pentaho Analyzer
By the end of this workshop, you'll understand how advanced dimensional features transform basic OLAP cubes into sophisticated analytical platforms. You'll see how shared dimensions eliminate redundancy and ensure consistency, how annotations enable smart features like geographic mapping and time intelligence, how member properties provide rich context without cluttering dimension hierarchies, and how degenerate dimensions efficiently handle low-cardinality attributes. These production-grade techniques - gained through hands-on implementation - prepare you to build enterprise-quality schemas that meet demanding business intelligence requirements including year-over-year trending, geographic drill-downs, customer segmentation, and multi-dimensional analysis across complex organizational structures.
Prerequisites: Completion of Miniature Model workshop; Schema Workbench and Pentaho Server installed and configured; Access to SampleData database with DIM_TIME table; Strong understanding of dimensions, hierarchies, levels, and measures
Estimated Time: 90 minutes
Start Schema Workbench:
Ensure Pentaho Server is running:
Ensure that the Pentaho Server is up and running (automatically started in Pentaho Lab):
Follow the guide below to Extend the Miniature Models Schema:
Markets Dimension
We're going to extend the model by adding a MARKETS dimension that will enable users to drill down into the data by geo.
To add a MARKETS dimension, in the left pane, right-click Sales Cube, and click Add Dimension.
To create the MARKETS dimension, type or choose:
name
MARKETS
ForeignKey
CUSTOMERNUMBER
To view the hierarchy, in the left pane, expand MARKETS, and then click New Hierarchy 0.
To add the CUSTOMER_W_TER table, right-click New Hierarchy 0, and click Add Table.
Click in the Value for schema and select PUBLIC.
Click in the Value for name and select CUSTOMER_W_TER, and press Tab.
To name the hierarchy and set the primary key, click New Hierarchy 0.
To create the Markets hierarchy, type or choose:
name
Markets
allMemberName
All Markets
primaryKey
CUSTOMERNUMBER
To add a level, in the left pane, right-click the Markets (hierarchy) under MARKETS and select Add Level.
To define 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 Markets (hierarchy) under MARKETS and select Add Level.
To define 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 Markets (hierarchy) under MARKETS and select Add Level.
To define the State level, type or choose:
name
State Province
column
STATE
type
String
levelType
Regular
hideMemberIf
Never
To add another level, in the left pane, right-click the Markets (hierarchy) under MARKETS and select Add Level.
To define the City level, type or choose:
name
City
column
CITY
type
String
levelType
Regular
hideMemberIf
Never
x
Annotations
Geo annotations will overlay the results via OpenStreetmap REST API calls when displaying the data as a Geo Map.
In the left pane, right-click Country, and select Add Annotations. This adds the annotation folder. You must now add the individual annotations as sub-folders.
Under the Country level, right-click Annotations, and select Add Annotation.
To create the Data.Role & Geo.Role annotations, type:
name
Data.Role
cdata
Geography
name
Geo.Role
cdata
Country
Under State Province level, right-click Annotations, and select Add Annotation.
Create the following annotations:
name
Data.Role
cdata
Geography
name
Geo.Role
cdata
State
name
Geo.RequiredParents
cdata
Country
Under City level, right-click Annotations, and select Add Annotation.
Create the following annotations:
name
Data.Role
cdata
Geography
name
Geo.Role
cdata
City
name
Geo.RequiredParents
cdata
Country,State
Save the Schema.
CUSTOMERS Dimension
To clarify the analysis, we're going to delete the current Territory & Country Levels in the CUSTOMERS diemension and add some Properties to the Customer Name.
Delete the following Levels from the CUSTOMERS dimension:
Territory
Country
In the left pane, right-click the Customer Name level under Customers (Hierarchy), and select: Add Property.
To create the Customer Number property, type or choose:
name
Number
column
CUSTOMERNUMBER
type
String
When adding properties, you must type the column name.
In the left pane, right-click the Customer level under Customers, and select Add Property.
To create the Contact First Name property, type or choose:
name
Contact First Name
column
CONTACTFIRSTNAME
type
String
In the left pane, right-click the Customer level under Customers, and select Add Property.
To create the Contact Last Name property, type or choose:
name
Contact Last Name
column
CONTACTLASTNAME
type
String
(Optional) Add additional member properties for Phone (PHONE), Address (ADDRESSLINE1), and Credit Limit (CREDITLIMIT).
Save the Schema.
Delete the following Levels from the PRODUCTS dimension:
Vendor
In the left pane, right-click the (Products) Line level under Products (Hierarchy), and select Add Property.
To create the Description property, type or choose:
name
Description
column
PRODUCTDESCRIPTION
type
String
In the left pane, right-click the (Products) Name level under Products (Hierarchy), and select Add Property.
To create the Code property, type or choose:
name
Code
column
PRODUCTCODE
type
String
To create the vendor property, type or choose:
name
Vendor
column
PRODUCTVENDOR
type
String
7. Save the Schema.
TIME - A Shared Dimension
Some dimensions (such as Time or Date) may be used across multiple cubes. These dimensions are known as Shared Dimensions.
Because Shared Dimensions don't belong to a cube, you must declare an explicit table (or other data source). When you use them in a cube, you need to specify the foreign key.
This example shows the CUSTOMERS dimension being joined to the Classic Models cube using the ORDERFACT.CUSTOMERNUMBER foreign key, and to the Warehouse cube using the WAREHOUSE.WAREHOUSE_CUSTOMERNUMBER foreign key:
When generating the SQL for a join, Mondrian needs to know which column to join to. If you are joining to a join, then you need to tell it which of the tables in the join that column belongs to (usually it will be the first table in the join).
In the left pane, right-click Schema, and click Add Dimension.
To create the TIME dimension, type or choose:
name
TIME
type
TimeDimension
In the left pane, expand TIME, and then click New Hierarchy 0.
To add the DIM_TIME 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 DIM_TIME, and press Tab.
To name the hierarchy and set the primary key, click New Hierarchy 0.
To define the Time hierarchy, type or choose:
name
Time
allMemberName
All Years
primaryKey
TIME_ID
In the left pane, right-click the Time hierarchy under TIME, and select Add Level.
To create the Years level, type or choose:
name
Years
column
YEAR_ID
type
String
uniqueMembers
Selected
levelType
TimeYears
hideMemberIf
Never
Save and Publish the Schema.
Check that you can access Schema as a Data Source.
Time Annotations
In the left pane, right-click Years, and select Add Annotations.
Under the Years level, right-click Annotations, and select Add Annotation.
To create the AnalyzerDateFormat annotation, type:
name
AnalyzerDateFormat
cdata
[yyyy]
In the left pane, right-click the Time hierarchy under TIME, and select Add Level.
To create the Quarters level, type or choose:
name
Quarters
column
QTR_NAME
ordinalColumn
QTR_ID
type
String
levelType
TimeQuarters
hideMemberIf
Never
In the left pane, right-click Quarters, and select Add Annotations.
Under the Quarters level, right-click Annotations, and select Add Annotation.
To create the AnalyzerDateFormat annotation, type:
name
AnalyzerDateFormat
cdata
[yyyy].[‘QTR’q]
To add another level, in the left pane, right-click the Time hierarchy under TIME, and select Add Level.
To create the Months level, type or choose:
name
Months
column
MONTH_NAME
ordinalColumn
MONTH_ID
type
String
levelType
TimeMonths
hideMemberIf
Never
In the left pane, right-click Months, and select Add Annotations.
Under the Months level, right-click Annotations, and select Add Annotation.
To create the AnalyzerDateFormat annotation, type:
name
AnalyzerDateFormat
cdata
[yyyy].[‘QTR’q].[MMM]
Save the schema.
The next step is to refer to the TIME dimension from within the SALES cube.
Dimension Usage
Right mouse click on the Cube and select: Add Dimension Usage. Notice the different icon to indicate that this is a Shared Dimension.
To create the TIME Dimension, type or choose:
name
TIME
foreignKey
TIME_ID
source
TIME
Check the Schema and view the xml.
Order Status Degenerate Dimension
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.
In the left pane, right-click Sales_FY2003_2005 cube and click Add Dimension.
To create the ORDERSTATUS dimension, type or choose:
name
ORDERSTATUS
foreignKey
STATUS
In the left pane, expand Order Status, and then click New Hierarchy 0.
To name the hierarchy and set the primary key, click New Hierarchy 0.
To define the Order Status hierarchy, type or choose:
name
Status
allMemberName
All Status Types
primaryKey
STATUS
When there is no hierarchy name, Mondrian uses the dimension name as the hierarchy name.
In the left pane, right-click the default hierarchy under Order Status, then on the toolbar click Add Level.
To create the Type level, type or choose:
name
Type
column
STATUS
type
String
uniqueMembers
Selected
levelType
Regular
hideMemberIf
Never
Save the Schema.
Add Quantity Measure
Adding a Quantity extends the model as the measure canbe used in Calculated Measures.
To add a measure for Quantity, in the left pane, right-click Sales Cube, and click Add Measure.
To create the Quantity measure, type or choose:
name
Quantity
aggregator
sum
column
QUANTITYORDERED
formatString
#,###
datatype
Numeric
Publish the Schema
Before you make changes to your model .. Save .. this will help with Model lifecycle managemnt and troubleshooting any issues.
To publish the schema, from the menu, select File > Publish.
To publish the schema:
In the User field, type admin.
In the Password field, type password.
Click Publish.
To dismiss the Schema Publish dialog, click OK.
Create an Analyzer Report
From the User Console Home Perspective, click Create New > Analysis Report.
In the Select Data Source dialog, click Miniature Models: Sales.
Drag Sales to the Measure drop zone.
Drag Territory and Line to the Rows drop zone.
Drag Years to the Columns drop zone.
Close the Star Schema Training Exercise report.
If you're connected to the internet .. try creating a Geo Map report.
Was this helpful?