BI & Warehousing

In this blog entry, my first since joining the company as a consultant a little over a month ago, I will be taking you through the process of embedding a D3 visualisation into OBIEE. In my first few weeks in this new role I’ve had the pleasure or working with the forward thinking people at Nominet and one of the technologies they’ve exploited in their BI solution is D3. They are using it to display daily domain name registrations’ by postcode on a map of the UK within OBIEE- very cool stuff indeed and after seeing it I was sufficiently inspired to have a go myself. We’ll start with an overview of D3 itself and then move onto a worked example where we will look at why you may want to use D3 in OBIEE and the best way to go about it. This blog entry is not intended to be a D3 tutorial, there are plenty of very good tutorials out there already, this is more targeted at OBIEE developers who may have heard of D3 but haven’t yet had a “play” ( yes, it’s great fun ! ) with it yet in the context of OBIEE. So without further delay let’s begin…..

What is D3 ?

To answer this question I will first tell you what D3 isn’t – D3 is not a charting library, you will find no predefined bar charts, no line graphs, no scatter plots, not even a single pie chart. “What no pie charts ?!” I here you say, don’t despair though, you can create all these types of visuals in D3 and a whole lot more. D3 which is short for “Data-Driven Documents” is a visualisation framework written entirely in JavaScript by Michael Bostock. The term framework is the key word here, D3 is generic in nature which allows it to tackle almost limitless visualisation challenges. The downside though is that you have to tell D3 exactly what you want it to do with the data you throw at it and that means rolling up your sleeves and writing some code. Fear not though, D3 has a great API that will enable you to get a fairly basic visual up and running in a short space of time. You’ll need a basic understanding of HTML, CSS and SVG and of course some JavaScript but with the help of the online tutorials and the D3 API reference you’ll be up and running in no time.

How does it work ?

D3′s API allows you to bind data to place holders in the browser’s DOM. You can then create SVG or HTML elements in these place holders and manipulate their attributes using the dataset you pass to it. Below is the code to display a very simple D3 visualisation to give you a basic idea of how it works.

var dataset = [ 10, 15, 20 ];                    

var svg = d3.select("body")               
               .append("svg")                                    
               .attr("width", 200) 
               .attr("height", 200); 

svg.selectAll("circle") 
               .data(dataset) 
               .enter() 
               .append("circle") 
               .attr("fill","none") 
               .attr("stroke", "green") 
               .attr("r",function(d){  
                    return d ; 
               }) 
               .attr("cy",100)
               .attr("cx",function(d){ 
                    return d * 8; 
               });

This code produces the following visual, admittedly this is not very impressive to look at but for the purpose of this example it will do just fine.

Let’s break this down and see what’s going on.

var dataset = [ 10, 15, 20 ]; 

Above is our data in a JavaScript array, no D3 here. We’ll look at how to generate this from OBIEE a bit later on.

var svg = d3.select("body") 
               .append("svg")                                    
               .attr("width", 200) 
               .attr("height", 200); 

Here we are seeing D3 for the first time. This is a single line of code split over several lines so it’s easier to read. Anyone familiar with jQuery will notice the chaining of methods with the “.” notation. The first line selects the html body tag and then appends an SVG element to it. The two “.attr()” methods then set the width and the height of the SVG.

 svg.selectAll("circle") 
               .data(dataset) 
               .enter() 
               .append("circle") 
               .attr("fill","none") 
               .attr("stroke", "green") 
               .attr("r",function(d){  
                    return d ; 
               }) 
               .attr("cy",100)
               .attr("cx",function(d){ 
                    return d * 8; 
               }); 

Here’s where it gets interesting. The first line selects all circles within the SVG, but wait, we haven’t created any circles yet ! this is where the the place holders come into play that I mentioned earlier. These place holders exist in memory only and are waiting to have data bound to them and then finally an actual circle element. The next line binds the data to the place holders, 10 to the first one, 15 to the next and 20 to the last. The magic .enter() method will then execute all the remaining statements once for each of our data elements, in this case 3 times. The .append(“circle”) will therefore be called 3 times and create 3 circle elements within the SVG.

