Master Detail Linking

OBIEE 11g: Master-Detail Linking

Posted on June 3, 2013 by Andrew Fagence

One of the fresh and impressive features that got introduced with the release of OBIEE 11g was ‘Master-Detail Linking’. What Master-Detail Linking makes possible for you is to create relationships between two or more Views within an analysis, one of these Views is termed as the ‘Master View’ and forces an alteration within the other View(s) automatically.

For example, you may have an analysis with a Table View containing a ‘Department’ column and a Bar Graph View showing sales. You can create a relationship between the two Views making the Table View as the Master View, then when you click on a cell within the Department column the Bar Graph View will alter to show the sales data corresponding to that specific Department.
In this example I am going to show you how to create Master-Detail Links, I am using OBIEE 11.1.1.6.7 and the Sample App data.
Start by logging into OBIEE, and then create a new analysis. I am going to select ‘A – Sample Sales’ as the Subject Area. The columns I am selecting for my analysis will be:

• T05 Per Name Year – Found within the ‘Time’ folder.
• D4 Company – Found within the ‘Offices’ folder.
• P2 Product Type – Found within the ‘Products’ folder.
• 1 – Revenue – Found within the ‘Base Facts’ folder.

Once we click on the Results tab, a Table View will automatically be created showing these columns (this will be our Master View):

Create a Bar Graph View (in this case a Horizontal Bar Graph View) and place it underneath the Table View. Now we need to edit the Bar Graph View to display the correct columns, so click the ‘Edit View’ icon. Configure the Bar Graph View with the following amendments:

• Graph Prompts – Place the T05 Per Name Year column within this pane.
• Sections – Place the D4 Company column within this pane and tick the ‘Display as Slider’ box.

Once this has complete click ‘Done’ and then click the ‘Criteria’ tab, we are now going to establish the relationships. Navigate into the T05 Per Name Year Column Properties:

Click the ‘Interaction’ tab and then select ‘Send Master-Detail Events’ as the primary interaction within the ‘Value’ section, a text box now appears which we can specify a channel (the channel is what’s going to create the relationship). You can specify any channel name you like but in this example I am going to use the channel name ‘YEAR01’:

We are also going to configure the D4 Company column to send Master-Details Events, I have specified the channel as ‘COMP01’. Once both channels have been specified return back to the Results tab and click the Edit View icon on the Bar Graph View, then click the ‘Edit graph properties’ icon. Within the ‘General’ tab tick the box next to ‘Listen to Master-Detail Events’ and specify the YEAR01 channel in the ‘Event Channels’ text box:

 

Return back to the Compound Layout, we should now have a functional Master-Detail link. The way to test this out is to manually change the year within the Graph View (from the prompt) and then click on any cell within the T05 Per Name Year column of the Table View that represents a different year. The Graph View should automatically alter to match the same year as the one you selected:

We are now going to add another View to the analysis which will link up to the COMP01 Master-Detail link. Add a Pie Graph View to the analysis, Move it next to the Bar Graph View/ Underneath the Table View and then click the Edit View icon within it. Configure the Pie Graph View with the following amendments:

• Graph Prompts – Place the D4 Company column within this pane.
• Sections – Place the T05 Per Name Year column within this pane and click the ‘Display as Slider’ box.
• Slices – From the ‘Subject Areas’ pane on the left hand side, expand the Offices file and drag the ‘D3 Organization’ column into this pane:

Click the ‘Edit graph’ properties icon at the top of the webpage and click the tick box next to ‘Listen to Master-Detail Events’ within the General tab to put a tick in it, then specify the ‘COMP01’ channel in the text box next to Event Channels:

