Complete Pentaho Installation on Ubuntu, Part 3: Saiku

Edit 2:

Pentaho 5.0 in 2014 changed some things and somehow Pentaho Reporting stopped working. Their developers had announced a code fusion with Pentaho Analytics but no date has been announced as they need support to develop it. All their effort is focused on the Saiku Analytics 3.x version. For installation you just need to use the toolbar option ‘Marketplace’ and click on the install button. No need to configure datasources.

Edit:
On May 2011, Saiku OLAP was made available. On June 2012, Saiku Reporting was anounced. Both projects are a promising alternative to Jpivot and WAQR, that are still needed for advanced funcionality not yet implemented on the new tools. But that thing is changing on each new release. On October 2012 version 2.4 was released.

Saiku Analytics

Saiku Analysis is a very interesting project for the Pentaho community and it may grow to take over ‘jpivot’, the old official multidimensional web browser. It was initially known as the Pentaho Analysis Tool (PAT).

Install SAIKU and Add OLAP Datasources

These notes are based on Tom Barber’s article. And updated on June 2011 and 2012 🙂

What you Need

Download two files from the saiku site:

  1. The plugin: saiku-plugin-2.4.zip
    [Saiku Pentaho BI Server Plugin. 2.48MB. October 2012].
  2. [Edit – From 2.4 and on, this is no longer necesary]
    The web-app file: saiku-webapp-2.4-war
    [Saiku Backend WAR 2.4. 24.2MB. October 2012].

If you need the old version you can get those files the links are saiku-plugin-2.3.zip. [6.5MB, June 2012], saiku-webapp-2.3-war. [24.7MB, June 2012].

Steps to Install

  1. Open the (plugin) zip file and extract the content into:
    /biserver-ce/pentaho-solutions/system/
    it will create a new saiku folder.
  2. [Edit – also not necesary]
    Create a saiku folder in the:
    /biserver-ce/tomcat/webapps/
    and extract the contet of the web-app file in it.

    (With a .war file you can put the file on the /tomcat/webapps directory and restart the server, use the procedure that you like most)
  3. That’s it!

Start the Pentaho server and browse to your main page:

BI Server with new Saiku Analitycs buttons

You’ll see a new Icon for ‘New Saiku Analytics’. Double click on it. On the Cube List select a FoodMart, SampleData or SteelWheels ‘cube model’, drag a measure to columns and another to rows, put a metric on one of them also. You can filter double clicking on them or use the filter area. Check their screencasts.

Saiku Multidimensional Browser

Saiku Interactive Multidimensional Browser

jPivot Multidimensional Browser

jPivot Multidimensional Browser

Notes:

  1. Remember that you uploaded the FoodMart MySql data and set up the datasource on the previous post. Saiku reads this configuration and uses it itself.
  2. User access control to SR reports can be achieved by opening and update
    /Pentaho/biserver-ce/pentaho-solutions/system/pentaho.xml
    by adding the file extension .adhoc to the <acl-files> property:
    <acl-files>xaction,url,prpt,prpti,xdash,xcdf,adhoc</acl-files>

Set up Saiku Reporting

On June 11, 2012 Pentaho Reporting was announced for the Pentaho BI Server in the community edition.The proyect page is on github.

Now you can drag and drop fields exposed by your metamodel directly on your browser.

To install it:

  1. Download saiku-adhoc-plugin-1.0-GA.zip. This is the project page.
  2. Unpackit into Pentaho/biserver-ce/pentaho-solutions/system.
    it will create a saiku-adhoc folder.

To use it I can recomend:

First, watch this video http://www.youtube.com/watch?v=7mLpxhZZT4I.

Then, to start:

  1. Click on the Saiku Reporting toolbar button
  2. Select a model on your left.
  3. On the upper right, click on Row Limit and set it to 10 registers so it will not retrive a big recordset while you build your report and filters.
  4. Drag and drop fields, set up filters and groupings.
  5. You can change the theme, font or colors.
  6. Change the Title by double clicking on it.
  7. There should be an easy way to change the logo. But you can copy the image overwritting the file Pentaho/biserver-ce/pentaho-solutions/system/saiku-adhoc/web/js/images/src/logo_red.png that is an 136×23 image.

This is how it looks:

Saiku Reporting

Saiku Reporting

There are a couple of things that are not obvious and I havent found yet, those are: 1) Totals and subtotals and 2) How to calculate additional columns with formulas, as soon as I find out I will update this.

Notes:

