General
Usage
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.
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).
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?
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.
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.
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.
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.
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.
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.
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.
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);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.
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.
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> #ff0000 </#if>
It's just a simple examle and there's much more possibility of what it can do as shown in this video example.
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.
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.