Thursday, February 14, 2013

OBIEE - Model based on one table

There are times you want to create a Subject Area or data model based on a single table in the Oracle BI Administration Tool.
If you try to take a table without any joins into the Business Model you will have an error 38133, The Logical Table is not joined to any other logical table.
They are few approaches I can think of. I'll mention one briefly and then talk about what is consider the correct one.

Approach 1. Fake join: create an extra object in Physical Layer. It might be a dummy table or an Alias of the original table. Join the them,  then move both to Business Model but expose only the original table in the Presentation. This approach will limit the usability in OBIEE, for example, you can't have Logical Dimensions this way.

Approach 2. Single Physical and multiple Logical Tables. This is usually the recommended approach. So how can we do it?

Drag your single table several times to the Business Model. One for the fact component, in this logical table we will have only the the columns we can aggregate.
For the descriptive columns we can create one or several dimension. Based on the business needs. For each such dimension we will add one more copy of the original table.
In my case I wanted 2 dimension tables:
Rename them and delete all irrelevant columns in each logical table.
   >>>>

Now create only the basic joins in Business Model Diagram (No Physical Diagram needed).
And define keys for each dimension table.
You can add logical dimension now:



All is left to do is expose it in Presentation Level. If for business needs you want it to appear as a single table, you can do it of course.
When you run queries OBIEE server will know it is the same, single physical table and creates efficient queries. For example the following analysis:

Created an efficient SQL in the DB with single access to the table:
 
WITH 
SAWITH0 AS (select sum(T48304.STORES) as c1,
     sum(T48304.AREA) as c2,
     T48304.COUNTRY as c3,
     T48304.MALL_NAME as c4
from 
     MALLS T48304 /* Single Table */ 
group by T48304.COUNTRY, T48304.MALL_NAME)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5 from ( select distinct 0 as c1,
     D1.c3 as c2,
     D1.c4 as c3,
     D1.c2 as c4,
     D1.c1 as c5
from 
     SAWITH0 D1
order by c2, c3 ) D1 where rownum <= 650001
 



5 comments:

  1. A note to myself:
    Kurt Wolff wrote a nice post about modeling: What To Do When OBIEE Fact Tables Do Not Join to All Dimension Tables?
    http://www.kpipartners.com/blog/bid/83120/What-To-Do-When-OBIEE-Fact-Tables-Do-Not-Join-to-All-Dimension-Tables

    ReplyDelete

  2. Thank you provide valuable informations and iam seacrching same informations,and saved my time OBIEE Online Training

    ReplyDelete
  3. Finally, an issue that I am passionate about. I have looked for information of this caliber for the last several hours. Your site is greatly appreciated. medicine shoppe franchise review

    ReplyDelete
  4. very useful really good information thanks for posting such a good information it will hepls the people a lot keep it up , Regards, obiee training in hyderabad

    ReplyDelete
  5. Very Impressive Obiee tutorial. The content seems to be pretty exhaustive and excellent and will definitely help in learning Obiee course. I'm also a learner taken up Obiee training and I think your content has cleared some concepts of mine. While browsing for Obiee tutorials on YouTube i found this fantastic video on Obiee. Do check it out if you are interested to know more.:-https://www.youtube.com/watch?v=Ajh_ePwlf88

    ReplyDelete