OrderStarCustomer

Manipulating Metadata Properties ..

Workshop - OrdersStarCustomer

Pentaho Metadata Editor Properties are the building blocks that give your metadata domain structure, meaning, and functionality. Properties define the characteristics of your business objects—whether they're physical tables, business columns, or concepts—and control how SQL is generated and how data is presented to end users. Through required properties that safeguard system integrity and custom properties that enhance your model, you can create a rich, well-documented data environment that ensures consistency across your organization while preventing critical errors.

In this workshop, you will master the fundamentals of properties and learn how to apply them strategically across your metadata domain.

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

• Understand the distinction between required properties (which maintain system integrity) and custom properties (which enhance your model)

• Create and configure properties for physical and business objects in your metadata model

• Use formatting, security metadata, and custom properties to build a clearer, more efficient data model

Once complete, you'll have a fully enriched metadata domain with well-defined properties, ready to deliver a consistent, business-friendly representation of your data across your organization.

Metadata Properties

---

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 OrderStarCustomer domain:

1. Connection & Tables

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.

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

Field	Parameter
Connection name	hsqldb:sampledata
Connection Type	Hypersonic
Access	Naitive (JDBC)
Host name	localhost
Database Name	sampledata
Port Number	9001
User Name	pentaho_admin
Password	password

1. Click Test.

2. Click OK to dismiss the Database Connection Test dialog, and then click OK to close the Database Connection dialog.

3. Leave the Metadata Editor and the Import Tables dialog open.

---

Import Tables

The next step is to import table metadata from your data source into the physical layer and then add the table metadata to the abstract business layer.

1. If the Import Tables dialog is not open, right-click hsqldb:sampledata and choose Import Tables.

2. In the Import Tables dialog, press and hold the Ctrl key and use the mouse to select the following tables:

PRODUCTS

ORDERFACT

DIM_TIME

CUSTOMER_W_TER

The ORDERFACT table is the FACT table for the star schema.

1. Click OK to import the table metadata.

2. Expand Connections > hsqldb:sampledata and verify each of the tables appears in the physical layer.

2. Business Model

Business Model

The model is also the place where you define the relationships between your business tables and how you organize the business view.

1. Right-click Business Models and choose New Business Model.

2. In the Business Model Properties dialog, in the General section, for Name, replace the existing value in the String column by typing: OrderStarCustomer.

The model’s unique ID, BV_MODEL_1, appears at the top of the Business Model Properties dialog.

Do not change this value. Its used internally to reference the correct model when generating SQL statements.

Optional: Type a description in the String column in the Description section such as: Business model for Steel Wheels star schema.

1. In the upper right corner of the dialog, for Connection, choose: hsqldb:sampledata.

2. Click OK to close the ‘Business Model Properties’ dialog.

Business Model - OrderStarCustomer

---

Business Tables

Tables that represent the model. These expose the inherited BASE metatadata Properties that can be edited / enriched to ensure consistency for your business users.

1. Expand Business Models > OrderStarCustomer.

2. Drag the PRODUCTS table from Connections > hsqldb:sampledata to Business Models > Business Tables to add the table (and columns) to the abstract business layer.

The tables cannot be dragged into the ‘Business Tables’ view as a group. They must be added individually. When you receive the Business Model Properties dialog, note the table ‘ID,’ BT_PRODUCTS_PRODUCTS, and the ‘Physical Table’ name, PT_PRODUCTS.

1. Click OK to dismiss the properties dialog. Using the Business Model Properties dialog, you can (optionally) remove columns from the tables before adding them to the business model.

2. Repeat the previous steps to add:

ORDERFACT

DIM_TIME

CUSTOMERS_W_TER

1. Save.

3. Relationships

Relationships - OrdersStarCustomer

Follow the guide to define the Relationships:

1. ORDERFACT - CUSTOMER_W_TER

1. Expand Business Models > OrderStarCustomer (if necessary), right-click Relationships and choose New Relationship.

2. In the Relationship Properties dialog, in From Table / Field, choose BT_ORDERFACT_ORDERFACT as the source table.