Click ‘OK’ at the bottom of the window and then click ‘Done’ at the top right hand side of the webpage to return to the Compound Layout within the Results tab. Our Second Master-Detail Link should now be functional. The way to test this out is to change the company within the Pie Graph (in this case I am going to change the company to ‘Tescare Ltd.’:

Then click on a different company within the D4 Company Column of the Master
(Table) View, if the Pie Graph automatically alters to match the selection you chose within the Master View then the Master-Detail link has been configured correctly:

We can also configure a view to listen to two or more channels at the same time. Click the Edit View icon within the Bar Graph and then click the Edit Graph Properties icon at the top of the webpage. When we specify more than one channel, we separate the names of the channels with a comma (,). Type ‘, COMP01’ within the text box next to Event Channels:

Click OK at the bottom of the window. Within the Layout Section of the Edit Graph webpage, make the following amendments:

• Graph Prompts – Drag the D4 Company column within this section.

The Bar Graph view should be configured like below:

 

Click ‘Done’ at the top right hand side of the webpage to return to the Compound Layout webpage. Change the Year within the Bar Graph View (in this example we have changed the Year to ‘2010’):

Click on a different Year within the T05 Per Name Year Column (This is to test that the YEAR01 Master-Detail link is still functioning):

Then click on a different Company within the D4 Company Column to that of the Bar Graph View. If the Company choice within the Bar Graph View changes to your selection then we have successfully set up two Master-Detail links within one view:

We can also change both the Year and the Company at the same time. Expand the Table View to show all of the rows and then click on a different Company in a different Year to those that are currently selected, the view should now change:

 

This is just a basic example of how to use Master-Detail linking within OBIEE11g. In some cases you could have very complex links, as many as six or seven different Master-Detail links within a single report, and all of them could be used on a single View!

Master Detail View

OBIEE11g – Master Detail View

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.
For example, If you have the following two views: A table that shows Revenue by Brand and A graph that shows Revenue by Product Category with Brand on the graph Prompt section.
Using the master detail linking functionality, you can link the two views so that when you click a particular Brand in the table, the Product Category on the Prompt section of the graph as well as the data in the graph changes to reflect the Brand that was clicked on the table.
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.
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
Let us see How Master Detail Linking Works:
Let us take an example and select few columns including Brand, Revenue and Product Category.

Go to the Revenue Column properties and click on Interaction.

Select Primary Interaction “Send Master-Detail Events” and specify the Channel: Channel01. This channel name should match with channel name which we will update in the graph.
Create a simple table view or pivot view to show the Revenue by Brand.

Create a simple Graph view as below

Select on the graph properties

Check on the “Listen to Master-Detail Events” and provide the same channel name as given in Revenue column. This channel name will link the master view. Ensure to have the Brand column either on Graph prompts or on Section as Slider.
Add these two views into the compound layout.

When you click on any of the Brand Revenue Value, the Graph view will automatically change the view to bring the Product Category Revenue details for the Brand value clicked.
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

Event polling in OBIEE 11G

                   

Event polling in OBIEE 11G

An event polling table is a way to notify Oracle BI server that one or more physical tables have been updated. Each row that is added to an event table describes a single update event. The cache system read rows from – or polls – the event table, extracts the physical table information from the rows, and purges cache entries that reference those physical tables. The SQL for creating an event table can be found in the \\<OBIEE_MW_Home>\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\schema folder.

Steps to create event table to purge the cache automatically

1)      Locate the  SAEPT.sql file in the loction \\<OBIEE_MW_Home>\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\schema

2)      Create the event table in the database by running the script in the SAEPT.sql .

3)      Import the created event table into the repository

4)      Go to Tools à Utilities. Select Oracle BI Event tables and click execute.

5)      Select the event polling table. Here I have selected the event polling table S_NQ_EPT. Set the polling interval as the time u need to purge the cache.

6)       Now you can see that icon of the table S_NQ_EPT changes as now we have made it as the event polling table. Checkin the  changes and save the rpd.

Now lets see the working of the event polling table.

7)      Create an entry in the S_NQ_EPT table which will force the oracle BI server to purge the cache.

After the polling interval times verify that the cache entries corresponding to TABLE_NAME mentioned above gets deleted. The cache folder in OBIEE 11 G is \\<OBIEE_MW_Home>\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\cache. The entry in the table S_NQ_EPT also get truncated after the polling interval.