http://diethardsteiner.blogspot.com/2009/07/pentaho-metadata-editor.html
The 10 Seconds Pentaho Metadata Editor Tutorial
- 3 Reasons for a Metadata Model
- Quick Step By Step Guide
- Using the same dimensional table for more fact tables
- How to implement data security
3 Reasons for a Metadata Model
- It's an additional layer between the data and the presentation. When a column name changes in the data, you only have to change it in the metadata model and not in all reports.
- Data access restrictions: You can define specific data access rights for users/groups.
- the medadata model is used for the Pentaho BI Server Ad-hoc report functionality. Tired of people asking you to create special reports? With the ad-hoc report users can create their own reports and specify exactly which data points they want to have in it.
The main wiki doc you can find here. Please read the Wiki. The below is just a summary of the most important steps. I assume that you are familiar with the interface and the basic steps. Now let's start:
- Define Connection. You have to use the same JNDI connection as you specified on the BI Server (in the administration panel). In order that the metadata editor can connect, you have to change the jdbc.properties file in Metadata Editor/simple-jndi. Add the following (this is an example for a database name pentaho on a MySQL DB server):
- localhost/type=javax.sql.DataSource
- localhost/driver=com.mysql.jdbc.Driver
- localhost/url=jdbc:mysql://localhost:3306/pentaho
- localhost/user=root
- localhost/password=
- Then go back to the metadata editor, click on test connection and then it should work.
- Import all tables that you want.
- Double click on the table and go to "Model Descriptor", click on the overwrite icon and define the table type (fact or dimension). Click OK.
- If you want to add calculated fields, it's time to do so now: Double click on the respective table. Once the window is open, click on the + icon. Give the field a new name (i.e. PC_Amount_of_users). Define the aggregation type (i.e. Count). Define Data Type. If you don't know the length and precision yet, set it to -1. Define the formula (if you have just a simple count or sum, then only write the name of the column in there). That's it. (Field type can stay on "other").
- In the formula field you can use database specific functions as well (i.e. "YEAR(date)"). In this case you have to click on "Is formula exact?".
- You can add other properties like text alignment or date mask by clicking on the + icon.
- Right Click on "Business Model" and select "New Business Model". Name it etc.
- Click on your business model. Go to Tools/Security and import Roles from BI Server by entering following URL: http://localhost:80/pentaho/ServiceAction (or similar). This will allow you to restrict the data for certain roles. If the connection works ok, you will see an XML extract of the roles definition.
- Double click on your Business Model and go to "Metadata Security" and give rights to some roles.
- Then go to the "Data Constraints" section in the same window and select role based constraints (if you want to allow certain users to only access certain data). Add the role and define the constraint in the form [business table name.column name]=value (i.e. [BT_SN_LT_COUNTRIES_SN_LT_COUNTRIES.BC_SN_LT_COUNTRIES_COUNTRY_NAME]="FRANCE").
- Drag and drop the tables into the business model.
- In order to create relationships between tables, select the tables by holding the CTRL key and then right click on the last table and choose "New Relationship".
- Once the business tables and relationships are established, we can create the business view. Right click on "Business View" and select "New Category".
- Define Categories, i.e. Time, Measures, Coutries etc
- Once you have defined the Categories, right click on "Business View" and choose "Manage Categories". Define columns for each category.
- Now establish the security settings for the Categories (if you want to restrict the access by category). Double Click on each category and add the relevant roles to the "Metadata Security" section.
- Now the main metadata model is defined. Now it is time to test the model. Click on the "MQL Query Builder" icon and run some test queries. You can check the generated SQL by clicking on the SQL icon.
- If testing goes ok, publish the model to the BI server. The final metadata model is saved as an xmi file. On the BI Server, there can be only one xmi file per solution folder.
- Click on "New Report" and create an Ad-hoc report! Now, this is were everything shines!
Using the same dimensional table for more fact tables
In case you are using the same dimensional table for more fact tables, just drop the dimensional table several times into the business view and rename each of them. Then create separate relationships for all of them.
How to create formulas: http://wiki.pentaho.com/display/ServerDoc2x/02.+Pentaho+Metadata+Formulas
How to implement data security
Info: http://wiki.pentaho.com/display/ServerDoc1x/06.+Adding+Row+Level+Security+to+a+Pentaho+Metadata+Model (BI Server already recognizes security by default!).
Although this part is already covered above, I thought I readdress it here again:
- Go to your business model (this is one hierachy below "Business Models" and has a brown briefcase symbol next to it) and right click, choose "edit". It is important this it is implemented on this level as otherwise it won't work.
- In the "Metadata Security" section add all the users/groups that you want to allow access.
- In the data constraint section add the users/groups and specify their access rights. If you want to restrict the access to specify countries, then you have to write something like this: [business table name.column name]=value (i.e.: [BT_SN_LT_COUNTRIES_SN_LT_COUNTRIES.BC_SN_LT_COUNTRIES_COUNTRY_NAME]="ITALY"). Also, if one user/group has to have access to everything, you have to set the constraint to TRUE().
- Go to each category (if you want to restrict the access by category), click edit and add the relevant roles to the Metadata Security section.
Posted