Using D3 and OBIEE

Standard

OBIEE is an integration of several pieces of technology that creates an enterprise grade scalable platform for delivering business analytics.  Because of the capabilities of OBIEE, people with no programming skills can create rich and complex visualizations using just the base functionality of the product.

When organizations do need special visualizations that can’t be accomplished with OBIEE’s delivered visualizations there can be a tendency to turn to other technology stacks because of the perception that either OBIEE isn’t easy to integrate or that using JavaScript libraries, like D3, within OBI will make it harder to upgrade the OBI in the future.

The proliferation of open source technologies for visualizing data in recent years has made it far easier for organizations to build rich and dynamic visualizations targeted at specific analytics needs that simply are not available inside of purchased applications.

OBI provides a number of key capabilities that are difficult, expensive and time consuming to reproduce.  Some of those capabilities include: a scalable webserver with performance and logging capabilities that has 24/7 worldwide support, the capability to query and federate hundreds of different data sources and create a reusable and extensible business metadata layer, security services that can concurrently integrate with multiple corporate identity management systems and extend that security to row level results, and an analytics deployment mechanism that already extends across the organization.

Given a business case where (Gasp!) OBIEE can’t provide the needed functionality out of the box how can an organization take advantage of functionality that OBIEE provides and still satisfy their internal requirements?

OBIEE has always provided the capability to integrate with other technologies and one of the easiest technologies to integrate is open source JavaScript and CSS libraries such as D3, jQuery and jQueryUI.

The steps to using a D3 visualization within OBI are easy:

  1. Make libraries available
  2. Create an analysis to get data you can use with D3
  3. Create visualization

The first two steps are super easy. The third step is where the actual effort is because you have to write some JavaScript code.

Note: you will need to have, or develop, some skills to do step 3 but those skills are on par with other skills within the scope of OBI development. Don’t let this stop you from trying this out!

If you have ever done RPD or ETL work then you can handle JavaScript development.  If you haven’t ever worked on the RPD or ETL, but you are sure you could if someone would just show you how, then you can handle JavaScript development.  If you have ever created an Access Database, or an absurdly complicated Excel spreadsheet with formulas and macros, then you can handle JavaScript development.  If you are obsessive compulsive and capable of using Google then you can handle JavaScript development.  If all of these things sound beyond you then you should probably stop now and save yourself the time and effort.

Step 1 – Making Libraries Available

There are 3 possible approaches to getting the JavaScript/CSS you need depending on your situation.

Situation 1 – I have control of my OBI server or the person who does is reasonable and will do stuff if I ask them to and therefore I can put files on my OBI server and deploy the analyticsRes directory.

Oracle documents detailing how to deploy analyticsRes can be found here – https://docs.oracle.com/middleware/12212/biee/BIESG/GUID-237D613D-CC0D-464D-BBC2-9A223CD12567.htm#BIESG9186

There are also at least 100 blog entries on how to do it.  It takes about 15 minutes.

Here’s a screenshot from sampleApp Weblogic console showing the mapping of the analyticsRes deployment to the /app/oracle/biee/user_projects/domains/bi/custom_wls_apps/analyticsRes/ file system directory on the OBI server.
Pic1

Here’s a screenshot of the contents of that directory on the filesystem –

Pic2

The libraries directory is where I have stashed all my stuff, so if I wanted to prove that the directory can be served over the web I could enter http://MyOBIServer:7780/analyticsRes/libraries/d3/d3.js into a browser and I should see the JavaScript code for D3 –

Pic3

At this point all of the libraries I need to access to make my very own visualization are deployed on the OBI server and available by using a script tag like this:

Or a link tag like this for CSS:

http://librariesd3d3.min.js

Situation 2 – You can reference links outside your corporate firewall

This is super-duper easy and takes about 1 minute.  D3 and jQuery will get you started nicely and you can reference them using the following:

If it’s easier you can also load all the libraries you need into an AWS S3 bucket (or your favorite bucket storage) and make the bucket available via URL.  The script tags will be exactly as above except referencing the URL for the bucket you have created.   You can read up on buckets here: http://docs.aws.amazon.com/AmazonS3/latest/dev/Welcome.html

