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.

Left Outer Joins

Im writing this simple post to document left outer join and displess confusion around usage:

1. Syntax one (supported by oracle):

SELECT *
FROM employee LEFT OUTER JOIN department
  ON employee.DepartmentID = department.DepartmentID

In above example, query will fetch all records from employee, even if department value is missing as it is specified on the "left"

If above is changed to below:

SELECT *
FROM department LEFT OUTER JOIN employee
  ON employee.DepartmentID = department.DepartmentID

Now, the above will fetch all records from department even if employees are not present in that department. The above query and query below have same results:

SELECT *
FROM department RIGHT OUTER JOIN employee
ON employee.DepartmentID = department.DepartmentID

2. Alternate Syntax (deprecated - may not work in future releases):

SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID(+)
This will fetch all records from employee, although the "+" is specified next to department. I think oracle decided to deprecate this as its non intuitive? We add + to department but query returns all records from employee? Non intuitive.

Note, Oracle BI does not use deprecated syntax, it is shown just for trouble shooting. To add outer joins in OBIEE RPD, navigate to logical layer and edit the join. This is shown below:

 

Now here is something to think about: developers often add outer join and then add prompts on the dashboard. As soon as prompts are applied, outer join would not work.

 

For example if you want to join sales fact to a product cost table, which may or may not have a cost type value for all cost types. This can be achieved by using an outer join. Next, if you want user to be able to select cost type from the prompt,  this will now result in outer join to not work.

 

There are some work arounds for this too, for example use an opaque view in physical layer, specify a variable to filter for a cost type, and then in dashboard prompt, update value of the variable to change cost types. This ensures that outer join will keep working.