Cell Text exposure to OBI

Yr. Obt. Svt.’s introduction

I have yet again managed to sucker convince a guest writer, in this case Igor Slutskiy to do my work for me share valuable information with you, Gentle Reader.

This post is a bit unusual in that it targets OBIEE but I’ve been remiss in not covering, or trying to cover, OBI.  Happily Igor has taken that off my hands.  

I’ll also note that while many of us are not OBI geeks, the techniques Igor shows below allows Cell Text (and other index-based Planning repository data) to be viewed within any relational tool and it also shows how to take fact data and create a star schema from it.  Fully (or almost fully) normalized tables rule!

Also, I should note that this approach shows how to take a periods across the columns table and convert it to one that places periods down with a single fact column.  Glenn aka MMIC just wrote about one approach to do this but I like this one better.   The below technique is predicated on using a relational target, but we should be doing everything in SQL, right?  Death to flat files!

With that, let Igor take it away.

The Issue

Cell Text (CT) is a functionality of Planning that allows storing text in a cell.  In the picture below, the first 2 columns store text and the next column stores values.  In order to store text, a member has to be setup with a “Text” data type.    

Essbase can store only values, so Planning creates a numerical index for a text item and stores it in Essbase, while storing the actual text in the Relational Database (RDB).  The table in RDB has only 2 columns:  the CT and the numerical index for it.  OBI can connect to Essbase, but it would only retrieve a numerical index, as CT is not stored there.  OBI can also connect to RDB, but RDB does not store metadata for the CT items.  

The Solution

Bring metadata for the CT items into RDB and use the same approach to retrieve CT as is used for Cell Notes retrieval.  Here are the steps:

1. Create a table in RDB that will contain metadata and the numerical index for CT.  We can bring the content will come from Essbase.
2. Define a data set with text members in Essbase.
3. Export the above data set from Essbase into RDB table created earlier.
4. Reconstruct the RDB table into a star-schema compatible table.
5. Create dimensional tables and a fact table for a complete star-schema.
6. OBI connects to the star-schema in RDB, joins to Essbase and retrieves CT.

NOTE1:  the solution may be changed to use a single table in step 1 and using views instead of other tables.  Views typically perform slower than tables.  Performance could be tested and a decision on the method would be finalized.  

NOTE2:  the tables/views would be created in the Planning schema.

NOTE3:  all processes can be scripted and automated.

Refer to the flowchart below as you read this design document.

T_CELL_TEXT_ESSBASE_DATA

T_CELL_TEXT_DATA

 T_CELL_TEXT_FACT

HSP_TEXT_CELL_VALUE

Solution Steps

Create a table in RDB that will contain metadata and the numerical index for CT

Essbase exports data in the following format:  1 column for each dimension except for a Period dimension; 1 column for each Period dimension.  For example, if we have a total of 12 dimensions and want to export Level 0 periods, we will have a total of 23 columns (11 columns for each dimension except for a Period dimension plus 12 columns for Jan-Dec periods).

A table T_CELL_TEXT_ESSBASE_DATA is created:

-- 1. CREATE A TABLE TO TAKE THE IMPORT FROM ESSBASE (ONE TIME EVENT)

 DROP TABLE T_CELL_TEXT_ESSBASE_DATA;

CREATE TABLE T_CELL_TEXT_ESSBASE_DATA

  (

    "YEAR" VARCHAR2(255 CHAR),

    "SCENARIO" VARCHAR2(255 CHAR),

    "VERSION" VARCHAR2(255 CHAR),

    "ENTITY" VARCHAR2(255 CHAR),

    "DATATYPE" VARCHAR2(255 CHAR),

    "LEDGER" VARCHAR2(255 CHAR),

    "PRODUCT" VARCHAR2(255 CHAR),

    "SEGMENT" VARCHAR2(255 CHAR),

    "SOURCE" VARCHAR2(255 CHAR),

    "CURRENCY" VARCHAR2(255 CHAR),

    "ACCOUNT" VARCHAR2(255 CHAR),

    "JAN" NUMBER(*,0),

    "FEB" NUMBER(*,0),

    "MAR" NUMBER(*,0),

    "APR" NUMBER(*,0),

    "MAY" NUMBER(*,0),

    "JUN" NUMBER(*,0),

    "JUL" NUMBER(*,0),

    "AUG" NUMBER(*,0),

    "SEP" NUMBER(*,0),

    "OCT" NUMBER(*,0),

    "NOV" NUMBER(*,0),

    "DEC" NUMBER(*,0)

  );

