Table not found in Database

This could be annoying when analyzing databases for available tables in the schema. For quick reference, here is the query that can help locate any table in the database:

Select *

From Dba_Objects 

where lower(object_name) = 'table_name';

OBIEE BI Publisher prompts not visible when added to dashboard page

When creating a BI Publisher report from a BIP data model, we can add prompts on the report directly to have interactivity on the report. This is specifically useful if you end up creating a BIP report when all required field are not available in the Subject Area, and the report is more like a one-of report (not requiring any further Ad-Hoc analysis).

The report and prompts work well when accessed from a catalog directly. But when you try to add the same BIP report to a dashboard page, it does not show the prompts for some reason.

There are two workarounds for this problem:

1. Build the BI Publisher report from a Subject Area (and not a data model) and then use dashboard prompts to have interactivity on the report. This may not be an easy option for most cases, because if it was, you might not have started with a BI Publisher report in the first place.

2. Second and easy option is to create a link to BI Publisher report on the dashboard instead of directly adding it to the page. This is a bit tricky, but it allows report to be accessed through a dashboard link. If you try to add link, ordinarily, you will not be able to browse to the BI Publisher object:

To avoid the above situation, select "Destination" as URL, and copy the report URL there, but replace server name and port number by "../" . This is very important from migration to higher environments perspective. 

Once the page is saved, navigating back to the dashboard page will show a link to the BI Publisher report: 

Clicking on it does open the BI report with all the interactivity required. 

Date column showing values 0/0/0 12:00:00 AM in OBIEE 11g

Noticed strange appearance of dates in certain cases as below:

I checked the database, the date values for these records was null. It appears there is an easy fix for the problem: 

Open RPD > go to the problem date field > find physical layer column for that field > open physical layer column properties. 

The date might have been set to be Not Nullable. Make sure to Check 'Nullable' property in the physical layer

From data perspective the physical layer properties doesn't make much difference, but BI uses this for formatting reasons. An another example might be if you have a varchar field of length 150, but in physical layer you set it to 100, then BI presentation layer will only display upto 100 characters and truncate the rest. This situation is particularly possible when database changes are made by DBA after importing physical table in the RPD, and changes are not communicated to OBIEE architects.


Thankfully this was an easy fix (very annoying though)

How to export entire OBIEE dashboard to excel (all tabs) in one shot?

Its a common request, you have an OBIEE dashboard, with multiple tabs on it. How do you export the entire dashboard to excel in one go?

I just found out that in the version OBIEE 11.1.1.7.1, you can do exactly that. Look at the dashboard below with seven tabs. Navigate on top right to page options > Export to Excel > Export Entire Dashboard:

So cool, it saves entire dashboard into an excel file, with one excel tab for each dashboard tab:

There is one caveat to this functionality, i.e. you will have to make sure all dashboard have been defaulted to the right prompt which you want to download. Other than that, its great addition to OBIEE. 

Update: This option is available through Agents as well in 11.1.1.7.1 version: 

 

 

 



Here is the exact version number of OBIEE for reference:

DateTime field conversion to a Date field in OBIEE

If you have a field in physical table which is of type DateTime, but you want to display it as only date in the report, then you this can be converted. There are two approaches to it in the RPD:

 1. In the physical layer, navigate to the physical table and double click the datetime column. Next change the datatype from 'DATETIME' to 'DATE'.

         

This will make only cosmetic change, i.e. anytime the field is exposed in answers, it will not display the time, but for calculations (or joins), it will use the database value of date time.

2. A better way might be to use cast function to convert the field to date in the logical layer expression builder as below:

Adding cast function to this field will append TRUNC function in the physical query fired to the database. Had to add this post, in the expression builder, there is no truncate function in "Calendar Date/Time Functions". While if you look documentation, for cast, it clearly states that it supports DateTime as well as Date data types: