Fun with Jython and FDMEE

The past few months I have had the opportunity to assist in FDM to FDMEE migrations and during that time I have been able to explore Jython scripting.
 
I have loved programming since I was in high school and wrote my first program on an Apple 2.  Yeah, a very long time ago.  No matter where my career path has taken me I seem to find a way back into some form of programming.
 
As part of the migration efforts I needed to convert the FDM VBScript to Jython.  While some of the Event Scripts could have been kept in VBScript it was cleaner to convert to Jython for 2 reasons...VBScript is not strategic for Oracle with FDMEE and I like a puzzle.
 
One of the migrations I had was from FDM Classic with the Essbase Source Adapter to FDMEE 11.1.2.4.  This particular client had stored email server and email addresses in the integration settings to be used to send an email upon failure during any part of the data loading done by FDMEE.
 

 
The one thing I did not want to do was to embed this information in my each one of my Event Scripts - AftImport, AftValidate, AftExportToDat, AftLoad, AftCheck...
 

 

 
So where could I store this information and yet make it accessible to easily modify once.  Why not the Integration Options for a Location?
  

 
If I look at the TPOVPARTION table which stores location information is the 4 Integration Option values.

A simple SQL statement

So how do I get these easily without having to write SQL in my Jython?

Enter the FDMEE API and particularly the 

 

     getLocationDetails(BigDecimal pPartitionKey)

 

 

So what does this look like in Jython in my Eclipse editor?

 

And the Output...

 

So putting it into my FDMEE Event Scripts...

Now the neat thing about this approach is the if there are different people responsible for different locations they could get individual emails when there is an issue with their location by switching out the hardcoded fdmLocID to the fdmContext["LOCKEY"].

Well that was a fun diversion from BI and since I am having fun with Jython and FDMEE I may find some other neat tidbits to share in the future.

OBIEE & Essbase Value Hierarchy Bug & Workaround

To begin with this article is based on a bug I submitted.

Bug 22302005 - LOGICAL LEVEL KEYS GET MESSED UP WHEN DIMENSION IS SET TO VALUE BASED HIERARCHY

When OBIEE 11.1.9 was released it took a while for me to actually start ‘Net New’ with my RPD development.  I just upgraded and kept working.  Well the day came that I needed to bring a new cube into my OBIEE 11.1.9 environment.  When I attempted to query a hierarchy dimension that I had set to Value Based / Parent-Child I got the following error.

So I looked at the Log to see the Logical SQL.

I know I had used this cube on my 11.1.1.7 instance and I had no issues so I went and looked at the RPD in my 11.1.1.9 instance.

Physical layer there is the Measures Member Key…

So I went to my 11.1.1.7 instance and re-ran the same analysis

Then I looked at my Business Model

And the Physical Layer. Hmmm… no Measures Member Key

 

A Side by Side view often helps to see the difference.

 

Using my OBIEE 11.1.1.7 instance as my reference 

I edited the Detail Level properties of the Measures Hierarchy.

Then added the Gen3,Measures as a new Logical Key

 

Next I edited the properties of the Measures Dimension to set the Primary Key - DetailKey to the Gen3,Measures.

Finally I deleted the the offending column.

 

The last thing to do was to save and check in my changes then test the hierarchy in an OBIEE Analysis and now no errors.

As a point of reference this bug is in current release of OBIEE 12c as well.

So until Oracle fixes Bug 22302005 this is a simple work around.

OBIEE Repository Variable as Column Header

We had a client with a business requirement to produce a columnar dataset with the column headers of a table for the measure to display the value of an Essbase Substitution Variable which was being stored as a dynamic repository variable.  In a previous post I showed how I manage Essbase Substitution Variable in the repository.
 

I know I can use Presentation variables to change column headers, but my Essbase Substitution Variables are Repository Variables and that just doesn’t seem to work. 

  

Now the first thing one might ask is why not use a pivot with periods as columns.  Well like most clients there is a particular view they wanted and there were other columns needed that are not shown.

So after some noodling and playing around I came up with an option.  Use a Dashboard Prompt to set a Presentation Variable and use the Repository Variable to set the default value of the prompt.
 

 

 

I used the Presentation Variable to set the Custom Heading for the column. 

 

 

This little analysis was then embedded in a dashboard and working with Dashboard Properties add the dashboard prompt is hidden. 

 

 

 
 
The dashboard looks like this.  Notice that the 3^rd column has the proper month name showing.
 

 
Thanks and until next time.

Multi-Value Performance Tile

This post is a bit of a diversion from my working with Maps since it revolves around a requirement I had with a client.

