Pentaho Mondrian: Custom Formatting with Cell Formatter


Repost from http://diethardsteiner.github.io/mondrian/2015/07/29/Mondrian-Cell-Formatter.html

Formatting measures in cubes is quite essential for the readability of any analysis results. Pentaho Mondrian features a formatString (attribute of measure declaration in a Mondrian Cube Definition) and FORMAT_STRING (MDX query) options which allow you to define the format in a Visual Basic syntax. Say you want to display 12344 as 12,333 you can easily create following formatting mask: #,###. This kind of approach works for most use cases.

However, sometimes you might required a custom formatting option which is not covered by the Visual Basic formatting options. Imagine e.g. that one of the measures of your cube is a duration in seconds. The integer value that you store in your database is not really a nice way of presenting this measure, unless of course you are only dealing with very small figures. But what if you have e.g. a figure like 102234 seconds. How many days, hours etc is this?

One approach of dealing with this is to create hidden calculated members which break this figure down into days, hours, minutes etc:

measure mdx calculation
days CAST(INT(sec/(60*60*24)) AS INTEGER)
hours CAST(INT((sec/(60*60))-(days*24)) AS INTEGER)
minutes CAST(INT((sec/60)-(days*(24*60))-(hours*60)) AS INTEGER)
seconds CAST(ROUND(sec-(days*(24*60*60))-(hours*60*60)-(minutes*60),0) AS INTEGER)

You could then create a visible measure which concatenates all these invisible measures and displays the figure as 1d 4h 24min 54sec. Example final calculated visible measure:

CAST([Measures].[Days] AS STRING) || "d " || IIF([Measures].[Hours] < 10, "0", "") || CAST([Measures].[Hours] AS STRING) || ... and so forth

However, while this approach works, you will realize that you cannot sort by this measure properly! That’s rather inconvenient.

Fortunately enough, Mondrian also provides a Cell Formatter, which allows you to access the value of e.g. a measure and manipulate it any way for display purposes, but – and this is the very important bit – this does not influence the underlying data type. So in our example, the integer value for the duration will still be an integer value and hence the sorting will work! The other really good point is that you can use various languages to manipulate the value, e.g. Java or JavaScript.

To add a special Cell Formatter, simply nest the CellFormatter XML element within the Measure or CalculatedMeasure XML element. Then nest another Script XML element within this one to specify the Script Language and finally nest your code within this element. Example (this time including weeks as well):

<Measure name="Duration" column="duration" visible="true" aggregator="sum">
    <CellFormatter>
         language="JavaScript">
            var result_string = '';
            // access Mondrian value
            var sec =  value;
            var weeks = Math.floor(sec/(60*60*24*7));
            var days = Math.floor((sec/(60*60*24)) - (weeks*7));
            var hours = Math.floor(sec/(60*60) - (weeks*7*24) - (days*24));
            var minutes = Math.floor((sec/60) - (weeks*7*24*60) - (days*24*60) - (hours*60));
            var seconds = Math.floor(sec - (weeks*7*24*60*60) - (days*24*60*60) - (hours*60*60) - (minutes*60));
            result_string = weeks.toString() + 'w ' + days.toString() + 'd ' + hours.toString() + 'h ' + minutes.toString() + 'min ' + seconds.toString() + 'sec';
            return result_string;
        
    </CellFormatter>
</Measure>

You could of course improve the JavaScript further by only showing the relevant duration portions:

var result_string = '';
// access Mondrian value
var sec =  value;
var weeks = Math.floor(sec/(60*60*24*7));
var days = Math.floor((sec/(60*60*24)) - (weeks*7));
var hours = Math.floor(sec/(60*60) - (weeks*7*24) - (days*24));
var minutes = Math.floor((sec/60) - (weeks*7*24*60) - (days*24*60) - (hours*60));
var seconds = Math.floor(sec - (weeks*7*24*60*60) - (days*24*60*60) - (hours*60*60) - (minutes*60));
if(weeks !== 0){
    result_string = weeks.toString() + 'w ' + days.toString() + 'd ' + hours.toString() + 'h ' + minutes.toString() + 'min ' + seconds.toString() + 'sec';
} else if(days !== 0){
    result_string = days.toString() + 'd ' + hours.toString() + 'h ' + minutes.toString() + 'min ' + seconds.toString() + 'sec';
} else if(hours !== 0){
    result_string = hours.toString() + 'h ' + minutes.toString() + 'min ' + seconds.toString() + 'sec';
} else if(minutes !== 0){
    result_string = minutes.toString() + 'min ' + seconds.toString() + 'sec';
} else if(seconds !== 0){
    result_string = seconds.toString() + 'sec';
} else {
    // always provide a display value - do not return null
    result_string = '0sec';
}
return result_string;

