Tag Archives: obiee customization

Creating a pivot table view with mixed granularity

Hello everyone.

I just realized that this post is inspired by Venkat.

I’m finally posting a solution to the problem I’ve identified a while ago related to a specific Pivot Table View. I think the final outcome should look something like this image:

Pivot table view

To get this result – I’ve used the “New calculated column” menu on the pivot table (circled in red):

OBIEE pivot table menu
OBIEE pivot table menu

Then, what you really need to have in the Columns is the most granular level column (sounds redundant – in this case it’s a Date column). I used Excel to quickly generate formulas, such as this for Q1.  It’s really easy to generate these formulas below. I hope it’s pretty self-explaining – you add things up in a granular level – this way you can combine things – that you wouldn’t be able to combine otherwise:

‘1/1/2007’+’1/2/2007’+’1/3/2007’+’1/4/2007’+’1/5/2007’+’1/6/2007’+’1/7/2007’+’1/8/2007’+’1/9/2007’+’1/10/2007’+’1/11/2007’+’1/12/2007’+’1/13/2007’+’1/14/2007’+’1/15/2007’+’1/16/2007’+’1/17/2007’+’1/18/2007’+’1/19/2007’+’1/20/2007’+’1/21/2007’+’1/22/2007’+’1/23/2007’+’1/24/2007’+’1/25/2007’+’1/26/2007’+’1/27/2007’+’1/28/2007’+’1/29/2007’+’1/30/2007’+’1/31/2007’+’2/1/2007’+’2/2/2007’+’2/3/2007’+’2/4/2007’+’2/5/2007’+’2/6/2007’+’2/7/2007’+’2/8/2007’+’2/9/2007’+’2/10/2007’+’2/11/2007’+’2/12/2007’+’2/13/2007’+’2/14/2007’+’2/15/2007’+’2/16/2007’+’2/17/2007’+’2/18/2007’+’2/19/2007’+’2/20/2007’+’2/21/2007’+’2/22/2007’+’2/23/2007’+’2/24/2007’+’2/25/2007’+’2/26/2007’+’2/27/2007’+’2/28/2007’+’3/1/2007’+’3/2/2007’+’3/3/2007’+’3/4/2007’+’3/5/2007’+’3/6/2007’+’3/7/2007’+’3/8/2007’+’3/9/2007’+’3/10/2007’+’3/11/2007’+’3/12/2007’+’3/13/2007’+’3/14/2007’+’3/15/2007’+’3/16/2007’+’3/17/2007’+’3/18/2007’+’3/19/2007’+’3/20/2007’+’3/21/2007’+’3/22/2007’+’3/23/2007’+’3/24/2007’+’3/25/2007’+’3/26/2007’+’3/27/2007’+’3/28/2007’+’3/29/2007’+’3/30/2007’+’3/31/2007′

Calculated item OBIEE
Calculated item OBIEE

The same logic works for Q2,3,4. For individual dates, it would be the same idea:

screen5

And for months, it’s the same logic:

screen6

To sum up  – by using the “calculated item” feature of pivot table view – you can create reports with mixed granularity on them. The pros are that you’ll be able to support legacy views (in some instances it’s absolutely critical to mimic existing view). The cons are that it involves some manual entry / data manipulation and that it’s not very flexible (I couldn’t find out how to use variables in this example).

Last thing – it seems as first column gets added on the left and the consequent ones to the right – keep this in mind while designing this view – since I’ve noticed you can’t really manipulate with the locations of the calculated columns.

Creating a table view with two levels of headers

This tutorial applies to:Business Intelligence Server Enterprise Edition – Version: 10.1.3.4.0 [1900] – Release: 10g

The question was rather frequent – “How to create a table view with two levels of headers?”

i.e. the high-level heading as the table title heading and then another level of heading where there is one sub-heading/title for one set of columns and a different sub-heading for another set of columns

The high-level heading can be created by adding a title view for this.

And then for 2nd level of sub-heading that should be different for one set of columns and another sub-heading for another set of columns, you can achieve this by displaying the table headings as well in addition to the column headings and then set the custom table heading appropriately for the respective set of columns that require the same heading.

To do this, you need to follow a 2-step approach
1) go to the table view and edit the table view properties. Set the ‘display column & table headings’ property to ‘as separate rows’ instead of ‘only column headings’.
2) Then go to each column and set the custom table heading. Set the same table heading for all the columns that require the same heading.

Direct Query Security Options

This is an interesting one. I can see what they’ve tried to accomplish with that, however, to me it sounds as hammering nails with a golden watch. The whole point of OBIEE is to isolate users from PL/SQL and make their life easier. That’s why I get surprised that there’re always numerous questions regarding using stored procedures in OBIEE on OTN forums.

I wonder if you are using many direct query requests at all in your systems?

Someone has been investigating the Direct Query security options under the Admin menu, Manage Privileges in Analytics.

They wish to allow Web Administrators to be able to write direct query requests, and want users to only be able to run the requests and not be able to modify the SQL/Criteria tab.