Situation 3 – Nobody trusts you or it’s just too painful and I wanna do it right now!

You can just copy paste the code from D3 and jQuery into a text area on your dashboard

http://librariesd3d3.min.js

Pic4

It’s quick, it’s effective, it’s lemon scented but for the love of all that is holy please don’t make this your production deployment approach because it’s also maddeningly slow every time you edit or save your dashboard page, not reusable and will offend anyone with a sense of decency.  But I’m not here to tell you how to live so do what you have to do.

Step 2 – Create an analysis to get data you can use with D3

For this example, I am going to produce a visualization that shows all of the sales reps for a given manager as boxes next to that manager’s name. This example pulls data from the commonly-used SampleApp application.

This is as simple as creating a narrative view on an analysis and having it translate data into JSON.

The basic format is as follows:

Prefix

var myVar={};

myVar.data=[];

Narrative

myVar.data.push({“col1”:@1,”col2”:@2});

Postfix

//do something with myVar.data

Using Sample App we can create a simple example using the Sample Sales subject area:

Pic5

Pulling the Manager Name, Sales Rep Name and Revenue we get a table view like the following –

Pic6

Next we create a narrative view using our format from above and then using the Developer Tools console of our browser (Internet Explorer in this case) we can check whether our JavaScript variable has been created by entering something in the console and hitting enter.  When we do this we see that our object has been created and we can drill down on it to see the data we expect:

Pic7

With this working we are now ready to move on to creating a visualization.

Step 3 – Create Visualization

Here comes the actual work.  JavaScript and D3 (a JavaScript library) are both magical and perplexing.  The hardest part about JavaScript is realizing it doesn’t wait to finish one thing before it gets started on the next.  This means if you need it to wait for something to happen (like fetch your data) then you need to use a callback function.  This will hurt your brain but it’s actually relatively simple after you get the hang of it.  We won’t need it for this example but just keep it in mind for later when you get all crazy.

D3 binds data to HTML elements (so you can build awesome visualizations) but what’s strange about it is D3 actually creates placeholders for the visual representations of your data before they exist.  It’s not terribly difficult to get used to but it’s a new way of thinking.

So let’s create our very own visualization.  First we need to add references to the code libraries in our narrative view and then add a div element that will contain the svg canvas D3 uses to draw our visualization.  Then we add the svg to our div element:

Pic8

Now if we run our narrative view and use the DOM explorer on our developer tools we can see the element we’ve created:

Pic9

The first thing we will do with our data is create an array of our distinct Manager names using some simple JavaScript and then we will begin using the magic of D3 by creating an html group element for each manager.

Let’s dig in to the function we used to add groups to the page:

myGrp=mySVG.selectAll(‘g’).data(distinct).enter().append(‘g’).attr(‘id’,function(d){return d.replace(” “, “”);});

First you’ll notice that d3 allows us to chain methods.  Each period represents a function and we can chain these together to get a lot of work done without being too verbose about it.

myGrp is a variable we assign the results of our function to and will be very useful for the next bit of code.

mySVG is the variable we assigned our drawing canvas to when we appended an svg to the page.

selectAll(‘g’) is the place holder I mentioned earlier and tells d3 that we have some data coming that we would like to do something with

data(distinct) is a reference to the array we created for our manager names

enter() is a function for joining each element in our array to an html element

append(‘g’) actually appends the group elements to our svg

and finally attr(‘id’,function(d){return d.replace(” “, “”);}) gives the html element an attribute ‘id’ equal to the manager name with the space removed.  ‘d’ is d3’s way of referring to the data being bound with an element and we use a function to return the value of d when assigning it to an attribute.  By inspecting the page with our browser DOM explorer we can see the elements created by our function.

Pic10

At this point we still don’t have anything visible on the page we’ve just created a group element and bound it to our data, but because we have bound the data the rest is easy.  Using the myGrp variable we can append a rectangle and d3 will automatically do it for each data value.  Our rectangle just needs some basic information to render like width, height, and the x/y position to draw it.  You will see that we use a function to refer to another automatic variable I that represents the element number in our data.  We have statically set x to 25 pixels from the edge of our svg and y is incremented 30 pixels down from the top for each element.  We can see the result of adding our rectangle below –