I needed to modify the metadata model (check post 9) on the time dimention table from integer selection fields to string type of fields or the filtering would not work. That means that I had to add fields for year, quarter, month (2012, 1, 12) and copied to (‘2012’, ‘1’, ’12’).

One nice thing is that if you set a date field in the metadata with aggregation to minimum and maximum, saiku anilytics will open two date calendars so you can specify a range for that field.

On the instructions, it mentions that the CDA should be updated. On part 11 the CDE is installed and CDA updated.



Setup an OLAP Datasource

Edit June 2011:
This steps are needed if you add a OLAP datasource to your BI server.
We’ll need this steps on a future post about mondrian modeling tool: Schema Workbench,
then we’ll have an XML model and we need to tel saiku and jpivot where to find it.

For the Saiku OLAP datasource

Navigate to:

/Pentaho/biserver-ce/tomcat/webapps/saiku/WEB-INF/classes/saiku-datasources/

Create a text file (.xml) like: DataSourceName.mondrian.xml with gedit or your favorite editor and add:

type=OLAP
name=DataSourceName
driver=mondrian.olap4j.MondrianOlap4jDriver
location=jdbc:mondrian:Jdbc=jdbc:mysql://localhost:3306/YourDatabase;Catalog=[PATH]/DataSourceName.mondrian.xml;JdbcDrivers=com.mysql.jdbc.Driver;
username=pentaho_user
password=password

Note: You can put the fileo two places:
– Folder on Saiku under the /Pentaho/biserver-ce/tomcat/
[PATH]: ../webapps/saiku/datasources
– Folder on pentaho-solutions: /Pentaho/biserver-ce/pentaho-solutios/yourfolder
I prefer this one as it can be reached by jpivoy and saiku and is consistent with the config.
[PATH]: /yourfolder

This would be an example for the SteelWheels file (you dont need it as saiku considers it) so you can check the files path’s:

type=OLAP
name=SteelWheels
driver=mondrian.olap4j.MondrianOlap4jDriver
location=jdbc:mondrian:Jdbc=jdbc:mysql://localhost:3306/sampledata;Catalog=/steel-wheels/analysis/steelwheels.mondrian.xml;JdbcDrivers=com.mysql.jdbc.Driver;
username=pentaho_user
password=password

Save it and exit.

For the Pentaho BI OLAP datasource.

To add the OLAP datasource to jpivot and BI server, navigate to:

/Pentaho/biserver-ce/pentaho-solutions/system/olap/

edit datasources.xml

add copy and paste the lines for the DATASOURCE tag before the closing </Datasources> tag:

 <Catalog name="SteelWheels"> 
 <DataSourceInfo>Provider=mondrian;DataSource=SampleData</DataSourceInfo> 
 <Definition>solution:steel-wheels/analysis/steelwheels.mondrian.xml</Definition> 
 </Catalog> 
 <Catalog name="SampleData"> 
 <DataSourceInfo>Provider=mondrian;DataSource=SampleData</DataSourceInfo> 
 <Definition>solution:steel-wheels/analysis/SampleData.mondrian.xml</Definition> 
 </Catalog> 
 </Catalogs> 
 </DataSource>

<DataSource>
 <DataSourceName>Provider=Mondrian;DataSource=DataSourceName</DataSourceName>
  <DataSourceDescription>NAME Cube</DataSourceDescription>
  <URL>http://localhost:8086/pentaho/Xmla?userid=joe&amp;password=password</URL>
  <DataSourceInfo>Provider=mondrian;</DataSourceInfo>
  <ProviderName>PentahoXMLA</ProviderName>
  <ProviderType>MDP</ProviderType>
  <AuthenticationMode>Unauthenticated</AuthenticationMode>
  <Catalogs>
   <Catalog name="DataSourceName">
   <DataSourceInfo>Provider=mondrian;DataSource=DataSourceName</DataSourceInfo>
   <Definition>solution:steel-wheels/analysis/DataSourceName.mondrian.xml</Definition>
  </Catalog>
 </Catalogs>
</DataSource>

</DataSources>

If you paste the code, be carefull with the ampersand “&amp;” sometimes it gets replaced. Check for the complete string: =joe&amp;password=

If you start the server and click on ‘New Analysis View’ or ‘New Saiku Analytics’ main icons you’ll see your models.

Advertisements

