Skip to primary content
Skip to secondary content

Interesting IT Tip's

IT & IS articles that might work for you

Interesting IT Tip's

Main menu

  • Home
  • GitLab
  • Mayan EDMS
  • Pentaho Index
  • Pentaho v3.8-4.8
  • Pentaho 5.0
  • Pentaho 5.x
  • Pentaho 6.x

Category Archives: Metadata Editor

Additional Databases

Posted on June 16, 2018 by Francisco Javier Martínez Guzmán
1

Sample Databases

The Pentaho Demo comes with SampleData (SteelWheels based) HSQLDB that will suffice for modeling and learning all the Pentaho tools. But you may want to model metadata* or mondrian from scratch, require more rows in your tables, or want to replicate lots of samples that refer to the Microsoft Server. Well, at least that what crossed my mind when I started using these tools.

So I looked up the links to recreate two additional databases in MySQL for you to use. 🙂

I. DATABASE

Two Databases

The classic databases for learnimg SQL and MDX are AdventureWorks (OLTP) [Updated link on 2022] and FoodMart (DW). AdventureWorks is a relational database and FoodMart is a Data Wharehouse database.

Download them and Unzip them. You need to add this line on the top of FoodMart file:

CREATE DATABASE IF NOT EXISTS foodmart; USE foodmart;

Upload them to your MySql database and grant access to pentaho_user.

mysql -u root-user -p root-password < AWBackup.sql
 mysql -u root-user -p root-password < foodmart_mysql.sql
 echo "grant all on adventureworks.* to pentaho_user@localhost identified by 'password';`" | mysql -u root-user -p root-password
 echo "grant all on foodmart.* to pentaho_user@localhost identified by 'password';" | mysql -u root-user -p root-password

II. CONFIG

Add datasources to jndi list

  1. Go to ~/Pentaho8/pentaho-server/pentaho-solutions/system/simple-jndi
  2. Open jdbc.properties
  3. For new datasources, add (You can define it also in the web app if you like)
    FoodMart/type=javax.sql.DataSource
    FoodMart/driver=com.mysql.jdbc.Driver
    FoodMart/url=jdbc:mysql://localhost:3306/foodmart
    FoodMart/user=pentaho_user
    FoodMart/password=password
    AdventureWorks/type=javax.sql.DataSource
    AdventureWorks/driver=com.mysql.jdbc.Driver
    AdventureWorks/url=jdbc:mysql://localhost:3306/adventureworks
    AdventureWorks/user=pentaho_user
    AdventureWorks/password=password

Add datasources in the web app

Download the FoodMart mondrian (snowflake schema) here and unzip them. We will use the FoodMart file in next section.

Add datasources in the web app

  1. Click at the left top menu: File → Manage Data Sources
  2. Create a JDBC Data Source for each Database:
    a. Click on the New Data Source button
    b. Name the Datasource and type: FoodMart, Database. Click Ok.
    c. Create (plus sign icon) a new Connection: FoodMart.
    Name: FoodMart; Select Type: MySql; Host: localhost; Database: foodmart;
    user name: pentaho_user; password: password.
    d. Click Test and it should report a succesfull connection, click Ok.
    e. Repeat for AdventureWorks just change name and Database: adventureworks.
    f. After you create both data sources, click Cancel to return to the Data Source Dialog.
  3. Create the Analisys Data Source for the FoodMart model:
    a. Click in the Gear Icon.
    b. Select New Connection.
    c. Click on Import Analysys.
        select the downloaded FoodMart.Mondrian.xml file.
    d. Click Import.

That’s it.

To use FoodMart.mondrian.xml you can use the Desktop Tools Schema-Workbench and Aggregation-Designer (here is an excelent tutorial by Diethard Steiner), but for the Pentaho-Server you will need to install jPivot4J from the marketplace as jpivot or Saiku cannot read it.


* There are two tutorials for using the meta-data editor to configure user or group business models and to provide restriction access to databases. The first one is by Diethard Steiner on 2009 or one written by Herwin Rayen in 2014. The PRD can be programmed to use this access. But I have to tell that no client has ever asked/payed for it. 🙂

Advertisement
Posted in Aggregation designer, BI Server, Business Intelligence, Metadata Editor, Mondrian, MySql/MariaDB, OLAP Model, Pentaho, Uncategorized | Tagged MDX Database, Mondrian, MySql, Pentaho Databses Config, Pentaho Datasources, Pentaho Metadata Editor, Pentaho Schema Workbench, SQL Database | 1 Reply

Pentaho 4.5 – Tools Update

Posted on May 8, 2012 by Francisco Javier Martínez Guzmán
2

If you followed the articles about installing Pentaho (CE) applications in this blog, now is the opportunity to upgrade them as the have been posted on sourceforge on Monday (5/7/2012) with these quick instructions.

Part I

You need to go to http://sourceforge.net/projects/pentaho/files/ for your tar.gz or zip files.

Update PDI – Data Integration (ETL)

  1. Rename your folder to ‘data-integration-old
  2. Download pdi-ce-4.3.0-stable.tar.gz
  3. Unpack its content ‘data-integration’ into Pentaho folder
  4. Copy your ‘development’ folder from the older directory
  5. Optional plugins: Copy two folders /Pentaho/data-integration-old/plugins/spoon : DataCleaner and kettle-profiling-datacleaner to your new directory.
  6. Copy your database drivers (Oracle, Mysql, etc) from /Pentaho/data-integration/libext/JDBC
  7. Clean the cache: Tools->Database->Clear cache.

Be carefull I couldn’t save a transformation after an error, and an warning about saving was generated after a timeout from a database. That happened to Kettle a few versions before (2.5) I think but I will keep using this and previus versions for a while.