Note: The script has to return a value in any situation – it must not return null, otherwise there can be issues with the client tool. E.g. Analyzer doesn’t display all the results properly if null is returned.

Important: When adding the CellFormatter make sure that you removed the formatString attribute from the Measure or CalculatedMeasure XML Element, otherwise this attribute will take precedence over the CellFormatter.

Amazing isn’t it? If I had only known about this feature earlier on! A big thanks to Matt Campbell for pointing out that Mondrian has a Cell Formatter.

Free Learning Campaign #Packt


packtApacktB Every day Packt Publishing is giving away books for free to help teach new tech skills

From 30th April, 2015 Packt Publishing has thrown open the virtual doors of its new Free Learning Library and offering its customers a daily chance to grab a fresh free eBook from its website. The publisher is encouraging people to learn new skills and try out new technologies and so every day it will be offering a different eBook from its huge list of titles free for anyone to download. The Free Learning Library will be open all year-round but each title will only be up for 24 hours, so make sure you keep checking back to get your hands on the latest book! Packt has well over 2000 titles published and the range of topics that could potentially feature is huge. From AngularJS to Zabbix, there’s going to be something to appeal to everyone – this is a great opportunity to try out a different technology or a new technique. All you’ll have to do is simply click on the day’s free eBook and it will instantly be added to your account. New customers are also encouraged to take advantage, with the offer being a brilliant chance to try out Packt’s great range of books and products – all that’s required is a Packt account. Find out more:http://bit.ly/1Kt6U6w #FreeLearning

Working with Big (lots) Data and Pentaho – Extreme Performance


Originally posted on Codeks Blog:

OK, firstly, I’m not talking proper BigData here.  This is not Hadoop, or even an analytical database.  (Lets not get into whether an analytical database counts as bigdata though!). And it’s certainly not NoSQL.  Disk space we’re looking at 100’s of gigabytes, not terabytes.  Yet this project involves more data than the Hadoop projects I’ve done.

So tens of billions of records. Records that must be processed in a limited environment in extremely tight time windows.  And yes; I’m storing all of that in MySQL!

Hey, wake up, yes, I did say billions of records in MySQL, try not to lose consciousness again…  (It’s not the first time I’ve had billions of rows in MySQL either – Yet I know some of you will guffaw at the idea)

In fact, in this project we are moving away from a database cluster, to a single box. The database cluster has 64 nodes and 4TB…

View original 495 more words

Pentaho Mondrian: The MDX Generate Function


Repost from http://diethardsteiner.github.io/cde/2015/04/23/MDX-Generate-Function.html

This is a brief overview of how you can use the MDX GENERATE() function. All the examples below work with the SteelWheelsSales Cube, which ships with the Pentaho BA Server. Try the examples listed below either with Pentaho Analyzer, the Saiku CE plugin, Pivot4J or Roland Bouman’s Pentaho Analysis Shell (Pash) (The last 3 ones are plugins, which you can download via the Pentaho Marketplace).

The GENERATE() function has two different applications:

  • If the second parameter is a set, it will return the members that exist in both sets ( a UNION() of the sets). You can think of it as a kind of for-each function which checks if members in the first set exist in the second one. ALL can be used as a third parameter to keep any duplicates.
  • If the second parameter is a string expression then the members will be concatenated.

Examples:

Find the members that are in both sets:

SELECT
    [Measures].[Sales] ON 0
    , NON EMPTY GENERATE(
        {[Markets].[Country].[Australia], [Markets].[Country].[USA]}
        , [Markets].[Country].[Australia]
    ) ON 1
FROM [SteelWheelsSales]
Country Sales
Australia 630,623

In this case Australia survives, because it is a member of the first as well as the second set.

For each member of the first set retrieve the first child:

SELECT
    [Measures].[Sales] ON 0
    , NON EMPTY GENERATE(
        {[Markets].[Country].[Australia], [Markets].[Country].[USA]}
        , [Markets].currentMember.firstChild
    ) ON 1
FROM [SteelWheelsSales]
State Province Sales
NSW 305,567
CA 1,505,542

Next we’d like to know the top performing country within each territory:

SELECT
    [Measures].[Sales] ON 0
    , NON EMPTY GENERATE(
        [Markets].[Territory].Members
        , TOPCOUNT([Markets].CurrentMember.Children, 1, [Measures].[Sales])
    ) ON 1
