Where can I use Pivot4J?

If you are a developer, you can build a pivot grid style GUI frontend to any OLAP server with Pivot4J. Also, you can use it as a normal OLAP client to browse and analyse your own OLAP data.


But doesn't JPivot already do that?

Yes. But unfortunately, the project is no longer actively maintained since 2008. Moreover, JPivot started as a JSP tag library so even its design endeavors to separate the model layer from the presentation layer, there's still some basic assumptions in the codebase which is no longer appropriate.

And as the code was written long ago, and never intended to be used apart from its JSP component, it's not very pleasant to work with and rather hard to maintain (i.e. no generics, WCF dependency, inconsistent class hierarchy, and cumbersome build process, and etc).


What about Olap4J?

Olap4J has very solid backend design and implementation from which Pivot4J also benefits greatly. And it also has some APIs to do what exactly Pivot4J is intended to do (notably, the org.olap4j.query and org.olap4j.transform package).

However, there's no working implementation for query transform operations so in order to make basic pivot operations like drill down or sort, you need to manipulate the MDX parse tree yourself.

Of course, we could just add those missing implementation classes instead of starting a new project. But writing a robust query transform API is not an easy task, and some of Olap4J's design choices - i.e. lack of parent node reference, immutable parse tree, and etc - makes it even harder to replicate JPivot's core functionalities while preserving the MDX query structure.

On the otherhand, JPivot already has a feature rich and well tested implementation for query transformation with considerations for various corner cases. So why not just reusing that codebase, after refactoring it to suit the modern design standard?


What is the difference between Pivot4J and Saiku?

Although, both projects intends to provide an alternative for now outdated JPivot, Saiku is rather like a standalone OLAP analysis application while Pivot4J aims to be a pivot component model which can be easily integrated with any other project.


What is Pivot4J Analytics and how is it different from the Pivot4J itself?

Pivot4J itself is a backend API for building a pivot style OLAP client application in any kind of UI technology. On the other hand, Pivot4J Analytics is a standalone client application built using the Pivot4J core API.

It is based on JSF, and you can use it as a drop-in replacement for the old JPivot web application also.


I heard Pivot4J Analytics is a sample application. Can I use it in a production environment?

It depends on the requirement you need. Even Pivot4J Analytics started as a reference implementation and a sample application to demonstrate usage of the core API, now it has more features than the original JPivot web application. So, it can be used in many situations as a full fledged OLAP client but it still lacks certain advanced features like single sign on or ACL support.

In case you need such features, you can build your own application based on Pivot4J, or use a Pentaho BI plugin version of the Analytics client, as it delegates all the security related tasks to the platform itself.



Where should I start in building my own UI with Pivot4J API?

Unlike JPivot, Pivot4J core does not come with any specific UI implementation. However, you can use the renderer API to build the actual grid UI of a given PivotModel instance. It's very flexible so you can easily create your own renderer class to generate output data in a format suitable for your specific need.

Pivot4J also includes a concrete implementation which generates generic HTML source as well. So, you can use it, or extend it to make your own implementation, or you can build it from the scratch extending from the AbstractPivotUIRenderer class.


I don't want to write my own client from the scratch. Can I just customize the Pivot4J Analytics instead?

Believe me, writing your own client with the core API is not that much difficult at all! However, if you all you need is just a few minor tweaks on Pivot4J Analytics, then sure, you can do whatever you want with it as long as you honor the license.

However, unlike the core API itself, the Analytics client never intended to be something easily extended, you might find it rather difficult to customize or the the code might change without notification. So, if you decided to take this route, please proceed with care.


How can I add drill down or sort buttons to the generated HTML?

Override the startCell method of AbstractPivotRenderer class then write suitable event handling code(write an 'a' tag, adding an 'onclick' attribute or whatever) using the CellCommand and CellParameters class. Generally, you don't have to write separate code for different operations.


I have an existing connection pool. How can I use it?

If you intend to use an existing connection pool configured in your application server, just look up the DataSource instance from JNDI or get a DI then wrap it with the WrappingOlapDataSource class to use it.


How can I get a reference of a member which is not included in the query?

If you want to reference a member which is not included in a current CellSet, the easiest way to do it is using following code :

Cube cube = model.getCube();

Member member = OlapUtils.lookupMember("[Time].[1998]", cube);


What do you mean by non-MDX based aggregation? Isn't there some MDX function to do that?

Yes, Mondrian for example provides a MDX function to aggregate measures and there's nothing wrong with it. Server side aggregation is more efficient too, so if you are comfortable with using it, you can continue to do so.

However, not all OLAP backends provide such a function and syntax is different from one another. So, it's not easy for OLAP clients to handle them correctly and of course, not everyone wants to meddle with MDX themselves when all they need is a simple total column added to the grid.

To summarize, you can just think it as a vendor independent, slightly less efficient, but more intuitive alternative to a MDX aggregation function.


What do you mean by conditional formatting? Can't we do that in MDX already?

In this case, no, I think something is seriously wrong with the traditional approach, using FORMAT_STRING and CELL PROPERTIES. If developers normally stay away from embeding CSS style rules in their SQL query, why should we do that with MDX?

To make the matter worse, if we need some conditional formatting, like say 'only sales below $10,000 in this quarter show in red' there's no other way than nesting multiple IIf functions and mix them with some CSS values all in the MDX query itself. It will make the MDX very hard to understand and maintain and it will be a nightmare for OLAP client to parse them correctly too.

We believe that formatting like colors for fonts should be handled on the client side, separate from the MDX itself. It will make life easier for everyone, users and server developers, client developers alike.


Ok, I'm sold! But when I open the properties dialog, it looks too simple and I don't see much use. I certainly don't want to paint all values in red! Where's the flexibility you promised?

There are two different UI for the conditional formatting feature, but as for now only one of them has been implemented. It's less intuitive but fortunately, more flexible as well.

But in order to utilize it to its full potential, you need to use a simple expression language to describe what you want to achieve. For example, if you want to show all the measure values below 15,000 in red color, you can use below expression in the foreground color property of the cell area :

<#if cell?? && cell.doubleValue < 15000>

It's just a simple examle and there's much more possibility of what it can do as shown in this video example.


Is there some documentation or a manual available? Or where can I learn about syntax of the expression language used in the conditional formatting feature?

Unfortunately, lack of documentation is one of the weakest point of the project so far. For now, you need to learn how to use them from the source code itself and for some cases you can check the relevant test cases for reference too.

I hope the situation could be improved before the 1.0 release. Till then, if you have any question about API usage, please ask them in our mailing list or on the IRC channel.

As to the expression language syntax, you can refer to the FreeMarker project's documentation here, as Pivot4J by default uses it as its expression language support. You can also see the API documentation for the RenderContext class to see what context variables are available when it renders each cells.


It does not work!

Sorry for the inconvenience. It's still in an early stage of development so please don't expect it to run flawlessly. If you encounter a bug or having a problem, feel free to ask a question in the support forum, or open a bug report.


Back to top

Last Published: 2017-10-15 07:13:59.

Reflow Maven skin by Andrius Velykis.