How to find RPD column usage in the BI reports?

A column could have been used in several reports spread across the OBIEE dashboards, and the question as to where exactly it is being referred is the one many business analysts like to be answered, and becomes more relevant when a change has to be made to physical column and impact analysis needs to be done for production reports. While there are several third party tools that build the complete column lineage, from dashboard to subject area, to physical column, and then to column in source system, I found OBIEE also has something nifty that can be used to answer many questions of not all.

Try this,

-          open catalog manager (offline mode preferred for faster response).

-          Select folder /shared (or whatever you are trying to explore)

-          Navigate to ‘tools > create reports’ and viola you are at the screen that will let you build the column mapping report

-          Select the columns you need in the report, specify the output report name with path, and hit OK.

-          Be patient, depending on the catalog size, this runs for a long time, in my case a good 20 mins, as we had several prebuilt dashboard as well.

This process will map the reports, you can run it again to have another output for dashboards. To go a step further, export the two outputs to access or a database, and you could possibly join to now map dashboard to column names. Now if you are thinking what I am thinking, you can join it to RPD extract to go upto the physical column name. Pretty neat…  huh?

Catalog report file issue

When using Catalog Manager export feature, it creates a tab delimited file. Unfortunately, if some of the columns formulas have tab too, while converting to excel will throw off the tab delimitation. Does anyone have a work around for this?

iBot missed daily and weekly jobs due to service down, how to reschedule missed jobs

We have hand full of power users, who use iBot extensively. Some of the users have over 20 jobs running on daily or weekly basis. One perfect Monday morning, I disovered iBot services were down while rest of the BI was working. I was getting calls from all over the place, so I checked the server.

Although I started the BI scheduler service and BI Delivers seemed to be working ok, what do we do with the missed jobs? Rescheduling hundreds of missed jobs would take entire day? That’s too painful. Thankfully, in BI Job Manager, there is an option to reschedule the missed job, simply by right clicking and selecting option ‘Run Now’. This option does not messed with the schedule, and that way job will pick up regular schedule next day automatically.

EXPLAIN PLAN

We were using state_tolerated for materialized views in OBIEE. To troubleshoot, when we ran explain plan in oracle, turns out, it does not show query was hitting materialized views.

Reason was, explain plan uses a new session, and did not take state_tolerated value for that session. So to see which MV the query was hitting, we had to get explain plan using following query syntax instead of explain icon (ambulance in toad):

EXPLAIN PLAN For

***Sql query***

Then execute following query to view the explain out put:

Select * from plan_table

In the object_name column, you should be able to view the materialized view name

How to reset OC4J password if you have forgotten

To reset OC4J password if you have forgotten, goto

C:\OracleBI\oc4j_bi\j2ee\home\config

Edit

system-jazn-data.xml

erase entry between tag credential  

Cryptotools Problems

I used cryptotools when database password was changed and iBots was required. After using the utility, I was getting the error when trying to login to OBIEE:

An encrypted password was found in the credential 'Admin', but no decryption passphrase has been specified. Please ensure that a decryption passphrase is specified in configuration. See documentation for details on how to configure the credential store.

When I traced back to what was done, I specified ‘admin’ instead of ‘Admin’. So it is case sensitive. When I ran the utility again and restarted the presentation services, server was back to normal and delivers was working too!!

OBIEE login takes forever

When trying to login to BI server, we were getting a perpetual message :

Logging in... please wait.

We checked the presentation server, BI server, memory, CPU, hard disk free space, temp files, you name it, all was good. Turns out, database server was the culprit. A simple query like :

Select * from w_day_d

was running for over 5 mins. Asked DBAs to do their magic, and sure enough, everything was back to normal. But why in the name of god, login would hang up if database is preoccupied? Why can’t BI log you in and show a blank page in you’re My Folders?

Answer: Initialization blocks. Every time someone logs in, all session variables are populated, and till the results from BI comes back, BI would ask user to wait.

OBIEE BI Dashboard tab missing

A user of mine created dashboards with two tabs in My Dashboard area, and had to delete one for some reason. He ended up with just one page on the dashboard, everything was working, but he didn’t though something was missing.

Turns out, it’s a BI feature, that if there is only one page on the dashboard, BI does not display tabs at the top.

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.