FROM [SteelWheelsSales]
Country Sales
Australia 630623
Spain 1215687
Japan 188168
USA 3627983

Taking the previous example a bit further, we can get the top performing country and customer combination based on sales for each territory:

SELECT
    [Measures].[Sales] ON 0
    , NON EMPTY GENERATE(
        [Markets].[Territory].Members
        , TOPCOUNT([Markets].CurrentMember.Children * [Customers].[Customer].Members, 1, [Measures].[Sales])
    ) ON 1
FROM [SteelWheelsSales]
Country Customer Sales
Australia Australian Collectors, Co. 200,995
Spain Euro+ Shopping Channel 912,294
Singapore Dragon Souveniers, Ltd. 172,990
USA Mini Gifts Distributors Ltd. 654,858

There is also an option to retain duplicates in the results using the ALL flag as the third argument:

SELECT
    [Measures].[Sales] ON 0
    , NON EMPTY GENERATE(
        {[Markets].[Country].[Australia], [Markets].[Country].[Australia]}
        , [Markets].currentMember.firstChild
        , ALL
    ) ON 1
FROM [SteelWheelsSales]
State Province Sales
NSW 305,567
NSW 305,567

Now let’s have a look at a completely different way we can use the GENERATE() function:

Let’s show the territories with all their countries next to each other in one line. We can define a delimiter as the third parameter of the GENERATE() function:

WITH 
MEMBER [Measures].[Countries] AS
    GENERATE(
            [Markets].CurrentMember.Children
            , [Markets].CurrentMember.Name
            , "-"
        )
SELECT
    {[Measures].[Countries]} ON 0
    , NON EMPTY [Markets].[Territory].Members ON 1
FROM [SteelWheelsSales]
Territory Countries
#null Germany-Ireland-Israel-Netherlands-Poland-Portugal-Russia-Singapore-South Africa-Spain-Switzerland
APAC Australia-New Zealand-Singapore
EMEA Austria-Belgium-Denmark-Finland-France-Germany-Ireland-Italy-Norway-Spain-Sweden-Switzerland-UK
Japan Hong Kong-Japan-Philippines-Singapore
NA Canada-USA

As mentioned earlier, this is just a brief intro the extremely useful GENERATE() function. I hope that the example gave you some ideas on how to use this function.

Install Pentaho BI Server 5 Enterprise Edition with PostgreSQL repository


Reblog from:

Install Pentaho BI Server 5 Enterprise Edition with PostgreSQL repository.

Pentaho provides different ways to install Pentaho BI server. Each method has its own flexibility in installation.
1. Installer mode – Easy to install BA & DI server & tools in one flow with default PostgreSQL repo & default Tomcat server. (New Postgres installed on port 5432.)
2. Archive mode – BA server installed with own BA repository & default Tomcat server. Necessary tools need to be installed manually.
3. Manual mode – BA server installed with own BA repository & own application server (Tomcat or JBoss). Necessary tools need to installed manually.

We have a Postgres instance running on our server and are good with Tomcat as application server so Archive mode of installation is suitable for us. Pentaho installation requires two things be installed before starting with Pentaho installation.

  • Java 7
  • PostgreSQL

Archive mode installation files can be accessible only to license purchased users. Download biserver-ee-5.x-dist.zip from Pentaho customer portal with account credentials here: https://support.pentaho.com/forums/20413716-Downloads

Unzip the archive file and you can see the installation files inside extracted directory.

1
2
3
$ unzip biserver-ee-5.x-dist.zip
$ cd biserver-ee-5.x;ls
install.bat  installer.jar  install.sh  license.txt  README.txt

In remote servers Pentaho can be installed on console mode with ‘-console’. Accept the license and provide the installation path to install Pentaho BI server.

1
$ java -jar installer.jar -console

Find biserver-ee directory under the installation path and set sh files to executable mode.

1
2
$ cd biserver-ee;
$ find . -type f -iname '*.sh' -exec chmod a+x {} \;

Let’s create repository databases by running queries in SQL files located at biserver-ee/data/postgresql.
quartz, hibernate and jackrabbit databases will be created by executing these SQL files. Database names, usernames and password can be changed by modifying in SQL files if required.