The resulting table contains a numerical index of the Cell Text in each Period column:

Define a data set with text members in Essbase

For POC the following data set has been defined which mainly corresponds to the screenshot in The Issue section:

   

@RELATIVE("YearTotal",0),@DESCENDANTS("Source "), "Actual", "Working", "Amount", "GAAP", "Product 1", @DESCENDANTS("Accounts"), "Company 1", "SEG 1", "USD", "FY10"

Export the data set from Essbase directly into RDB

A script is created to accomplish this task:

Name:          Exp_CellText_Level0.mxls

Location:    /opt/app/hyp/batch/maxl/

Code:        the code below is included inside a wrapper

SET DATAEXPORTOPTIONS

{

DataExportLevel "LEVEL0";

DataExportRelationalFile ON;

DATAEXPORTOVERWRITEFILE ON;

};

FIX (@RELATIVE("YearTotal",0),@DESCENDANTS("Source"), "Actual", "Working", "Amount", "GAAP", "Product 1", @DESCENDANTS("Accounts "), "Company 1", "SEG 1", "USD", "FY10");

   DATAEXPORT "DSN" "dsn_name" "T_CELL_TEXT_ESSBASE_DATA" "schema" "password";

ENDFIX;

Reconstruct the RDB table into a star-schema compatible table.

The following code creates a T_CELL_TEXT_DATA table which is reconstructed from T_CELL_TEXT_ESSBASE_DATA to a star-schema compatible format.  Note, this table is constructed for the quarter-end months, to reflect the current reporting practices.  This script includes quarter-end periods, but could be easily modified to include additional periods.

  

-- 2. CREATE A SINGLE-MEASURE COLUMN TABLE FROM THE ABOVE (PERIODIC UPDATE).

 DROP TABLE T_CELL_TEXT_DATA;

CREATE TABLE T_CELL_TEXT_DATA AS

SELECT

 'Mar' AS "PERIOD",

 "YEAR", "SCENARIO", "VERSION", "ENTITY", "DATATYPE", "LEDGER", "PRODUCT", "SEGMENT", "SOURCE", "CURRENCY", "ACCOUNT",

 "MAR" AS TEXT_ID

FROM T_CELL_TEXT_ESSBASE_DATA

  UNION ALL

SELECT

 'Jun' AS "PERIOD",

 "YEAR", "SCENARIO", "VERSION", "ENTITY", "DATATYPE", "LEDGER", "PRODUCT", "SEGMENT", "SOURCE", "CURRENCY", "ACCOUNT",

 "JUN" AS TEXT_ID

FROM T_CELL_TEXT_ESSBASE_DATA

  UNION ALL

SELECT

 'Sep' AS "PERIOD",

 "YEAR", "SCENARIO", "VERSION", "ENTITY", "DATATYPE", "LEDGER", "PRODUCT", "SEGMENT", "SOURCE", "CURRENCY", "ACCOUNT",

 "SEP" AS TEXT_ID

FROM T_CELL_TEXT_ESSBASE_DATA

  UNION ALL

SELECT

 'Dec' AS "PERIOD",

 "YEAR", "SCENARIO", "VERSION", "ENTITY", "DATATYPE", "LEDGER", "PRODUCT", "SEGMENT", "SOURCE", "CURRENCY", "ACCOUNT",

 "DEC" AS TEXT_ID

FROM T_CELL_TEXT_ESSBASE_DATA ;

The restructured table above T_CELL_TEXT_DATA is different from the T_CELL_TEXT_ESSBASE_DATA table in that it establishes an additional column for the Period dimension and pivots the Period columns (Jan-Dec) into rows, leaving a single value column labeled TEXT_ID.  This is the column that holds the numeric index of the CT items.

Create star-schema tables

The T_CELL_TEXT_DATA table will be used as a basis to create the star-schema tables required for OBI – 1 fact table and 12 dimension tables.

Create a Fact Table

To get CT, T_CELL_TEXT_DATA table will be joined to HSP_TEXT_CELL_VALUE, a Planning native table that holds CT and its numeric index.  The 2 tables will be joined by the numeric index (TEXT_ID) creating the T_CELL_TEXT_FACT table.

-- 3. CREATE A FACT TABLE BY JOINING THE ABOVE TABLE TO HSP_TEXT_CELL_VALUE (PERIODIC UPDATE).

 DROP TABLE T_CELL_TEXT_FACT;