The remaining statements will change attributes associated with the circles and this is where you can use the data to “drive” the document. Notice the attr(“r”… and the attr(“cx”… method calls, The “r” attribute defines the circle radius and the “cx” attribute sets the centre “x” coordinate of the circle. Both these methods have functions passed as arguments, the “d” variable in each function represents the current data element  (or datum) in the array, 10 the first time, 15 the next and 20 on the last. These functions then return a value to the attr() methods. In this case the radius of each circle is being set to 10,15 and 20 respectively and the x coordinate of each is being set to 80, 120, 160 respectively.

Right, that’s a basic overview of D3 and how it works, let’s now have a look at an example where you might want to use D3 in OBIEE.

A Worked Example

Let’s say we have some customers and each month, with a bit of luck ( and with the help of a Rittmanmead implemented BI system ! ) we sell to those customers. Based on the sales total for each customer each month we then place these customers into a scoring group. Group 1 for customers for which revenue exceeded £10,000, Group 2 for revenue greater than £5,000, Group 3 for revenue greater than £2,000 and Group 4 for revenue greater £0. At the end of each month we want to compare each customers current scoring group with the scoring group they occupied the previous month. It’s the movement of customers between groups each month that we are interested in analysing.

Here’s the criteria we have in OBIEE Answers.

And here are the results.

As you can see the Movement column is  calculated as  ( Group Last Month – Group This Month ).

Now at this point you might say “Job done, nothing more to do”, we can clearly see this months and last months scoring group and the movement between the two, we have successfully conveyed the information to the user with minimal fuss and you’d be right. We could even develop the results further by conditionally formatting the colour of each row to reflect the current group and maybe we could add an additional column that contains an up arrow, down arrow or equals sign image to indicate the direction of movement. This is where it gets subjective, some users love visuals, some prefer the raw data and some the combination of the two. For this example we are going to try and visualise the data and you can make up your mind which you prefer at the end.

Lets see what we can produce using some standard OBIEE visuals.

First up the Vertical Bar Chart

That’s not bad, we can see by how many groups a customer has shifted since last month with some customers clearly staying put. What we can’t see is from which groups they moved to and from, if we were to add in the Group Last Month and Group This Month measures to the chart it would look a complete mess.

Lets try something else, let’s try a line graph only this time we’ll include all three measures to make sure we’ve got all the information the user requires.

Mmmm, again not bad but to my mind it’s not particularly intuitive, you have to study the graph to get the information you’re after by which time most users will have switched off completely and may be better off a simple table view.

Lets have try again with a combined line and bar chart

A slight improvement on the previous attempt but still not great. An issue I have with the last 2 examples is that to my mind customers in Group 1 ( the best group ) should be at the top of the chart while customers in Group 4 should be at the bottom of the chart – the cream rises to the top, right ?! We have no way of inverting the Y axis values so we are stuck with this rather counterintuitive view of the data. We could ask the users if they could would kindly change their grouping system but it’s unlikely they’ll agree and anyway that would be cheating !

Here’s one last attempt just for fun !

Nope, we’re not making much progress here, this is just making me feel nauseous and believe me when I tell you I came up with several more ridiculous solutions than this, pie chart anyone ? So far the table view has been the clear winner over the visualisations in terms of ease of interpretation and this isn’t a dig at OBIEE’s visuals, OBIEE’s visual’s are great at doing what they are designed to do and they do it quickly. A D3 solution will take time and planning ( and debugging ) but you will have total control over the output and you’ll be able to express your data in a way that no OBIEE visualisation will ever be able to do. So with that in mind let’s have a look at one possible solution written in D3.

Developing a D3 Solution

In order to embed a D3 visualisation in OBIEE you’ll need to use a Narrative view. The Narrative view will enable you to gain access to the data that we need to drive our visualisation using the @n substitution variables where n equals the column position in the criteria. We’ll use this technique to generate some JavaScript from our analysis results that we can then use in our D3 script. Let’s look at doing that now.

In the Prefix section at the top we are declaring a JavaScript array variable called “data” that will contain the data from the analysis. The Narrative section contains the following code

data.push({customer:"@1",prev_group:@2,cur_group:@3,delta:@4});

Because this line of code is in the narrative section it will be output once for each row in the result set, each time substituting @1, @2, @3 and @4 for Customer, Group Last Month, Group This Month and Movement respectively and will dynamically generate the JavaScript to populate our array.  Notice that within the parentheses we have this format

{ key:value, key:value, key:value } 

This will in fact create a JavaScript object for us in each array element so we can then reference our data in our D3 script by using data.customer, data.prev_group and data.delta to get at the values. As you can see below the postfix section we now have a load of JavaScript code ready to be used in our D3 script for testing and development purposes.

At this point I would strongly recommend leaving OBIEE and opening up your favourite development IDE, you will soon get frustrated writing JavaScript code directly into OBIEE. Personally I like NetBeans but there are several other free alternatives out there.

To get started in your favourite IDE you can either download a copy of the D3 library from http://d3js.org/ or reference the online version from within your script. I’d recommend uploading a copy to the OBIEE server once you’ve finished developing and are ready to go into production. If you want to reference the online version you will need to include the following snippet between the <head></head> tags in your html file.

<script src="http://d3js.org/d3.v3.min.js" charset="utf-8"></script>

So to start developing your D3 solution create a new HTML project in your IDE. Add a reference to the D3 library and then copy and paste the generated JavaScript code from the OBIEE narrative between some <script> tags. You should end up with something like this:-

You are now ready to make a start, simply save the project and open up the resulting HTML file in your favourite browser to test any changes you make along the way.

When you are ready to embed the finished code into OBIEE you’ll first need to decide from where you wish to reference the D3 Library and the D3 Code you have just written. With regards to the D3 library as I mentioned earlier you can either reference the online version of the D3 library or copy the library to the OBIEE server and reference it from there. With the custom D3 code you’ve written you can again either upload the code to a file on the OBIEE server or you can just copy and paste your code directly into the narrative. I’d recommend uploading it to the OBIEE server so you can reference it in other analyses at a later date but for now let’s just paste it in.

Lets have a look at the completed Narrative View.

The First thing to note is that the “Contains HTML Markup” checkbox is ticked, this is required as we have now entered some <script> tags and a <div> tag and without this ticked OBIEE will not interpret them correctly. The first <script> tag in the prefix section is referencing the online D3 library. The second <script> tag in the prefix section is closed at the start of the postfix section and wraps around the “data” variable and the JavaScript used to populate it. Below the closing </script> tag in the postfix section we are creating a HTML DIV element that will contain the SVG created by the D3 script. Finally we either enter a reference to our custom D3 script on the OBIEE server or just paste it in between script tags. One important thing to note is that when we transfer the code from our IDE to OBIEE we only want to bring across the D3 code, we want to leave behind the “data” variable and all the html tags as OBIEE will be generating these for us.

So lets take a look at the solution written in D3.

As you can see this contains all the information we require. We can clearly see the current group that the customer occupies and the movement, if any, from the group they occupied the previous month. This could easily be enhanced so that when you hover your mouse over a customer the raw data is presented to the user in some way, you’d simply need to change the narrative section to include a new column from the criteria and the then write the required code in D3 to display it – you really are only limited by your imagination. It may take more time and effort to produce a D3 solution over using a standard OBIEE visual and yes, as with all customisations, you are exposed from a risk point of view when it comes to upgrades and when the person who developed the solution decides to go travel the world but for these edge cases where you just can’t get the right result using a standard OBIEE visual, D3 may just save your bacon.

Below is a live demo of the visualisation outside of OBIEE, it is seeded from random data each time you press the reload button.

It’s been tested in Firefox, Chrome, Safari and IE9. IE versions 8 and below do not natively support SVG although there are various workarounds, none of which have been implemented in this example.

And here’s a link to the code on jsfiddle.net so you can have a play with it yourself. D3 Demo

So in closing here are some tips around developing D3 in OBIEE.

• Try to sketch out on paper what you want your D3 solution to look like and then try a replicate it with code ( the fun bit ! ).
• Head over to d3js.org for some inspiration, there are some truly mind blowing examples on there.
• Use the Narrative view to generate some hard coded data, you can then use it for development and testing purposes.
• Don’t develop directly in OBIEE, use a purpose build development IDE
• Once you’re ready to go into production with your solution upload the D3 library and your custom code to the OBIEE server and reference it from there.

Have fun….

Yesterday I was configuring EM 12c for a Sun Super Cluster system. There were a total of 4 LDOMs where I needed to deploy the agent (Setup –> Add targets –> Add targets manually). Out of these 4 everything went fine for 2 LDOMs but for the other two it failed with an error message. It didn’t give much details on the EM screen but rather gave a message to try to secure/start the agent manually. When I tried to do that manually the secure agent part worked fine but the start agent command failed with the following error message:

oracle@app1:~$emctl start agent
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Starting agent ………………………………………………………. failed.
HTTP Listener failed at Startup
Possible port conflict on port(3872): Retrying the operation…
Failed to start the agent after 1 attempts.  Please check that the port(3872) is available.

I thought that there was something wrong with the port thing so I cleaned the agent installation, made sure that the port wasn’t being used and did the agent deployment again. This time it again failed with the same message but it reported a different port number ie 1830 agent port no:

oracle@app1:~$emctl start agent
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Starting agent ……………………………………………. failed.
HTTP Listener failed at Startup
Possible port conflict on port(1830): Retrying the operation…
Failed to start the agent after 1 attempts.  Please check that the port(1830) is available.

Again checked few things but found nothing wrong. All the LDOMs had similar configuration so what worked for the other two should have worked for these two also.

Before starting with the installation I had noted the LDOM hostnames and IPs in a notepad file and had swapped the IPs of two LDOMs (actually these two only ). But later on I found that and corrected. While looking at the notepad file it occurred to me that the same stuff could be wrong in /etc/hosts of the server where EM is deployed. Oh boy that is what it was. While making the entries in /etc/hosts of EM server, I copied it from the notepad and the wrong entries got copied. The IPs for these two LDOMs got swapped with each other and that was causing the whole problem.

deinstalled the agent, correct the /etc/hosts and tried to deploy again…all worked well !

Oracle distribute the new OBIEE SampleApp as a VirtualBox image, but it is dead easy to run it on VM Fusion instead. Here’s how:

1. Unzip the SampleApp archive files, and import the OVF to VM
2. Add and amend network adaptors if required. I always use three:
   • a host-only network private to my Mac and any other VMs running
   • NAT
   • Bridged

   . The first means I can always work with the VM from my Mac (SSH, web, etc) regardless of external network changes. The second two, NAT and Bridged, cover internet access out from the VM (software updates, etc) for 95% of situations.

   
3. Boot up. Expect to get X server error (because VMWare has a different video driver to VirtualBox). Keep selecting No, until you get to the console
   
4. Login as root/root
   
5. Remove the VirtualBox additions services:
   1. List the VirtualBox additions services

      chkconfig --list|grep vbox
      

   2. Disable VirtualBox additions, as root:

      chkconfig --list|grep vbox|awk '{print $1}'|xargs -I'{}' chkconfig --del {}
      

   3. Confirm they’ve been removed:

      chkconfig --list|grep vbox
      

   
6. Install VM Tools
   1. Select “Update VMWare Tools” from VM Fusion menu
   2. Mount the CD image on the guest

      mkdir /media/cdrom
      mount /dev/cdrom /media/cdrom
      

   3. Copy the installer to the guest

      cp /media/cdrom/VMwareTools*.tar.gz /tmp
      

   4. Unpack the installer

      cd /tmp
      tar xf VMwareTools*.tar.gz
      

   5. Run the installer. Go with all the defaults given.

      cd vmware-tools-distrib/
      ./vmware-install.pl
      

   6. Reboot the VM.

      reboot
      

   7. On reboot, the VM should startup to the graphical (GNOME) Desktop
      

Pimp my server

This is all optional, but steps I run on a new server instance to get it how I like it:

1. Open a terminal window and run setup

   sudo setup
   

   From here you can amend setting for things such as the keyboard layout, timezone, and networking. You can also use the desktop GUI tools to do this if you prefer

   
2. Add your public SSH key to ~/.ssh/authorized_keys to enable connecting over SSH without entering a password each time
3. Create ~/.screenrc (screen is already installed on SampleApp. See this post for details of what screen is and why it is so useful).

   hardstatus alwayslastline "%{= RY}%H %{kG}%{G} Screen(s): %{c}%w %=%{kG}%c  %D, %M %d %Y  LD:%l"
   startup_message off
   msgwait 1
   defscrollback 100000
   nethack on
   

   
4. Add EPEL yum repository:

   wget http://www.mirrorservice.org/sites/dl.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpm
   sudo rpm -i epel*.rpm
   

5. Update all packages

   sudo yum update -y
   

6. Set the OBIEE and DB instances to start automagically at bootup, using init.d script such as these

It's not often that I run across articles that really resonate with me. Last night was one of those rare occasions. What follows is a sampling of what I consider to be required reading for any IT professional with a slant towards database development.

• Bad CaRMa
• Big Ball of Mud
• Business Logic - PL/SQL Vs Java - Reg
• Thinking Clearly About Performance

Bad CaRMa

how NOT to design a database schema - super classic article. Every data architect should read this ! simple-talk.com/opinion/opinio… @timothyjgorman

— Kyle Hailey (@dboptimizer) June 11, 2013
That led me to Bad CaRMa by Tim Gorman. This was an entry in Oracle Insights: Tales of the Oak Table, which I have not read, yet.

A snippet:

...The basic premise was that just about all of the features of the relational database were eschewed, and instead it was used like a filing system for great big plastic bags of data. Why bother with other containers for the data—just jam it into a generic black plastic garbage bag. If all of those bags full of different types of data all look the same and are heaped into the same pile, don't worry! We'll be able to differentiate the data after we pull it off the big pile and look inside.

Amazingly, Randy and his crew thought this was incredibly clever. Database engineer after database engineer were struck dumb by the realization of what Vision was doing, but the builders of the one-table database were blissfully aware that they were ushering in a new dawn in database design...
This is from 2006 (the book was published in 2004). Not sure how I missed that story, but I did. Big Ball of Mud I've read this one, and sent it out, many times over the years. I can't remember when I first encountered it, but I read this once every couple of months. I send it out to colleagues about as often. You can find the article here.

A BIG BALL OF MUD is haphazardly structured, sprawling, sloppy, duct-tape and bailing wire, spaghetti code jungle. We’ve all seen them. These systems show unmistakable signs of unregulated growth, and repeated, expedient repair. Information is shared promiscuously among distant elements of the system, often to the point where nearly all the important information becomes global or duplicated. The overall structure of the system may never have been well defined. If it was, it may have eroded beyond recognition. Programmers with a shred of architectural sensibility shun these quagmires. Only those who are unconcerned about architecture, and, perhaps, are comfortable with the inertia of the day-to-day chore of patching the holes in these failing dikes, are content to work on such systems.
Read it. Remember it. Business Logic - PL/SQL Vs Java - Reg The article can be found here.

I'm don't believe this is the one that I would read just about every day during my first few years working with Oracle, but it's representative (I'll link up the original when I find it). I cut my teeth in the Oracle world by reading AskTom every single day for years. Some of my work at the time included working with java server pages (jsp) - at least until I found APEX. I monkeyed around with BC4J for awhile as well, but I believe these types of threads on AskTom kept me from going off the cliff. In fact, I got to a point where I would go to an interview and then debate the interviewer about this same topic. Fun times.

if it touches data -- plsql.

If it is computing a fourier transformation -- java.

If it is processing data -- plsql.

If it is generating a graph -- java.

If it is doing a transaction of any size, shape or form against data -- plsql. Thinking Clearly About Performance Cary Millsap. Most of the people seem to know Cary from Optimizing Oracle Performance, I didn't. I first "met" Cary virtually and he was gracious enough to help me understand my questions around Logging, Debugging, Instrumentation and Profiling. Anyway, what I've learned over that time, is that Cary doesn't think of himself as a DBA, he's a Developer. That was shocking for me to hear...I wonder how many others know that. So I've read this paper about 20 times over the last couple of years (mostly because I'm a little slow). I organize events around this topic (instrumentation, writing better software, etc) and this fits in perfectly. My goal is to one day co-present with Cary, while playing catch, on this topic (I don't think he knows that, so don't tell him). Link to his paper can be found here. Enjoy! I'll continue to add to this list as time goes on. If you have any suggestions, leave a comment and I'll add them to the list.