They have accomplished this by setting the “Issue SQL Directly” option to Web Administrators only, the “Edit Direct Database Requests” option to Web Administrators only, and the “Execute Direct Database Requests” to All Users. This works fine. Now what they want to allow the users to modify the requests but only be able to view the Results tab. The idea here is to not let the users modify the SQL (criteria tab), but to allow them to build pivot tables and charts off of the dataset.

Is this possible? What security need to be set to accomplish this? And the answer was

This is not currently possible

Removing My Dashboard

Customer want to take away from all users ‘My Dashboard’. They want that the users only have the choice between the ‘common Dashboards’.
But the users (the report creators) need My Folders.
So Manage Privileges – Catalog – Personal Storage (My Folders and My Dashboard) does not fit exactly.

Customer has created default shared dashboards that they want the users to see/read by default, but they want to restrict user access to the Shared Dashboard folders.
They want users to be able to access their My Folders directory, but not have access to their My Dashboard link or My Dashboard folders. This is not possible with out of box functionality.

The following enhancement request has been created to benefit the users.

BUG 7665499 – TAKE AWAY FROM ALL OUR USERS ‘MY DASHBOARD’

Solution

The only way to deny access to ‘My Dashboard’ is via the “Personal Storage (My Folders and My Dashboard)” Catalog privilege. Denying this privilege to a group or user will take away access to both ‘My Dashboard’ and ‘My Folders’ – currently, you can’t separate the two. Therefore I have logged the following Enhancement Request.

BUG 7665499 – TAKE AWAY FROM ALL OUR USERS ‘MY DASHBOARD’
Alternatively, here is a suggestion of using current functionality would be:

1. Create a web catalog group and dashboard via “Manage Presentation Catalog Groups and Users” to be used as the default user dashboard.
2. Grant the ‘Everyone’ web group the appropriate privilege to this catalog group via “Manage Presentation Catalog” – e.g. ‘Read’.
3. In the admin tool, create a session init block to set the ‘PORTALPATH’ session variable to the webcatalog path of the default dashboard page.
3.a. You can find the path by looking at the properties of the folder under ‘_portal’ in the catalog group folder the default dashboard was created in – e.g. “/shared/user_default/_portal/Home”.
3.b. The init string could either select the literal path (e.g. select ‘/shared/user_default/_portal/Home’ from dual) so all users would be routed to the same dashboard. Alternatively, the path could be stored externally, e.g. in a table, and selected from it per userid. That way, it would be manageable outside of the admin tool.

This would force all users to the default dashboard specified in the path used to populate PORTALPATH for all users.
One issue that may occur is with existing users that have set their ‘Default Dashboard’ to anything but ‘Default’ in the ‘My Account’ link – they may be required to set it to ‘Default’, then log-off/log-on for the default dashboard setting to take affect.
I know of no way to get around this potential issue for this scenario short of each user making the change OR deleting users from the webcat such that a subsequent logon will ‘re-initialize’ their account (which will then use the init block set PORTALPATH value). Both of these options seem severe, and the second may prove problematic as long as the ‘My Account’ link is still active.

How to set session variables using url variables

The goal is to set session variables using url variables, but can you also do this for the user and password ?
url variable (&Upwd) is not passed to session variable USER_PWD.
The variable USER is correctly passed, the variable USER_PWD is not!

Solution

The steps to set an OBIS session variable via a URL call utilizing the
instanceconfig.xml tag should be as follows

1. Create a session init block that will act as a ‘placeholder’ for the
session variable to be set via the url call – the variable can be set to
anything.

2. Set the ‘Enable any user to set the value’ option for the variable.

3. Add the following tag block to the instanceconfig.xml file anywhere
between the <ServerInstance></ServerInstance> tags:

<Auth>
<UserIdPassword enabled=”true”>
<ParamList>
<Param name=”NQ_SESSION.TEST_VAR”
source=”url”
nameInSource=”SETVAR”/>
</ParamList>
</UserIdPassword>
</Auth>
“TEST_VAR” should match the session variable name (case sensitive).

4. The following option will need to be appended to the OBI url passed –
&SETVAR=’variable value to pass. So a full example would be:
http://localhost:9704/analytics/saw.dll?Dashboard&nqUser=USER001&nqPassword=US
ER001&SETVAR=SomeValue

However, note that you cannot set the value of any System Security Session variable (specifically USER, PROXY, GROUP and WEBGROUPS) using any source method (e.g.: url, cookie, httpHeader) by design. Having this ability would open possible security breaches.

If you attempt to set the USER variable with the following instanceconfig.xml setting:

<Param name=”NQ_SESSION.USER” source=”url” nameInSource=”nquser” />

You will get the following error when using the url: http://localhost:9704/analytics/saw.dll?Dashboard&nquser=user1&nqpassword=public :

nQSError: 10018: Access for the requested connection is refused
nQSError: 1315 You do not have the permission to set the value of the variable :USER