CREATE TABLE T_CELL_TEXT_FACT AS

 SELECT T_CELL_TEXT_DATA.*, HSP_TEXT_CELL_VALUE.VALUE AS CELLTEXT

 FROM T_CELL_TEXT_DATA

 INNER JOIN HSP_TEXT_CELL_VALUE

 ON T_CELL_TEXT_DATA.TEXT_ID = HSP_TEXT_CELL_VALUE.TEXT_ID;

Here is the Planning native HSP_TEXT_CELL_VALUE table:

Note a new CELLTEXT column in the resulting T_CELL_TEXT_FACT table.

Create dimensional tables

The following code creates T_CELL_TEXT_DIM_* dimension tables where * denotes a dimension name.

-- 4. CREATE DIMENSION TABLES (PERIODIC UPDATE).

 DROP TABLE T_CELL_TEXT_DIM_PERIOD;

CREATE TABLE T_CELL_TEXT_DIM_PERIOD AS

 SELECT DISTINCT "PERIOD"

 FROM T_CELL_TEXT_DATA;

 

 DROP TABLE T_CELL_TEXT_DIM_YEAR;

CREATE TABLE T_CELL_TEXT_DIM_YEAR AS

 SELECT DISTINCT "YEAR"

 FROM T_CELL_TEXT_DATA;

 

 DROP TABLE T_CELL_TEXT_DIM_SCENARIO;

CREATE TABLE T_CELL_TEXT_DIM_SCENARIO AS

 SELECT DISTINCT "SCENARIO"

 FROM T_CELL_TEXT_DATA;

 

 DROP TABLE T_CELL_TEXT_DIM_VERSION;

CREATE TABLE T_CELL_TEXT_DIM_VERSION AS

 SELECT DISTINCT "VERSION"

 FROM T_CELL_TEXT_DATA;

 

 DROP TABLE T_CELL_TEXT_DIM_ENTITY;

CREATE TABLE T_CELL_TEXT_DIM_ENTITY AS

 SELECT DISTINCT "ENTITY"

 FROM T_CELL_TEXT_DATA;

 

 DROP TABLE T_CELL_TEXT_DIM_DATATYPE;

CREATE TABLE T_CELL_TEXT_DIM_DATATYPE AS

 SELECT DISTINCT "DATATYPE"

 FROM T_CELL_TEXT_DATA;

 

 DROP TABLE T_CELL_TEXT_DIM_LEDGER;

CREATE TABLE T_CELL_TEXT_DIM_LEDGER AS

 SELECT DISTINCT "LEDGER"

 FROM T_CELL_TEXT_DATA;

 

 DROP TABLE T_CELL_TEXT_DIM_PRODUCT;

CREATE TABLE T_CELL_TEXT_DIM_PRODUCT AS

 SELECT DISTINCT "PRODUCT"

 FROM T_CELL_TEXT_DATA;

 

 DROP TABLE T_CELL_TEXT_DIM_SEGMENT;

CREATE TABLE T_CELL_TEXT_DIM_SEGMENT AS

 SELECT DISTINCT "SEGMENT"

 FROM T_CELL_TEXT_DATA;

 

 DROP TABLE T_CELL_TEXT_DIM_SOURCE;

CREATE TABLE T_CELL_TEXT_DIM_SOURCE AS

 SELECT DISTINCT "SOURCE"

 FROM T_CELL_TEXT_DATA;

 

 DROP TABLE T_CELL_TEXT_DIM_CURRENCY;

CREATE TABLE T_CELL_TEXT_DIM_CURRENCY AS

 SELECT DISTINCT "CURRENCY"

 FROM T_CELL_TEXT_DATA;

 

 DROP TABLE T_CELL_TEXT_DIM_ACCOUNT;

CREATE TABLE T_CELL_TEXT_DIM_ACCOUNT AS

 SELECT DISTINCT "ACCOUNT"

 FROM T_CELL_TEXT_DATA;

 

Here is an example of the resulting ACCOUNT dimension table:

Summary

In the above example, fifteen tables are created:

1 for Essbase Data

1 for Conversion

1 for Facts

12 for each Dimension

As noted above, all tables except for the Essbase Data table could be substituted with Views.

All processes above could be scripted and completely automated.

Yr. Obt. Svt’s conclusion

Igor, again my thanks for writing this.  There’s more to life than just Planning and Essbase (although those are the products that Keep Cameron Housed) and many Oracle EPM folks use OBI.  Hopefully this post will be one in a series of OBI/EPM posts.