3. In the field to the right of From Table / Field, choose source key value BC_ORDERFACT_CUSTOMERNUMBER.

This creates the relationship between the ORDERFACT and CUSTOMER_W_TER tables. The editor defines logical relationships based on a key value. A complex join (with multiple keys) can be specified, but a well-structured data warehouse should rarely use complex joins.

1. In To Table / Field, choose BT_CUSTOMER_W_TER_ CUSTOMER_W_TER as the destination table.

2. In the field to the right of To Table / Field, choose destination key value BC_CUSTOMER_W_TER_CUSTOMERNUMBER. You may also use Guess Matching Fields to try to automatically determine the relevant key values.

3. For Relationship, choose N:1 (many to 1).

4. For Join type, choose Inner. An inner join produces a result set when there is at least one row in each table that matches the join condition.

5. Accept the remaining default options and click OK. A visual representation of the relationship appears in the Graphical View.

2. ORDERFACT - PRODUCTS

1. Right-click Relationships and choose New Relationship.

2. In the Relationship Properties dialog, in From Table / Field, choose BT_ORDERFACT_ORDERFACT as the source table.

3. In the field to the right of From Table / Field, choose source key value BC_ORDERFACT_PRODUCTCODE. This creates the relationship between ORDERFACT and PRODUCTS.

4. In To Table / Field, choose BT_PRODUCTS_PRODUCTS as the destination table.

5. In the field to the right of To Table / Field, choose destination key value BC_PRODUCTS_PRODUCTCODE.

6. For Relationship, choose N:1 (many to 1).

7. For Join type, choose Inner.

8. Accept the remaining default options and click OK. A visual representation of the relationships will appear in the Graphical View.

3. ORDERFACT - TIME

1. Right-click Relationships, and choose: New Relationship.

2. In the Relationship Properties dialog, in From Table / Field, choose BT_ORDERFACT_ORDERFACT as the source table.

3. In the field to the right of From Table / Field, choose source key value BC_ORDERFACT_TIME_ID.

4. In To Table / Field, choose BT_DIM_TIME_DIM_TIME as the destination table. This creates the relationship between ORDERFACT and DIM_TIME.

5. In the field to the right of To Table / Field, choose destination key value BC_DIM_TIME_TIME_ID.

6. For Relationship, choose N:1 (many to 1).

7. For Join type, choose Inner.

8. Accept the remaining default options and click OK. A visual representation of the relationships will appear in the Graphical View.

9. Save the OrdersStarCustomer domain.

4. Properties

Properties

After adding the tables to the Business Tables view, the next step is to edit the columns to introduce your organizations Terms friendlier to the business user or business analyst using the data for reporting.

1. PRODUCTS

1. Right-click PRODUCTS and choose: Edit.

2. In the Business Table Properties dialog, in the Subject pane, select: PRODUCTCODE.

3. In the Available pane, click: General > Name.

4. In the Settings section, click: the Override button.

5. In the String column, type: Product Code.

6. In the Subject pane, select: PRODUCTNAME.

7. Repeat the previous steps to change the Name string to: Product Name.

8. Repeat the previous steps to change the names of the remaining columns to the following:

Product Line

Product Scale

Product Vendor

Product Description

Quantity In Stock

Buy Price - There is no need to edit the MSRP name.

1. Click OK and save your work.

2. ORDERFACT

1. Expand Business Models > OrderStarCustomer > Business Tables.

2. In the Business Tables view, right-click ORDERFACT and choose: Edit.

3. In the Settings section, click: the Override button.

4. Repeat the previous steps to change these column names to the following:

Order Number

Product Code

Quantity Ordered

Order Line Number

Total Price

Order Date

Required Date

Shipped Date

Status

Comments

Customer Number

1. Click OK and save your work.

3. DIM_TIME

1. In the Business Tables view, right-click DIM_TIME and choose: Edit.

2. In the Subject pane, select: TIME ID.

3. In the Available pane, click: General > Name, click Override, and change the String value to: Time ID.

4. Repeat the previous steps to change the remaining column names to the following:

Year ID

Month Name

Month Description

Qtr Name

Qtr Description

1. Click OK and save your work.