Update PRD – Report Designer

  1. Rename your folder to ‘report-designer–old’
  2. Download prd-ce-3.9.0-GA.tar.gz
  3. Unpack its content ‘report-designer’ into Pentaho folder
  4. Copy your ‘development’ folder from the older directory
  5. Copy your database drivers (Oracle, etc) from /Pentaho/report-designer/lib/jdbc
  6. [Edit]
    If you use a Pentaho data Integration as a Data Source, you might get the following error when the preview button is used:
    org.pentaho.reporting.engine.classic.core.ReportDataFactoryException: Caught Kettle Exception: Check your configuration
    The
    easy way to avoid it is to copy the files from
    Kettle:
    /Pentaho/data-integration/libext/JDBC/
    into your PRD:
    /Pentaho/report-designer/lib/jdbc/

Update PME – Metadata Editor

  1. Rename your folder to ‘metadata-editor–old’
  2. Download pme-ce-4.5.0-stable.tar.gz
  3. Unpack its content ‘metadata-editor’ into Pentaho folder
  4. Copy mdr.btd and mdr.btx from the older directory

Part II

Now go to http://sourceforge.net/projects/mondrian/files/

Update  PSW – Schema Workbench

  1. Rename your folder to ‘schema-workbench–old’
  2. Download psw-ce-3.4.1.1.tar.gz
  3. Unpack its content ‘schema-workbench’ into Pentaho folder
  4. Copy your ‘development’ folder from the older directory
  5. Copy your database drivers (Oracle, MySql, etc) from /Pentaho/schema-workbench/drivers

Update  PAD – Aggregation Designer

  1. Rename your folder to ‘aggregation-designer–old’
  2. Download pad-ce-1.4.0-stable.tar.gz
  3. Unpack its content ‘aggregation-designer’ into Pentaho folder
  4. Copy .databaseMetaInfo and .schemaInfo from the older directory
    (In a the nautilus file browser remember to check the option show hidden files)
  5. Copy your ‘development’ folder from the older directory
  6. Open the app. Close the wizzard if it appears. Then in the menu File->Open and search for pad_workspace in your development folder to restore your work.

Notes

Now only the BI Server and the Studio Designer needs to be upgraded. I will edit this for the BI server as it is a bit complcated. The Design Studio hasn’t been posted yet.

Keep both folther the new and -old for a while. When you feel confident about your new software then erase the -old directories.

Posted in Aggregation designer, Metadata Editor, PDI/Kettle, Pentaho, Report Designer | Tagged Pentaho Tools | 2 Replies

Complete Pentaho Installation on Ubuntu, Part 9

Posted on August 6, 2011 by Francisco Javier Martínez Guzmán
3

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.

Table

Metadata Physical layer

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

Metadata Business & Relationships

Metadata Business & Relationships

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

Metadata Business View

Metadata Business View

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

Medatada BI usage

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

    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

    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

    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.

Posted in Business Intelligence, Metadata Editor, Pentaho | Tagged Metadata Editor, OLAP Model, Pentaho | 3 Replies

Author

Francisco Javier Martínez Guzmán

Contact

View Javier Martínez's profile on LinkedIn

Content License

Everything you see here is licensed under a Creative Commons License.

Categories

  • Aggregation designer
  • Ansible
  • Apache HOP
  • BI Server
  • Business Intelligence
  • ctools
  • Dashboard
  • Dojo Toolkit
  • GitLab
  • GitLab HTTP configuration
  • Hitachi Vantara
  • Human Resources
  • Install
  • k3s
  • Knowage
  • Kubernetes
  • Mayan EDMS
  • Metadata Editor
  • Mondrian
  • MySql/MariaDB
  • OLAP Model
  • PDI/Kettle
  • PDI/Pentaho Design Studio
  • Pentaho
  • Pentaho 5.0
  • Pentaho 5.x
  • Pentaho 6.x
  • Raspberry Pi
  • Report Designer
  • Uncategorized
  • Visualizations
  • Web App Testing
  • Weka

Archives

  • January 2022
  • December 2021
  • October 2021
  • September 2021
  • August 2021
  • July 2021
  • May 2021
  • February 2021
  • March 2020
  • September 2019
  • December 2018
  • June 2018
  • November 2017
  • September 2017
  • May 2017
  • September 2016
  • April 2016
  • October 2015
  • February 2015
  • October 2014
  • June 2014
  • March 2014
  • May 2013
  • January 2013
  • December 2012
  • November 2012
  • May 2012
  • January 2012
  • October 2011
  • September 2011
  • August 2011
  • July 2011
  • June 2011
  • May 2011
  • April 2011
  • September 2010
  • August 2010

Visiting Colleges


Previous visitors:

Pentaho BI Blogs

Pentaho Project
Matt Casters on Data Integration
Pedro Alves Blog
Roland Bouman's blog s
Reporting Tales sherito.org
Julian Hyde on OLAP and stuff
Will Gorman Blog
Gretchen Moran
Independent
Diethard Steiner:  ⇦2014 ⇨
James Dixon’s Blog
Pentaho BI Suite Blogspot

Great Old Ones
Adventures on Open Source BI
Pentaho Musings
Goodman on BI
Ravings of a madman
Andres Chaves blog
Michael Tarallo @ Pentaho
BI in General
50 blogs, 2015

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 72 other subscribers

Meta

  • Register
  • Log in
  • Entries feed
  • Comments feed
  • WordPress.com
Create a free website or blog at WordPress.com.
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
  • Follow Following
    • Interesting IT Tip's
    • Join 72 other followers
    • Already have a WordPress.com account? Log in now.
    • Interesting IT Tip's
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
 

Loading Comments...