OrdersME Domain

Create an Orders Domain ..

The metadata domain is a Pentaho term that represents all the business objects created, stored and used in the metadata layer. A domain may consist of one or more connections, one or more models, security information, business tables, business views, categories, columns and concepts. You can create and save multiple metadata domains using the Metadata Editor.

A metadata domain is accessed by the BA Server by publishing or exporting the domain to an .xmi file, and placing the file in the Pentaho solutions repository.

In this workshop, you will…

• Use JDBC to connect to the sampledata database

• Import Physical Tables and Columns

• Create a Business Model

• Define the Relationships between the Tables

• Define Business View

Creating a Pentaho Metadata Domain

JDBC Connection

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.

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, and you will see your database in the Connection Type list.

New Domain

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

New Database Connection

1. In the Database Connection dialog, type or choose:

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/libext/JDBC/

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

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

Import tables

If you would like to remove extraneous columns from your physical tables, from here it's easy to do:

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

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

PT_Tables

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

Business Model

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

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.

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.

Business Tables

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.

x

x

The following table describes the

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.