Master detail view in OBIEE

Master detail linking of views enables you to establish a relationship between two or more views such that one view, called the master view, drives data changes in one or more other views, called detail views.

In our example graph is master report and table view is detailed report.

What is master detail ?

Master Views: A master view is one which, primary interaction value in the column properties have set to Send Master-Detail Events using a channel name.

Detail Views: Explores the whole data as based on the master event.

The following types of views can be master views: Graph, Funnel graph, Gauge, Map, Pivot table & Table.
The following types of views can be detail views: Graph, Funnel graph, Gauge, Table & Pivot table

To create master detail event we can use following steps:

1.I have created analysis as shown in screen shot.

2. Select Revenue metrics interaction property, Select primary selection as Send Master-Detail Events.
    Specify channel name here we used master_detail 

3. Create master and detail reports refer screen shot A and B.

A. Master Report

B. Detail Report 

4. Now select detail reports properties. Select checkbox in front of Master-Detail. Specify Event Channels. You can     add multiple event channels separating by coma.

5. This is the output of reports on dashboard page. To see the master detail event click anywhere on the bar graph. You will see same changes will be reflected into table.

6. Here we have selected Audio. Now we are able to see two sub types of Audio product.

7. If you want see sub types in more details, again click on the sub types. You will see all detailed information related to products in table. Refer bellow image.

8. In this way we have implemented master detail view in OBIEE.

Note:

1.Master View:
a. Master column cannot be displayed on the prompt section or on the section slider in the master view. It must be displayed in the body of the view. In our example, the revenue column is the master view and it is present in the body of the view.

2.Detail View:
a. Can listen to master-detail events from multiple master views
b. Can be in the same analysis as the master view or in a different analysis
c. Cannot act as a master to another view

OBIEE 11g: Multiple dashboard prompts with single Apply and Reset Button

Single apply and reset button for multiple dashboard prompts:

Generally each single dashboard prompt has Apply and Reset button.

Refer the screenshot:

We need to hide those apply and reset button. 

Steps to follow :
1.Don't hide "Apply" button from the dashboard.
    If you hide "Apply" button, values pass automatically from prompts to reports.
2.Hide "Reset" button from the dashboard.Java script code for creating APPLY and RESET button:

Hide & Stop passing the values until click on the "Apply" button. For that follow the code

<style type="text/css">.PromptViewCell .button{display:none}</style>

For Single "Apply" button and the  functionality as follows,

<DIV CLASS = "XUIPromptEntry minibuttonOn">
<input class="button" type = "button" value = "Apply" onclick='javascript:

try
{ 
var aElm=PromptManager.getPromptManager().getAllPromptCollectionJSON();

   for(var i = 0; i < aElm.length; i ++ )
  {

   {

     for(var j = 0; j < aElm[i].promptSteps[0].prompts.length;      j ++ )

     {

  var promptid =    aElm[i].promptSteps[0].prompts[j].promptStreamID   ;
      var g = null;
      var h = PromptManager.getPromptManager().getPromptCollectionInfoWithViewID(aElm[i].viewStatePath);

        if(h)
       {
          try
 {
  var b = h.getAllPromptExprsArray();
  if( ! h.verifyPromptValuesAndDisplayError(b))
 {
   return
     }
          g = PromptManager.buildPromptExprGivenExpr(" ", b)
         }
         catch(f)
         {
           alert(f);
           return
     }
        }

       PromptManager.submitPrompt(aElm[i].viewStatePath, true, "PromptFinish", g)
     }

    }
  }
}

catch(e) {alert("Apply ALL Button Exception " + e.description);}'></input>
<input class="button" type = "button"  value = "Reset" ONCLICK = 'return PersonalizationEditor.removeDefaultSelection(false)' > </input>
</DIV>

Settings with Apply And Reset Button:

                         

 1.Apply Button:

2.Reset Button:

3.Criteria and Filters:

4.Text box:1 for hiding apply button script:

5.Text box:2 for Creating Apply And Reset  button script:

6. Dashboard page creation :

7. Final result on dashboard: 

Kindly share your feedback if it helps..

Enjoy coding..!!