myGrp.append(‘rect’)

.attr(‘width’,150)

.attr(‘height’,25)

.attr(‘x’,function(d,i){ return 25})

.attr(‘y’,function(d,i){return i*30});

Pic11

The obvious thing missing from our rectangles is some text to tell us what they represent.  We add this the same way also setting the font color, size and family with the style attribute.

myGrp.append(‘text’)

.attr(‘x’,function(d,i){return 30})

.attr(‘y’,function(d,i){return (i*30)+18})

.text(function(d) { return d;})

.attr(“style”,”fill:white;font-family:sans-serif;  font-size:15px;”);

Pic12

Now if we would like to add rectangles for the Sales Reps that report to each manager it’s as easy as rinse and repeat by looping through our distinct manager array and appending a group, rectangle and text element for each rep.

Pic13

While rectangles aren’t particularly exciting the basic concept shown here can be used to produce nearly any visualization you can think of.

Once you master the basics of creating a simple visualization, the possibilities are almost endless. Here is another example of a project similar to a project I recently completed for a customer.

Imagine that we want to render incidents along I-70 at Eisenhower Pass in Colorado and show both the milepost they occurred at and the relative elevation and grade along with creating context menu’s for detail information we might create a visualization like this:

Pic14

While this may look much more complicated, it’s just adding some additional properties and uses the same basic techniques as the previous simplistic example.

I hope this post gives you an idea of how easy it is to use d3 and other libraries inside of OBI and a sense of what’s possible if you have a requirement a custom visualization.  Happy coding!

Adding a coordinate system to Oracle 12c Database

Standard

Had a situation where I was given a shape file from ESRI with EPSG 102005 which isn’t in the database.  When importing the shapefile through MapBuilder it of course flamed so I needed to figure out how to add the coordinate system to the database.

Found this on MOS –  Example Adding A New Coordinate System based on the EPSG model introduced in 10gR2 (Doc ID 395171.1) and was able to put together what I needed to import shapefile and work with it using MapBuilder and MapViewer Editor.

Here’s the SQL I used to insert new definitions into database –


/*
PROJCS[
"USA_Contiguous_Equidistant_Conic",
GEOGCS["GCS_North_American_1983",
DATUM[
"North_American_Datum_1983",
SPHEROID["GRS_1980",6378137,298.257222101]],
PRIMEM["Greenwich",0],
UNIT["Degree",0.017453292519943295]],
PROJECTION["Equidistant_Conic"],
PARAMETER["False_Easting",0],
PARAMETER["False_Northing",0],
PARAMETER["Longitude_Of_Center",-96],
PARAMETER["Standard_Parallel_1",33],
PARAMETER["Standard_Parallel_2",45],
PARAMETER["Latitude_Of_Center",39],
UNIT["Meter",1],
AUTHORITY["EPSG","102005"]]
*/

insert into MDSYS.SDO_COORD_OPS ( 
COORD_OP_ID, 
COORD_OP_NAME, 
COORD_OP_TYPE, 
SOURCE_SRID, 
TARGET_SRID, 
COORD_TFM_VERSION, 
COORD_OP_VARIANT, 
COORD_OP_METHOD_ID, 
UOM_ID_SOURCE_OFFSETS, 
UOM_ID_TARGET_OFFSETS, 
INFORMATION_SOURCE, 
DATA_SOURCE, 
SHOW_OPERATION, 
IS_LEGACY, 
LEGACY_CODE, 
REVERSE_OP, 
IS_IMPLEMENTED_FORWARD, 
IS_IMPLEMENTED_REVERSE) 
VALUES ( 
102005000, 
'USA_Contiguous_Equidistant_Conic', 
'CONVERSION', 
NULL, 
NULL, 
NULL, 
NULL, 
9802, 
NULL, 
NULL, 
NULL, 
NULL, 
1, 
'FALSE', 
NULL, 
1, 
1, 
1);

//8821: Latitude_Of_Origin
insert into MDSYS.SDO_COORD_OP_PARAM_VALS ( 
COORD_OP_ID, 
COORD_OP_METHOD_ID, 
PARAMETER_ID, 
PARAMETER_VALUE, 
PARAM_VALUE_FILE_REF, 
UOM_ID) 
VALUES ( 
102005000, 
9802, 
8821, 
39.0, 
NULL, 
9102);

