Constrained prompt date issue

If you have a constrained prompt on the dashboard with date or date range also there as calendar that is defaulted to current date, then rest of the prompts show data only for that default date even if the date is cleared out. This is a known BI bug. As a work around, run the report once (you can hit cancel after running though), this will update the constrained prompt query behind the scenes.

BI report returning no results, query in toad does?

We had this problem, the BI report was not returning any data, while the tables had data. We took query from the BI session log, and fired it in the TOAD client, it did return expected values?

This what we found after much head scratching:

There was a materialized view, which TOAD query was not using, while same query from BI was using. Same query had different explain plans. Reason behind this was following startup query hiding in BI connection pools:

alter session set query_rewrite_integrity=STALE_TOLERATED

Toad was not using the stale materialized view, and that explained the difference. I hope this blog saves lot of people from losing their hair (and sleep)!!

Drill down on union reports

When creating drill down on union reports in BI, the value user clicks on for drilling down is not passed to the target report. But this problem can be overcome, if field is converted to html using the syntax below:

'<span style="">&nbsp</span>'||'<a class="Nav" href=saw.dll?Dashboard&PortalPath=/shared/Sales+Reports/_portal/&Page=Market+Share+Report&Action=Navigate&col1=Product.%22Item%20Number%20%28NDC11%29%22&val1="'|| Replace("Product"."NDC-11",' ','%20')||'">'||"Product"."NDC-11"||'</a>'

In example above, Item number is passed from summary report to the target.  The syntax above will also set values in prompts for the product field. This can further be used to set a presentation variable, and then using variable filter a direct database query. Direct database will call the variable using syntax below:

'@{item_num}{00099999}'

Where:

item_num is the variable setup in the prompt.

‘00099999’ is the default value

The above method has a limitation, that it will pass only one value to the target, which should be good for most situations. But if not, then we can also implement an intermediate report where one value will be passed to intermediate target report, but user can then click again to reach final destination and all values will be passed. The intermediate report in this case will have just one query. Downside of this method is user has to click twice to reach the destination report, but it works for those picky users we all know how demanding they can be.

Manage Priveliges - Subject areas not visible??

The OBIEE admin has ability to hide or show a subject area to groups of users using manage privilege link. However this link does not have subject areas listed by default.

The functionality is as follows:

1. A Subject Area is stored in a webcat once a user has accessed it via Answers. 2. The Subject Area will be visible in the Admin and Manage Privileges link only if a user has accessed the Answers link.
3. Subject Areas will persist for the life of the webcat, but will not be accessible via the Admin and Manage Privileges link after the webserver services have been stopped and restarted. They will only be accessible once a user (any user) has accessed the Answers page in a webclient session.
4. The behavior described in Step 3 ensures that potentially archived, deleted or renamed subject areas are not visible for setting privileges.
5. The View privileges will also not be accessible via the Admin > Manage Privileges link after a webserver service recycle until and unless a user (any user) has accessed it in a webclient session.

a. When you run a request, the following privileges become visible in the Admin > Manage Privileges link
View Compound
View Filters
View Narrative
View Nested Request
View Pivot Table
View Logical SQL
View Table
View Ticker
View Title b. When the user clicks on "Customize View", the following privileges become visible:
View Create Segment
View Chart
c. When the user clicks on Views Tab, the following privileges become visible:
View Question
View Column Filter
View Global Filter
View Image

Problem with using domain name with user ID when logging in.

We have multiple LDAP / AD servers setup for out BI server, and users from muitilple domain are able to access BI with no issues. However I didnt knew, till today, that we can prefix domain name to user ID when logging into BI. For example:

northam/arunk

where northam is the windows domain name and arunk is the network ID. It does lets you in, but it wrecks the initialization blocks logic, as it will pass northam/arunk as ID not arunk. Unless the initializatiob block has logic to handle this, the logic will be broken and you will get unexpected results in output for reports which have data level security. One of our user was getting following error due to this:

View Display Error

Odbc driver returned an error (SQLExecDirectW).

Error Details

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 17001] Oracle Error code: 1722, message: ORA-01722: invalid number at OCI call OCIStmtExecute:

This was happening, because one of the filter was expecting a number from session variable, but was getting a null. When I asked the user not to use domain name, issue got resolved.

Direct Database Connection error

We created a direct database connection, and it worked fine when tested as Admin. But when I was trying to give a end user access to that report, it kept throwing access denied error:

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 13017] User or group has not been granted the Direct Database Access privilege to access the database 'Oracle Data Warehouse'. Please verify the User/Group Permissions in the Oracle BI Administration Tool. (HY000)


I checked the permissions, and user add access to both:

Edit Direct Database Requests

and

Execute Direct Database Requests

Finally the problem was fixed by making a change in RPD. Do following if you are facing same problem:

-In the physical layer, click on database for which you need to enable direct database query for non-admin users (thats the green cylinder icon).
-Check option - "Allow direct database requests by default"

Thats it. It should work now.

Multiple LDAP servers

OBIEE does allow us to add multiple LDAP server. We needed this functionality as some of the global users were on different domain than the majority of users. Apparently when more than one LDAP servers are configured, BI attempts to get user authenticated with first server in the list, if it fails then it tries using second LDAP server.

This method can also be user for implementing redundant LDAP servers.