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