List of problems with Pivot4j in Pentaho

[sanjoyus]

Friends these are the list of problems I am facing while using Pivot4j in Pentaho BI Tools Major Issues

1. We can have maximum two attributes from each Dimension.
2. We can have maximum four attributes from any combination of dimensions with two attributes from each dimensions.
3. If any attribute is dragged to filter area then the entire dimension with all attributes are moving to the Filter area and further query on any other attribute of that dimension is not possible.
4. The attribute on which I am filtering the query result will not be shown in the result and thus making the entire result set meaningless.
5. For dimensions like Customer which is having large data is taking too much time to execute without having any filter.
6. It is taking too much time for execution for large real time data (without any filter). Some time it is taking too much time and throwing the following error: Unhandled exception has occurred mondrian.olap.QueryTimeoutException: Mondrian Error:Query timeout of 300 seconds reached.

Minor Issues

1. It will show all the combination irrespective of whether it is having value or not (empty/null values will be included)
2. There is no cancellation option to cancel the query if it is taking too much time.
3. To get the field name we have to click twice I) Hierarchy II) Actual field name

[sanjoyus]

Please can any one help me to solve these issues

[mysticfall]

@sanjoyus Hi, As I already wrote in one of your posts from the Pentaho forum, I have hard time understanding the exact nature of the 'two attributes' problem.

It'd help me much if you could elaborate a little bit more (for example, what do you mean by 'attribute').

As to the filter problem, as far as I can see, its by nature of OLAP cube that it prevents the same dimension from appearing on more than one axis (filter axis included) at the same time.

The feature to allow users to canel long running queries is already filed as #102. We're about to release the 0.9 version, so I'm afraid it won't be making for this release. But I think it's quite a meaning ful enhancement so I'll definately consider it for the next release.

If you see exception like QueryTimeoutException then I believe there's little to do from Pivot4J's side. It might help if you consult Mondrian's documentation on how to optimize the schema.

And lastly, I'm afraid I failed again to understand what do you mean by getting the 'field name', or where did you click twice to achieve the desired result.

Sorry if it didn't help much. But if you can help me understand them further, I'll see what I can do about them.

Thanks.

[sanjoyus]

Thank you so much for your reply since my requirement is very urgent and very important. I will explain you first the first two points mentioned in my List. By attributes I mean level like I have created a dimension 'Project' and its levels are Projectname, Projectdescription, ProjectCode which are in separate hierarchy then in the query result I am able to drag only two such levels like Projectname, Projectdescription then if I drag the third one like ProjectCode also then in the result projectdescription is getting replaced by projectcode but my client wants to see measures with all three levels. This is my first point. My second point is that suppose I have three dimensions project, customer, Program each having three levels like Customer with levels like customerfirstname, customerlastname, customercustomercode in separate hierarchies and dimension Program having levels like ProgramName, Programcode and ProgramType then in the queryResult I am able to see the measures with combination of any four level only with two level from each dimensions.For example I can see measure by ProgramName, Programcode ,customerfirstname, customerlastname, then If I drag any other level from any dimensions it is not allowing me suppose I drag projectcode now customerlastname will get overwritten by projectcode keeping the total number of levels to 4 only. But this restriction is not right since I should see measures with as many combination of levels as I wish I hope I am able to explain you my requirements.Please provide me some solutions how to drag as many levels I wish and drill down to see the measures accordingly. Thank you so much Sanjoy

[mysticfall]

Thanks for the clarification. Now I understand most of what you said, but I have one more question about the structure of your schema.

You said that 'Projectname', 'Projectdescription', and 'Projectcode' are levels but in a separate hierarchy. By this, did you mean that they are each have their own hierarchies (one for each)?

And can you explain me about the motivation for such a design please?

For it was my understanding that it makes little sense to drop different levels in different hierarchies in a same dimension at the same time. For instance, typical use of multiple hierarchies is to represent a different scheme of representing the same data, like [Time] and [Fiscal Time]. Problem is, there's no way to know how their child levels are related to each other, so if I'm not mistaken you just can't mix and match levels from different hierarchies in the same hierarchy and expect them to make a coherent structure.

Actually, I did not expect this kind of usage, so I just found out Pivot4J has some problem dealing with such a request to drop another level from a different hierarchy when there's already other levels from the other hierarchies in the same dimension.

I believe in that scenario, the most reasonable thing would be to replace all the existing levels, not adding to them.

I also notice that Mondrian itself seems to allow such a MDX as it didn't throw any error, but returns a result that is quite unexpected. For example, I could run the following MDX without getting an error :

SELECT {[Measures].[Unit Sales]} ON COLUMNS, CrossJoin({[Time].[1997], [Time].[1998]}, 
{[Time.Weekly].[1997], [Time.Weekly].[1998]}) ON ROWS FROM [Sales]

But, of course, the resulting data does not make any sense and we don't have anything to do how Mondrian handles such a situation from Pivot4J's side.