18 thoughts on “Complete Pentaho Installation on Ubuntu, Part 3: Saiku

  1. Hi,
    Thanks a lot for the guide, finally I can get the pentaho install by following this guide.

    Just something to add where I encounter a problem where the login giving problem on the following day where the system was idle for more than 8 hours. Schedule daily job also does not turn out.

    Error in the pentaho.log as follow:
    ===
    java.net.SocketException
    MESSAGE: Broken pipe

    STACKTRACE:

    java.net.SocketException: Broken pipe
    ….
    ===

    After several days searching and testing, finally found the reason which is cause by MYSQL timeout issue, which can be solved as the guide on

    http://wiki.pentaho.com/display/ServerDoc2x/Configuring+for+MySQL

    It will be good if you can include this part in your guide.

  2. Hi,

    we released our 2.0 GA version (including the plugin).
    It is now much much easier to install the plugin: http://projects.analytical-labs.com/knowledgebase/articles/10

    basically just download the plugin from http://analytical-labs.com and extract it into pentaho-solutions/system/

    It will pick up the OLAP datasources from system/olap/datasources.xml automatically.

    No separate webapp or other config needed.
    It just works 🙂

    Feel free to contact me/us for questions. Any feedback is also appreciated 🙂

    -Paul

  3. It may be worth noting, as well, that a restart of the BI Server (as of this post, obviously) is necessary to pick up newly added datasources. I struggled with this for a bit and haven’t found a more ‘soft reset’ saiku resolution.

    I love that this recent version picks up the existing Pentaho olap datasources.xml file, though…

  4. How to prevent one user to see other users’ data using data source

    Hi,

    Please consider following case.

    – I’ve BI server 3.7 CE stable version
    – There is one table say ‘UserInfo’, having User information with user id as primary key.
    – All user,existing in ‘UserInfo’ table have permission to create Ad-Hoc report by new report option in pentaho
    – Please provide steps so that one user can not see other users’ data i.e. how can I use user id implicitly in Data Source so that user will be restricted to only his data.
    or
    – It is possible that login user name dynamically used in datasource querry wizad.
    – like if user pat login, pat data are display or if suzy login then suzy data display.

    -Thanks in advance,
    Saurabh

  5. Hello Martinez
    I retraced my steps to get foodmart cubes, as you suggested. The error was in port number in datasources.xml. I had just copied and pasted it from this post. Please correct it. Anyway re-installed saiku plugin as per the comment by Pstoellberger. Then copied the FoodMart.mondrian.xml from pentaho documentation to steelwheels/analysis.
    Now I am getting the cubes in saiku, but not in pentaho analytic tool. I am re-checking. Thank you.
    mahamood

  6. Thanks A lot ….
    Great post i had referred.. Gives me more information about Saiku Analytics installation part,adding adhoc plugin.
    Can anyone please provide me the links/guide to use Saiku compared to Penataho Analyzer in Enterprise Edition .

  7. Pingback: Pentaho CE 4.8 – BI Server Update | Interesting IT Tip's

  8. Hi!
    First of all, thanks so much for your hard work to write your blog.
    A few days trying to create a new template based on the saiku_small.prpt for the report to use it saiku-reporting. Set saiku adhoc using the script https://github.com/pmalves/ctools-installer/blob/master/ctools-installer.sh

    The Internet is not found instructions on how to create templates for saiku-reporting. Tried to replace the logo saiku in the template saiku_small.prpt on a different logo, after I saved in Pentaho Report Designer the edited file saiku_small.prpt and uploaded it to the folder pentaho-solution/system/saiku-adhoc\resources\templates. In the editor saiku reporing I chose the template saiku_small, but the application is an error HTTP Status 400 – Bad Request.

    Also strange is that if I open the template saiku_small.prpt in the editor Pentaho Report Designer and store it without changes, then this template can also not be used.

    Any ideas how to edit the standard templates saiku?

  9. Great tutorials. Where you say:

    You’ll see a new Icon for ‘New Saiku Analytics’. Double click on it. On the Cube List select a FoodMart, SampleData or SteelWheels ‘cube model’, drag a measure to columns and another to rows, put a metric on one of them also. You can filter double clicking on them or use the filter area. Check their screencasts.
    ——
    1) No Cubes are available to select.
    2) In the new analysis view, I can see only SampleData & SteelWheels schemas. When choosing Quardrant Analysis, I get error: “JPivot has been replaced by Pentaho Analyzer.”

    I tried to follow tutorial step by step, copying & pasting from your blog. Any direction would be much appreciated!

    • Fixed item #1 above, as you address it in the next section. (my bad!) I’m going to assume you address my 2nd issue as well. Thanks for posting this. Its a great help!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s