1
2
3
4
5
$ cd biserver-ee/data/postgresql
$ psql -U postgres -p 5432 -a -f create_jcr_postgresql.sql
$ psql -U postgres -p 5432 -a -f create_quartz_postgresql.sql
$ psql -U postgres -p 5432 -a -f create_repository_postgresql.sql
$ psql -U postgres -p 5432 -a -f pentaho_mart_postgresql.sql

Pentaho uses postgresql as default database and files are configured to use postgresql. So just verify the database_name, username and password to work with installed postgresql and databases created.

– biserver-ee/pentaho-solutions/system/quartz/quartz.properties

1
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate

– biserver-ee/pentaho-solutions/system/hibernate/hibernate-settings.xml

1
    <config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>

– biserver-ee/pentaho-solutions/system/hibernate/postgresql.hibernate.cfg.xml

1
2
3
4
5
    <property name="connection.driver_class">org.postgresql.Driver</property>
    <property name="connection.url">jdbc:postgresql://localhost:5432/hibernate</property>
    <property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>
    <property name="connection.username">pentaho_user</property>
    <property name="connection.password">password</property>

There are more occurrences in this file. Carefully do the necessary changes in all the places.
– biserver-ee/pentaho-solutions/system/jackrabbit/repository.xml
– biserver-ee/pentaho-solutions/system/jackrabbit/repository/workspaces/default/workspace.xml

– biserver-ee/tomcat/webapps/pentaho/META-INF/context.xml

1
2
3
4
5
<Resource name="jdbc/PDI_Operations_Mart" auth="Container" type="javax.sql.DataSource"
            factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
            maxWait="10000" username="pentaho_user" password="password"
            driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
            validationQuery="select 1"/>
1
Download postgresql and h2 drivers then place it under biserver-ee/tomcat/lib

postgresql-9.x.jdbc4.jar
h2-1.2.x.jar

Change Tomcat port on these two files to run Pentaho on different port.
Specify the Pentaho solutions path, server URL and port in web.xml of Tomcat webapp.

biserver-ee/tomcat/webapps/pentaho/WEB-INF/web.xml

1
2
3
4
5
6
7
8
9
        <context-param>
                <param-name>solution-path</param-name>
                <param-value>$INSTALLATION_PATH/biserver-ee/pentaho-solutions</param-value>
        </context-param>
        <context-param>
                <param-name>fully-qualified-server-url</param-name>
                <param-value>http://localhost:8080/pentaho/</param-value>
        </context-param>

Pentaho can be configured to run on different ports by changing ports on Tomcat server.xml and web.xml
– biserver-ee/tomcat/biserver-ee/tomcat/server.xml

1
2
3
    <Connector URIEncoding"UTF-8" port"9090" protocol"HTTP/1.1"
               connectionTimeout"20000"
               redirectPort"8443" />
1
2
3
4
5
- biserver-ee/tomcat/webapps/pentaho/WEB-INF/web.xml
         <context-param>
                <param-name>fully-qualified-server-url</param-name>
                <param-value>http://localhost:9090/pentaho/</param-value>
        </context-param>

Install license

A license needs to be installed to use Pentaho. Navigate to the license-installer directory in installation path. Feed license file to install_license.sh, separating more than one license file with spaces.

1
$ ./install_license.sh install ../license/Pentaho\ BI\ Platform\ Enterprise\ Edition.lic

Install plugins
Archive mode of installation installs only BI Server. Necessary plugins can to be installed manually. Here install the plugins for reporting, analyzer and dashboard. Plugins are available at the same place where download BI server. Download these three files and place at any path on server
Reporting – pir-plugin-ee-5.x-dist.zip
Analyzer – pdd-plugin-ee-5.0.0.1-dist.zip
Dashboard – paz-plugin-ee-5.0.0.1-dist.zip

All the plugins installed through same procedure
– Unzip the plugins and navigate to extracted directory
– run installer on console, accept the license and provide $INSTALLTION_PATH/biserver-ee/pentaho-solutions/system as location to install plugins

1
$ java -jar installer.jar -console

Lets start the BI server

1
biserver-ee$ ./start-pentaho.sh

Install the licenses for the plugins by login as admin user (default – Admin:password) or install through the command line
Administration -> License -> install licenses for plugin by click +

Troubleshooting:

1
2
biserver-ee$ tail -f tomcat/logs/catalina.out
biserver-ee$ tail -f tomcat/logs/pentaho.log

If the pentaho.xml is present at tomcat/conf/Catalina directory, delete it. It will be generated again when you start the BA Server.

Start and stop the BI server :

1
2
biserver-ee$ ./start-pentaho.sh
biserver-ee$ ./stop-pentaho.sh