Now that the new v305 OBIEE SampleApp that we previewed in May is out,  I thought it’d be a good time to take a look at an experimental feature added into OBIEE 11.1.1.7′s logical SQL that gives you the ability to create, or “spin-off”, an entire Essbase cube from an OBIEE dashboard or RPD.

If you read my coverage of the new Essbase integration features in OBIEE 11.1.1.6.2 just over a year ago, you’ll remember that this release gave OBIEE the ability to persist aggregates to an Essbase ASO cube as well as regular Oracle, SQL Server or IBM DB/2 databases. This was impressive stuff but it came with one significant limitation: the Essbase ASO databases that the aggregate persistence wizard created contained just a single aggregation, for example sales by product category, month and customer type, rather than all levels and aggregations as you’d expect for a multi-dimensional database. The reason for this was obvious – this is how the aggregate persistence wizard worked with relational databases, and Oracle just took the same paradigm but allowed it to persist to Essbase as well as the various relational stores.

So fast-forward now to OBIEE 11.1.1.7, and the new v305 SampleApp has an intriguing dashboard page within the “8.21 Oracle Essbase Interaction” dashboard called “Cube Spin Off”. What this page demonstrates is a kind of preview of where OBIEE is going in the future, where it’ll be easy for users to take data within a subject area, spin it off as an Essbase cube and then automatically report against it, enabling faster reporting and access to MDX functions, forecasts and so on. In this first iteration, put together by the SampleApp team to show off the new logical SQL “CREATE CUBE” command, we’ve got an example of a command that will spin-off the Essbase cube along with an analysis created against the result of that command.

To create, or “spin-off” an Essbase cube using this command, cut and paste the logical SQL displayed in the analysis description, then select Administration > Issue SQL, and then paste the logical SQL into the text box provided, like this:

Note how I’ve bumped-up the logging level to 3, so I can take a closer look at what the BI Server does when I press the Issue SQL button.

Looking at the logical SQL command itself, what’s interesting is that it’s referencing logical dimension levels from the RPD in the command, rather than logical tables as would have been the case with the aggregate persistence wizard. Looking at the SampleApp v305 RPD in question, you can see that there’s more logical dimensions in total within this business model, so the logical SQL command can pick an arbitrary subset of the dimensions within the model, pick an arbitrary level to aggregate up from, and then pick one or more of the measures to create the cube definition.

Pressing the Issue SQL button, and then switching over to Essbase Administration Services (http://obieesample:9704/easconsole), you can see the Essbase database and application created by the CREATE CUBE logical SQL command.

Looking at the database (cube) definition itself, you can see that it’s a BSO (Block Storage Option) database rather than the ASO type that the BI Administraton tool’s Aggregate Persistence Wizard creates, and its also set up to allow duplicate member names across dimensions and levels, something that’s usually required when building cubes against relational sources that don’t have this member name restriction.

Looking back at the NQQuery.log file entries create by the logical SQL command, all you can see is the CREATE CUBE statement itself, like this:


[2013-06-12T05:07:09.000-04:00] [OracleBIServerComponent] [TRACE:3] [USER-0] [] [ecid: 274d73ee2c4a9d00:42d39d4a:13f2e44ecb4:-8000-00000000000018c7] [tid: 2e497940] [requestid: 37b70014] [sessionid: 37b70000] [username: weblogic] ############################################## [[
-------------------- SQL Request, logical request hash:
c88166e2
set variable LOGLEVEL = 3;CREATE CUBE "ESSCUBE2" for "B - Sample Sales Exa".."Base Facts"
("2- Billed Quantity","11- Fixed Costs")
AT LEVELS (
"B - Sample Sales Exa".."Products"."Products Hierarchy"."Product",
"B - Sample Sales Exa".."Time"."Time Hierarchy"."Month",
"B - Sample Sales Exa".."Offices"."Offices Hierarchy"."Offices");
/* QUERY_SRC_CD='rawSQL' */

]]


All of the complexity of the cube creation is abstracted away by the BI Server, with (presumably) the embedded Essbase Studio servlet used by the Aggregate Persistance Wizard also being used to define the Essbase database and load data into it.

When you open up the BI Administration too and view the repository that provided the source for the CREATE CUBE statement, what’s particularly impressive is that the new Essbase database has been mapped into the Physical layer of the repository, and then into the Business Model and Mapping layer as an LTS for the source logical tables, as the aggregate persistence wizard would do with regular, single-aggregation aggregate tables.

Very interesting stuff, and presumably at some point it’ll be incorporated formally into the Aggregate Persistence Wizard UI rather than having to run it from a logical SQL script. I was warned also that this feature is “experimental”, so expect there to be issues, limitations etc, but it’s an interesting glimpse into what appears to be Oracle’s original vision for data mart automation in OBIEE – just press a button and your collection of mapped-in sources becomes the design for an Essbase cube load, with the cube then taking the place of the original sources within the RPD model.

Finally and on a similar topic, you can read about the wider changes and improvements to Essbase integration in OBIEE 11.1.1.7 in my earlier post on this blog, as well as full details on the new SmartView integration in OBIEE 11.1.1.7 that also came along with this new release.

On June 9, the rates for Kscope13 go up $300 per person (basically, you're going up to the last minute, I-don't-know-why-I-waited-but-now-it-costs-a-lot-more price).  If you haven't registered yet for what is by far the best Oracle EPM, BI, Hyperion, Business Analytics, Essbase, etc. conference in the world, go right now to kscope13.com and register.  It'll be the best training experience of the year: you're basically getting 4.5 days of training that you won't see anywhere else the entire year... for the price of 2 days of training at an Oracle training center.

And when you register, don't forget to use promo code IRC to save $100 off whatever the current rate is.

