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 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:
- The plugin: saiku-plugin-2.4.zip
[Saiku Pentaho BI Server Plugin. 2.48MB. October 2012].
- [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].
Steps to Install
- Open the (plugin) zip file and extract the content into:
it will create a new saiku folder.
- [Edit - also not necesary]
Create a saiku folder in the:
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)
- That’s it!
Start the Pentaho server and browse to your main page:
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.
- 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.
- User access control to SR reports can be achieved by opening and update
by adding the file extension .adhoc to the <acl-files> property:
Set up Saiku Reporting
Now you can drag and drop fields exposed by your metamodel directly on your browser.
To install it:
- Download saiku-adhoc-plugin-1.0-GA.zip. This is the project page.
- Unpackit into Pentaho/biserver-ce/pentaho-solutions/system.
it will create a saiku-adhoc folder.
To use it I can recomend:
Then, to start:
- Click on the Saiku Reporting toolbar button
- Select a model on your left.
- 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.
- Drag and drop fields, set up filters and groupings.
- You can change the theme, font or colors.
- Change the Title by double clicking on it.
- 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:
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.
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
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/
- 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.
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:
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&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 “&” sometimes it gets replaced. Check for the complete string: =joe&password=
If you start the server and click on ‘New Analysis View’ or ‘New Saiku Analytics’ main icons you’ll see your models.