OrdersME

Create an Orders Domain ..

Workshop - OrdersME

A metadata domain is the foundation of Pentaho's metadata layer. It's a comprehensive container that holds all your business objects—including database connections, data models, business tables, business views, security rules, and metadata concepts. By organizing these elements together, a metadata domain creates a consistent, business-friendly representation of your data that end users and applications can reliably work with.

In this workshop, you will build a complete metadata domain from the ground up and learn how to publish it to the Pentaho Server for organizational use.

By the end of this workshop, you will be able to:

• Establish a JDBC connection to a database (using the sampledata database as an example)

• Import physical tables and columns from your database into the metadata layer

• Create a business model that organizes your data in a way that makes business sense

• Define relationships between tables to enable meaningful data connections and queries

• Create business views that present data in a user-friendly format tailored to business needs

Once complete, you'll publish your enriched metadata domain as an .xmi file to the Pentaho Server, making it available across your organization.

Creating a Pentaho Metadata Domain

---

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. Start the Pentaho Server (not required if using Pentaho labs):

Windows (Powershell):

cd \
cd Pentaho/server/pentaho-server
./start-pentaho.bat

Linux:

cd
cd /opt/pentaho/server/pentaho-server
sudo ./start-pentaho.sh

Follow the guide below to create your OrdersME domain:

1. JDBC Connection

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.

JDBC Connection

---

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.

1. To define a new domain, select: File > New > Domain File from the main menu.

New Domain - OrdersME

1. Right-mouse click on Connections or: File > New > Connection

New Database Connection

1. Select the installation environment and enter the connection settings:

Windows: Enterprise Edition

Enterprise Edition

If you've downloaded & installed the on-prem Windows 30-day Enterprise Edition then configure the connection with the following settings:

Field	Parameter
Connection name	hsqldb:sampledata
Connection Type	Hypersonic
Access	JNDI
JNDI name	SampleData

JNDI (Java Naming and Directory Interface) is a Java API that allows applications to look up and access resources—like databases, message queues, or LDAP directories—from a central naming or directory service. Instead of hardcoding resource details into your application, you reference a JNDI name (like java:/comp/env/jdbc/MyDB), and the application server manages the actual connection details, making applications more portable and flexible.

JNDI Connection

Linux: Pentaho Lab

Pentaho Lab

If you're using the Pentaho Lab, enter the following settings:

Field	Parameter
Connection name	mysql:sampledata
Connection Type	MySQL
Access	Naitive (JDBC)
Host name	localhost
Database Name	sampledata
Port Number	3306
User Name	pentaho_admin
Password	password

Database Connection

---

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.

1. Copy the JDBC driver to:

~/Pentaho/design-tools/metadata-editor/lib/JDBC/

1. Once the driver JAR is in place, you must restart the server or client tool.

2. Import Tables

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.

Import Tables

---

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.

1. Hold down the Ctrl key and use the mouse to select the following:

Import tables

Table	Description
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:

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

Note: you can also get to the Physical Table Properties dialog by double-clicking the physical table node.

1. The Physical Table Properties dialog displays. In the dialog's Tree Navigator, select a column you wish to remove.

Delete Columns

1. Click the delete icon (the one with the red circle), to the right of the word Subject above the dialog's Tree Navigator.

2. Repeat with any remaining columns that you want to remove. Click OK when you are done.

PT_Tables

Each table / column has a Base set of Parent Concepts, which will be inherited - unless explicitly broken - by the defined Business Models.

3. Business Model

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

1. Business Model

Business Model

We're basically going to define an Product Order business model.

Business Model

---

Create Business Model:

1. Right-mouse click the Business Models node in the Tree Navigator.

2. Select New Business Model.

New Business Model

The Model Properties dialog displays.

At the top of the dialog, there is an ID text field, pre-populated with a value. We recommend you accept the pre-populated value as this value MUST be unique across all models that you define.

1. To name your new model, enter: OrdersME in the Name property text box on the right.

OrdersME - Business Model

1. From the Connection drop-down box, select: mysql:sampledata.

2. Click the OK button to close the dialog.

Your business model will show up in the Navigator Tree. All business models by default are created with a place to hold business tables, relationships and a business view. These are the next items you will want to define.

2. Business Tables

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.

Business Tables

---

Select Tables:

1. Right-click (or ALT-click) on the Business Tables branch in the Navigator Tree.

2. Select: New Business Table.

1. Select the physical table you want to associate with this new business table.

Add tables

Another method is to drag and drop the required tables onto the canvas.

• Drag & drop PT_PRODUCTS

• Drag & drop PT_ORDERS

• Drag & drop PT_DIM_TABLE

Business tables - OrdersME

3. Relationships

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.

Relationships

---

Follow the guide to create the required Relationships:

1. Define 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).

1. Right-click (or ALT-click) on the Relationships branch in the Navigator Tree.

2. Select the New Relationship... option from the popup menu. The ‘Relationship Properties’ dialog displays.

3. Select from the: From Table / Field list the business table that you would like to start the relationship from:

From Table / Field	To Table / Field
BT_DIM_TIME_DIM_TIME BC_DIM_TIME_TIME_ID	BT_ORDERFACT_ORDERFACT BC_ORDERFACT_TIME_ID

In the Relationship Properties dialog, in From Table / Field, choose BT_DIM_TIME_DIM_TIME as the source table and BC_DIM_TIME_TIME_ID as the source field.

1. Select the To Table / Field list the business table that you would like the relationship to go to:

In To Table / Field, choose BT_ORDERFACT_ORDERFACT as the destination table and BC_ORDERFACT_TIME_ID as the destination field.

Relationship Properties

You must also specify the business columns (from the adjacent lists) from each business table that identify this relationship. An alternative, if the business column names are similar, is to click the Guess Matching Fields button, and let the dialog attempt to determine the columns for you.

1. Next step, define the relationship from the Relationship drop down list. For Relationship, choose 1:N (1 to many), and then click OK.

2. If the relationship requires a complex join, select the complex join checkbox, and enter a formula in the text box provided.

3. Click OK

You should see a new relationship line drawn between the two tables on the Editor Graph, and the relationship represented in the tree.

DIM_TIME Relationship

2. Editor Graph

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.

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

2. Once your business tables are selected in the Graph, right-click (or CTRL-click) on the selection.

3. Click: Add Relationship.

Add Relationship

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

2. In To Table / Field, choose BT_PRODUCTS_PRODUCTS as the destination table and BC_PRODUCTS_PRODUCTCODE as the destination field.

Relation Properties

1. For Relationship, choose N:1 (many to 1) and then click OK.

Relationship - OrderFact (N) Product (1)

1. Repeat the workflow for Orders.

Quick recap: The ORdersME model is a Star schema. ORDERFACT is the FACT Table with a N:1 relationship with the Dimension tables: TIME, PRODUCTS & ORDERS.

Complex Joins

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.

Relationship Reference

The following table describes the possible table relationships:

Relationship	Description
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.

4. Views

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.

Business Views

---

Follow the guide to create the required Business Views:

1. Categories

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.

1. Right-click (or ALT-click) on the Business View branch in the Navigator Tree.

2. Select the New Category... option from the popup menu.

New Category

1. In the Business Category Properties dialog, in the ID field, type: CT_ORDERS.

2. In the General section, for Name, type: Orders in the String column, and then click OK.

CT_ORDERS Properties

2. Manage Categories

Manage Categories

Arranging Business Columns in Categories Now that you have a category, let's add some business columns to that category

1. In the Navigator Tree, make sure that the business tables branch, and the business view branch of your model are both expanded.

2. Under Business Tables, expand the table whose columns you want to move.

ORDERS Table & Columns

1. Click on a column under the Business Table and drag it to the category branch where you want it to reside.

---

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

2. The Category Editor dialog displays.

Manage Categories

1. In the Manage Categories dialog, in the Available Business Tables pane, expand ORDERS.

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

Add Columns

An item in the Available Business Tables and Business View Categories panes must be selected before clicking Add.

1. Repeat the previous step to add the following columns from the ORDERS table to the Orders category.

ORDERDATE

REQUIREDDATE

STATUS

COMMENTS

6. Repeat the previous steps to add the following columns from the PRODUCTS table to the Products category:

PRODUCTNAME

PRODUCTLINE

PRODUCTVENDOR

PRODUCTDESCRIPTION

1. Repeat the previous steps to add the following column from the ORDERFACT table to the Measures category:

QUANTITYINSTOCK – located in PRODUCTS

QUANTITYORDERED

TOTALPRICE

PRICEEACH

Populate Categories

4. Publish Model

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.

Publish OrdersME Domain

---

Before you publish, ensure the Pentaho Server is up and running ..!

1. Save the metadata domain by choose File > Save from the menu options or by clicking the Save icon.

2. In the Save Domain dialog, type Order Info and click OK.

3. From the menu, choose File > Publish To Server.

Publish to Pentaho Server

1. In the Publish To Server dialog, type or choose the following, and then click OK.

Field	Value
Web Publish URL	http://localhost:8080/pentaho/plugin/dataaccess/api/metadata/import
Server UserID	admin
Server Password	password
Domain Name	OrdersME

Publish - OrdersME

You may have to wait a short while as the API request is satisfied ..

5. Test

Test - Interactive Report

Finally, we will use the published metadata domain as a data source to create a report using Interactive Reporting.

Test - Interactive Report

---

1. Log into the Pentaho User Console.

2. From the Home Perspective, click Create New > Interactive Report.

3. In the Select Data Source dialog, choose OrdersME and click OK.

Data Source - OrdersME

1. Create the following report:

Test Interactive Report

1. From the menu, choose File > Save.