Having written that, I’m making an appeal to you, oh Best and Brightest:  if you are an OBI/EPM geek, and you want to see your name in print, please contact me (you likely know my email address from Kscope, OOW, etc. or via LinkedIn) and I’ll give you a post or more if you’re really enthusiastic.  This site gets around 9,000 page views and about 7,000 sessions so you’ll have an audience.  

This offer goes out to all interested writers, OBI geek or otherwise.  The purpose of this blog is to share information.  I’m happy to provide the medium to do so.

Be seeing you.

Yup, another South Florida EPM meetup and the best yet

Yup, another one and the best yet

I just attended my sixth (I think it was the sixth but it’s all beginning to blur a bit.  Fifth?  Fourth?  No matter.) South Florida meetup out of the 15 or so meetup groups ODTUG have appleseeded.  Wherever they are, meetups are in one word fantastic.  Why?

1. They’re informal
2. They’re grassroots
3. They’re inclusive
4. They’re free
5. They’re educational
6. They’re awesome

Six reasons aren’t enough?  One would hope so.  If you’re not convinced let me review for you what made the latest meetup on 18 August 2016 in Miami, Florida so special.

The sponsors

While meetups can run on a shoestring – and sometimes these are the best of all – this latest event was a wee bit more organized and thus took money but not to the attendees.  Yup, you read that right:  this mini conference was free, gratis, sans frais, and geen kosten.  Did I mention I like ODTUG meetups?  Why yes I did.

ARC EPM, Secure24, and Top Down Consulting all generously contributed.  Thanks, Jessica Cordova, Monica Gordy, and John Riley.  It wouldn’t have happened without you.

What happened?

Good grief there was an awful lot going on.

The kickoff

Jessica, meetup organizer extraordinaire as well as ODTUG EPM Community meetup lead, kicked it off.  I’ve talked to Jessica in my role as the ODTUG board’s EPM liaison and she’s gone from nervous neophyte to seasoned practitioner.  Let her use her experience to help you set up a local meetup.

Who says there’s no such thing as a free lunch?  ‘Cos there was.  I had the chicken.  It sure beats the PB&J I typically have whilst working at home.

Speed dating without pain

Having once witnessed that as a disinterested observer (they were going through existential angst, I was having a Rob Roy at the bar), I’m entirely happy to relate that wasn’t occurring at the meetup.  What actually was happening was geek speed networking which happily misses the aforementioned exercise’s desperation but shares the notion of enabling EPM geeks to meet fellow EPM geeks.  

There, I’ve probably insulted half of my readers although I think it’s debatable which group I’ve insulted with what.  Sometimes I amaze myself, but almost always in a bad way.  Whew.   

Clawing my way back to relevance, it was a fun and effective way of meeting perfect strangers.  My only complaint is that we didn’t have enough time to do this for everyone (not all of us could meet everyone) but it was a big group (22 not including Yr. Obt. Svt. and two others).  This is a nice problem to have.

Some of us really got into it.  And why not?

The presentations

As noted Yr. Obt. Svt. reprised the Kscope16 presentation Jason Jones and I had on PBCS administration vs. on-premises.  Jason, I mangled the bit about your PBJ API framework for PBCS but I blundered through to the best of my ability.  Here I am rehearsing that morning disheveled, unshaven, and unshowered.  Eeek.  See, Gentle Reader, I do this all for you.

The dog seemed to like it.

Ron Moore and Ludovic de Paz presented their “(Keep) Pushing the Envelope with New Calcs Features”.  I used Ron’s section on FIXPARALLEL directly after the meetup to parallelize a DATACOPY section of an administrative currency conversion fx calc script.  One can’t ask for more than that when it comes to actionable information.  I should note that long ago in another professional life Ron Moore taught me ASO in one of his classes.  I’ve presented with Ludovic as well as Paul Hoch re Calculation Manager usage.  I guess my point is both of them know me, both of them knew I would be there, and both of them showed up anyway.  Thanks, guys.

Do people value these things?

Do you see that upper left photo in the collage below?  That’s Dhaval Shah and he drove two hours to attend the meetup.  He even won my latest book.  That’s hunger for knowledge.

You’re going to be at the next one, right?  

Or start up your own in your area if one doesn’t exist?  Right?  Why not?  Reach out to ODTUG and git a goin’.

Be seeing you.