4. CUSTOMER_W_TER

1. In the Business Tables view, right-click CUSTOMER_W_TER and choose: Edit.

2. In the Business Table Properties dialog, in the Subject pane, click: CUSTOMERNUMBER.

3. In the Available pane, click General > Name, click: Override icon, and change the String column value to: Customer Number.

4. Repeat the previous steps to change the remaining columns to the following:

Customer Name

Contact Last Name

Contact First Name

Phone

Addressline1

Addressline2

City

State

Postal Code

Country

Employee Number

Credit Limit

Territory

Territory Colour

1. Click OK and save your work.

5. Calculated Values

Calculated Values & Formulas

Formulas serve four critical functions in Pentaho metadata: they define constraints in Metadata Queries to filter data subsets, enable physical table columns to combine multiple database columns or perform complex aggregate calculations, support sophisticated multi-key joins and logic in business model relationships, and enforce row-level security.

Pentaho uses JFreeReport's libFormula package to interpret formulas in OpenFormula syntax, then automatically converts them into database-native SQL, providing flexibility and power across your metadata layer.

1. Aggregation

Aggregation

Setting predefined values.

1. In the Subject pane, select: PRICEEACH from ORDERSFACT.

2. In the Available pane, click General > Name, click: Override, and change the String value to: Price Sold

3. In the Available pane, in the Model Descriptors section, click: Default Aggregation.

4. Click the Override icon.

After clicking Override, the Settings pane may jump to the top of the page.

Click Default Aggregation again or scroll down to the section in the Settings pane.

1. For Aggregation Type, choose: Sum.

2. In the Available pane, click: Optional Aggregations.

3. Select all the following options in the Aggregation List using the Ctrl key:

Average

Minimum

Maximum

1. Click OK and save your work.

2. Data Type

Data Type

Enables you to apply masks, defining and formatting the object's data type.

1. In the Subject pane, select: Order Date.

2. In the Available pane, click: Model Descriptors > Data Type,

3. Click: Override, and change the Data Type value to: Date.

It is important to set the correct data type in this case for Java date formatting to be applied. We will only change the Data Type for the Order Date now. We will apply formatting later in this workshop.

1. Click OK and save your work.

3. Formulas

Formulas

After configuring the display names for your table columns, the next step is to add any formulas you wish to have calculated at the metadata level. In this exercise, you use formulas to define physical table columns that combine values from other columns.

1. Expand Connections > hsqldb:sampledata.

2. Right-click: ORDERFACT > Edit.

3. In the Physical Table Properties dialog, above the Subject pane, click: Add New Column.

4. In the New Column dialog, type: PC_TOTAL and click OK.

5. In the Subject pane, click: Total, and

6. In the Available pane, click: Default Aggregation.

7. For Aggregation Type, choose: Sum.

8. Click: Optional Aggregations.

9. In the Aggregation List, use the Ctrl key to select the following options:

Average

Minimum

Maximum

1. In the Available pane, click: Model Descriptors > Data Type.

2. From the Data Type drop-down list, select: Numeric.

3. In the Available pane, click: Calculation > Formula.

4. In the Formula section, in the Value field, type:

[QUANTITYORDERED]*[PRICEEACH]

1. Check Is the formula exact?

2. Click OK and save your work.

4. Total

Total

You can also use Formulas to calculate various Totals.

1. Expand Business Models > OrdersStarCustomer > Business Tables (if necessary).

2. Right-click ORDERFACT and choose: Edit.

3. In the Business Table Properties dialog, above the Subject pane, click: Add New Column button.

4. In the Add New Column dialog, click: Total and click: OK.

5. Click OK and save your work.

5. Contact

Contact

An example of how to concatinate Business Columns in MQL - Metadata Query Language.

1. Expand Connections > hsqldb:sampledata.

2. Right-click CUSTOMER_W_TER and choose Edit.

3. In the Physical Table Properties dialog, above the Subject pane, click the Add New Column.

4. In the New Column dialog, type PC_CONTACT and click OK.

5. In the Subject pane, select Contact and in the Available pane, click General > Name.

6. In the String column, type Contact Name.

