BI Publisher reports showing stale data?

Oracle has done a great job at integrating BI Publisher (BIP) with OBIEE. However not may folks are aware of the fact, that BI Publisher was an independent tool sold separate from OBIEE for a long time.

BI Publisher has a caching mechanism of its own, which is totally independent of OBIEE server cache. If you are experiencing stale data being shown on BI Publisher reports, it could be this caching which needs to be addressed. But here is the tricky part: there is no way to clear the 'BI Publisher' cache using scripts (atleast not at the time of writing this post). If you have scripts to clear BI Server cache, it is great, but that script will not clear the BI Publisher's reporting cache.

Here is the good news:

It is possible to disable the BIP cache, or during report runtime, if you refresh the report, it can bypass the cache (if it is enabled by the report developers). To access BIP cache setting, use following steps:

1. Open BIP report in edit mode.

2. Click on properties and then caching:

3. This where you can :change default BIP caching options:

If you deselect 'Enable Data Caching' it will disable the BIP level caching. This option should be disabled by companies where data is loaded during the day, and even a 30 or 60 minute report caching could make stale data appear for the user. Or if a company has global user base, and Europe users need to see new data as soon as US operations are done loading, then caching is best disabled for BIP.

Note: If BIP report uses a subject area for generating results, then BI server could also do caching (if it is enabled). Although this cache is much easier to manage and purge. 

Another interesting thing is BIP also has document caching in addition to data caching. When this is enabled, BIP stores entire report output (data and document) in the cache for time specified, 

In addition to report level caching, BIP also has server level caching. To access these properties, navigate to administration and click on 'BI Publisher > Manage BI Publisher 

Then click on 'Server Configuration':

Then scroll down to Caching section:

Here are the default values from Oracle for these:

Expiration: 30 mins

Cache size: 1000 items (data)

Max cached report definitions: 50 

On a side note there is an option on data model "Enable Scalable Mode". This option allows some level of local processing of data rather than server. It is useful when there are several users trying to execute several complex BIP report, and can ease the load on server. 

Open World 2014 Customer Appreciation Event

I should be blogging about new learning from Open World 2014 :( (I will very soon)

Here is a small preview of customer appreciation event on Wednesday night last week. Aerosmith gave a hot hot performance, totally worth it.

Oracle Openworld 2014

Gaining insight at Oracle Open World 2014. Here are few images from the event.

How to select last updated transaction for every ID from a history table?

Here is the scenario:

You have multiple invoices in a table, with one record everytime the Invoice goes through a process, for example Invoice Created, Invoice Submitted, Invoice Approved etc. On top of that there could be multiple records for a step, for example an Invoice could be approved by multiple approvers, and that will end up being new records in the transaction history table.

If you need to create a report, that will show list of Invoices approved, and name of the last approver and the date, though could be tricky to get. After doing some research I found a simple way of determining that programatically:

We select the invoice along with timestamp of record, and sort them in descending order. We also select row_number for this query. The trick is to filter this result set where row_number is 1. Here is the exact sql if you need to refer:

Select  User_Id, approve_date,  Invoice_Id From (
 Select Sth.User_Id user_id, Sth.Timestamp approve_date, Swpv.Property_Value Invoice_Id,
  Row_Number() Over (Partition By Swpv.Property_Value Order By  To_Char(Sth.Timestamp,'DD-MON-YYYY HH24:MI:SS') Desc) R
  From Sf_Transition_History sth, Sf_Workitem_Property_Values_V swpv
Where Sth.Workitem_Instance_Id = swpv.Workitem_Instance_Id
   And Sth.Event_Name = 'Approved'
   And Swpv.Property_Name = 'InvoiceID'
)
Where R = 1

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:

Catalog Manager - point release upgrade

I tried to open OBIEE 11.1.1.6.9 catalog manager using OBIEE 11.1.1.7.1 client tools, but I get following error thrown back

Unable to conned to OracleBI Presentation Server. Please check if Presentation ServerfWebServer is running and URL is corredly typed http:fI< host>/analytics/saw.dll. {urn:f/oracle.bi.webservices/private/v8)SAWSessionService is not a valid service. Valid services are: (urn://oracle.bi.webservices/v6}SAWSessionService,,{urn:f/oracle.bi.webservices/v6}W ebCatalogService1(urn://oracle.bi.webservices/vt}XmlViewService,(urn://oracle.bi.we bservices/v6}SecuritySei-vice,(urn://oracle.bi.webservices/v6}ConditionService,{urn:// oracle.bi.webservicesfv6}HtmlViewService,{urn://oracle.bi.webservicesfv6}IBotService ,{urn://oracle.bi.webservices/v6}JobManagementService,{urn:f/oracle.bi.webservices/ Æ}MetadataService,{urn://oracle.bi.webservices/v6}ReplicationService{urn://oracle.b i.webservicesfv6}ReportEditingService

 

Looks like even though its just a point release, yet we will  have to go through upgrade assistant utility (ua.bat) in order to move to new version. Note, this is not true for RPD version. When I open the RPD version, it pops open with no issues. When saving the RPD, it gives a message that you are upgrading version of the RPD file, and once upgraded, it cannot be opened in older version of AdminTool (which makes perfect sense. Anyways it’s a good idea to work on a copy of RPD file to revert back just in case).

 

 

 

OBIEE generating incorrect SQL Query - Query fails with error Invalid Identifier

This post is a different variation of a post I wrote last time, which was causing BI to generate incorrect query (click here to read that blog article)

I'm designing a RPD model based on a relational database model, and have run into a really weird issue, that I never even though was possible.  The model does work for several combinations, but in few instances, I get following error  when I run an analysis report:

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: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 17001] Oracle Error code: 904, message: ORA-00904: "T380"."REQUISITION_LINE_ID": invalid identifier at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)

 