We have all seen the Performance Tile.  It provides a quick instant view of key metrics.

 

 

We can use conditional formatting to indicate a change from target or values within a threshold.  The one limitation of a Performance Tile is that it holds 1 and only 1 value.

What if you wanted to show a tile with Current Value as well as Month Ago Value and Year Ago Value so that you could quick see how you are performing in context of time?

 

 

I had such a request from a client. And after some playing around I figured why try to do this with a Pivot or other visualization.  Why not use a couple of tiles in the compound view and when put on the Dashboard the tile looks whole and tells a better story than a single value.

 

 First I looked at the original Tile to get an idea about the size.

 

 

Then I adjust the size so that it is about 2/3 the original height

 

 



Next I modify my Analysis to bring in the other 2 values.

 



Then create 2 new tiles with the height 1/3 the original height.  The key to this working is also adjusting the font sizes and checking the option Do Not Autofit.

 

Finally I put it all together in my compound layout.

 



 Then on the Dashboard next to the original tile.

 

 

All it takes now is a bit of fine tuning.

 

Well that was fun, until next time.

 



SQLDeveloper and Map Data - part 2

In my last post SQLDeveloper and Map Data I showed how one can view Map Geometry Columns using SQL Developer. 

In the last part of the post I showed regions of the United States made up a number of states.

 

And the SQL statement identifying those regions made reference to a Region ID in  the TL_2014_US_STATE  geometry table.

Using this information and a Spatial function within the database wecan make a new geometry table with Regions allowing a user to drill through Maps as they would on a Dimension in a pivot.

Region
 - State
   - County

Creating new aggregated geometry from existing geometry table can be done in 5 simple steps:

1.Create New Table with Geometry Column and at least one key column

2.Update Oracle Spatial Metadata
3.Load data into new table using sdo_aggr_union Function
4.Create Spatial Index on new table
5.View new table / Custom Region

So let's get started.  We will connect to our MVDATA schema where we have our TL_2014_US_STATE table of US States with the Region Column.

First Create the New Table

create table us_custom_regions (
  region_id varchar2(2),
  region_name varchar2(15),
  region_name_caps varchar2(15),
  geometry mdsys.sdo_geometry);
commit;

Update the Oracle Spatial Metadata

This can be done via Right Mouse Click on the new table and bringing up the Spatial menu and selecting Update Spatial Metadata

Enter 8307 for the coordinate system identifier. Add two rows for the spatial dimensions and enter the values Longitude, -180, 180, 0.05 and Latitude, -90, 90, 0.05

This interface makes entries into the user_sdo_geom_metadata table

The SQL insert statement for the above action is:

Insert into user_sdo_geom_metadata values ('S_REGIONS', 'GEOMETRY',
mdsys.sdo_dim_array(mdsys.sdo_dim_element('Longitude', -180, 180, 0.05),
mdsys.sdo_dim_element('Latitude', -90, 90, 0.05)), 8307);

commit;

   

Load Data

 

We will source our TL_2014_US_STATE table of the US States and aggregate the geometry into one shape per region using the SDO_AGGR_UNION function within the database.

The SQL for selecting and loading the data is below

INSERT INTO US_REGIONS
select '1', 'East', 'EAST', sdo_aggr_union(sdoaggrtype(geometry, 0.05)) FROM US_STATE WHERE REGION = '1';

INSERT INTO US_REGIONS
select '2', 'Central', 'CENTRAL', sdo_aggr_union(sdoaggrtype(geometry, 0.05)) FROM US_STATE WHERE REGION = '2';

INSERT INTO US_REGIONS
select '3', 'South', 'SOUTH', sdo_aggr_union(sdoaggrtype(geometry, 0.05)) FROM US_STATE WHERE REGION = '3';

INSERT INTO US_REGIONS
select '4', 'West', 'WEST', sdo_aggr_union(sdoaggrtype(geometry, 0.05)) FROM US_STATE WHERE REGION = '4';

COMMIT;
 

Create Spatial Index

The Spatial index is created by invoking the Spatial Menu and selecting Create Spatial Index.

 

Provide a Name.

 

Again the actions done via the GUI can be done using SQL Statements.  The create spatial index SQL statement for the above screen is shown below
 CREATE INDEX MV_US_REGION_SPINDX ON "MVDATA"."US_REGIONS"("GEOMETRY") INDEXTYPE IS MDSYS.SPATIAL_INDEX

 

Viewing New Map Data

Bring up the Map Viewer within SQL Developer

  



 Create a SQL Statement

And view the results



 

 

 My next post will look at bringing this all together and incorporating in OBIEE.