Smart Tables in OBIEE 11g

This article introduces the concept of Smart Tables in OBIEE. I call them 'Smart' because these tables have the ability to produce different data on the fly. These can morph themselves according to the user requirement. Smart Tables also make the data model dynamic in nature as a Smart Table can change characteristics on the fly. Let me now explain the procedure of implementing Smart Tables.
Objective: We will create 2 tables in the database. We will configure the rpd such that OBIEE picks one of these 2 tables based on the value of a session variable.
Steps:

1.    Create 2 tables with the following definition and data
        create table  smart_table_1 (column_1 varchar2(15 char));
        create table  smart_table_2 (column_1 varchar2(15 char));
        insert into smart_table_1 values ('Smart table 1');
        insert into smart_table_2 values ('Smart table 2');
        commit;

2. Create a non system session variable with the following definition. Note that the 'smart_table_1' has been assigned to the session variable. The initilization string is

select 'smart_table_1' from dual

We will use this session variable in the definition of the smart table created in the physical layer.

3. Create a physical table with the following definition. Make sure that you have added column_1 in the columns tab of this physical table. Note that Use Dynamic Name check box is checked and the non system session variable, created in step 2, is selected.

4. Drag this table twice in the BMM layer. Name one of the logical table as Smart table dim and the other as Smart table fact. Define a key for Smart table dim as shown below.

5. Join the 2 logical tables as shown below.

6. Create an analysis by selecting the column from the fact table as shown below.

7. The result will be the following. Note that the value from the 1st table is displayed.

8. Now change the query in the initialization block to the following.

 select 'smart_table_2' from dual

Save the rpd in online mode and relogin into the presentation server so that the new value of the session variable is set for the new session. The result will be the following. Note that the value from the 2nd table is displayed.

So we see that OBIEE picks different physical tables for the same presentation table based on the value of a session variable.