…………………….

So far so good, but I uncover a strange phenomenon, when I pull up the SQL query generated by the BI Server:

WITH
SAWITH0 AS (select distinct T8727.SEGMENT1 as c1,
     T8727.SEGMENT2 as c2
from
     PO_DISTRIBUTIONS_ALL T1819 /* Fact_PO_DISTRIBUTIONS_ALL */ ,
     PO_REQUISITION_LINES_ALL T533 /* Fact_PO_REQUISITION_LINES_ALL */ ,
     MTL_CATEGORIES_B T8727 /* Dim_MTL_CATEGORIES_B_Purch_Category */
where  ( T380.REQUISITION_LINE_ID = T533.REQUISITION_LINE_ID and T533.CATEGORY_ID = T8727.CATEGORY_ID ) )
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select distinct 0 as c1,
     D1.c1 as c2,
     D1.c2 as c3
from
     SAWITH0 D1
order by c2, c3 ) D1 where rownum <= 65001

Notice carefully the where clause, it refers to alias table T380, but that alias table does not exist in the FROM clause. Weired?? Totally!! I was expecting a "Unable to Navigate" error if BI could not determine the join path and in no case an incorrect query, and yet here we have it.

I start digging into a work around, and a reason behind this. I did following while modelling my physical and business layers:

1. Created physical joins the way relational tables are joined, just making sure that there was only one path to reach every table, and not multiple. (BTW, news flash on a side note: OBIEE does support circular joins in physical layers, not in logical BMM layer though).
2. I created a logical star schema in the BMM layer, by pulling all the facts columns into one logical fact table, and pulling the attributes to respective dimensions. This also meant most logical table sources had more than one physical table sources.
3. For attributes that were coming from fact source tables, I created another logical dimension, and pulled those attributes to this logical table, and joined it to logical fact in the BMM layer.
4. The logical dimension that was getting attributes from fact sources, had more number of sources than the logical facts, as I did not want to create logical dimension for every single attribute.

The last point #4 was causing several query errors, so I gave up being lazy, and next I created a dimension for every attribute not coming from the fact sources. This was to ensure logical sources in Fact table were the same as logical sources in fact details dimension.

