OrderStarCustomer
Manipulating Metadata Properties ..
Workshop - OrdersStarCustomer
I'll fetch the content from that URL to see what needs to be rewritten.Workshop - OrderStarCustomer
Building effective business intelligence solutions begins with creating a semantic layer that shields business users from technical database complexity while ensuring consistent, accurate data access across the organization. In this comprehensive workshop, you'll master the Pentaho Metadata Editor by constructing a complete metadata domain from scratch, transforming a raw star schema database into a business-friendly semantic model that enables self-service reporting. You'll learn how to bridge the gap between technical database structures and business terminology, creating a metadata layer that translates technical column names into meaningful business concepts while automatically generating proper SQL joins and aggregations.
In this hands-on workshop, you'll experience the complete metadata modeling lifecycle, from establishing database connections and importing physical tables through creating business models with relationships and culminating in user-facing business views organized into logical categories. You'll master the essential techniques for configuring properties that control data types, aggregations, and display formatting, while learning to create calculated fields using formulas that extend your data model beyond what exists in the physical database. You'll discover how to build a three-layer architecture consisting of physical tables, business tables with enriched properties, and business views that present intuitive category-based navigation to report designers. You'll also develop the critical understanding of how metadata properties drive SQL generation, ensure data consistency, and create the foundation for enterprise-wide self-service analytics.
What You'll Accomplish:
Start Pentaho Metadata Editor and create a new domain file for the OrderStarCustomer model
Establish a JDBC connection to the hsqldb:sampledata Hypersonic database with proper authentication
Import four physical tables (PRODUCTS, ORDERFACT, CUSTOMER_W_TER, DIM_TIME) that form the star schema foundation
Create the OrderStarCustomer business model with a unique system identifier and database connection reference
Add business tables by dragging physical tables into the business layer to expose inherited metadata properties
Define three N:1 (many-to-one) relationships with Inner joins to establish the star schema structure connecting ORDERFACT to CUSTOMER_W_TER via CUSTOMERNUMBER, to PRODUCTS via PRODUCTCODE, and to DIM_TIME via TIME_ID
Override inherited Name properties across all business table columns to transform technical names into business-friendly labels (PRODUCTCODE becomes Product Code, CUSTOMERNUMBER becomes Customer Number)
Configure Model Descriptor properties including Data Type (setting Order Date to Date type) and Default Aggregation (setting Price Sold to Sum with optional aggregations for Average, Minimum, Maximum)
Create calculated columns using formulas including PC_TOTAL (QUANTITYORDERED multiplied by PRICEEACH) in the physical layer and BC_TOTAL in the business layer
Build a concatenated Contact Name field using the formula CONTACTLASTNAME comma CONTACTFIRSTNAME to combine customer contact information
Organize business columns into four logical Business View categories (CT_ORDERS for Orders, CT_CUSTOMERS for Customers, CT_PRODUCTS for Products, CT_TIME for Time)
Populate the Orders category with nine columns including Order Number, Order Date, Status, Price Sold, Quantity Ordered, and the calculated Total field
Populate the Customers category with thirteen columns including Customer Number, Customer Name, Contact Name, complete address details, Territory, Employee Number, Credit Limit, and Phone
Populate the Products category with nine columns including Product Code, Product Name, Product Line, Product Scale, Product Vendor, Product Description, Quantity In Stock, Buy Price, and MSRP
Populate the Time category with seven temporal attributes including Time ID, Year ID, Month Name, Month Description, Quarter Name, and Quarter Description
Save the completed OrderStarCustomer domain file ready for publishing to the Pentaho Server
By the end of this workshop, you'll have created a production-ready metadata domain that demonstrates best practices in semantic layer design. You'll understand how the three-layer architecture (physical tables, business model, business views) separates technical implementation from business presentation, how properties control SQL generation and data formatting, and how formulas extend the data model with calculated fields. You'll have mastered the relationship modeling that enables automatic join generation, the property configuration that ensures consistent aggregations and data types, and the business view organization that creates intuitive, category-based navigation for report designers. These skills form the foundation of enterprise metadata modeling, enabling you to create semantic layers that democratize data access, ensure consistency across reports, and shield business users from database complexity while maintaining data integrity and performance.
Prerequisites: Pentaho Metadata Editor installed, Pentaho Server running with hsqldb:sampledata database accessible Estimated Time: 45 minutes
Start Metadata Editor:
Start the Pentaho Server (not required if using Pentaho Labs):
Follow the guide below to create your OrderStarCustomer domain:
Define a Domain & JDBC Connection
In the OrderStarCustomer workshop, the foundational step involves creating a new domain file and establishing a JDBC database connection that links the Pentaho Metadata Editor to the physical data source. The connection named "hsqldb:sampledata" is configured as a Hypersonic database type using Native JDBC access, pointing to localhost on port 9001 with the database name "sampledata" and credentials (pentaho_admin/password).
After successfully testing the connection, four physical tables are imported from the database into the metadata domain's physical layer: PRODUCTS (product dimension), ORDERFACT (fact table containing transactional order data), CUSTOMER_W_TER (customer dimension with territory information), and DIM_TIME (time dimension).
These imported tables form the physical foundation of the metadata model and can be verified in the Connections tree under hsqldb:sampledata, representing the raw database structures that will later be transformed into business-friendly abstractions through the Business Model and Business View layers.
To define a new domain, select: File > New > Domain File from the main menu.
Right-mouse click on Connections or: File > New > Connection:
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
Click Test.
Click OK to dismiss the Database Connection Test dialog, and then click OK to close the Database Connection dialog.
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.
If the Import Tables dialog is not open, right-click hsqldb:sampledata and choose Import Tables.
In the Import Tables dialog, press and hold the Ctrl key and use the mouse to select the following tables:
Click OK to import the table metadata.
Expand Connections > hsqldb:sampledata and verify each of the tables appears in the physical layer.
Business Model
In the OrderStarCustomer workshop, the Business Model serves as the central container that connects physical database tables to business-friendly abstractions and defines the relationships that enable proper SQL generation. The model is created with the name "OrderStarCustomer" and assigned a system-generated unique ID (BV_MODEL_1) that Pentaho uses internally to reference the correct model when generating SQL statements.
It is linked to the hsqldb:sampledata database connection and contains a description identifying it as a "Business model for Steel Wheels star schema." Four business tables (PRODUCTS, ORDERFACT, CUSTOMER_W_TER, and DIM_TIME) are added by dragging them from the physical layer, exposing their inherited metadata properties for enrichment. Three N:1 (many-to-one) relationships are then defined with Inner joins to establish the star schema structure: ORDERFACT connects to CUSTOMER_W_TER via CUSTOMERNUMBER, to PRODUCTS via PRODUCTCODE, and to DIM_TIME via TIME_ID.
These relationships appear visually in the Graphical View and enable the metadata layer to automatically generate proper table joins when users create reports.
Right-click Business Models and choose New Business Model.
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.
In the upper right corner of the dialog, for Connection, choose: hsqldb:sampledata.
Click OK to close the ‘Business Model Properties’ dialog.
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.
Expand Business Models > OrderStarCustomer.
Drag the PRODUCTS table from Connections > hsqldb:sampledata to Business Models > Business Tables to add the table (and columns) to the abstract business layer.
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.
Repeat the previous steps to add:
Save.
Relationships
Database table relationships define how data in one table connects to data in another table, establishing logical associations that reflect real-world business rules and enable efficient data retrieval through joins. Relationships are implemented using primary keys (unique identifiers in a table) and foreign keys (columns that reference primary keys in related tables), ensuring referential integrity and eliminating data redundancy.
The three fundamental relationship types are one-to-one (1:1), where each record in one table corresponds to exactly one record in another table; one-to-many (1:N or N:1), the most common type where a single record in a parent table can relate to multiple records in a child table (such as one customer having many orders); and many-to-many (N:M), where multiple records in one table can relate to multiple records in another table, typically implemented through an intermediary junction table.
These relationships form the backbone of relational database design, enabling complex queries across multiple tables while maintaining data consistency, reducing storage redundancy, and supporting business logic through enforced constraints that prevent orphaned records and maintain data quality across the entire database schema.
Follow the guide to define the Relationships:
Expand Business Models > OrderStarCustomer (if necessary), right-click Relationships and choose New Relationship.
In the Relationship Properties dialog, in From Table / Field, choose BT_ORDERFACT_ORDERFACT as the source table.
In the field to the right of From Table / Field, choose source key value BC_ORDERFACT_CUSTOMERNUMBER.
In To Table / Field, choose BT_CUSTOMER_W_TER_ CUSTOMER_W_TER as the destination table.
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.
For Relationship, choose N:1 (many to 1).
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.
Accept the remaining default options and click OK. A visual representation of the relationship appears in the Graphical View.
Right-click Relationships and choose New Relationship.
In the Relationship Properties dialog, in From Table / Field, choose BT_ORDERFACT_ORDERFACT as the source table.
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.
In To Table / Field, choose BT_PRODUCTS_PRODUCTS as the destination table.
In the field to the right of To Table / Field, choose destination key value BC_PRODUCTS_PRODUCTCODE.
For Relationship, choose N:1 (many to 1).
For Join type, choose Inner.
Accept the remaining default options and click OK. A visual representation of the relationships will appear in the Graphical View.
Right-click Relationships, and choose: New Relationship.
In the Relationship Properties dialog, in From Table / Field, choose BT_ORDERFACT_ORDERFACT as the source table.
In the field to the right of From Table / Field, choose source key value BC_ORDERFACT_TIME_ID.
In To Table / Field, choose BT_DIM_TIME_DIM_TIME as the destination table. This creates the relationship between ORDERFACT and DIM_TIME.
In the field to the right of To Table / Field, choose destination key value BC_DIM_TIME_TIME_ID.
For Relationship, choose N:1 (many to 1).
For Join type, choose Inner.
Accept the remaining default options and click OK. A visual representation of the relationships will appear in the Graphical View.
Save the OrdersStarCustomer domain.
Properties
In the OrderStarCustomer workshop, Properties are the configurable attributes that transform technical database column names into business-friendly terms and control how data is processed and displayed to end users. After importing physical tables into the Business Model, developers systematically override the inherited Name properties for each column across all business tables (PRODUCTS, ORDERFACT, DIM_TIME, and CUSTOMER_W_TER).
This converts technical names like PRODUCTCODE and CUSTOMERNUMBER into readable labels like "Product Code" and "Customer Number" that business analysts can easily understand. Model Descriptor properties such as Data Type and Default Aggregation are also configured to control SQL generation behavior. For example, Order Date is set to Date type for proper formatting, and Price Sold is configured with Sum as the default aggregation plus optional aggregation methods.
These property configurations ensure that when users build reports, the metadata layer automatically applies correct data types, aggregation functions, and business terminology without requiring users to understand the underlying database structure or write SQL code.
Right-click PRODUCTS and choose: Edit.
In the Business Table Properties dialog, in the Subject pane, select: PRODUCTCODE.
In the Available pane, click: General > Name.
In the Settings section, click: the Override button.
In the String column, type: Product Code.
In the Subject pane, select: PRODUCTNAME.
Repeat the previous steps to change the Name string to: Product Name.
Repeat the previous steps to change the names of the remaining columns to the following:
Click OK and save your work.
Expand Business Models > OrderStarCustomer > Business Tables.
In the Business Tables view, right-click ORDERFACT and choose: Edit.
In the Settings section, click: the Override button.
Repeat the previous steps to change these column names to the following:
Click OK and save your work.
In the Business Tables view, right-click DIM_TIME and choose: Edit.
In the Subject pane, select: TIME ID.
In the Available pane, click: General > Name, click Override, and change the String value to: Time ID.
Repeat the previous steps to change the remaining column names to the following:
Click OK and save your work.
In the Business Tables view, right-click CUSTOMER_W_TER and choose: Edit.
In the Business Table Properties dialog, in the Subject pane, click: CUSTOMERNUMBER.
In the Available pane, click General > Name, click: Override icon, and change the String column value to: Customer Number.
Repeat the previous steps to change the remaining columns to the following:
Click OK and save your work.
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.
In the Subject pane, select: PRICEEACH from ORDERSFACT.
In the Available pane, click General > Name, click: Override, and change the String value to: Price Sold
In the Available pane, in the Model Descriptors section, click: Default Aggregation.
Click the Override icon.
For Aggregation Type, choose: Sum.
In the Available pane, click: Optional Aggregations.
Select all the following options in the Aggregation List using the Ctrl key:
Click OK and save your work.
In the Subject pane, select: Order Date.
In the Available pane, click: Model Descriptors > Data Type,
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.
Click OK and save your work.
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.
Expand Connections > hsqldb:sampledata.
Right-click: ORDERFACT > Edit.
In the Physical Table Properties dialog, above the Subject pane, click: Add New Column.
In the New Column dialog, type: PC_TOTAL and click OK.
In the Subject pane, click: Total, and
In the Available pane, click: Default Aggregation.
For Aggregation Type, choose: Sum.
Click: Optional Aggregations.
In the Aggregation List, use the Ctrl key to select the following options:
In the Available pane, click: Model Descriptors > Data Type.
From the Data Type drop-down list, select: Numeric.
In the Available pane, click: Calculation > Formula.
In the Formula section, in the Value field, type:
Check Is the formula exact?
Click OK and save your work.
Expand Business Models > OrdersStarCustomer > Business Tables (if necessary).
Right-click ORDERFACT and choose: Edit.
In the Business Table Properties dialog, above the Subject pane, click: Add New Column button.
In the Add New Column dialog, click: Total and click: OK.
Click OK and save your work.
Expand Connections > hsqldb:sampledata.
Right-click CUSTOMER_W_TER and choose Edit.
In the Physical Table Properties dialog, above the Subject pane, click the Add New Column.
In the New Column dialog, type PC_CONTACT and click OK.
In the Subject pane, select Contact and in the Available pane, click General > Name.
In the String column, type Contact Name.
Optional: In the Description section, in the String column type: Full Name of Contact Person.
In the Available pane, click Calculation > Formula.
In the Formula section, in the Value field, type:
Check Is the formula exact?
Expand Business Models > OrderStarMetadata > Business Tables (if necessary).
Right-click CUSTOMER_W_TER and choose Edit.
In the Business Table Properties dialog, above the Subject pane, click the Add New Column button.
In the Add New Column dialog, click Contact Name and click OK.
Click OK and save your work.
Business Views
In the OrderStarCustomer workshop, Business Views are created as the final presentation layer by organizing business table columns into four logical categories—CT_ORDERS (Orders), CT_CUSTOMERS (Customers), CT_PRODUCTS (Products), and CT_TIME (Time)—that business users can easily understand and navigate.
Using the "Manage Categories" dialog, developers selectively add relevant columns from the underlying business tables (ORDERFACT, CUSTOMER_W_TER, PRODUCTS, and DIM_TIME) into their appropriate categories, such as placing Order Number, Order Date, Status, and the calculated Total field into the Orders category.
This categorization transforms the technical star schema structure into intuitive, business-friendly groupings that enable analysts to build reports by simply selecting fields from familiar categories without understanding the underlying table relationships or SQL joins.
Follow the guide to apply the Business Views:
Orders Category
The CT_ORDERS category is a business view within the OrderStarCustomer metadata model that organizes order-related data from the ORDERFACT table into a business-friendly structure.
This category, named "Orders" for end users, consolidates nine key order management columns including Order Number, Order Date, Required Date, Shipped Date, Status, and Comments for tracking order fulfillment, along with transactional metrics such as Price Sold, Quantity Ordered, and Total (a calculated field multiplying quantity by price).
Expand Business Models > OrdersStarCustomer (if necessary), right-click Business View and choose New Category.
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.
Click OK (later .. you will configure the security).
Expand Business Models > OrderStarCustomer > Business View (if necessary).
Right-click Business View > Orders and choose Manage Categories.
In the Manage Categories dialog, in the Available Business Tables pane, expand ORDERFACT.
In the Available Business Tables pane, select the Order Number column, in the Business View Categories pane, select Orders, and click the Add button.
Repeat the previous steps to add the following additional columns from the ORDERFACT table:
CT_CUSTOMERS
The CT_CUSTOMERS category is a business view within the OrderStarCustomer metadata model that consolidates customer-related information from the CUSTOMER_W_TER table into a user-friendly structure for business reporting and analysis.
This category, labeled "Customers" for end users, contains thirteen comprehensive customer attributes including Customer Number as the key identifier, Customer Name, and Contact Name (a calculated formula field that concatenates contact first and last names with a comma separator). The category also includes complete address details across multiple fields (Address Line 1, Address Line 2, City, State, Postal Code, Country), geographic sales Territory assignment, Sales Rep Employee Number for account ownership tracking, Credit Limit for financial management, and Phone for communication.
To the right of the Business View Categories pane, click the Add New Category button.
In the Business Category Properties dialog, in the ID field, type: CT_CUSTOMERS.
In the General section, for Name, in the String column, type Customers.
Click OK.
In the Manage Categories dialog, in the Available Business Tables pane, expand CUSTOMER_W_TER.
In the Available Business Tables pane, select the Customer Number column, in the Business View Categories pane, select Customers, and click the Add button.
Repeat the previous steps to add these additional columns to the Customers category:
CT_PRODUCTS
The CT_PRODUCTS category is a business view within the OrderStarCustomer metadata model that organizes product dimension data from the PRODUCTS table into a comprehensive, business-friendly structure for product analysis and inventory management.
This category, named "Products" for end users, encompasses nine key product attributes including Product Code as the unique identifier, Product Name for item identification, and descriptive classification fields such as Product Line (for product family groupings), Product Scale (indicating model or size specifications), and Product Vendor (identifying suppliers or manufacturers).
The category also includes Product Description for detailed item information, inventory management metrics with Quantity In Stock for availability tracking, and pricing data through Buy Price (wholesale or cost basis) and MSRP (Manufacturer's Suggested Retail Price) for margin analysis and pricing strategy. By consolidating these product dimension attributes into a single category, the business view enables analysts to perform product performance analysis, inventory optimization, vendor evaluation, pricing strategy assessment, and product portfolio management without requiring technical knowledge of the underlying database schema.
In the Manage Categories dialog, click the Add New Category button.
In the Business Category Properties dialog, in the ID field, type: CT_PRODUCTS.
In the Settings section, for Name, in the String column, type: Products.
Click OK.
When you are returned to the Manage Categories dialog, in the Available Business Tables pane, expand Products and click the Product Code column.
In the Business View Categories pane, click Products and click the Add button.
Repeat the previous steps to add these additional columns to the Products category:
CT_TIME
The CT_TIME category is a business view within the OrderStarCustomer metadata model that provides comprehensive temporal analysis capabilities by organizing time dimension attributes from the DIM_TIME table into a hierarchical, business-friendly structure for date-based reporting and trend analysis.
This category, named "Time" for end users, includes seven essential time attributes starting with Time ID as the unique temporal identifier, followed by The Full Date for complete date reference, and granular time components including Day of Week (enabling weekday pattern analysis), Day of Month (supporting daily cycle analysis), Month (facilitating monthly comparisons), Year (enabling annual trending), and Quarter (supporting quarterly business cycle analysis).
By structuring these temporal attributes into a dedicated category, the business view enables analysts to perform sophisticated time-series analysis, seasonal trend identification, period-over-period comparisons, fiscal reporting, and date-driven business intelligence without requiring knowledge of the underlying time dimension table structure or complex date manipulation SQL, supporting common business scenarios such as year-over-year growth analysis, quarterly performance reviews, and day-of-week sales patterns.
In the Manage Categories dialog, click the Add New Category button.
In the Business Category Properties dialog, in the ID field, type: CT_TIME.
In the Settings section, for Name, for String, type: Time.
Click OK.
When you are returned to the Manage Categories dialog, in the Available Business Tables pane, expand DIM_TIME and choose the Time ID column.
In the Business View Categories pane, select Time and click the Add button.
Repeat the previous steps to add the following additional columns to the Time category:
Close the Manage Categories dialog. You will publish the metadata domain to the server after you complete the next exercise on metadata concepts.
Save your work. Leave Metadata Editor open.
Last updated
Was this helpful?