Complete Pentaho Installation on Ubuntu, Part 9

Install Pentaho Metadata Editor

An elegant way to show your users their data without the complexity of teaching them table joins or making them think between counting unique items or using formulas for aggregations, and at the same time being able to control access to data using roles is the purpose of the Pentaho Metadata Editor which lets users pick a ‘Calls Number’ element insted of ‘Select Count(Distinct IdCall) AS ‘Calls Number’…’.

In the PME application you work on three steps. First the physical layer, where you set up the connection and import each table. Then set the concepts or field type, formating  and optionally aggregations. Then in the Business layer you assign relationships between tables (and can define field caracteristics also). And finally make categories (groups) for example document data, time performance values, amounts, to make it easy to pick the needed ones.

Once you have a XML metadata model [.xmi] you can use it from the BI Server with WAQR (a simple Web Adhoc Query Reporting builder) or the Pentaho Reporter. It also seems that the new Agile BI initiative also builds on it as it lets you create .xmi files interactively and then save them to your server for sharing.

Installation

1. Download a stable version from the Pentaho project in sourceforge.
   or a release candidate like pme-ce-4.0.0-RC1.zip.
2. Extract its content in the /Pentaho folder so you end up with a:
   /Pentaho/metadata-editor/
3. Make the .sh files excecutable and delete the .bat ones.
4. Start the app with:
   $ ./metadata-editor.sh

There are three files that you should back up frequently as they are your repository. On the main folder: mdr.btb, mdr.btd, mdr.btx.

To start, import the metadata file (File->Import xmi) in:
/Pentaho/biserver-ce/pentaho-solutions/steel-wheels/metadata.xmi

You will see in connections in the SampleData, the tables and the concepts (type of data and format, etc.), you can check them in Tools->Concept Editor.

Metadata Physical layer

In the business models, in  the Orders model you can see the formating

Metadata Business & Relationships

And in the bottom you have the categories: customer data, orders, products, payments:

Metadata Business View

This is how the user sees it in the web BI Server (New BI report):

Medatada BI usage

Articles

• Matt Casters on 2006 list the benefits: here.
• Diethard Steiner’s 10 seconds tutorial. And link on security post.
  His post about joining two fact tables.
• Pentaho getting started.
• Advanced articles by Will Gorman on row level security: here.
  Customizing Query in Pentaho reporting and MQL syntax.
• In spanish: video

[Edit]

Staring from Zero

1. Edit the JNDI file in Pentaho/metadata-editor/simple-jndi/jdbc.properties
   You can copy the connection values from Pentaho/biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
2. Start the metadata-editor
3. In the left panel, right click on connections and select  new connection
   Fill the values as you are now familiar, just check that your access at the bottom of the dialig is JNDI. Test it and close it
4. Select the tables you’re interested in and click ok
5. If you open the tree you’ll see the connection, the tables and fields.
   This is a good time to open the Concept Editor
   

   Concept Editor

   In this image all the items are Base, but you can change to ID (numbers without format) or Currency or Date. First you add the ‘Concept name’, then select the property (2) and the value (3). (to overide the inheritance click on the chain icon on every field).

   

   Concept Editor Attributes

6. After defining at least ‘Date’, ‘Currency’, ‘ID’, ‘Number’, ‘Sequence’ (this you can set it to hide it to user. Close the dialog.
   Set your fields to a concept with a right click an select the one needed.
7. In ‘Business Model’ right click and click on ‘New Business Model’, name it like ‘BV_your-model-name’, select a connection, and name it.
   In the tree open your model and select ‘Business Tables’
8. Right click and select ‘New Business Tables’: right click and select ‘New Business Tables’ select one table and click OK.
   TIP: Rename to short names like ‘BT_UNIQUENAME’ insted of the suggested names or your queries wont fit on the query cache.
9. Repeat with all tables.
10. Make shure you are viewing the entity diagram (on the right panel ‘Graphical View Tab’)
    Edit relationships selecting two tables and right click to define a’New Relationship’. Dont forgent about cardinality 1:n, inner-outer join, etc.
11. Use the ‘Category Editor’ icon on the toolbar
    Create categories (almost are the same that tables) fields into your categories.
    Group your fields.
    

    Category Editor

12. Save and backup your repositories files frequently.
13. Export to xmi (do not publish)
    Into a directory in /Pentaho/biserver-ce/pentaho-Solutions/YourFolder/metadata.xmi
14. To see your changes on the BI Sever, you need to click on: Tools->Update->Update Report Metadata, You’ll be ok

Additional Code

To save the xmi with a different name even in the same folder, you can modify the publish dialog with: this. It is not necesary to do it as we dont publish, but remind us  the beauty of open source: if we like to improve something, we can.