OrdersME
Create an Orders Domain ..
Workshop - OrdersME
While exploring pre-built metadata domains helps you understand the architecture, the real learning comes from building one yourself from the ground up. Creating a metadata domain requires understanding how each layer—from physical database connections through business models to semantic categories—works together to transform raw database schemas into business-friendly data access layers that empower non-technical users to create their own reports and analyses.
In this hands-on workshop, you'll construct a complete metadata domain called OrdersME from scratch using the Steel Wheels sampledata database. Starting with a blank canvas, you'll establish database connectivity, import physical tables, design a star schema business model, define relationships that enable accurate query generation, and create an intuitive business view organized into logical categories. Finally, you'll publish your completed domain to the Pentaho Server and validate it by building an Interactive Report—experiencing the full lifecycle from metadata design to end-user consumption.
What You'll Accomplish:
Install JDBC drivers and establish database connections in Pentaho Metadata Editor
Import physical tables and columns from a database into your metadata domain
Create a business model based on star schema design principles
Define business tables and map them to physical tables with enhanced metadata
Create relationships between tables using both one-to-many and many-to-one cardinalities
Build business views with logically organized categories that reflect business terminology
Use multiple techniques (Tree Navigator and Editor Graph) to create relationships
Understand complex join scenarios for multi-column relationships
Publish your metadata domain to the Pentaho Server as an XMI file
Test your domain by creating an Interactive Report that leverages your semantic layer
By the end of this workshop, you'll have hands-on experience with every aspect of metadata domain creation. You'll understand how physical database structures transform into business-friendly semantic layers, how relationships enable cross-table analysis, and how categories organize fields into intuitive groupings. Most importantly, you'll see the complete workflow from initial database connection through to end-user report creation—validating that your metadata design successfully bridges the gap between technical database schemas and business user needs.
Prerequisites: Pentaho Metadata Editor installed; Access to SteelWheels sampledata database; JDBC driver for your database; Basic understanding of relational database concepts and star schema design
Estimated Time: 90 minutes
Start Metadata Editor:
Start the Pentaho Server (not required if using Pentaho Labs):
Follow the guide below to create your OrdersME domain:
Connect to the Database
A connection represents connection information of a specific database, and acts as the parent in the hierarchy for all physical tables and physical columns that are defined for that database.
Pentaho metadata models can connect to most common relational databases using JDBC. The Pentaho Metadata Editor (and the Pentaho Metadata Architecture) supports a vast and rich set of data sources. Before you begin defining your business model, you must first describe the database or data source that you would like to model. You do this by defining one or more connections in the editor.
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 JDBC driver for your database into the PME install directory ...\metadata-editor\lib.
To resolve MariaDB issues, it is advised to use the MySQL JDBC driver.
Restart the Pentaho Metadata Editor, to register the driver.
Define a Domain & JDBC Connection
Before you begin defining your business model, you must first describe the database or data source that you would like to model.
To define a new domain, select: File > New > Domain File from the main menu.
Right-mouse click on Connections or: File > New > Connection
Select the installation environment and enter the connection settings:
Adding JDBC driver
Before you can connect to a data source in any Pentaho server or client tool, you must first install the appropriate database driver.
Before copying a new JDBC driver, ensure that there is not a different version of the same JAR in the destination directory. If there is, you must remove the old JAR to avoid version conflicts.
Copy the JDBC driver to:
Once the driver JAR is in place, you must restart the server or client tool.
Import Tables
Fortunately, when you import a physical table, all the table's columns come with it, so the import is a one-step exercise instead of two. You can later remove those columns that you do not want in the connection or the model.
Schema
In this workshop we're going to model a Star Schema - a database design pattern used in data warehousing where a central fact table containing quantitative business metrics (like sales amounts or quantities) is surrounded by dimension tables that provide descriptive context such as customers, products, time, and geography.
Hold down the Ctrl key and use the mouse to select the following:
ORDERFACT
(2,996 rows) - A fact table for OLAP analysis containing order transaction data linked to customers and other dimensional tables
PRODUCTS
(110 rows) - Contains product catalog information including product line, vendor, code, name, scale, description, quantity in stock, buy price, and MSRP
ORDERS
(330 rows) - Records customer orders placed with the company, containing order-level information like order dates and customer references.
ORDERDETAILS
(3,001 rows) - Stores line-item details for each order, breaking down individual products and quantities within each order.
DIM_TIME
(265 rows) - A time dimension table for temporal analysis, supporting date-based reporting and analytics.
To remove extraneous columns from your physical tables:
Right-click (or <CTRL+click>) on the physical table node you wish to edit in the Tree Navigator. Select the Edit option from the popup menu.
The Physical Table Properties dialog displays. In the dialog's Tree Navigator, select a column you wish to remove.
Click the delete icon (the one with the red circle), to the right of the word Subject above the dialog's Tree Navigator.
Repeat with any remaining columns that you want to remove. Click OK when you are done.
Business Models
Metadata in Pentaho is based on relational data modeling, which maps the physical structure of your database into a logical business model. The goal of the relational data modeling in Pentaho is to simplify the experience of business users when they are creating reports.
Follow the guide to define: OrdersME Business Model
Create Business Model:
Right-mouse click the Business Models node in the Tree Navigator.
Select New Business Model.
To name your new model, enter: OrdersME in the Name property text box on the right.
From the Connection drop-down box, select: mysql:sampledata.
Click the OK button to close the dialog.
Business Tables
After creating the business model, the next step is to add the business tables and business columns, then create the relationships between our business tables.
Select Tables:
Right-click (or ALT-click) on the Business Tables branch in the Navigator Tree.
Select: New Business Table.
Select the physical table you want to associate with this new business table.
Relationships
Once you have all your business tables created, you will need to define the 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.
Follow the guide to create the required Relationships:
Relationships
To create a new relationship between business tables using the Tree Navigator, first make sure that the model you want to add this relationship to is selected, and the Relationships node is visible (lives under the business model name node).
Right-click (or ALT-click) on the Relationships branch in the Navigator Tree.
Select the New Relationship... option from the popup menu. The ‘Relationship Properties’ dialog displays.
Select from the: From Table / Field list the business table that you would like to start the relationship from:
BT_DIM_TIME_DIM_TIME
BC_DIM_TIME_TIME_ID
BT_ORDERFACT_ORDERFACT BC_ORDERFACT_TIME_ID
Select the To Table / Field list the business table that you would like the relationship to go to:
Next step, define the relationship from the Relationship drop down list. For Relationship, choose 1:N (1 to many), and then click OK.
If the relationship requires a complex join, select the complex join checkbox, and enter a formula in the text box provided.
Click OK
Editor Graph
In the Editor Graph, creating a new relationship is simplified a bit, because you select the two businesstables on the canvas, and the Relationship Properties dialog is pre-populated with your selections.
Select the two business tables you want to include in the new relationship, either by click and dragging a marquee around the tables, or by holding the SHIFT+CTRL keys, then clicking on the tables.
Once your business tables are selected in the Graph, right-click (or CTRL-click) on the selection.
Click: Add Relationship.
In the Relationship Properties dialog, in From Table / Field, choose BT_ORDERFACT_ORDERFACT as the source table and BC_ORDERFACT_PRODUCTCODE as the source field.
In To Table / Field, choose BT_PRODUCTS_PRODUCTS as the destination table and BC_PRODUCTS_PRODUCTCODE as the destination field.
For Relationship, choose N:1 (many to 1) and then click OK.
Repeat the workflow for Orders.
Complex Joins
Complex joins appear in the WHERE clause of the SQL statement, so currently any joining that takes place in the FROM clause of the SQL statement is not supported.
An example of a complex join might be TABLE_A.COL_A=TABLE_B.COL_A AND TABLE_A.COL_B=TABLE_B.COL_B.
This represents a join of two tables based on two key columns versus a single join column. Also note, the complex join expression provided must use the names of the physical tables and physical columns, not business tables and business column names.
The following table describes the possible table relationships:
1:N
A one-to-many mandatory relationship is the most common relationship in databases. The primary key table contains only one record that relates to none, one, or many records in the related table. This relationship is similar to the one between you and one of your parents. You have one mother, but your mother may have several children.
N:1
A many-to-one is opposite of one to many (1:N) relationship.
1:1
In a one-to-one relationship, both tables are limited to one record only on either side of the relationship. Each primary key value relates to a single record, or no record, in the associated table. They are like spouses — you may be married, or not; however, if you are married, both you and your spouse can have only one partner. Most one-to-one relationships are forced by business rules. If you do not have a business rule, you can, in most cases, combine both tables into one table without breaking normalization rules.
0:N
A zero to many optional relationship indicates that a person may have no phone, one phone, or many phones, and that the phone may not be "owned," but can only be owned by a maximum of one person.
N:0
Opposite of a zero to many relationship
0:1
A zero to one relationship might indicate that a person may be a programmer, but a programmer must be a person. It is assumed that the mandatory side of the relationship is the dominant.
1:0
Opposite of a zero to one relationship
N:N
In a many to many relationship each record in both tables can relate to an unlimited number of records (or no records) in the other table. For example, if you have many siblings, your siblings also have many siblings. Many-to-many relationships must have a third table, referred to as an associate or linking table, because relational systems cannot accommodate the relationship directly.
0:0
A zero to zero optional relationship indicates that a person may occupy one parking space, but that a person is not necessary to have a space and a space does not need to have a person.
Building Business Views
The Business View is a collection of business categories that represents the "view" of your model, typically consumed by your end users. Each model can have one and only one business view. Business Views are made up of a logically (logically relevant to your organization or end-users) organized business categories and business columns.
Follow the guide to create the required Business Views:
Categories
A business category is just a named bucket for you to group and re-group your business columns in. They can mimic your business table names or be based on business terminology. Categories do not have metadata associated with them, have no tie back to any business table (although our Editor will give you the impression this relationship exists - don't be fooled), and have the simple purpose of allowing you to bucket the business columns in your model as intuitively as possibly for your data consumers.
Today, categories are a single level entity. We hope in the future to support nested categories. Building a business view consists of creating your categories, then moving your business columns from the business tables into the categories. You can move columns from different business tables into the same category, and even duplicate the same business column into two different categories.
The Editor Graph only represents the business tables portion of the business model, so we use the Tree Navigator and the Category Editor to create a business view.
Right-click (or ALT-click) on the Business View branch in the Navigator Tree.
Select the New Category... option from the popup menu.
In the Business Category Properties dialog, in the ID field, type: CT_ORDERS.
In the General section, for Name, type: Orders in the String column, and then click OK.
Manage Categories
Arranging Business Columns in Categories Now that you have a category, let's add some business columns to that category
In the Navigator Tree, make sure that the business tables branch, and the business view branch of your model are both expanded.
Under Business Tables, expand the table whose columns you want to move.
Click on a column under the Business Table and drag it to the category branch where you want it to reside.
Either double-click on the Business View branch in the Navigator Tree or choose the Manage Categories option from either the main menu or the main toolbar.
The Category Editor dialog displays.
In the Manage Categories dialog, in the Available Business Tables pane, expand ORDERS.
In the Available Business Tables pane, select the ORDERNUMBER column, then in the Business View Categories pane, click Orders, and then click the Add button.
Repeat the previous step to add the following columns from the ORDERS table to the Orders category.
6. Repeat the previous steps to add the following columns from the PRODUCTS table to the Products category:
Repeat the previous steps to add the following column from the ORDERFACT table to the Measures category:
Publish Model
The final step is to publish the metadata domain to the BI server for use as a data source in the reporting tools.
Before you publish, ensure the Pentaho Server is up and running ..!
Save the metadata domain by choose File > Save from the menu options or by clicking the Save icon.
In the Save Domain dialog, type Order Info and click OK.
From the menu, choose File > Publish To Server.
In the Publish To Server dialog, type or choose the following, and then click OK.
Server UserID
admin
Server Password
password
Domain Name
OrdersME
You may have to wait a short while as the API request is satisfied ..
Test - Interactive Report
Finally, we will use the published metadata domain as a data source to create a report using Interactive Reporting.
Log into the Pentaho User Console.
From the Home Perspective, click Create New > Interactive Report.
In the Select Data Source dialog, choose OrdersME and click OK.
Create the following report:
From the menu, choose File > Save.
Was this helpful?