Hitachi Vantara

Today September 19, 2017, Hitachi Vantara was announced, it is a new business entity that will unify the operations of Hitachi Data Systems, Hitachi Insight Group, and Pentaho.

So we’ll be saying both Pentaho and Vantara for a while to refer to this BI suite.  🙂

This is the press release: https://www.hitachivantara.com/en-us/news-resources/press-releases/2017/gl170919.html

The main new Site: https://www.hitachivantara.com/

And new twitter account to follow: https://twitter.com/HitachiVantara

The Community Edition at Sourceforge: https://sourceforge.net/projects/pentaho/ with the Hitachi Logo and title “Hitachi Vantara | Pentaho”. The wiki in http://wiki.pentaho.com/display/COM/Community+Wiki+Home

Data integration is still “PDI” or “Kettle” in the community area: https://community.hds.com/docs/DOC-1009855

It seems (#HitachiNEXT) they’ve got a nice business and consulting strategy. Lets see what they do with the original Open Source philosophy.

Advertisements

Pentaho 7.1 – Demo Install

The newest release Pentaho 7.1.0.0.12 in its community edition is available for download at sourceforge (link) since the 22th of may of 2017.

Pedro Alvez announced its availability the same day with an excellent post in his blog (link). He mentions new visualizations, scalability with big data engines and repository improvements -someday they’ll deliver something you can work confortable with, maybe-, and a new web theme. We’ll see how they are supported in the CE edition. A new mobile access for the EE. The PDI team is exited about its ‘metadata injection’ improvements.

You’ll find it at Sourceforge.net:

  • Web application: Business Intelligence Server [1.1 GB] with 58 closed reports.
  • The best ETL aplication: PDI [904 MB] with 142 closed reports.
  • Report Designer [606 MB] with 19 closed reports in their JIRA system.
  • Pentaho Metadata [787 MB] with 5 closed reports.
  • And Big Data Shims folder.

Steps for backup, install, upgrade to a MySQL local DB are the same that you are already familiar with and has been previously posted in this blog (link) and well documented at the Pentaho Wiki.

Knowage Suite 6.0 CE

I found Spago BI since version 5.1, that is about two years ago. It was a complete Open Source BI sotution, the only one, they said. They were proud of it!. And I was waiting for 5.3 to start a series of posts as they announced amazing changes.

On may 4th @SpagoBI anounced they were starting a #spagobirevolution. The same company (Engineering Group) did an extreme makeover to XU experince and released a comunity edition (CE) and an Enterprise Edition (EE) with a Knowage Brand.

What I have seen is that the CE is an interesting product, somewhat crippled as important functionality like scheduling and MDX calculated fields and more are excluded, but lets hope its cockpit designer, metadata, models and widgets deliver a promising alternative for data exploration.

Knowage6

Check their overview

Visit their site: http://knowage-suite.com/

Install notes

[Updated on june 20th 2017]
Prerequisites: You’ll need java.
CE Manual: pdf

  1. Download
    Knowage-6.0.0-CE-Installer-Unix-20170623.zip [1.078GB from ow2]
    All-in-One-Knowage-6.0.0_RC_CE_20170429.zip [798mb] from ow2
  2. Open a Terminal, create a folder, unzip its content:
    mkdir knowage6
    cd knowage6
    cp ../dowloads/Knowage-6.0.0-CE-Installer-Unix-20170623.zip knowage.zip
    unzip knowage
    chmod 777 Knowage-6.0.0-CE-Installer-Unix-20170623.sh
    ./Knowage-6.0.0-CE-Installer-Unix-20170623.sh
  3. An instaler will ask:
  4. a. Open a welcome dialog. click Next
    b. Ask if you accept the license agreement. Do it so an click next
    c. Ask for your preference on a charting library. Accept an click next
    d. Ask for six modules to be installed. If all are selected, Click next
    e. Select a destination folder. I selected HOME$/knowage6 it will create a Knowage-Server-CE
    f. It will ask for your MySql credentials (jdbc:mysql://localhost:3306/, user and password).
       it said that two schemas: kwowage-ce and foodmart-demo will be created
  5. The installer will extract the server, the .war files and update the database.
  6. Unselect all and click finish to end the installer.
  7. I had to modify the startup and shutdown scripts to set the correct path to java:
  8. cd /HOME$/knowage6/knowage-Server-CE/bin
    gedit startup.sh
    [add your JRE path to the first line like:]
    export JRE_HOME="/usr/lib/jvm/java-8-openjdk-amd64"
    [save your script and repeat for shutdown.sh]
    
  9. Start your tomcat server:
    cd $HOME/knowage6/Knowage-server-CE/bin/
    ./startup.sh
    
  10. Start yor browser and set it to:
  11. http://localhost:8080/knowage

You will find examples, enjoy.

If you have problems with the server you can check your ./logs folder.

Visiting Pentaho. Waiting for 7.0

[Update november 14th]

Ok, I’s here. It’s up since november 9th.

Go and get it at the wonderfull site: Sourceforge.net files.

[Update october 18th]

Pentaho Business Analytics 7.0 will be available for download in mid november. Theses are the announcement and overview.

[Original Post]

In october 2016 we should be getting next version of Pentaho. According to its Jira records it will be a stability oriented release, I count 58 fixes for BI Server, 139 in PDI, 10 in PRD, 6 in CDE/CTOOLS (you can check it for each project in 7.0.0 version at http://jira.pentaho.com/) and few improvements.

Having a reliable suite is a benefit for all specially in this market where de community editions are being abandoned in FOSS and their enterprise editions are being developed attending corporate needs rather than the medium or small companys.

On that topic, I browsed the forums (http://forums.pentaho.com/) to see what  people are asking. This is what I found from the 1st to the 22th of september:

Project Questions Unanswered Notes
BI Platform 22 8 Installation and simple questions. 2 people even asked if anyone has any clues, they were lost.
Pentaho Reporting [PRD] 30 15 Advanced questions. 2 moved as they placed it in wrong forum.
Pentaho Data Integration [PDI] 127 23 Advanced questions. Some can be solved with the modified java step or mail step examples.
CTools 32 11 They ask how to modify graphs in specific ways, some of them answered in CTools web site, but need clarification on frameworks used.

Of these projects the BI Server is the one having most novices asking already answered questions. A FAQ should help these people. For the PDI and PRD there are some senior members giving excelent advice I felt good about humanity with the existence of these people.

I guess there will be no modifications in installing or upgrading instructions, maybe I should clean up the old ones in this site.

Well that’s my 2 cents on the CE application and support for the upcoming 7.0.0 version.

Pentaho 6.1

As it was announced by Pentaho-Hitachi, version 6.1.01 was realeased on the 9th of april of 2016 in EE and CE editions.

Sourceforge.net: Data IntegrationBusiness Intelligence ServerReport DesignerPentaho Metadata and Big Data Shims.

There are lots of fixes: 179 of 180 issues have been resolved in PDI, 59 of 59 issues for the BI Server and 14 of 14 issues for the PRD.

These are the main improvements acording to Pedro Alvez: Services, ODBC, metadata injection, several steps improvement and enabling tests in the BI server from the PDI, this was lost since studio went down the tube.

Steps for upgrading must be the same as previously posted. If not I will get back to you and annote this post. [Which is very much unlikely].

 

Pentaho 6.0.x Install

Introduction

After reading the changelog, I thought that the numbering from 5.4 to 6.0 was not justified. But then I realized that this is the first version of Pentaho under the Hitachi brand, and, seting a round number setted a milestone as completed. A good sign is that they kept the release day of the community version (CE), the same as the commercial one.

Recomendation: Check the ETL, it is a wonderfull tool. Spend time with it, it will let you clean and process data from several sources (Text, NoSql, DB, Excel, SAP ERP) and send it on its way [to services or users] in different formats. Of course it can be used with other suites. Then proceed with the Web server that is primary a client side tool that show processes according to user privileges and calendarize processes. To build dashboards you can choose between two sets of bulding blocks: CTools or Ivy. They are rudimentary but let you set parameters and recover data from the ETL or Report Designer and let user click on graphs for you to run queries. The Report Designer that has its own [complex] way of doing things but will let you create fixed-format reports mainly in pdf or html. The Metadata and Schema tools will help you make business/olap models and data governance.

Demo Install

First Download each file and install it as stated in the guide, play with it and then proceed with another.

Download links

Pentaho files: sourceforge zip files 

  1. ETL Tool [>810MB]: v6.0v6.0.1
  2. Tomcat Web Server [>900MB]: v6.0, v6.0.1
  3. Report Designer [>530MB]: v6.0, v6.0.1
  4. Metadata Editor [>500MB]: v6.0, v6.0.1
  5. OLAP Schema Workbench [~30MB]: v3.11
  6. OLAP Aggregation Designer [~30MB]: v6.0

Guide

Install steps for the pentaho demo applications have been using similar steps since 4.8. So the old 5.x guide works with the new files. Please follow this post using the new files.

You can skip the Java SDK installation if you already have it on your system. On a new box you’re better off with java SDK 1.8.0 as Pentaho 6.0 now works with it. Warning: If you’re on 5.x you’re probably on java SDK 1.7.0 and you can work with it, just rememeber to backup your development files before upgrading to java 1.8.0.

In that post you’ll also find instructions to install MySql that is an option in case you want to use your data. BTW I now use MariaDB and its working fine.

Memory adjustements:
I tested the apps on a windows 32 bits system and I had to edit the startup batch files to  lower the memory limits:
DATA INTEGRATION 32 bits: spoon.bat
FROM      -Xms1024m -Xmx2048m
TO            -Xms768m -Xmx1024m
REPORT-DESIGNER 32 bits: report-designer.bat
FROM      -Xms1024m -Xmx2048m
TO            -Xms512m -Xmx1024m
BI SERVER 32 bits: start-pentaho.bat
FROM     -Xms2048m -Xmx6144m
TO           -Xms768m -Xmx1024m

Update Pentaho BI Server CE from 5.2 to 5.3, and 5.4.

Pentaho BI Server CE 5.2 migration to 5.3 and then 5.4 has been a difficult process. Soo many changes, undocumented, some without obvious reasons. I hope it’s just because I don’t get the new vision and that it will get easier over time.

This notes are from my system in which I use MySql for hibernate, quartz and business databases. The Jackrabbit repository is kept on the filesystem. The steps are the same on Linux and Windows, just change the suffix from .sh to .bat. By the way if you’re on windows 7 32 bits, stick to jdk 1.7, Pentaho is not compatible with the current 1.8 version.

Note: They fixed in 5.4, for people using i18n localization in PRD parameters, so numbers in text fileds doesn’t get reformated (i.e. years got visualy modified to 2,015).

Part I: Backup

  1. Take an inventory:
    Log into your system, browse your ‘solutions’ and make an inventory, maybe take some snapshots so you can check that every option did make it through the export-import step.
    Browse your files (enable ‘show hidden files’) for resources, and files like .xmi, .sailu, .cda, etc.
    List the Marketplace plugins you use.
    In Datasources and administration make note of your configuration an users you have set up.
  2. Stop your server:
    ./stop-pentaho.sh
  3. Modify the exporter utility to backup your Saiku files (optional):
    Open the file: biserver-ce/pentaho-solutions/system/importExport.xml
    Add in convertersMap:

    <entry key="saiku" value-ref="streamConverter"/>
    

    Add to the map of NameBaseMimeResolver bean:

    <entry key="saiku" value="text/xml"/>
    

    Add to the list approvedExtensionList:

    <value>.saiku</value>
    

    Credit: Joel Blog.

  4. Start Your Server
    ./start-pentaho.sh
  5. Start a Terminal window and run the command in the \biserver-ce folder:
    ./import-export.sh --export --username=YOUR-USERNAME --password=YOUR-PASSWORD --url=http://localhost:8080/pentaho --charset=UTF-8 --path=/public --file-path=$HOME/Pentaho5/exported-file.zip

    You can read the infocenter documentation here and here.

  6. Copy the exported-file.zip file to upload.zip (keep the original as backup). Open it and delete folders that are from Pentaho Demo (Steel Wheels, cde, plugin-samples, bi-developers), those ‘solutions’ already exist in Demo. Just keep your files.
  7. Edit the exportManifest.xml in the same zip file to remove all items (xml opening and closing lines) that are not from files, your solution folders, take for example one at the bottom that correspond to a graphics file:
    〈ExportManifestEntity path="public/plugin-samples/samples.png"〉
    〈ExportManifestProperty〉
     〈EntityMetaData name="samples.png" createdDate="2015-02-21T00:34:32.695-06:00" isFolder="false" path="public/plugin-samples/samples.png" isHidden="true" locale="es_ES" title="samples"/〉
    〈/ExportManifestProperty〉
    〈ExportManifestProperty〉
     〈EntityAcl〉
      〈entriesInheriting〉true〈/entriesInheriting〉
      〈owner〉admin〈/owner〉
      〈ownerType〉USER〈/ownerType〉
     〈/EntityAcl〉
    〈/ExportManifestProperty〉
    〈/ExportManifestEntity〉
    
  8. Shutdown your Server.

Part II: Install CE Demo

  1. Download from:
    – BI Server 5.3 Sourceforge Folder. With a 673MB 5.3.0.0-213 zip file
    – For 5.4 we have the Sourceforge Folder or the 814MB biserver-ce-5.4.0.1-130.zip file.
  2. Rename your current folder to something like ‘Pentaho5-old’.
  3. Create a new Pentaho5 directory an unzip the biserver-ce into that folder
  4. Start the Server (Demo).
  5. Log in to http://localhost:8080/pentaho
    This will deploy web server files and upload initial content (biserver-ce/pentaho-solutions/system/default-content) for the web application.
  6. Stop the server.

Part III: Replace your MySql Database instead of demo’s in-memory HSQLDB

You will be updating config files, so double check their content (you can use Meld in Linux or Win Diff in windows) before copying them. Also verify they are in utf-8 format.

  1. Delete file
    biserver-ce/tomcat/conf/Catalina/localhost/pentaho.xml
    It will be created automatically as a copy from the one edited in point 3.
  2. Copy
    biserver-ce/tomcat/webapps/pentaho/META-INF/context.xml
    mine has two almost identical MySql connection strings, the lines changes to:

    driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"
    validationQuery="SELECT 1" />
  3. To disable HSQLDB startup, edit
    /biserver-ce/tomcat/webapps/pentaho/WEB-INF/web.xml

    Comment out block at line 73:

      〈!-- [BEGIN HSQLDB DATABASES]
      〈context-param〉
        〈param-name〉hsqldb-databases〈/param-name〉
        〈param-value>sampledata@../../data/hsqldb/sampledata,hibernate@../../data/hsqldb/hibernate,quartz@../../data/hsqldb/quartz〈/param-value〉
      〈/context-param〉
      [END HSQLDB DATABASES] --〉
    

    And line 205:

    〈!-- [BEGIN HSQLDB STARTER]
      〈listener〉
        〈listener-class>org.pentaho.platform.web.http.context.HsqldbStartupListener〈/listener-class〉
      〈/listener〉
      [END HSQLDB STARTER] --〉
    

    At line 539 you can change your session-timeout. Its’ in seconds:

      〈session-config〉
        〈session-timeout〉180〈/session-timeout〉
      〈/session-config〉
    
  4. Copy
    biserver-ce/pentaho-solutions/system/hibernate/hibernate-settings.xml
    The only change is to replace mysql: mysql5.hibernate.cfg.xml
  5. Copy jindi datasources:
    biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties
    The strings change to MySql like this:

    SampleData/driver=com.mysql.jdbc.Driver
    SampleData/url=jdbc:mysql://localhost:3306/sampledata
    

    Copy and paste the appropiate lines to configure your databasources.

  6. Copy MySql config for Hibernate:
    biserver-ce/pentaho-solutions/system/applicationContext-spring-security-hibernate.properties
    Change MySql strings and the dialect to:

    jdbc.driver=com.mysql.jdbc.Driver
    jdbc.url=jdbc:mysql://localhost:3306/hibernate
    jdbc.username=hibuser
    jdbc.password=password
    hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
  7. Copy
    biserver-ce/pentaho-solutions/system/applicationContext-spring-security-jdbc.properties
    It contains in different lines:

    datasource.driver.classname=com.mysql.jdbc.Driver
    datasource.url=jdbc:mysql://localhost:3306/hibernate
    datasource.username=hibuser
    datasource.password=password
    datasource.validation.query=SELECT 1
    

    Note: They used the string userdb instead of hibernate but Im not using such database for users as they do in the demo so I changed it back.

  8. Copy
    biserver-ce/pentaho-solutions/system/pentaho.xml
    I changed:

    〈login-show-sample-users-hint〉false〈/login-show-sample-users-hint〉
    

    to stop the login message that show users and:

      〈sampledata-datasource〉
    	  〈name>SampleData〈/name〉
    	  〈host>localhost〈/host〉
    	  〈type>MySql〈/type〉
    	  〈port>3306〈/port〉
    	  ...
    	  〈query>select 1〈/query〉
    
  9. Check your quartz5 prefixed database against the script on, including case sensitive if your OS or DB are sensitive.
    biserver-ce/data/mysql5/create_quartz_mysql.sql
    If there are diferences it is better to recreate the database.
  10. Modify
    biserver-ce/pentaho-solutions/system/quartz/quartz.properties
    the quartz config, to:

    org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
  11. Copy special DB drivers like (ojdbc14.jar, orai18n.jar) if you have them. MySql 5.17 is already there from your backup folder to biserver-ce/tomcat/lib.
  12. If you’re updating 5.4 a problem that we though was solved is present again (you do not need to do this for 5.3). Move to a backup folder the following file:
     biserver-ce/tomcat/webapps/pentaho/WEB-INF/lib/pentaho-hadoop-hive-jdbc*.jar
  13. Start your demo:
    ./start-pentaho.sh
  14. If you get an error, clear your browser cache, for Quartz errors check your database prefix.
    Login as ‘Admin’ again.
  15. Install Plugins:
    If you use a different language than English, in Home->Marketplace, install your language package, restart the server. Then from the Tools -> Language Packs choose install and restart again. These are two related steps.
    In Home->Marketplace install plugins like Saiku, Saiku Chart Plus, WAQR, restart.
    In Home->Administration, create your users. Asign the admin role to at least one of them.
    In Manage DataSources, modify the SampleData jdbc to access MySql. You can create your SQL Mysql Database Connections or wait until you move in your files in next section.
  16. You can check the new Dashboard in Browse Files->Public->Steel Wheels->Dashboards->CTools Dashboards.

Part IV: Move in Your Content

  1. Wiith your server running:
    In a terminal, load your backup file. Use your user and password that you created in las section:

    ./import-export.sh --import --overwrite=true --username=YOUR-USERNAME --password=YOUR-PASSWORD --overwrite=true --permission=true --retainOwnership=true --url=http://localhost:8080/pentaho --charset=UTF-8 --path=/ --file-path=$HOME/Pentaho5-old/upload.zip
  2. Copy your content from your backup folder in
    biserver-ce/pentaho-solutions
    This files (solution folders, metadata files, mondrian models) are not used as they are outside of the repository but I use them for development and then upload them.
  3. In ‘Manage DataSources’, create your JDBC, Analisys, Metadata and OLAP connections, as you upload files that were not included in the export-import process like mondrian.xml, metadata.xmi. This is where your inventory from your old system is most valuable (you can start it as long as both are not running at the same time).
  4. Restart your server. Clear your browser’s cache, reset your pentaho solutions.

Part V: Apply your Look and Feel

As of this writing (5.2->5.3: 02/21/2015, 5.3->5.4: 06/28/2015):

  • The changes on login screen images, login page html and index main content works as I wrote in here.
  • There is no Emerald Theme yet from the marketplace.
    But I got most of it when I copied it from the backup 5.2 directory:

     pentaho-solutions/system/pentaho-emerald-theme/

    Haven’t checked those .css. They need minor tune up.

  • I didn’t use my edited localization messages.properties created in previous posts as the translation in the marketplace are good enough.

That’s it. You have now a Pentaho BI CE 5.3/5.4 system.

Part VI: Install your Desktop Development Tools

To update your Desktop Tools rename their current folders to somethining-old, download the new files, that are really slow form the legendary sourceforge.net and unzip them on your Pentaho5 folder so you’ll have them all in one place:

  • For Pentaho Report Designer:
    1. Dowload the 5.3 file. or 5.4 file.
    2. Copy your ‘development folder’ from the backup.
    3. Copy your DB drivers, in my case: mysql-connector-java-5.1.17.jar, ojdbc14.jar, orai18n.jar, that works with Oracle 8 & 9, from your backup to report-designer/lib/jdbc
  • For the new look in PDI/Kettle:
    1. Download the 5.3 file or 5.4 file.
    2. Copy your ‘development folder’ from the backup.
    3. Copy your DB drivers from your backup to data-integration/bin.
  • For Metadata 5.3:
    1. Download the 5.3 file or 5.4 file.
    2. Copy your model files (mdr.btd and mdr.btx) in your old metedata-editor forder to your new one.
    3. Copy your DB drivers from your backup to metadata/bin.
    4. Start your editor and Define your connection.
  • There are two other projects that you would like to install if you work with OLAP models (check some older documentation here):
    1. Mondrian schema workbench here.
    2. Mondrian agregated designer here.