Cloud

O SpecGold OracleBusIntApps7 clr

 Gcloud

 

   Call us now 

  Manchester Office

  +44 (0) 8450 940 998

 

  

 

John's Blog

This is my blog of various topics, from things that I think might be useful to others, to things that I just find interesting personally. If you have any comments or questions on any of my posts then please do ask - any positive contribution is very much welcomed.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.
  • Team Blogs
    Team Blogs Find your favorite team blogs here.
  • Login
    Login Login form

Organization or Position Hierarchies in Oracle OBIEE

  • Font size: Larger Smaller
  • Subscribe to this entry
  • Print

A while back I created a post describing how to produce an organization chart in Oracle APEX using Google visualizations. If you didn't catch that then go and take a look here first before reading on as it will provide the background reading to this post.

So in this post I am going to demo how we can do this in OBIEE - and it's actually quite easy because OBIEE has already done a lot of the work for us.

First we need a level based hierarchy (or even just a representation of a hierarchy as levels across columns). This is how all BI Applications hierarchies are implemented, for example the organization and position hierarchies. I am going to use SampleApp with the "Sample Sales"."Offices" hierarchy.

Columns

Then we simply select all the columns in our hierarchy into a simple analytic. As we have multiple top level nodes I have applied a filter to restrict to just one company, however this isn't necessary - if you have multiple top level nodes then you simply get multiple trees.

Analytic

If we use the default Table view then we see something like this. Note I have changed the column order in this view simply to make the hierarchy structure clearer.

Table Results

 

So now we want to create our organization chart. We will use a Narrative view as this allows you to produce HTML for each row returned in the analytic. Create a new narrative view and remember to tick Contains HTML Markup
As per the APEX example, we need to set up the chart first, so we place the following code in the Prefix.


<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
  google.charts.load('current', {packages:["orgchart"]});
  google.charts.setOnLoadCallback(drawChart);
  
  function drawChart() {
    var data = new google.visualization.DataTable();
    data.addColumn('string', 'Name');
    data.addColumn('string', 'Manager');
    data.addColumn('string', 'ToolTip');

We now need to output our relationships. This is easy - because we have all the columns in our view. To reference a column value we use @N where N is the postional reference of the column in the criteria. We need to create a parent->child relationship from the office to the department (@1 -> @2), department to the organization (@2 -> @3) and organization to the company (@3 -> @4).


data.addRows([
          ['@1', '@2', ''],
          ['@2', '@3', ''],
          ['@3', '@4', '']
        ]);

Due to executing the narrative for each row of the analytic, we are of course adding rows that have already been added in previous iterations. However Google takes care of that for us and we see no side effects. We then simply close off the chart and create a dummy element to hold the rendered chart:


  var chart = new google.visualization.OrgChart(document.getElementById('chart_div'));
        chart.draw(data);
}

</script>

<!--Div to hold the rendered org chart -->
<div id="chart_div"></div>

Our view now looks as follows.

View Definition

Click Done, and that's all there is to it. We now have an organization chart rendered for our office structure:

Org Chart

We can use any of the configuration options/methods that Google provides, for example we can modify our call to chart.draw() to be chart.draw(data, {allowCollapse: true}); which then makes the chart collapsible.

Of course you can do various things with this such as only showing to a specified level, or write yourself a little JavaScript routine to collapse at a certain point to avoid rendering large charts. Similarly with a BI Applications environment you may do any number of enhancements such as displaying the holders of a position within a position hierarchy. A kind of reverse version of the demo Google uses.

Google Demo

If you've done anything interesting or outside the box with Google charts then feel free to comment below.

Last modified on
Tagged in: OBIEE Oracle BI 12c
in Techniques Hits: 969 2 Comments
0

My primary area of interest and expertise is Oracle E-Business Suite, particularly Foundation, Human Resources, Payroll, Time and Labor, Warehouse Management and Inventory. Whilst I prefer to focus on functional aspects I do have a keen interest in technical areas too such as SQL tuning/the optimizer, Application Express (APEX), Unix (particularly Linux) and general development.











  ACE Logo




Cert Logo  
Cert Logo 2

Comments

  • Guest
    Geoff Scott Wednesday, 12 July 2017

    Great blog, John.
    I've implemented this in OBIEE 11g using the narrative view in a dashboard analysis.
    I've also successfully added a dashboard prompt/presentation variable to filter the result set based on a structure level.
    This all works as expected, however when a new prompt value is selected and the Apply button is "pressed", the narrative view disappears!
    I suspect there is a javascript method to call to reload the data and analysis but I have zero javascript knowledge.
    Any thoughts, John?

  • John Keymer
    John Keymer Monday, 17 July 2017

    Hi Geoff,
    I tried to replicate your issue however couldn't get it to fail - I have put a simple narrative with a prompt on the top level value, and it appears to work fine.

    http://www.wegobeyond.co.uk/images/jk_blog_pics/org_chart_obiee/org_chart_dashboard.png

    This is using Oracle Business Intelligence Product Version 12.2.1.1.0 (Build BIPS-20160604082456 64-bit).
    As a shot in the dark, you could try changing the first to lines to the following, as it could be a timing thing based on the point the chart is rendered:

    $(document).ready(function() {
    google.charts.load('current', {packages:["orgchart"]});
    google.charts.setOnLoadCallback(drawChart);
    });



    Thanks,
    John

Leave your comment

Guest
Guest Wednesday, 22 November 2017