A couple of years ago, my good friend and colleague Pete Tamisin and I had the pleasure to do a silly, fun presentation at Kscope15 (the presentation between trips to the beach and pool with my family — Hollywood, FL was a great location, ODTUG!). If you’re an ODTUG member, you can download our slides and video for our “How OBIEE Can Help You Achieve Your GOOOOOALS!!” presentation here.
Without getting into too many details, the presentation used a fake soccer match that some Rittman Mead colleagues and I created in order to build a game tracking page within an OBIEE dashboard. It was a lot of fun making ourselves look like idiots in the video, but the ultimate “goal” (get it?) was to explore all the different ways we could creatively make Oracle Business Intelligence Enterprise Edition (OBIEE) a friendlier tool for consumers, designers, modelers and analysts in order to drive usage.
Updating Data – How We Did It
As in any game tracking page, the stats needed to be updated very often (we chose 30 seconds as our window) and we explored a couple of different ways to get it done. Even though we knew which one we would like better, we wanted to compare. We used OBIEE’s out of the box Write Back (WB) functionality (not great), and we also built an interface using JQuery and AngularJS libraries that inserted/updated data into our database using RESTful web services (yes, there are other pieces to it, but we don’t have time for that now).
One option that we discussed, but didn’t end up using was building the same interface using Oracle APEX. In theory, we thought it could have been a good alternative for OBIEE’s Write Back (WB). This is what we built using WB (keep in mind the team names are not important here; we just needed a way to update amounts for goals and fouls):
Could we build something similar using APEX? Well, let’s try!
Updating Data – What We Should Have Tried
But first of all, for those of you who are not familiar with APEX (yes, I’m looking at you, OBIEE professionals…), visit the Oracle APEX site for the crash course.
Now, a couple of disclaimers and observations:
- I came into this blog post as someone who never touched APEX before. I watched a 5 minute video on how to create a page and got it done
- I used my OBIEE SampleApp VM for this test, so the data in my example will not match the goals and fouls you saw in the previous image
- As with our OBIEE Write Back attempt, we want to have the application embedded inside an OBIEE dashboard
I’m going to spare you all the details, but using my SampleApp VM, I was able to connect to my APEX application and create a new page. As you may know, APEX is a web application, where you operate in a browser the whole time (no clients of any sort). After going through the wizard where I defined the table and columns to be displayed, the structure of the page, etc., I was able to run my application and access my new Report (one of the objects I chose for my page). So far, I think I spent about 15 minutes between watching the video and building my own page. My report looks like this:
My report is retrieving every record from the table now (I didn’t set any WHERE statements when building my page), but I can filter the results here. I added a filter limiting my results for Locations in Brazil, and I now have only the result set I’m looking for:
Now, we have arrived at the point where APEX would have been a good alternative for our game tracking page. If you need to update any records on the table, you can just click the Edit button (little yellow pencil – OBIEE anyone?). I will update the last record of the table above because I was just notified that the Angra-3 facility has been completed, and operations start before the end of the year. Once I update the record, I can immediately see the new results:
In SQL Developer, I was able to confirm that the record has been updated:
Bringing It Back To OBIEE
Lastly, I want to embed my new page in an OBIEE Dashboard.
In one of my SampleApp dashboards, I created a new test page. In this page, I added an Embedded Content object and pointed the object to my new page in the application (note: I had to change a security setting in APEX in order for this to work):
Now, I have the same interactivity I had previously, but from within the OBIEE dashboard:
So, I’m convinced. APEX could’ve been a very good option as a way to insert and update data in our game tracking page. I am sure there are several other good use cases for APEX and OBIEE, and this first experience really got me excited to learn more.
Until next time.
Note: This blog post was written our friend, Andy Rocha of Rittman Mead America.