Select Page

Art of BI: Consuming a Web Service in OBIEE Presentation Services using JQuery CDN

Christian Screen | | August 7, 2009

Understanding web services in general is a big benefit for anyone using integrated technologies today.  That includes any system that integrates a web client on a closed intranet network or open network with access to the world wide web.  Because OBIEE presentation services is ultimately a web-based tool using your favorite web browser one would hypothesize that utilization of web services could also be incorporated by bringing in external data or reading from network data sources.

This post will show how to leverage presentation services to pull in a web service and integrate the web service data into our Dashboard.  This is a low level example just to get you thinking about the grand possibilities of the integration.

The data we will consume stems from a Yahoo.com web service built inside an Answers report view.  It does not require any database integration or schema modifications. We will display it in the dashboard.  In the dashboard our final product looks like this:

Create the Answers Report

To create the answers request we need to select at least one column from our schema. I have selected the ACTUALS metric only just make this a simple example but yours may look different when conducting your integration depending on requirements.

Add the Narrative View / Web Service Call Code

Add a Static Text view in order to hold our web service call via dynamic html. As you can see we are combining JavaScript and HTML. We are heavily using tags as we did in my previous Google Maps integration post. You will also notice our use of a JQuery CDN script include tag. We are using Google’s CDN for JQuery. I’ll right another post with more details on the benefits of using a CDN compared to keeping the JQuery file on your own network. But for know you can check our Dave Ward’s post on the topic at Enconsia.com.

I’ll also right a post about using JQuery with OBIEE in the upcoming weeks so stay tuned for that.

In the code below copy and paste it into the “Static Text” field of the static text view. Be sure to check the checkbox “Contains HTML Markup”. Once you do this you will see that only part of what we want to see from our finished example above shows in the “Display Results” section even though the checkbox is checked. I’ll save why this happens in a different post. However know for now that OBIEE prevents dynamic HTML from being displayed in this section due to overriding dynamic syntax inherent in OBIEE presentation services.

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.js" type="text/javascript"></script>
<script type="text/javascript">
    $(document).ready(function() {
         
var yqlUrl3= "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20weather.forecast%20where%20location%3D28278&format=json&callback=?";
$.getJSON(yqlUrl3, function(data){
  $.each(data.query.results.channel, function(index, item){
    $('#weather')
    .append($('</p>
<p>
</p>
<p />').html(item.description));
  });	
});


var yqlUrl1;
yqlUrl1="http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20(%22YHOO%22%2C%22AAPL%22%2C%22GOOG%22%2C%22MSFT%22%2C%20%22PTRY%22)&format=json&env=http%3A%2F%2Fdatatables.org%2Falltables.env&callback=?";
      $.getJSON(yqlUrl1, function(data){
        $.each(data.query.results.quote, function(index, item){
          $('#quotes').append(
            $('</p>
<p>
</p>
<p />').append($('<span class="left"/>').text(item.Name)).append($('<span class="right"/>').text(' $'+item.Ask)).append($('<span class="right"/>').text(' ' + item.Change))
          );
        });
      });

});
</script>

Latest Stock Quotes

Weather for Zip Code 28278

Notice in the code above that we are also using the Yahoo’s new web service data source directory and their new query language called Yahoo Query Language or YQL. Starting with the variable declaration “var yqlUrl3=” you can see the YQL syntax. It’s not that crazy. We have already encoded the syntax so that it is safe for http data transfer.

Save and Display

Go ahead and save your Answers request/report. View the results in “Compound Layout”. Now you should see your “Static Text” view if it is the only view you’ve created per this tutorial. Add it to your dashboard or leave it as a stand-alone report and you are ready to rock. There is no difference in adding this report/view to the dashboard in contrast to any others you’ve done in the past. Get crakin’ and have fun.

Conclusion

I believe that there are a lot of great things that we can look forward to with OBIEE especially with the next revision of Answers (Answers+). Right now via the use of web services and other “Mashable” techniques OBIEE can be promoted to even a strong dashboard presentation tool. This gives even more reason for customization of the tool at a client. My simple example of stock quotes and weather reporting is just simple enough to be expounded upon but complex enough to shine some light on integrating non-intrusive data elements to a solution. I will give props again to Venkat for creating a blog a while back about using web services in OBIEE. His post got me thinking about this type of integration.  Although his example is intrusive it shows another way to use web services in an OBIEE integration. I talked about YSQL, CDN‘s, and JQUERY in this post so click on the respective links to find out more about those technologies on your own time. Cheers.

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Hyperion Myth #9: SOX Audit Requests Are Time-consuming

With serious financial penalties, SOX audits can be intimidating — but they don’t have to be. Find out how you can use Datavail’s software to automatically prove SOX compliance.

Jonathan Berry | March 13, 2018

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS