Thursday, December 23, 2010

Pivot table: OBIEE 11g Vs Qlikview

With the launch of new version of Oracle BI OBIEE 11g, some new features and enhancements got added.
I installed the new version and started looking at the new features. This is completely a new experience. The look and feel is very much improved now and is much better.
Here in this post I will discuss only about the new feature introduced in Pivot charts. Now the pivot has the ability to expand and collapse the levels using the + and - signs as shown below.
For those who have used or worked only on OBIEE before it will be a new way of interacting with a pivot but even for some niche tools like Qlikview, this is a very basic feature.
If we just compare this new feature of OBIEE11g with Qlikview from end user perspective, it stands nowhere.
Now lets discuss how we can achieve this in OBIEE11g and then we will compare it with Qlikview:
1. You first need to create a hierarchy in the presentation layer: For example, I have created a presentation hierarchy named "Products" having 3 levels "LOB" - "Type" - "Prod Name" as shown below.
Now when a report developer or user go to the Answers to create a pivot, we need to select this Hierarchy in the pivot as shown below.

Now if you run the report you will see the results like this:
Till here it was good but just think a user would just want to have just 2 levels "LOB" and "Prod Name" in the present pivot. To achieve that, the user needs to ask the developer to create a new presentation hierarchy with only required two levels or make modification to the existing presentation hierarchy and remove the "Type" level.
On the similar lines if a user require this pivot to have a column from some other dimension say for example "Time". If we just add a column from the "Time" dimension lets say "Year", the pivot will look like this:
This is definitely not what we need.
So we have seen the limitations and now we will see how easily an end user can create such reports without any help from developers or IT.
Simply create a Pivot table report and select the dimension columns and fact column. Here in this example I have selected the 3 columns which has been used to create the pivot in OBIEE11g. This is how the report will look like:

This is really simple and no hierarchies needs to be created anywhere. Now we will try to add some Time dimension column in the existing pivot and see how it looks. Simply add the column in the dimensions tab and arrange by promoting or demoting to see it wherever we want. I have added it to the top and this is how it will appear:
This is really very simple and effective and the users really find it easy to see how they want to see it.
Going one step ahead lets say I want to have the subtotals of levels to be displayed in the pivot, Qlikview does it by simply selecting some of the available options. In Properties-Presentation tab there is a check box which says show Partial sum. You can also choose for the level for which you require the partials sums above or below. In OBIEE11g, I could not find anything other than the grand total sum. This is how it finally looks in Qlikview:
This is really useful. Also there is no option of Expand All for the entire pivot or for a selected level. You can just expand a particular level value that means you needs to click on all the + signs one by one to expand the pivot whereas in Qlikview you can expand the complete pivot or a particular column or level.

These small small options may not sound that important to some of us but for an end user it makes the entire experience interactive and helps him feel more independent and brings the feeling of ownership.
Till next time...

Manohar Rana

Monday, December 13, 2010

Column Selector feature in OBIEE and Qlikview

In one of the projects, there was a requirement to have column selector. There should be some fixed columns in a report and some columns which the user should be able to change by simply selecting the column from a list and not going to the design mode or ad hoc mode.
Here we will see how we did it in OBIEE using the column selector feature and how we can do it in Qlikview.

Lets first create a simple request in OBIEE Answers as shown below. I have selected two dimension column and a fact column here. Click on Add view and from the list select Column Selector.

Now you will see the columns you have selected in column selector mode as shown below.

Select the check box below column 1 and click on the columns from the list of columns available in the left. Similarly do it for column 2 by clicking on the check box and selecting columns. Uncheck the check box which says" Automatically refresh when a new column is selected". Click on OK and you will see the column selector present in your compound layout.

Now you can check by selecting different columns and clicking on Go button to see that the columns in the reports gets changed.
You can also have some columns fixed and some columns changeable. To do so, create a dummy column "None" and make it hidden in the column properties as shown below.

Now go to the Column selector view and select the columns you would like to see in the list as shown below.

Click Ok and come to the compound layout and you will see only one column which is a fixed one. In the column selector, you will notice that None column is selected. When you select any other column from the column selector, the new column will get added to the report as shown below. If you again select 'None' column, it will again hide.

Isn't it a nice feature and very easy to use for end users and equally easy to develop.

Now we will try to implement the same feature in Qlikview and see to what level we succeed.
Create a simple report in Qliview again using 2 dim columns and one fact column.

Now lets create two Groups (Cyclic) and add the columns required in these groups as shown below.

Now, select these two newly created cyclic groups in the report as shown below.

Click Ok to see how it works. Just close to column headers, there will appear cyclic icons. When you click on this icon, the list of columns will appear and when selected the column will replace the existing column.

This is also very simple. But I did not find anything to make some columns fixed and some columns changable. I tried creating a dummy column and added that dummy column in cyclic group. But as the these groups are embedded in the report itself and not a seperate entity as in OBIEE, the dummy column when selected shown all blank rows as shown below.

This would definitely not be acceptable to the users. So I tried to make a column hidden based on a condition but that made the cyclic icon hidden alongwith the column hence the user can not select any other column from that group.
You can hide any column based on certain condition as shown below but it did not served the purpose here.

I am not sure if there is some way to accomplish this but with out of the box features and not by writing some macros.
If anyone has any pointers, please do suggest.

Manohar Rana