SteelWheels

Walk through of SteelWheels domain ..

The Pentaho Metadata Editor (PME) is a powerful tool that bridges the gap between raw database structures and business-friendly data models. At its core, PME helps organizations create metadata domains that transform complex database architectures into intuitive business models.

When you create a metadata model in PME, you're essentially building a logical map of your database that speaks the language of business. This model is stored in a central repository, giving administrators several valuable capabilities:

First, they can translate technical database terminology into clear, business-friendly language that makes sense to end users. This makes reporting and analysis more accessible to non-technical staff.

Second, when database changes occur at the technical level, the metadata layer acts as a buffer, minimizing disruption to business users and reducing maintenance costs.

The tool also enhances data governance through security controls that precisely manage user access to specific data elements in reports. Additionally, PME streamlines report maintenance by establishing consistent formatting rules for text, dates, and numbers across the organization.

For global organizations, PME supports localization, automatically adjusting how information is presented based on users' regional settings.

The metadata domain created by PME consists of two key components: the physical layer, which contains the technical database structure definitions, and the business model layer, which provides an abstracted, business-oriented view of that data. Together, these layers create a comprehensive framework that makes complex data structures more accessible and manageable.

Import SteelWheels

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

// Some code

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

3. Navigate to:

~/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.

Physical Layer

The 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.

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.

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 the 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 the Orderdetails Business Table.

Orderdetails Business Table

1. Double-click on 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.

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

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