The conference is June 23-27 in New Orleans though my favorite day is always the opening Sunday, so make sure you fly in Saturday night.  On Sunday, they turn the sessions over to the Oracle Development team to talk about everything they have planned for the next 1-3 years.  It's the one time each year that you can hear right from the people who are building it what you're going to be seeing in the future.  There's generally an hour on each major product line (an hour on Essbase, an hour on Hyperion Planning, an hour on mobile BI, etc.).  The keynote this year is Balaji Yelamanchili, the head of Oracle BI and EPM development for Oracle.  My only semi-complaint about this year's BI/EPM Symposium is that there's so much content that they're splitting it into three concurrent symposiums: Business Intelligence, EPM, and a special symposium for the EPM business users.

This year will be somewhat bittersweet for me since I am no longer actively involved with the chairing of the conference.  This means that I get to focus on going to sessions, learning things, playing/leading Werewolf games, and of course, presenting a few sessions.  Here are the ones I'm personally teaching:

• Using OBIEE to Retrieve Essbase Data:  The 7 Steps You Won’t Find Written Down.  This is in the BI track and it's basically all the quirks about connecting OBIEE to Essbase in a way that uses the strengths of each product.

• What’s New in OBIEE 11.1.1.7: Oracle on Your iPhone & Other Cool Things.  This is also in the BI track and it's an overview of all the things that people will like in 11.1.1.6 (for both Hyperion and relational audiences).

• Everything You Know About Essbase Optimization  is Incomplete, Outdated, Or Just Plain Wrong.  This is in the Essbase track and it's the one I'm most looking forward to delivering, because I get to break all of the optimization rules we all have been accepting as gospel for close to 20 years.

• Learn From Common Mistakes: Pitfalls to Avoid In Your Hyperion Planning Implementation.  This is a vendor presentation hosted by interRel.  I get to sit on the panel and answer Planning questions from the audience while talking about blunders I've seen during Planning implementations.  It should be fun/rousing.  Since it's all interRel, I wouldn't be surprised if a few punches were thrown or at minimum, a few HR violations were issued.

• Lunch and Learn: Oracle Essbase Panel.  This is an Oracle Technology Network panel during lunch on Tuesday.  I am chairing the panel and asking probing questions of a bunch of Oracle ACE Directors in the Essbase field.

• Innovations in BI:  Oracle Business Intelligence against Essbase & Relational (parts 1 and 2).  This is also in the BI track (somehow I became a BI speaker???) and I'm co-presenting this session with Stewart Bryson from Rittman Mead.  We'll be going over OBIEE on Essbase on relational and compare it to OBIEE on relational directly.  Stewart is a long-time friend and Oracle ACE for OBIEE, so it should let us each showcase our respective experiences with Essbase and OBIEE in a completely non-marketing way.

• CRUX (CRUD meets UX): Oracle Fusion Applications Functional UI Design Patterns in Oracle ADF.  This is in the Fusion track and I'll be talking about how to make a good user interface as part of the user experience of ADF.  No, this doesn't have a thing to do with Hyperion.