I did end up with lot less errors, but occasionally I still get issues on the front end. I'm still trying to find a solution for all the scenarios, hopefully I can write next article as soon as I have a solution to this. Stay tuned.

My first observation is the issue happens if your source is a heavily relational data model, where one logical table has multiple sources. The source is also heavily snow-flaked.

Second it could be linked to OBIEE version 11.1.1.7.0 or close to that. As I have never experienced this kind of issue in the past. Lets see. Hopefully soon I will have an answer.

Table in the physical query, but not in join criteria

Setup:

• I have a physical dimension, which has a primary key (not exposed to any logical column)
• Few fields from this dimension as exposed to a logical table. I added this table in excising LTS of a dimension to which this is snow flaked.
• There is no consistency error on the RPD.

Issue:

• When I pull fields from dimension in to analysis, it brings up two tables in the query, but this dimension is not joined to any other in the query. The join criteria is missing that I was expecting in the query.

 

Resolution:

 

• It was my mistake during defining the physical layer join. When I double clicked the physical layer join to view the criteria, I noticed I had defined a self join like below:

 

Table1.primay_key = table1.primary_key

 

Instead the correct join key should have been:

 

Table1.primary_key = table2.primary_key

 

Very simple mistake, but very annoying to get it fixed, especially if you have several physical tables to troubleshoot.

I noticed another variation to the problem above. Click here to read my second blog post.

Steps to connect to Oracle Database from RPD

Method One - ODBC - for Oracle and non Oracle databases:

1. Check the version of Oracle database using the command below:

SELECT version FROM V$INSTANCE



2. Download & install database client for this version from Oracle's website. Note: Please install "full client" as not doing so may have issues when trying to import tables into RPD from Oracle database. You may get error in AdminTool Log file as below:

[2014-01-17T14:51:46.000-05:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: ] [tid: 7c0]  [nQSError: 93001] Can not load library, oracore11.dll, due to, The specified module could not be found.

FYI, AdminTool log file is located at following location:

C:\OBIEE\oraclebi\orainst\diagnostics\logs\OracleBIServerComponent\coreapplication\NQSAdminTool.log

Follow link below for Oracle client:

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html

3. Make sure you create an environment variable TNS_ADMIN, and give full path of any folder, where you would like to create and save tnsnames.ora file. You may not find network\admin folder in this install!!

In folder above, create tnsnames.ora file using text editor if not alread present, and add your databases's TNS entry.

4. Create a ODBC connection to your database

Start > Control Panel > Administrative Tools > Data Sources (ODBC) > System DSN > New:

Create new by selecting the just installed client as highlighted above.

Here you should be able to view list of TNS entries that were added to tnsnames.ora file. Provide login and test connection.

5. Now, in RPD, following import metadata wizard:

File > Import Metadata... > and select ODBC 3.5 when asked.


Method 2 - For Oracle databases only (easy method):

1. Click File > Import Metadata...

select connection type as OCI 10g/11g

2. In data Source Name, give data base details in the following format:

//hostname:port_number/SID

On the next screen, make sure you uncheck Keys, and only select Tables, as we create joins only on Alias tables created after the import.

That is it. Rest of the steps are straight forward!! Remember, if you dont see your schemas or tables, then probably you need to install full Oracle database client first.

OBIEE Live demo from Oracle

Oracle has now made available a live OBIEE 11g environment for anyone who wishes to try it out.

Follow the link below to Oracle OBIEE sample app page:

http://www.oracle.com/technetwork/middleware/bi-foundation/obiee-samples-167534.html

and click on 'Try it Live'

http://slc02okm.oracle.com:7780/analytics

This would take you to OBIEE login page, which will have user name and password listed (i.e. 'Prodney\Admin123'). I have seen that link does not work at times.

Well as this is a demo environment only,  I would not rely too much on it, but could be a stop gap arrangement till have one installed on your local machine.