SteelWheels

Walk through of SteelWheels domain ..

Workshop - Overview of SteelWheels Domain

This workshop introduces you to the Pentaho sampledata database (also known as Steel Wheels), a comprehensive dataset modeling a fictional toy company's complete operations. The database contains 13 interconnected tables spanning customers and territories, employees and offices, products, orders and order details, payments, departmental management, time dimensions, performance metrics, and financial data. This realistic, multi-layered structure provides an ideal foundation for learning how to organize business data in the metadata layer.

By the end of this workshop, you will understand:

• How to establish a JDBC connection to access the sampledata database

• What physical tables and columns represent and how they form the foundation of your metadata

• How to organize tables into business models that align with business logic and reporting needs

• How to define relationships between tables to create meaningful connections and enable accurate data analysis

• How to create business views that translate physical data into intuitive, business-focused presentations

Through exploring the Steel Wheels dataset, you'll learn the essential building blocks of Pentaho metadata, preparing you to apply these same principles to real-world business data.

---

Follow the guide below to understand how a Domain is defined:

1. Import SteelWheels

SteelWheels Domain

The following steps show you how to use an example metadata model in Pentaho Metadata Editor to create an Interactive Reports.

Orders Business Model

---

1. Start Metadata Editor:

Windows (Powershell):

cd \
cd Pentaho/design-tools/metadata-editor/
./metadata-editor.bat

Linux:

cd
cd Pentaho/design-tools/metadata-editor/
./metadata-editor.sh

1. Import (open) an existing metadata domain:

2. From the menu, select: File > Import from XMI File

PME UI

1. Navigate to:

Linux:

~/Pentaho/design-tools/metadata-editor/samples

Windows:

C:\Pentaho\design-tools\metadata-editor\samples

1. Double-click: steel-wheels.xmi.

2. In the Save Model dialog, type: SteelWheels.

3. Click OK.

Note a domain can only have 1 database connection.

2. Physical Layer

Physical Layer

The physical layer of a Pentaho domain encompasses connections, physical tables and physical columns. These objects represent the database(s) you are trying to model and enrich with metadata. The physical layer is not considered part of the business model, because not all connections defined in the physical layer will be used in every business model.

Once imported you will see that:

• Physical Layer includes 13 tables.

Physical Layer

1. In the left pane, expand Connections > SampleData.

2. Expand Products and notice the list of columns.

Products Table

1. Double-click on Product Line.

A Properties Editor opens, which displays the metadata properties for each object (prefix):

PT - Physical Table

PC - Physical Column

BT - Business Tables

BC - Business Columns

CT - Categories

Product Line Base Metadata Properties

1. Scroll through the Base settings - Parent Concepts.

Notice most of the Base metadata settings can be overridden. The property - Parent Concept - will be inherited in the Business Models and Business Views Layers.

1. Expand > Orderdetails table.

2. Double-click on Total.

PC_TOTAL

If you have a requirement to create dimensions / measures for Interactive Reports that desn't exist in the Physical tables, then you can easily create the column based on existing columns.

1. Scroll through the Base settings.

TOTAL - settings

Notice the column is based on the calculation:

[QUANTITYORDERED]*[PRICEEACH]

The columns are referenced with [COLUMN]

This can be aggregated to: Sum, Average, Minimum & Maximum.

3. Business Models

Business Models

There are three Business Models within this metadata domain. Each model uses one or more tables from the physical layer.

1. Collapse Connections and Expand: Business Models.

2. Expand: Orders Business Model.

Business Models

The Orders Business Model includes five Business Tables:

• Customer W Ter

• Orders

• Orderdetails

• Products

• Payments

The Business Model Layer is where you overide and set the Parent Concept to be inherited in the Business View Layer.

1. Expand: Orderdetails Business Table.

Orderdetails Business Table

1. Double-click: Price Sold.

Price Sold - Properties

Notice a 'Mask for Number or Date' has been added and the value set as a Parent Concept. The Mask - $#,##0.00 format - will be inherited in the Business View Layer.

4. Relationships

Relationships

Once you have all your Business Tables created, you will need to create relationships between the tables, so that the query generators and SQL generators that work with Pentaho metadata can create the data queries correctly.

This is very much like drawing a relational diagram to show primary and foreign key relationships. Although relational links are not the only relationships that can be modelled. You can create a relationship between any two tables, link any two columns between them and dictate what the relationship is (one to many, many to many, etc.).

The important pieces of information to know before you try to create a relationship is:

• what two Business Tables would you like to associate with this relationship?

• what columns in the business tables identify the relationship?

• and what kind of relationship is it - one to one, one to many, many to one, etc?

Relationships

1. Expand: Business Models > Orders > Relationships

2. Double-click:

BT_ORDERS_ORDERS.BC_ORDERS_CUSTOMER_NUMBER - BT_PAYMENTS_PAYMENTS.BC_PAYMENTS_CUSTOMERNUMBER

Relationship Properties

The relationship defines the Inner JOIN with the constraint 1 order has 1 payment.

The SQL is generated from references the unique keys in the Tables using the following metadata syntax:

[BT_ORDERS_ORDERS].[BC_ORDERS_CUSTOMER_NUMBER]

5. Business Views

Business Views

Business Views acts as 'buckets - categories' that enable you to define the sematic layer that is exposed to the end user. Each Category can contain any column(s) that have been defined in the Business tables.

Business View - Orders category

1. Expand: Business View > Orders

2. Highlight Orders category.

3. Right-mouse click and select the option: Manage Categories.

Categories

1. The Panel enables you to associated the Business Column(s) with the Category.

Manage Categories