RPD Layers

Three Layers of RPD:

RPD (Repository) is divided into 3 layers

1. Physical Layer: This layer is used for

ü  Importing data

ü  Creating Aliases

ü  Building physical joins

ü  Setting up connection pool and its properties

ü  Enabling/ Disabling cache for individual table

2. BMM (Business Model & Mapping) Layer: This layer is used for

ü  Writing the business logic

ü  Creating Logical columns and tables

ü  Creating hierarchy

ü  Creating LBM (level based measures)

ü  Creating shares

ü  Creating Time series functions

ü  Creating Fragmentation on tables

ü  Creating filters on repository

3. Presentation Layer: This layer is used for

ü  Arranging the data for users view (Folder Structure)

ü  Creating Presentation hierarchy

ü  Creating Implicit Fact column

ü  Implementing Column level security

In short three layers of RDP consist of following functions:

PRESENTATION LAYER User Roles And Preferences Simplified Views Logical SQL Interface

BMM LAYER Dimensions Hierarchies Measures Calculations Aggregation Rules Time Series Functions

PHYSICAL LAYER Map Physical data Connections Schema Aliases, Joins

Slowly Changing Dimensions

Slowly Changing Dimensions (SCD):

Slowly changing dimensions are the dimensions in which the data changes slowly, rather than changing regularly on a time basis. Slowly changing dimensions (SCD) determine how the historical changes in the dimension tables are handled. 

Generally we use following three types of slowly changing dimensions like: SCD type1, SCD type 2, SCD type3.

SCD Types:

a.      SCD type 1

It is used when there is no need to store historical data in the dimension table. This method overwrites the old data in the dimension table with the new data. It is used to correct data errors in the dimension.

E.g. If you entered address for employee is Mumbai and after some time the employee address is changed then you can use SCD Type1.

EmpNo	EmpName	Address
1001	Sagar	Mumbai(India)

If new address is Pune then you can overwrite old one.

EmpNo	EmpName	Address
1001	Sagar	Pune(India)

b.      SCD type 2

SCD type 2 stores the entire history the data in the dimension table. With type 2 we can store unlimited history in the dimension table. In type 2, you can store the data in three different ways.

They are:

ü  Versioning

ü  Flagging

ü  Effective Date

SCD type 2 Versioning: In versioning method, a sequence number is used to represent the change. The latest sequence number always represents the current row and the previous sequence numbers represents the past data.
For the same example we can use SCD Type2

EmpNo	EmpName	Address	Version
1001	Sagar	Mumbai(India)	1

If new address is Pune then you can add new version data as:

EmpNo	EmpName	Address	Version
1001	Sagar	Mumbai(India)	1
1001	Sagar	Pune(India)	2

If we add new location then version no also changes.

SCD type 2 Flagging: In flagging method, a flag column is created in the dimension table. The current record will have the flag value as 1 and the previous records will have the flag as 0.

EmpNo	EmpName	Address	Flag
1001	Sagar	Mumbai(India)	1

If new address Pune is added then the old records will be updated with flag value as 0 and the latest record will have the flag value as 1.

EmpNo	EmpName	Address	Flag
1001	Sagar	Mumbai(India)	0
1001	Sagar	Pune(India)	1

SCD type 2 Effective Date: In Effective Date method, the period of the change is tracked using the start_date and end_date columns in the dimension table.

EmpNo	EmpName	Address	Start_date	End_date
1001	Sagar	Mumbai(India)	01-Mar-2010	20-Feb-2011
1001	Sagar	Pune(India)	21-Feb-2011	NULL

The NULL in the End_Date indicates the current version of the data and the remaining records indicate the past data. 

c.       SCD type 3

Only the current status and previous status of the row is maintained in the table. To track these changes two separate columns are created in the table. It maintains the most recently used record history.

EmpNo	EmpName	New address	Old address
1001	Sagar	Mumbai(India)	NULL

If you add new address Pune then data stored should look like

EmpNo	EmpName	New address	Old address
1001	Sagar	Pune(India)	Mumbai(India)

The type 3 method will have limited history and it depends on the number of columns you create.