7. Optional: In the Description section, in the String column type: Full Name of Contact Person.

8. In the Available pane, click Calculation > Formula.

9. In the Formula section, in the Value field, type:

[CONTACTLASTNAME]+","+[CONTACTFIRSTNAME]

1. Check Is the formula exact?

2. Expand Business Models > OrderStarMetadata > Business Tables (if necessary).

3. Right-click CUSTOMER_W_TER and choose Edit.

4. In the Business Table Properties dialog, above the Subject pane, click the Add New Column button.

5. In the Add New Column dialog, click Contact Name and click OK.

6. Click OK and save your work.

5. Business Views

Business Views

Business Views - OrdersStarCustomer

Follow the guide to apply the Business Views:

1. CT_ORDERS

Orders Category

1. Expand Business Models > OrdersStarCustomer (if necessary), right-click Business View and choose New Category.

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

CT_ORDERS

1. In the General section, for Name, type Orders in the String column.

Optional: In the Description section, for the en_US locale, type the following description in the String column: This category contains information about orders including order number, order date, required date, shipping status, etc.

1. Click OK (later .. you will configure the security).

2. Expand Business Models > OrderStarCustomer > Business View (if necessary).

3. Right-click Business View > Orders and choose Manage Categories.

4. In the Manage Categories dialog, in the Available Business Tables pane, expand ORDERFACT.

5. In the Available Business Tables pane, select the Order Number column, in the Business View Categories pane, select Orders, and click the Add button.

Items in both Available Business Tables and Business View Categories must be selected before clicking Add.

1. Repeat the previous steps to add the following additional columns from the ORDERFACT table:

Quantity Ordered Price Sold Total Price Order Date Required Date Shipped Date Status - Junk Comments - Junk Total

2. CT_CUSTOMERS

CT_CUSTOMERS

1. To the right of the Business View Categories pane, click the Add New Category button.

2. In the Business Category Properties dialog, in the ID field, type: CT_CUSTOMERS.

3. In the General section, for Name, in the String column, type Customers.

Optional: In the Description section, type a description in the String column such as: This category contains information about customers.

1. Click OK.

2. In the Manage Categories dialog, in the Available Business Tables pane, expand CUSTOMER_W_TER.

3. In the Available Business Tables pane, select the Customer Number column, in the Business View Categories pane, select Customers, and click the Add button.

4. Repeat the previous steps to add these additional columns to the Customers category:

Customer Name

Phone

Addressline1 & Addressline2

City

State

Postal Code

Country

Employee Number

Credit Limit

Territory

Contact Name

3. CT_PRODUCTS

CT_PRODUCTS

1. In the Manage Categories dialog, click the Add New Category button.

2. In the Business Category Properties dialog, in the ID field, type: CT_PRODUCTS.

3. In the Settings section, for Name, in the String column, type: Products.

Optional: In the Description section, in the String column, type a description such as: This category contains information about products.

1. Click OK.

2. When you are returned to the Manage Categories dialog, in the Available Business Tables pane, expand Products and click the Product Code column.

3. In the Business View Categories pane, click Products and click the Add button.

4. Repeat the previous steps to add these additional columns to the Products category:

Product Name

Product Line

Product Scale

Product Vendor

Product Description

Buy Price

MSRP

4. CT_TIME

CT_TIME

1. In the Manage Categories dialog, click the Add New Category button.

2. In the Business Category Properties dialog, in the ID field, type: CT_TIME.

3. In the Settings section, for Name, for String, type: Time.

Optional: In the Description section, in the String column, type a description such as: This category contains details about order dates, including quarters, years, and months.

1. Click OK.

2. When you are returned to the Manage Categories dialog, in the Available Business Tables pane, expand DIM_TIME and choose the Time ID column.

3. In the Business View Categories pane, select Time and click the Add button.

4. Repeat the previous steps to add the following additional columns to the Time category:

Year ID

Month Name

Month Description

Qtr Name

Qtr Description

1. Close the Manage Categories dialog. You will publish the metadata domain to the server after you complete the next exercise on metadata concepts.

2. Save your work. Leave Metadata Editor open.