I am looking forward to all the wacky, new things Mike Riley (my replacement as Conference Chair for Kscope) has in store.  My first Kscope conference was in New Orleans in 2008 (back when they called it Kaleidoscope and no one was quite sure why it wasn't "i before e") so this is a homecoming of sorts albeit with 8 times as many sessions on Oracle BI/EPM.  If you're there (and let's face it, all the cool kids will be), stop by the interRel booth and say "hi."  It's the only 400 square feet booth, so it shouldn't be hard to find.

A freshly updated white paper on how to integrate BI Publisher 11g reports into an Oracle Forms 11g application is now available from the BI Publisher OTN page along with sample code and a video:

Integrating BI Publisher with Oracle Forms | Download Sample Code | Video 

Thanks to Axel and Florin from PITSS and Juergen and Rainer from Oracle Germany

Today I'm giving the first presentation in a 9-week long series on all the new things in Oracle EPM Hyperion 11.1.2.3 and OBIEE 11.1.1.7.  The session today (and again on Thursday) is an overview of everything new in all the products.  It's 108 slides which goes to show you that there's a lot new in 11.1.2.3.  I won't make it through all 108 slides but I will cover the highlights.

I'm actually doing 4 of the 9 weeks (and maybe 5, if I can swing it).  Here's the complete lineup in case you're interested in joining:

• June 4 & 6 - Overview
• June 11 & 13 - HFM
• June 18 & 20 - Financial Close Suite
• July 9 & 11 - Essbase and OBIEE
• July 16 & 18 - Planning
• July 23 & 25 - Smart View and Financial Reporting
• July 30 & Aug 1 - Data & Metadata Tools (FDM, DRM, etc.)
• Aug 6 & 8 - Free Supporting Tools (LCM, Calc Mgr, etc.)
• Aug 13 & 15 - Documentation

If you want to sign up, visit http://www.interrel.com/educations/webcasts.  There's no charge and I don't do marketing during the sessions (seriously, I generally forget to explain what company I work for).  It's a lot of information, but we do spread it out over 9 weeks, so it's not information overload.
And bonus: you get to hear my monotone muppet voice for an hour each week. #WorstBonusEver

Mark Rittman once joked during a presentation on OBIEE and SCM that “MUDE” (multiuser development environment) was the closest thing to a dirty word in the OBIEE documentation. I know many people who feel that way… some justified, some perhaps not, as MUDE has certainly gotten better in the later releases of OBIEE. As I mentioned in the introduction, I’d like to introduce MUDE in this post to set the stage for, at the very least, how good a competing multiuser development methodology would need to be. I’m not a genuine fan of MUDE… but I promise not to present a straw man to rip apart in my later posts. If you feel I don’t give it a fair shake, please comment and let me know. If you are interested in some other thorough treatments around MUDE, Venkat wrote about it on his old blog when the feature was first introduced in 10g, as did Mark here on this blog.

Let me start by putting forth what I think are the SDLC (software development life-cycle) imperatives that any solution to metadata development (or any other kind of development) should tackle without question:

1. Multiple users should be able to develop concurrently… but we need to be clear exactly what we mean by this. Having all the developers login to a shared online repository is one way to do multiuser development… but this equates to multiuser development using serialized access to objects. Whether explicit or not in the requirements… what most organizations want is non-serialized access to objects… meaning that multiple users can be making changes to the same objects at the same time, and we will be able to merge our changes together, and handle conflicts.
2. Our VCS should give us the ability to rollback to previous versions of our code, either for comparative purposes… or because we want to rewind certain streams of development because of “wrong turns”.
3. Our VCS and supporting methodology should provide us the ability to tag, secure and package up “releases”… to be migrated to our various environments eventually finding their way to production.

So let’s see if MUDE is up to the challenge. To enable MUDE, I start by defining one or more Projects in my binary RPD. So, I open the RPD in the Admin Tool, choose the Manage option from the toolbar, and select Projects… from the drop-down list:

Next… I choose all the objects I want to include in the project. Our choice for which logical tables to include from the Business Model (BMM) is driven by which logical fact tables we choose, using either individual Presentation Subject Area measure folders, or explicit BMM logical fact tables. We also select any other objects we want to include in the project: explicit Presentation layer folders, init blocks, variables, etc.:

Once we have configured one or more projects in the repository, we then take the binary RPD file and place it on a network share accessible to all developers. From here on out, the RPD file is referred to as the Master Repository. Now, whenever we want to check out a project from the Master Repository, we register the shared network drive location in the Admin Tool. We get to the configuration screen by choosing Tools and then Options… and then choosing the Multiuser tab:

Once a Master Repository exists in the development directory specified in the Admin Tool, we are then able to check out a project from the Master Repository. To check out a project, we choose the File menu, and from the drop-down menu, we select Multiuser and then Checkout… after that. Then we choose the project we want to checkout:

The Admin Tool now prompts us to create a new “subset repository”. This is a fully-functioning binary RPD file containing only the subset of objects that were defined in the project. We can call this RPD anything we like–in my example I called it gcbc_project1.rpd–and it will exist in the “repository” directory buried in the bifoundation directory underneath our instance directory, for instance: [middleware_home]\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository:

Once we have checked out our project, it’s interesting to see the files that the Admin Tool generates behind the scenes to manage MUDE. If we look in the Master Repository directory (this was the C:\master directory we configured in the screenshot above), we should have the following files:

• gcbc.rpd: The original Master Repository binary RPD file
• gcbc.000: A backup of the Master Repository RPD file. We will constantly see new versions of the backup file as development continues on the Master Repository, and we’ll see the numeric extension increment over time.
• gcbc.mhl: The history file… this tracks all the history of changes, when they were performed, and by whom. This file can be output to an XML file using the mhlconverter utility so that it’s readable without the Admin Tool.

Now, if we look in our local repository directory, we have the following files:

• gcbc_project1.rpd: The subset binary RPD file we created when we checked out our project above.
  
• originalgcbc_project1.rpd: Initially… this is an exact copy of the subset binary RPD at the time of checkout. It will not be affected by changes to the actual subset RPD, as it’s purpose is to serve as the “original” copy of the RPD during the 3-way merge. Also… it facilitates the Discard Local Changes options in the Multiuser menu option.

In looking at this infrastructure… there’s nothing magic happening here: MUDE simply uses the basic functionality of the Merge… and the Compare… that are built into the Admin Tool. I’m not saying this disparagingly. There’s no reason to maintain the code path for two different versions of a 3-way merge. MUDE is really nothing more than a framework for making the Compare… and Merge… functionality easier and more predictable. So, it’s not shocking when we choose Compare with Original… from the Multiuser menu and see a screen identical to the basic Compare… option without MUDE:

Similarly, when we choose the multiuser option Publish to Network… we see a window identical to the standard Merge… option in the Admin Tool:

So this was a high-level look at MUDE… hopefully I’ve done it justice. Now I’d like to discuss where I think MUDE comes up short as a competent VCS, or SCM… or whatever solution we think it is. On our list of the three imperative boxes that a metadata development solution should tick, I think number (1) is a slam-dunk. Although there have been issues with MUDE… almost every BI or ETL tool I’ve worked with over the years has issues in this area. I think there is also a fair argument to be made that MUDE also ticks the boxes for (2) and (3). But is it complete enough in the areas?

Although MUDE provides us the ability to interact with previous versions of our code, it does this with a “siloed”, metadata-only approach. If I were building an entire BI solution, I would want to associate my metadata repository with my web catalog, and also presumably with my database DDL scripts and ETL routines, regardless of which tool I used for that. MUDE only handles the RPD. If I could somehow figure out how to use a standard VCS such as Git or Subversion to check in all my code into one place, then I could see how everything looked at a particular point in time. The same goes with tagging and packaging releases. MUDE makes it easy to prepare a binary RPD file for release, but it provides no benefit when it comes to packaging a release for the entire BI system. I want a more pervasive solution.

I also think MUDE misses the mark with the conflict resolution workflow, which is depicted in the screenshot directly above. The Define Merge Strategy dialog occurs for individual developers when they try to publish changes back to the Master Repository. I would argue that the handling of conflicts should not be the developer’s job. Suppose I add the logical column % of Discount to a logical fact table as depicted above. If my change conflicts with a change from another developer a continent away, am I really in a position to be able to determine the appropriate conflict resolution at that point in time? I may not even know the other developer… or understand why we are both making changes to the same logical column. So regardless of whether conflicts arise, developers should be able to “publish” their changes to be resolved downstream by the source master. This source master role may be a part-time or full-time role… but this is the person whose job it is to resolve conflicts. So our SDLC solution needs to support the decoupling of multiuser conflict resolution from the development process.

In the next post, I’m going to take a look at the combination of MDS XML and Git. I’ll talk a little bit about Git, and why it’s superior to Subversion for our purposes. I’ll see if this combination can tick all the right boxes I described above.

This is my first blog post in quite a while… mostly because of all the planning and preparation that went into the Rittman Mead BI Forum, which is now sadly behind us. There’s been a lot of other activity around Oracle BI as well. Of course, we had OBIA 11.1.1.7.1 PS1 release recently, and this has us very busy internally, preparing for our first implementation, and thinking about what the training course will look like. Mark covered the subject very well… but still expect something from me on the new OBIA in the not-so-distant future. I’ve also been busy with my upcoming Kscope New Orleans presentations. Edward Roske and I have a 2-hour, double-room presentation on Essbase and OBIEE integration; I have an OBIEE and Data Vault presentation that I’m presenting with Kent Graziano; and then, I have my only solo presentation concerning MDS XML versus MUDE as a way of doing multi-user RPD development.

I presented on the MDS XML topic already at Collaborate 13 this year, and what I noticed from discussions with the attendees is that most users don’t understand where this feature fits in. Honestly… I wasn’t too sure myself when the feature was first released, so I thought I would take a look and see how organizations might use it. I was planning on addressing Aggregation next on the blog (a subject I recently spoke on at Collaborate as well), but the MDS XML subject seems to have more momentum… so here we go.

If you aren’t aware of what MDS XML is, or if you have an idea, but are still throwing your hands in the air, then let me try to explain first what it is. The OBIEE (and Siebel Analytics, and nQuire before that) metadata repository file has always been binary… that single RPD file that we deploy to the BI Server. But a single, monolithic binary file is a problematic solution for the OBIEE metadata layer… just as it is problematic for almost any deployment large or small. Microsoft faced a similar crossroads not so long ago with Office file formats: the world wanted non-binary, and at that time, XML was king. So Microsoft launched a file-format conversion project to produce a resulting Office file format based on XML and open standards (or at least… as open as Microsoft can allow itself).

The paramount issue with binary files in any technology project is their difficulty integrating with version control systems (VCS). Most of the efficiency capabilities of these systems, from merging functionalities, to cheap delta copies, revolves around the ability to do basic text diff’ing. We immediately lose all of this functionality when working with a binary file. But even a monolithic text file would be difficult to manage as well because it becomes impossible to track the granular changes made to individual objects. So what we’ve needed in OBIEE is the ability to store our repository as a collection of granular text documents, using a recognized format (such as XML) and having them all act together to form our metadata definition.

We have that capability now with the MDS XML feature in the Admin Tool. Whenever we “Create” a repository, “Open” a repository, or “Copy|Save As” a repository, we have the option to work instead with a directory of XML files using Oracle’s standard MDS format.

The Admin Tool will ask us for a directory to serve as the “core” directory for the RPD… a container to hold all the subsequent subdirectories and individual XML files (I used “core” instead of “base” because there is actually a “base” subdirectory in the directory tree.) The core directory serves as the pointer to the RPD… we browse to this high-level directory when opening, creating or copying an RPD file stored in this way. In the below screenshot, the “gcbc” directory is the core directory I specified in the Admin Tool during metadata repository development.

In the next few posts, I’ll examine this new file format and see what we can actually do with it. In all honesty, I put my first MDS XML abstracts forward at conferences before I even knew what was possible. I wanted to put this new feature through it’s paces and see what (if any) holes it filled in the current project delivery paradigm. In the next post, I’ll take a brief look at MUDE (I know, I’m sorry…) to see where the bar is currently set with repository multi-user development for OBIEE. After that, I’ll take MDS XML for a stroll along with the Git version control system… arguably the most powerful VCS to date. Finally, I’m planning on taking a look at what a delivery methodology might look like using Git… including RPD migration and rollout.

Oracle Endeca Information Discovery (OEID) version 3.0 now supports secure connection over HTTP, something useful for when implementing Endeca in a production environment where its expected that all security risks been controlled. Sending requests and data over unsecured HTTP is generally considered one of the more higher-risk vulnerabilities, with the solution generally being to add SSL encryption to standard HTTP communications.

However, enabling SSL connections between different parts of the OEID package seems on the surface to be a bit tricky, since all communications with the Endeca Server are via web service calls; therefore, having the Endeca Server configured for SSL requires all other servers that connect to it to be updated and re-configured. In this blog post therefore I’m going to walk through a step-by-step guide on how to setup this feature, and how to ensure everything subsequently works correctly.

SSL configuration on Oracle WebLogic Server

All Weblogic domains will need to be ‘SSL enabled’ and the secure port should be defined.  This can be done as one of the steps in the ‘Fusion Middleware configuration Wizard’. To see the setting select the ‘Administrator Server’ from the list, as shown in the screenshot below, when you reach this option point.

The next step is to check the ‘SSL enabled’ option and choose a ‘SSL Listen Port’.

Generate keys

The first step to configure SSL configuration between different parts of OEID is to generate an SSL key, and then share it between them. A key generator script comes as part of the OEID Weblogic domain installation, and should be accessible on:

WebLogicInstallationpath/user_projects/Endeca_domain/EndecaServer/bin/generate_ssl_keys.sh

When running this script, the Endeca Server WebLogic Server needs to be running, and the script requires both credentials to access the Endeca server, and also an SSL passphrase.

Browser certification

As an OEID developer, you might want to check some web-service requests on your browser; for example, if you want to make sure the Endeca Server is up and running you can try requesting its WSDL document:

https://Endeca_server_host:Endeca_server_port/endeca-server/ws/manage?wsdl

Having the SSL configuration, you need to add SSL certificates to your browser in order to receive reply from the server.

Open your browser and go to its preferences page.  Go to Advanced > Encryption tab and click on ‘view certificates’. This is the place that you will need to import the generated esClientCert.p12 file and private passphrase to.

Integrator Configuration

Integration configuration would be done in three areas; Integrator initial file, JRE variables and each graph component’s settings.

• Integrator.ini, This files is by default in the root of the Integrator installation directory. Add following lines under “-vmargs”.

         -Djavax.net.ssl.keyStore=yourcertkeystorefile.jks

         -Djavax.net.ssl.keyStorePassword=keystorepass

         -Djavax.net.ssl.trustStore=yourtruststorefile.jks

         -Djavax.net.ssl.trustStorePassword=truststorepass

• JRE Configuration, Same variables should be added to the Integrator designer JRE. To do so open clover Preferences on Window menu. Under Java> Installed JREs select the available jdk and click Edit. Add same option to ‘Default VM arguments’ and Finish the edit.

Components

Any graph component requesting a web-service call must be configured for SSL connection. Settings are not all the same and differ for each type. For a WEB_SERVICE_CLIENT component like below it is enough to make sure all calls are to a https address and the correct port has been defined. You’ll also need to Disable SSL Certificate Validation.

For a BULK ADD/REPLACE component it is enough to check SSL Enabled option.

 

Endeca Studio Data Sources

Create a folder under default lifreay path/data and call it ‘endeca-data-sources’. All you need to do is to re-copy generated key-stores to the new folder.

As a result if you add the ssl passphrase to the data source definition in the Studio control panel, the definition should be correct and connect successfully.

Provisioning Service

In case of Provisioning Service, firstly copy key-stores to path to Oracle Web-Logic/user-projects/domains/oracle.eid-ps/eidProvisioningConfig/

Secondly, go to the WebLogic Administration console. For the current server, enter the SSL passphrase in the key-stores and SSL configuration page, and then restart the server from the control page.

One of the major change introduced in BI Apps 11.1.1.7.1 is the way
how we manage the ETL task sequence and trim the unnecessary tasks.

This functionality was accomplished earlier using DAC.
 The problem we frequently faced was that the DAC repository and the
INFA repository are maintained as two separate repositories.  We have to
sync up the name of tasks exactly in order to use DAC to manage the
task execution of the Informatica workflow tasks.

Load Plan and Load Plan Generator was designed for addressing this requirement.

Here is a good article that describes the story.

Load Plan Generator – An Inside Look

For those of you still sitting on the 10g fence ... or if you're new to Publisher 11g, take advantage of this educational opportunity:

Oracle BI Publisher 11g R1: Fundamentals

Learn To:

    Create data models by using the Data Model Editor.
    Create BI Publisher reports based on data models.
    Create report layouts by using the Layout Editor (online).
    Create reports based on OBI EE data sources.
    Publish the reports on OBI EE Dashboards.
    Schedule reports and burst these reports.

Date: 01-JUL-13
Duration: 3 days
Location: Online

Click here for more details and to enroll

One of the first moves towards integrating Oracle Endeca Information Discovery (OEID) with other Oracle products is the ability to source data from an Oracle BI Server repository for loading into an Oracle Endeca Server “data domain” (the new name for an Endeca Server datastore).  As mentioned in my previous post, this functionality was initially introduced in OEID version 2.4 and works much the same in the new version 3.0.

This feature is based around Oracle Endeca Integrator Designer (“Integrator” for short), the client part of Integrator Suit that is used to design data loading graphs and optionally run them, and uses a “template project” which allows users to connect to an Oracle BI Server, run a select-query and then load repository data into an Endeca Server data domain. Some data domain attribute and indexing settings will also be configured, so that guided navigation and attribute/record search within Endeca Studio are set up for users looking to analyze the data we’re loading.

Two different scenarios come to mind that could make use of this feature. First, you might want to join some modeled data from your Oracle BI Server with other data that you have stored in Endeca Server; for example, allowing you to combine and analysis unstructured and structured data. In other words, you can create dashboards on your unstructured data that has been enriched by some descriptive structured data!

The second use case could be if you wanted to create some quick dashboards against data modeled within the BI Server repository, to find answers to new questions without the need to make extensive changes within the actual BI Repository – in other words, to carry out information discovery!

There are probably other use cases too, but whatever the reason you need to bring data from Oracle BI server to Integrator has made this very easy. In this post, therefore, I’m going to provide a step-by-step guide on how to setup this particular functionality, and load data from the OBI Server repository to the Oracle Endeca Server using the 3.0 release of OEID.

1. Firstly, open Integrator and select File > New > Project from the menu bar.

Note that with the earlier 2.4 release of Integrator, you had to make sure that a .jar plug-in file (found inside the installation package) for connecting to the Oracle BI Server had been installed in order to be able to see the “Load data from Oracle BI Server” wizard under Information Discovery category in the New Project displayed dialog. This step is not required for version 3.0. Click Load Data from OBI Server and press Next to proceed.

2. The Load Data from OBI Server wizard should be displayed. On the first page, enter the project name you are creating (for example, OBIConnection) or alternatively you can Use an existing project. In which case, after pressing Next again, the wizard will add new files to the project you have. I would suggest creating a new project to prevent any conflict, as you can always load the data into an existing data domain.

3. Next, In the Endeca Data Domain Configuration page, provide your up and running Endeca Server details and press Next to continue, for example:

Endeca Server Host: localhost

Endeca Server Port: 7002

Data Domain Name: Sample_Sales

The data domain name that you provide will be used to create a data domain as one of the initial steps of the Integrator project in a graph called “InitDataDomain.grf”.

Note, where the Endeca Server has been installed on a secure mode, you need to provide the ssl-listen port.

4. Now you’ll need to enter the OBI Server connection details such as User, Password, OBI Server host and OBI Server port; For example:

User: Weblogic

Password:  Password01

OBI Server host:  10.67.70.117

OBI Server port:  9703

9703 is the port that I could connect to. You should validate the credentials by Connecting to OBI Server and once you see the Connected message on the top of the dialog, proceed to the Next step.

5. You are now able to choose the repository you want and select the relevant objects/tables you need from the list of Tables by checking them. Press Next to continue.

6. The wizard will automatically prepare the required meta-data information for the attributes that you selected. Using this page, you can configure some OEID features such as Search Interface by editing the displayed values (Note that this property is only available for STRING attributes). Check the Edit Finished box (located towards the bottom left of the window) when you are happy with all configurations and then click Finish.

7.  Having now finished the wizard, a new project with all the configurations, metadata files, connection, SQL statement and graphs required to connect to the OBI Server will be added to the Navigator pane.

 

8.  You can now see the required database connection, as shown in the screen shot below, where the Oracle JDBC driver has been used.

9. It’s now time to run the Baseline graph in order to load the OBI Server repository data into the Oracle Endeca Data Domain. Expand the Graph folder within Navigator pane and open Baseline.grf. Press the green Run button from the toolbar. Check the Console to see Execution of graph successful.

Some graph components may require SSL configuration, if you are using a secure Endeca Server implementation; For example in the Load_Data graph, the Bulk_Add/Replace component has a SSL Enabled property that should be updated to true. Also, you’ll also need to change all http requests to https.

The “Load Data” graph uses a Query-Statement, which is located in the data-in directory and contains a SELECT statement from OBI Server repository objects.

10. Now its time to log into Endeca Studio and create a Data Source in the Control Panel pointing to the data domain you created in the Integrator project.

Test connection to ensure that it is working properly.

11.  One more change in OEID version 3.0 is that the old ‘Liferay portal’ term ‘Community’ has been updated to ‘Application’, which to me, feels more related to the subject of Information Discovery and thus makes sense, but that’s just a personal opinion. An ‘Application’ refers to a subject area where users can create different dashboards on different views of one and only one data-source.  Being in the home page of Oracle Endeca Studio, select New Application using the approved, newly created and validated data-source.

12.  OEID version 3.0 kindly, generously and automatically generates a new dashboard with all the required components such as a searchbox, a breadcrumb, a guided navigation and of course a results table.

So far we have created an Oracle Endeca Information Discovery dashboard upon our Oracle BI Server repository, but what is the advantage of doing this? Rather than being able to create reports very quickly, Perhaps joining some unstructured data from other data-sources to the Oracle BI Server repository and a bit of Text tagging, enrichment and salience analysis could be done in Integrator and will make the dashboards much more interesting.

Good luck to those of you trying this out.  Look out for my next post where I’ll explain how to read from Endeca Server and join its data to those coming from other sources.

Just a quick note to highlight that we are running an Oracle Endeca Information Discovery (OEID) event at Oracle’s Birmingham office on Wednesday 26th June.  Providing a great opportunity to learn how OEID can complement your current BI tools, allowing you to answer previously unanswerable questions through insight from both structured and unstructured data sources (such as social feeds and word documents).

There will also be experienced experts available before, during and after the event, providing a rare opportunity to get your questions answered by people who have been there and done it.

Click here for more information and to register.

Well, we’re all back home now after two very successful Rittman Mead BI Forum events in Brighton, and then Atlanta, earlier this month in May 2013. Around 70 OBIEE, ODI, Endeca and Essbase developers from around Europe got together in the first week in Brighton, followed by around 60 in Atlanta, and we were joined by Cary Millsap (Method R Corporation), Alex Gorbachev (Pythian) and Toby Potter (Data Sift) as special guest speakers over the two events. Thank you again to everyone who came along and supported the event, and a special thanks to the speakers without whom, of course, the BI Forum couldn’t take place. In addition, sincere thanks to Mike, Adam, Philippe, Alan, Marty, Jack and Florian from Oracle for coming along and sharing plans and insights around the Oracle product roadmap, and finally; congratulations to Antony Heljula (Peak Indicators Ltd) and Jeremy Harms (CD Group) who won the “Best Speaker” award for Brighton and Atlanta respectively.

Photos from the two events (a selection from Brighton are above, some from Atlanta below this paragraph) are available in these Flickr photo sets:

• “Rittman Mead BI Forum 2013, Brighton” (Flickr photo set)
• “Rittman Mead BI Forum 2013, Atlanta” (Flickr photo set)

As we always do, we’re also making the slides (where allowed by the speaker, and not under NDA) available for download using the links below, including the one-day Oracle Data Integration Masterclass provided by Stewart Bryson, Michael Rainey and myself. Note that Christian Screen’s and Jeremy Harms slides are actually online, so I don’t think you’ll be able to download them from whatever service is hosting them – sorry.

Oracle Data Integration Masterclass (Stewart Bryson, Michael Rainey, Mark Rittman, Rittman Mead)

• “Introduction to Oracle Data Integrator 11g”
• “ODI and the Oracle Reference Architecture for Information Management”
• “ODI and GoldenGate – A Perfect Match…”
• “ODI and Hadoop, MapReduce and Big Data Sources”
• “The Three R’s of ODI Fault Tolerance : Resuming, Restarting and Restoring”
• “Scripting and Automating ODI using Groovy and the ODI SDK”

Brighton RM BI Forum, May 8th – 10th 2013

• “OBIEE SampleApp 11.1.1.7 functional highlights” (Philippe Lions, Oracle Corporation)
• “OBI Performance Tuning – Real Customer Success Stories” (Antony Heljula, Peak Indicators Ltd)
• “Secrets of OBIEE implementation at LGI” (Marco Klaassens, Liberty Global)
• TED Session 1:  ”Why I want to be working with Business Intelligence in 5 years time” (Jon Mead, Rittman Mead)
• TED Session 3 : “Incrementally loading Exalytics using Notepad” (Antony Heljula, Peak Indicators Ltd)
• “Oracle Data Integrator 11g Best Practices. Busting your performance, deployment, and scheduling headaches.” (Uli Bethke/Maciek Kocon, Independent)
• “New Developments in BI Multi-tenancy and Cloud” (Adam Bloom, Oracle Corporation
• “The Magic of Aggregates” (Michael Wilcke, sumIT AG)
• “Integrating Oracle BI, BPM and BAM 11g: The complete cycle of information” (Edelweiss Kammermann, Awen Consulting)
• “Endeca – Beyond the Demos” (Adam Seed, Rittman Mead)

Atlanta RM BI Forum, May 15th – 17th 2013

• “It’s all in the genes – The power of Oracle Exadata and the Oracle Database” (Rene Kuipers, VX Company)
• “In Memory Analytics – Times Ten, Essbase 11.1.2.2 – Analysis – A Comparison” (Venkatakrishnan J, Rittman Mead)
• TED Session 3 : “A BI Publisher Beginner’s MacGyver-Hack for Financial Reporting with OBIEE: A Quickie!” (Jeremy Harms, CD Group)
• “Performance Tuning the BI Apps with a Performance Layer” (Jeff McQuigg, KPI Partners Inc)
• “Thinking Clearly about Performance” (Cary Millsap, Method R Corporation) – see also the accompanying technical paper
• ”Forecasting and Time Series Analysis in Oracle BI” (Tim & Dan Vlamis, Vlamis Software Solutions Inc)
• “Hadoop versus the Relational Data Warehouse.” (Alex Gorbachev, Pythian)
• “How to Create a Plug-In for Oracle BI 11g” (Christian Screen, Capgemini)
• “ODI and Hadoop / Big Data” (Alan Lee & Marty Gubar, Oracle Corporation)
• “BI Applications 11g and ODI” (Florian Schouten, Oracle Corporation)
• “OBIA 11G – What You Need To Know: Part 1″ (Kevin McGinley, Accenture)

So once again – thank you to everyone who came along, especially the speakers but also everyone from our Brighton and Atlanta offices who helped set the event up, and made sure it all ran so smoothly. See some of you again in Brighton and Atlanta next year, and our next outing is to ODTUG KScope’13 in New Orleans – another great event with the BI Track organised by Kevin McGinley – make sure you’re there!

One of OBIEE’s many great strengths is aggregate navigation; the ability to choose from a list of possible tables the one which will probably give the optimal performance for a given user query. Users are blissfully unaware of which particular table their query is being satisfied from, since aggregate navigation happens on the BI Server once the user’s request comes through from an Analysis or Dashboard.

This seamless nature of aggregate navigation means that testing specific aggregates are working can be fiddly. We want to ensure that the aggregates we’ve built are (i) being used when appropriate and (ii) showing the correct data. This is the particularly the case in Exalytics when aggregates are put into in-memory (TimesTen) by the Summary Advisor and we need to validate them.

Whilst the log file nqquery.log (or Usage Tracking table S_NQ_DB_ACCT) tells us pretty easily which table a query used, it is nice to be able to switch a query easily between possible aggregate sources to be able to compare the data. This blog demonstrates how we can use the INACTIVE_SCHEMAS variable (as described in my previous blog on loading Exalytics incrementally) to do this.

INACTIVE_SCHEMAS is a Logical SQL variable that tells the BI Server to exclude the specified physical schema(s) from consideration for resolving an inbound query. Normally, the BI Server will parse each incoming query through the RPD, and where a Logical Table has multiple Logical Table Sources it will evaluate each one to determine if it (a) can satisfy the query and (b) whether it will be the most efficient one to use. By using INACTIVE_SCHEMAS we can force the BI Server to ignore certain Logical Table Sources (those associated with the physical schema specified), ensuring that it just queries the source(s) we want it to.

In the following example, the data exists on both Oracle database, and TimesTen (in-memory). Whilst the example here is based on an Exalytics architecture, the principle should be exactly the same regardless of where the aggregates reside. This is how the RPD is set up for the Fact table in my example:

The GCBC_SALES schema on Oracle holds the unaggregated sales data, whilst the EXALYTICS schema on TimesTen has an aggregate of this data in it. The very simple report pictured here shows sales by month, and additionally uses a Logical SQL view to show the contents of the query being sent to the BI Server:

Looking at nqquery.log we can see the query by default hits the TimesTen source:

[...]
------------- Sending query to database named TimesTen aggregates
WITH
SAWITH0 AS (select distinct T1528.Sale_Amoun000000AD as c1,
     T1514.Month_YYYY000000D0 as c2
from
     SA_Month0000011E T1514,
     ag_sales_month T1528
[...]

Now, for thoroughness, let’s compare this to what’s in the TimesTen database, using a Direct Database Request:

OK, all looks good. But, is what we’ve aggregated into TimesTen matching what we’ve got in the source data on Oracle? Here was can use INACTIVE_SCHEMAS to force the BI Server to ignore TimesTen entirely. We can see from the nqquery.log that OBI has now gone back to the Oracle source of the data:

[...]
------------- Sending query to database named orcl
WITH
SAWITH0 AS (select sum(T117.FCAST_SAL_AMT) as c1,
     T127.MONTH_YYYYMM as c2
from
     GCBC_SALES.TIMES T127 /* Dim_TIMES */ ,
     GCBC_SALES.SALES T117 /* Fact_SALES */
[...]

and the report shows that actually we have a problem in our data, since what’s on the source doesn’t match the aggregate:

A Direct Database Request against Oracle confirms the data we’re seeing – we have a mismatch between our source and our aggregate:

This is the kind of testing that it is crucial to perform. Without proper testing, problems may only come to light in specific reports or scenarios, because by the very nature of aggregate navigation working silently and hidden from the user.

So this is the feature we can use to perform the testing, but below I demonstrate a much more flexible way that having to build multiple reports.

Implementing INACTIVE_SCHEMAS

Using INACTIVE_SCHEMAS in your report is very simple, and doesn’t require modification to your reports. Simply use a Variable Prompt to populate INACTIVE_SCHEMAS as a Request Variable. Disable the Apply button for instantaneous switching when the value is changed.

A Request Variable will be prepended it to any logical SQL sent to the BI Server. Save this prompt in your web catalog, and add it to any dashboard on which you want to test the aggregate:

Even better, if you set the security on the dashboard prompt such that only your admins have access to it, then you could put it on all of your dashboards as a diagnostic tool and only those users with the correct privilege will even see it:

Displaying the aggregate source name in the report

So far this is all negative , in that we are specifying the data source not to use. We can examine nqquery.log etc to confirm which source was used, but it’s hardly convenient to wade through log files each time we execute the report. Ripped off from Inspired by SampleApp is this trick:

1. Add a logical column to the fact table
2. Hard code the expression for the column in each Logical Table Source
3. Bring the column through to the relevant subject area
4. Incorporate it in reports as required, for example using a Narrative View.

Bringing it all together gives us this type of diagnostic view of our reports:

Summary

There’s a variety of ways to write bespoke test reports in OBI, but what I’ve demonstrated here is a very minimal way of overlaying a test capability on top of all existing dashboards. Simply create the Request Variable dashboard prompt, set the security so only admins etc can see it, and then add it in to each dashboard page as required.

In addition, the use of a ‘data source’ logical column in a fact table tied to each LTS can help indicate further where the data seen is coming from.

One of the major change introduced in BI Apps 11.1.1.7.1 is the way how we manage the ETL task sequence and trim the unnecessary tasks. This functionality was accomplished earlier using DAC.  The problem we frequently faced was that the DAC repository and the INFA repository are maintained as two separate repositories.  We have to sync up the name […]

I'm not sure why there wasn't a major announcement about this, but as of April 9, customers buying an Exalytics machine to speed up their Oracle Business Intelligence can get 2.4 Tb of PCIe flash drives from Oracle certified and engineered to run on Exalytics.  The cost (as of April 9's price list) is $35,000 (search for "flash upgrade kit").

While I haven't seen one in action yet, the flash pack seems to be 6 Sun Flash Accelerator F40 PCIe Cards each of which has a capacity of 400 Gb.  These cards run amazingly fast with read times of more than 2 GB/second (write time is about half that speed at 1+ GB/second).  These cards normally sell for almost $6K each, so Oracle is providing the flash add-on pack for no more markup than you'd get if you bought them on your own (but you'd then have to get them into the Exalytics machine all on your own).
This Matters If You Own EssbaseWhy would you want this?  Essbase, primarily.  Essbase uses a ton of disk I/O and one of the ways Exalytics can speed up Essbase is by pulling your cubes into a RAMDisk (since you have 1 Tb of RAM to play with).  At some point, though, it has to get that data from physical drives to a RAMDisk (unless you're building all your cubes at start up in memory each time).  Having blazingly speedy flash drives with .25 millisecond read latency allows you to store your cubes on the flash drive and then pull into RAM much more quickly than reading from traditional drives.

We have tested Essbase running on flash drives and it helps everything (particularly minimizes the negative effects of fragmentation since seek time drops to basically nothing on flash).  For customers buying Exalytics primarily for Essbase, the Exalytics Flash Upgrade Kit should be strongly considered with every Exalytics purchase (and if you already own Exalytics, buy it to put on top).

OBIEE is much less affected by hard drives, so while it may help OBIEE, this really matters a lot more to Essbase customers.
Oracle EPM Fully Supported on ExalyticsSince we're on the subject of Exalytics, now that 11.1.2.3 is out, all Oracle EPM/Hyperion components certified to run on Linux will run on Exalytics PS2.  These include:

• Administration Services
• Calculation Manager
• EPM Workspace
• Essbase Server
• Essbase Studio Server
• Financial Reporting
• Interactive Reporting (32-bit only)
• Oracle HTTP Server
• Planning
• Profitability and Cost Management
• Production Reporting (32-bit only)
• Provider Services
• Reporting and Analysis Framework Services and Web Application 
• Shared Services
• Web Analysis

In the two previous postings in this series on the Oracle BI Apps 11.1.1.7.1, we looked at the release at a high-level, and then at the product architecture including the new configuration and functional setup tools. From a technology and developer perspective though probably the most interesting thing about this new release is its use of Oracle Data Integrator as the ETL tool rather than Informatica, and the doing-away with the DAC for load orchestration and monitoring.

This introduction of ODI brings a number of potential benefits to customers and developers and gives Oracle the opportunity to simplify the product architecture, but bear in mind that there’s no migration path from the earlier 7.9.x releases to this version, with Informatica customers instead having to wait until the “patch set 2″ version due in the next twelve months; even then, migration between tools won’t be automatic, with existing Informatica-based installations expected to stay on Informatica unless they choose to re-implement using ODI.

So how does ODI work within this new release, and how has the DAC been replaced? Let’s take a look in this final piece in our short series on Oracle BI Apps 11.1.1.7.1, starting by looking at the overall role that ODI plays in the platform architecture.

Existing ODI developers will know that the tool uses two repositories, known as the Master and Work repositories, to store details of data sources and targets, mappings, data models and other aspects of an ETL project. Within the BI Apps these two repositories are stored in a schema called prefix_ODI_REPO, for example DEV_ODI_REPO, and are accompanied by a new schema called prefix_BIACOMP, again for example DEV_BIACOMP. The BIACOMP schema contains tables used by the various new WebLogic-based BI Apps supporting applications, and contain details of the functional setup of the BI Apps, load plans that have been generated and so forth. There’s also another schema called prefix_BIACOMP_IO which is used for read-write access to the BIACOMP schema, and all of these are held in a repository database alongside the usual schemas used for OBIEE, MDS and so forth.

The major difference in using ODI within this environment is that it’s treated as an “embedded” ETL tool, so that in most circumstances you won’t need to use ODI Studio itself to kick-off load plans, monitor their execution, set up sources and targets and so forth. This was the original vision for Informatica within the original BI Apps, but Oracle are able to do this far more effectively with ODI as they own all parts of the tech stack, can alter ODI to make it easier to embed, they’e got control over ODI’s various metadata APIs and so forth. What this means in practice is that the setup of the ODI topology (to connect to the ERP sources, and the target data warehouse) is done for you via a web-based application called the Oracle BI Applications Configuration Manager, and you can kick-off and then monitor your running ETL jobs from Configuration Manager and from ODI Console, the web-based operator tool that’s been around since the 11g release of ODI. The screenshot below shows Configuration Manager setting up the source database ODI topology entry, with the details that you provide then being pushed through to the ODI master repository:

Setting up a new BI Apps system involves using the Configuration Manager to define the connections through to the various source systems, then select the BI Apps modules (Financial Analytics, for example, and then the various subject areas within it) that you wish to implement. There are then a number of steps you can perform to set up system-wide settings, for example to select default currencies or languages, and then you come to run your first ODI load plan – which in this instance copies settings from your source system into the relevant tables in the BIACOMP schema, performing automatically the task that you had to do via the various domain configuration spreadsheets in the earlier 7.9.x releases – the screenshot below shows this ODI load plan listed out and having run successfully.

You can then view the execution steps and outcome either in ODI Console (embedded within Configuration Manager), or over at ODI Studio, using the Operator navigator.

Moving over to ODI Studio, the folders (or “adapters”) that in Informatica used to hold workflows and mappings for the various source systems, are contained with the BI Apps project within the Work repository and the Designer navigator. In the screenshot below you can also see the Fusion Apps adapter that’s not supported in this particular release, and the ETL Data Lineage adapter that should get enabled in an upcoming patch release.

In the screenshot above you can also see one of the loading tasks, SDE_ORA_APAgingBucketsDimenson, is a package that (were you to expand the Interfaces entry) makes reference to a regular, and also a temporary, interface.

Packages in ODI perform the same role as Informatica workflows in earlier releases of the BI Apps, and each package runs some steps to refresh variables, work out if its doing a full or incremental load, and then call the relevant ODI interface. Interfaces in ODI for the BI Apps typically load from other temporary interfaces, with these temporary interfaces performing the role of maplets in the Informatica version of the BI Apps, as you can see in the screenshot on the left below. On the right, you can see the flow for another mapping, along with one of the custom KMs that come as part of the BI Apps 11.1.1.7.1 package.

Individual packages are then assembled into the equivalent of BI Apps 7.9.x “execution plans” through a new JEE application called the Load Plan Generator, which also gets installed into ODI Studio as a plug-in so you can develop new data loading routines away from the full production setup. As you can see in the final screenshot below, these load plans are then visible from within ODI Studio (whether you generated them there, or from Configuration Manager), and like all ODI 11g load plans you can view the outcome of each load plan instance run, restart it if this feature is enabled, and so forth.

So there you have it – how ODI is used within the BI Apps 11.1.1.7.1. I’m going to take a break now as it’s almost time for the Atlanta run of the Rittman Mead BI Forum 2013, but once I’m back in the UK I’ll try and put something together for the blog on pulling together your first ETL run. Until then – have fun with the release.