Create a Business Model using Metadata editor

Pentaho Metadata Editor (PME) is a tool that allows you to build Pentaho metadata domains and relational data models. A Pentaho Metadata Model maps the physical structure of your database into a logical business model

Steps:

1. Start the Metadata Editor
2. Environment Setup
a. Right click on Connections -> choose New Connection
b. Provide required database credentials and test for the connectivity
c. Select tables to Import option will appear, choose the required tables for which adhoc model need to be build
d. Either it can be done in above step or right click on the Datasource being created and choose Import Table option
e. If required change the name of the table by right click and choose Edit option. Change the name in the String field under Name and Description

Once all the tables are imported, now create the business model, map the relationship between the tables and choose the required attributes from each model

3. Business Model
a. Right click on the Business Model and choose ‘New Business Model’
b. Enter the name for the business model in String field under Name and Description
c. Under the business model, there will be 3 options,

Business Tables: List of tables for which model is to be build

Relationships: Mapping between each table, primary and foreign key relationships

Business View: Choose the required attributes from each model and sequence and format it

d. Right click on the Business Table and choose New Business Table
Make selection dialog box appears, select required table one at a time and click OK
f. selected tables will appear on the Graphical View (right side of the screen)
g. After choosing all the tables, in the graphical view right click and select ‘New Relationship’
h. Select the Source and Destination tables and its corresponding fields, Relationship should be chosen as 0:N, click ok. Likewise do the same for all the required tables

In Business view

i. Right click and choose ‘manage categories’, dialog box appears.
j. Move the required tables into Business View Categories section, use the up and down arrows for changing the sequence
k. Choose the necessary fields from a table and remove other fields, click close
l. For dimensions, make sure data type is String.
m. For any measure related fields, its data type, format, aggregation options can be defined in business view area
n. Right click on the required field to be formatted. In the ‘Available’ section, choose Default Aggregation to select the required aggregations, then data type
o. For specifying the number formats like # or #,###.00 etc,. Click on the ‘+’ symbol under ‘Available’ and choose ‘Mask for Number or Date’. In its value, specify the above formats
p. Save the model by entering some name
q. This model can be exported to a xmi file from File Menu -> Export to XMI file, specify the name for the file
r. It can be imported into BI server from Manage Data Source