Extend OBIEE Charts – Part 1: Google Chart API

OBIEE Answers provides quite a number of useful charts but it’s always nice to have a few additional options, which is the reason for this entry.   A few of our clients have expressed an interest in seeing heat Maps, Spark Lines, Bullet Graphs, and several more custom graphs embedded within OBIEE.   For this entry, we’ll take a look at Google’s Chart API and show you how we’ve integrated Google Charts within OBIEE Answers.

The Google Chart API allows you to dynamically generate your graphs by simply passing the parameters to an URL and in turn, Google Chart will respond with a PNG-image of your chart. You can then embed this image in tag anywhere in a webpage. For more information on the Chart API see here.

In this illustration, we will create a heat map using US map and data from one of our OBIEE Answers request. Here is the table:

(first column contains US state abbreviations and the second is just a random number from 1 to 6)

Now insert a Narrative view and writing the following codes to its corresponding sections:

Prefix section:

<!--first show a dummy map-->
<img src="http://chart.apis.google.com/chart?cht=t&chm=usa&chs=440x220" id="GD_mapImg"/>

<script type="text/javascript">
  // setting up parameters
  var chartURL = "http://chart.apis.google.com/chart?cht=t&chtm=usa&chs=440x220&chds=1,6";
  var chartBGColor = "&chf=bg,s,EAF7FE";

  //specify the gradient: <default color>, <start of gradient>,.., <end of gradient>
  var chartPalette = "&chco=FFF5EB,FEE6CE,FDAE6B,F16913,D94801,A63603,7F2704";

  var chartDataLabels = new Array();
  var chartData = new Array();

Narrative Text:

  chartDataLabels.push('@1');
  chartData.push(@2);

Postfix Text:


  // putting it all together
  chartURL = chartURL + chartBGColor + chartPalette + "&chld=" + chartDataLabels.toString().replace(/,/g,'') + "&chd=t:" + chartData.toString();
  // now get the image from Google Chart
  document.getElementById('GD_mapImg').src = chartURL;
</script>

Check the box “Contains HTML Markup”. Remember this is required.

That’s it, you should see the map in the results section -

To customize the map, see here for more information


Limitations of Google Chart:
* Not interactive: it is just a static PNG image.
* Lack of Legend: especially no legend for Maps.
* Small chart size: largest Map is 440x220

Overall, this simple heat map works well and satisfies most of our current requirements. If you are looking for interactive and more eye-catching maps or charts in general, please be on the look-out for the second part of this series where we will show the same Narrative view approach with a slightly different API.  Spoiler alert: it is another great product from Google.

Utilizing EBS Responsibilities for OBIEE Authorization

As you may already know, the GROUP session variable in the OBI repository can be utilized to store group information brought in from external sources. The repository will assign users to local repository groups when the external and internal group names match.

For instance, if we had an EBS user with a responsibility of 'OBI Admin', we could read that information from EBS and store it in our local GROUP variable. We could then create a repository group with the same name, 'OBI Admin', and assign appropriate permissions to the group. The user would then automatically be assigned to the local 'OBI Admin' group and inherit all permissions within that group. The following diagram depicts this process flow:



The issue here is the GROUP variable is parsed in a certain manner and has specific requirements in order to work correctly. Namely, each group stored in the GROUP variable must reside in the same field and must be separated by a semi-colon. Therefore, row-wise initialization does not work for this process.

So, armed with the above information, we are able to create some custom SQL in the repository that will read all of the responsibilities from the EBS tables for the authenticated user and then roll them up into one field with each group name separated by a semi-colon.

Development Process:

1. Create a new Session Initializaton Block in the RPD. Name it something like 'getEBSGroups'.
2. Edit the Data Source for the initialization block to point to your EBS source.
3. Input the following SQL statement into the Default Initialization String block:





4. Create two Data Target Variables. The first is used to store the user name and the second is used to store the group information. You may call the first 'EBS_Username'. Make sure you name the second variable 'GROUP'.
5. Because you are authenticating against OID (LDAP), you should already have an initialization block for authentication purposes. You will need to ensure you edit your execution precedence to run the authentication initialization block prior to attempting to run this block.
6. Also ensure the user variable you utilized in your authentication initialization block is specified correctly in the following portion of the custom SQL:user_name=upper('valueof(NQ_SESSION.USER)')
7. Create repository groups that exactly match the names of the EBS responsibilities you are reading in and storing in the 'GROUP' variable.

Upon successful authentication into OBIEE, this new initialization block will kick off and query the EBS tables for the responsibilities of the authenticated user. It will store all of the user's EBS responsibilities in the GROUP variable. The GROUP variable will be parsed by the BI server and any responsibility in the variable that matches an existing repository group will result in the authenticated user being automatically assigned to that repository group!