//8822: Central_Meridian
insert into MDSYS.SDO_COORD_OP_PARAM_VALS ( 
COORD_OP_ID, 
COORD_OP_METHOD_ID, 
PARAMETER_ID, 
PARAMETER_VALUE, 
PARAM_VALUE_FILE_REF, 
UOM_ID) 
VALUES ( 
102005000, 
9802, 
8822, 
-96.0, 
NULL, 
9102);

//8823: Standard_Parallel_1
insert into MDSYS.SDO_COORD_OP_PARAM_VALS ( 
COORD_OP_ID, 
COORD_OP_METHOD_ID, 
PARAMETER_ID, 
PARAMETER_VALUE, 
PARAM_VALUE_FILE_REF, 
UOM_ID) 
VALUES ( 
102005000, 
9802, 
8823, 
33.0, 
NULL, 
9102);

//8824: Standard_Parallel_2
insert into MDSYS.SDO_COORD_OP_PARAM_VALS ( 
COORD_OP_ID, 
COORD_OP_METHOD_ID, 
PARAMETER_ID, 
PARAMETER_VALUE, 
PARAM_VALUE_FILE_REF, 
UOM_ID) 
VALUES ( 
102005000, 
9802, 
8824, 
45.0, 
NULL, 
9102);

//8826: False_Easting
insert into MDSYS.SDO_COORD_OP_PARAM_VALS ( 
COORD_OP_ID, 
COORD_OP_METHOD_ID, 
PARAMETER_ID, 
PARAMETER_VALUE, 
PARAM_VALUE_FILE_REF, 
UOM_ID) 
VALUES ( 
102005000, 
9802, 
8826, 
0.0, 
NULL, 
9001);

//8827: False_Northing
insert into MDSYS.SDO_COORD_OP_PARAM_VALS ( 
COORD_OP_ID, 
COORD_OP_METHOD_ID, 
PARAMETER_ID, 
PARAMETER_VALUE, 
PARAM_VALUE_FILE_REF, 
UOM_ID) 
VALUES ( 
102005000, 
9802, 
8827, 
0.0, 
NULL, 
9001); 

// create the projected CRS
insert into MDSYS.SDO_COORD_REF_SYSTEM ( 
SRID, 
COORD_REF_SYS_NAME, 
COORD_REF_SYS_KIND, 
COORD_SYS_ID, 
DATUM_ID, 
SOURCE_GEOG_SRID, 
PROJECTION_CONV_ID, 
CMPD_HORIZ_SRID, 
CMPD_VERT_SRID, 
INFORMATION_SOURCE, 
DATA_SOURCE, 
IS_LEGACY, 
LEGACY_CODE, 
LEGACY_WKTEXT, 
LEGACY_CS_BOUNDS, 
GEOG_CRS_DATUM_ID) 
VALUES ( 
102005000, 
'USA_Contiguous_Equidistant_Conic',  
'PROJECTED', 
4530, 
NULL, 
4269, 
102005000, 
NULL, 
NULL, 
NULL, 
NULL, 
'FALSE', 
NULL, 
NULL, 
NULL, 
6269); 

//Check your work
select srid, wktext from cs_srs where srid = 102005000;

select mdsys.sdo_cs.transform( 
SDO_GEOMETRY( 2001, 4269, SDO_POINT_TYPE( -79.5, 36, NULL), NULL, NULL), 102005000) 
from dual;

 

Running MapViewer Editor on Windows

Standard

I downloaded the latest version of  MapViewer Editor the other day and double clicked the jar file and it opened right up.  But when I tried to connect to a server the dialog window opened behind the main program causing me to utter several phrases not appropriate for this post.

Turns out it’s just a java issue.  Whatever is in my path doesn’t point to what MapViewer Editor wanted to act right but instead of jacking around with Java home or path variables I just opened it from the command line telling it to use one of the many JDK’s living on my desktop.

Here’s the command I used just in case this saves someone else some pain.

"C:\Program Files\Java\jdk1.8.0_65\bin\java.exe" -jar map_editor-12.2.1.2.0.jar