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!