Archive for August, 2009

There are alot of users that wish that reports within dashboards would not fire automatically once entering a dashboard or dashboard page.  So what is the solution?   There is a pretty good solution to this problem out there that uses the concept of guided navigation; but, it doesn’t work.  On the surface it looks like it works but in reality the report queries are still being fired off, as one would be able to see via the NQQUERY.LOG.  Now keep in mind if caching is turned on, and the report queries are cached you may not see the queries in the log file.  But you most definitely will see that they were fired off if you turn caching off.  Now my solution to the problem is as follows:

You will need Dashboard->”Save Selections”, and Dashboard->”Assign Default Selections” privileges.

1. Put the reports that are required to not fire off within a collapsible dashboard section.  The collapsible property is a section property.  Save dashboard page.

2. Now go to your dashboard and the dashboard page.  Clear out all the dashboard prompts’ values.

3. Collapse the sections you do not want to be fired off.

4. Select “page options”->”save current selections”->”For Others”.   A “Save Current Selections” box will appear like below:


Put in the selection name, check off “Make this my default for this page” and then click “Set Permissions”.  Here in the permissions select all groups you want to apply this default selection to, which most likely is all of them.

And you are finished.

One point to make.  Some may ask, Why did I have to clear out dashboard prompts and Won’t my saved selections now not show the default values of the prompts that are set.  The answer is, we cleared out the values of the prompts so that the selection we are saving only saves that the sections are collapsed and that we are not saving any prompt values.  When the prompts are clear it is not saying to clear out prompts whose default values are set via the dashboard prompts.  On the contrary, you will see when you go back to this dashboard page that the default prompts are correctly set and the sections are collapsed.

This might not be the most elaborate solution since the sections are collapsed and it entails that users uncollapse the sections to see the reports and fire them off.  But for the current release this is the best solution that I could come up with that worked.

Now what this will do is show your dashboard prompts and all the sections.  When a user want to then see a report and fire off the query all they will have to do is click on the collapsible button, which will open it up and fire off all reports contained.

How this works is that all sections that are collapsed are disabled and will not be enabled until opening up.  The guided navigation solution fired reports off since all that it does is hide the section but the section is still enabled.

Read Full Post »

On a project recently, there arose an issue where  logical columns that were not explicitly added to a hierarchy where suddenly drillable!  What would cause that?  Did someone change a property?  After careful analysis, I found out that there are 2 things that will make logical columns (not explicitly set in a hierarchy level) become drillable.  But please remember and  note that logical column(s) from a Logical Dimension Table source not explicitly assigned to a hierarchy level will by default be set and applied to the lowest(detail) level.  This is what makes them not drillable by default.  These two things are:

1. If the lowest level key that is set to “use for drilldown” is a logical column that is defined as a formula expression  (such as concats of two other logical columns).
2. If the lowest level is really not the lowest level due to setting preferred drill down to another level in another dimension.

These two above will result in all the logical columns not set in the hierarchy to now be drillable.

The work around for this is to set those columns defaults to be not drillable.  In order to set this, you have to go into Answers and set the system-wide defaults to “column heading interaction” and “value interaction” to “No Interaction.  This is done within the column properties via the “column format” tab.

Read Full Post »

Migration of OBIEE RPD from one environment to the next can be simple, just copy the RPD.  But then one would have to manually open up the RPD afterwards in the target environment to change connection pool information, maybe AD server configuration and the list can go on.  Pretty much each environment may necessitate changes to the RPD for that specific environment.  I  want to reduce these manual changes in the migration project and somehow automate them.  So I came up with a  design that I have effectively deployed that reduces manual intervention,  automates the process and is more secure than most design strategies I have seen, since the connection pool passwords are all encrypted.  I will share this design in hopes that it will help others or give them ideas on their solution to their migration process.

The design requires a repository variable, UDML script, a service account to an external table and a stand alone RPD.  These will all be explained in detail later.  The basic flow is that when a migration is requested to lets say DEV to QA we would just run a script.  This script will take DEV’s RPD and run the UDML script against it to generate the new target QA RPD and move this new RPD over to the QA environment.  The UDML script is used to update the one repository variable (lets call it RPD_ENV) and the service account’s connection pool password.  The service account is used to get the other connection pool passwords for the RPD based on the environment the RPD is in, hence the RPD_ENV repository variable.  An example of the UDML script is as follows:

DECLARE CONNECTION POOL “SampleSales”.”Connection Pool” AS “Connection Pool” UPGRADE ID 2150568163
PASSWORD ‘D7EDED84BC624A917F5B462A4DCA05CDCE256EEEEEDC97D51B4C9E811F09E97D0E252362E1DD04A6AC4D07C3A079829F’