I admit my experience in OLAP is rather limited, so I could be wrong in what I just said. In that case, please don't hesitate to correct me where I'm wrong and I'll try my best to understand it.

[sanjoyus]

Xavier Thank you so much for your quick reply and your help.I will try to explain why I am using levels under different hierarchy now. First point If I use all levels under same hierarchy under one dimension then the result is wrongly grouping for example if I have one hierarchy having first level as 'Projectcode' second level as 'Projectname' and third level as 'Projectdescription' under dimension Project then suppose I have two different projectcode value with same projectname then the projectname will come twice when I am grouping the measures by projectname, whereas if I keep these levels under separate hierarchies then the grouping will be correct since each levels are independent of each other and the result is also coming correct. My question is why Pivot4j is not allowing me to show more than two levels form each dimension and maximum four levels from any combination of dimensions. See in case of the example that you have showed above I have a question on that. First - you have taken only four levels on ROWS, suppose I want to have another level more to add will it allow me ? In my case it is not allowing me ,I want to know how to have more levels. Suppose I want to see the measure 'incentive' for a particular program year for a particular projectcode and projectname under that projectcode and projectdescription under that projectname so here I need four measures like program year projectcode projectname projectdescription incentive 2013 101 AA aaaaa 100 102 BB bbbbb 200 CC ccccc 300 2014 101 AA aaaa 400

Please show me how to achive this in Pivot4j. I hope I am able to explain you. Please ask me if you need some more clarification. thank you Sanjoy

[mysticfall]

@sanjoyus, Thanks to your explanation, I can now see why you wanted to keep those levels in separate hierarchies.

However, I'd like to strongly suggest you to either, redesign your OLAP schema, or reconsider if an OLAP backend is really appropriate for what you want to achieve, as such an approach is not something OLAP is excepted to support very well.

In your example, name, code, and description are all just attributes (as you initially described) of the same aspect of your data - the project. When using OLAP, you are expected to either make them attributes of a member (like unique name, or description), or properties.

If that is not feasible, you might need something like Excel's pivot grid feature which will construct a pivot style grid out of any kind of tabular data. However, that's not a typical OLAP client's job to do that.

I believe modelling those attributes as member properties would be the best approach, if the data itself is suitable OLAP analysis. Pivot4J supports member properties though it's rather rudimentary at the moment. Notably, there's no GUI configuration option to enable it, so I guess it might be problematic for your scenario.

But in case all you need is to show all such properties, you can just add below line to achieve it :

renderer.setPropertyCollector(new NonInternalPropertyCollector());

I guess we need to implement a dialog to allow users to configure this feature soon.

Hope this could be of some help. Cheers,

Xavier

[sanjoyus]

Thank you so much for your help Xavier. My data structure and OLAP structure is working perfectly with SSAS but I would not mind to change my OLAP Schema structure for Pivot4j if required. Can you please send me any code or script where you have used 'renderer.setPropertyCollector(new NonInternalPropertyCollector());' and can you please show me with steps how to achive this and advantage I will get using this line. regards Sanjoy

[mysticfall]

@sanjoyus Glad to know that you could modify your schema. Even if SSAS permits unconventional usage of level definition like this, I believe it would be a good practice to keep OLAP cube's structure sensible.

As to the necessary code, you can passt a property collector instance to the TableRender instance you're using to render the view at any time before actual rendering happens.

If you're using the pivot4j-analytics, then a good place to put this code would be inside the ViewHandler.initialize() method, like this :

        this.renderer = new DefaultTableRenderer();
        renderer.setPropertyCollector(new NonInternalPropertyCollector());

If you find any trouble, please le me know. Hope it helps!

[sanjoyus]

Hi Xavier, I am using Pivot4j Analytics from Pentaho Community edition .please tell me where or which folder have this ViewHandler.initialize() method, so that I can paste the code there. regards Snjoy

[mysticfall]

Sorry, it's a source level modification so you need to build the project.

I understand it might be a big task for casual users. I'll try to add a configuration option to do it without building from the sources this weekend.

By the way, as a reminder, it will only work for your scenario when those attributes you mentioned are modelled as member properties, and currently using the NonInternalPropertyCollector means showing all such properties indiscriminately.

I'll update this issue with instruction when it's done.

Thanks for the patience.

[sanjoyus]

Thank you so much Xavier ,I will wait for the changes that you are incorporating and please make those changes also so that I can view more than four columns in the query result frame ,that is what my client is interested in. regards Sanjoy

[mysticfall]

I just committed the relevant changes to make property collectors to be configurable. As I said, it's very rudimentary at the moment.

You can uncomment the relevant part in pivot4j-config.xml as below to make it show all non internal properties, after you install the latest snapshot :

    <render>
        <!-- Show all non-internal member properties -->
        <propertyCollector type="non-internal" />
    </render>