HEADER PATH {E:\\OracleBI\\server\\config\\NQSQueryHeader.xml}
TRAILER PATH {E:\\OracleBI\\server\\config\\NQSQueryTrailer.xml}


As you can see the connection pool password is encrypted and the RPD_ENV repository variable is being set to its target environment.  For QAT to PROD script, this UDML script would be modified so that the service accounts password would reflect the production services password and RPD_ENV value to “PROD”. ( This is taking into account that the service account uses the same user id for logging in, if not this could also be changed in the line above the password. )  Now the service account is pulling the passwords from an external table in a database.  The table that I used is simple and the DDL (assuming using Oracle for the external table) is:


CONN_POOL is the connection pool, ENV is for the environment (values would be ‘DEV,’QA’,’PROD’ or any/all environments) and ‘LOGIN’ is for the password for the connection pool.  I named the password field something different as a lot of DBA’s or compliance people are touchy on naming fields that would be obvious in case of the db being compromised.  This is just a simplistic version of the table and can be modified to satisfy the projects needs.

So I have explained the repository variable, the UDML script, with example, and the service account’s use.  So what is the stand alone RPD mentioned used for?  Well I created an RPD that is bare bones for the purpose of the DBA(s) to enter the password for the service account.  So the DBA would enter the service account’s password within this RPD.  When finished we would just have to copy the connection pool object and paste within notepad.  The result is the UDML for that connection with the password encrypted.  This way the DBA(s) are the only ones that know the service account password and thus how to access that external table.  So all connection pool password(s) would be secure and guarded by the DBA(s).  We only have the encrypted password, which does not help us use it, unless of course we know the encryption scheme OBIEE uses!:)  In my case, the service account password changes once a year.  So the DBA needs to update the password in this stand alone RPD and then I need to get the encrypted password in the process just discussed and replace the service accounts password string within the UDML script.  Also, the DBA(s) are the administrators of that external table and keeping the table’s passwords in synch when they change them.

A quick note on this approach.  In this approach, we have all the connection pool passwords located on an external database.  We use initialization blocks to set dynamic repository variables with the passwords to be used in the connection pools.  So that the connection pools passwords get there value from their respective repository variables, via VALUEOF(‘XXXX’) function.   Since all connection pool passwords, except for the service account, are now being retrieved and stored in repository variables there is a security risk that the OBIEE admin or users can get the passwords through the same VALUEOF() function (that is if they know the repository variables.)  There is no way, at least that I know, which allows me to add security and permissions on variables within the RPD.  Since this is a security risk this needs to be looked at by the OBIEE architects to assess how much of a risk this is.  A more secure approach then would be to house all connection pools in that stand alone RPD where the DBA(s) set the password for the service account.  In this alternate approach, the DBA(s) will set all the passwords in this RPD and then your UDML script will need to be modified to add each connection pool in it along with the encrypted password retrieved from this stand along RPD with the same copy and paste approach.  This way the passwords are 100% secure, to the best of OBIEE’s abilities.  So when the DB passwords change, the DBA(s) would need to modify and enter the new password in the stand alone RPD and the UDML script will need to be changed each time.  This may and will add more administration, but is more secure.

Now the main script used to run the migration can be in whatever scripting language you choose.  You may want to do pre and post work it is all up to the project requirements.  But to run the UDML script above use the following command nqUDMLexec. This tool takes the source/input RPD, runs the specified UDML script against it and results in a modified RPD specified from the output repository location and name.  The syntax of this command line is:

nQUDMLExec  [-U [userid]] [-P [password]] -I input_script_pathname [-B base_repository_pathname] -O output_repository_pathname [-8] -8 is for UTF-8
Eg 1: nQUDMLExec -U Administrator -P Administrator -I testudml.txt -B DEV.rpd -O QA.rpd

Any questions, comments or suggestions please let me know and I will post.  That way the OBIEE community can share from each other.


Read Full Post »

During migration or copying of presentation web catalogs, some will no doubt come across this error for users when they log in:

access denied for user to path/users/{username}/_portal/dashboard layout

What this generally is saying is that the user does not have access to their dashboard or any dashboard.  They could type ‘answers’ after the ‘?’ in the URL and be able to access answers, if they have that privilege.  Pretty much what happened is that the migration/copy of the web catalog user folder did not copy the users’ permissions correct.

If you want, you could open the source web catalog that was copied and the web catalog that has the issue via cat manager and see the permissions for that user folder for each web catalog.  You should see that the permissions are different.

Now to fix the issue.  It is pretty simple, open cat manager and set permissions for the user’s folder to that user.  So the user needs at least ‘Change/Delete’ permissions on that folder and have this permission set recursively to sub-items and sub-folders.

And that should fix it.


Read Full Post »