Want to hire me? For availability and scheduling please email info@arcepm.com

19 September 2016

Oracle Open World 2016, Day 2

Oracle Open World 2016, Day 1

Today is the first “real” day of the conference in that today we’re seeing Oracle’s sessions.

I’m particularly excited because today is a day of roadmaps.

And with that, off we go.

CON6132: Oracle Business Analytics: Product and Technology Roadmap, Samar Lotia, Hari Shankar, and Ragu Venkatasubramanian, Oracle

Data Visualizer looks really, really, really cool and is Oracle’s answer to Tableau.  I have to, have to download this and play with it.  Gawd it’s cool.
C:\Tempdir\Blog\IMG_2790.JPG

Synopsis is being demoed right now.  ODTUG President, Natalie Delemar, is so cool it can’t be real.  It is as I saw it at the ACED briefing.  So cool.

Here’s their take on data sources.  In short:  everything.
C:\Tempdir\Blog\IMG_2789.JPG

Do not, ever, forget Essbase Cloud aka EssCS.  This is what Tim German and I presented on at Kscope16 and if the creek don’t rise and God willing, we will again at Kscope17.

CON7505:  Oracle Enterprise Performance Management On Premises and in the Cloud:  Taking a Hybrid Approach

Tony Scalese is talking right now on how to implement a cloud implementation (in this case PBCS) across multiple tools both on premises and in the cloud.  I have a three part post on Data Management and PBCS and will give you a teaser by saying that he is absolutely right in his approach.  It’ll be an epic rant but for now I’ll listen to Tony’s much calmer than mine voice.

Conclusion for now

I have more, much more, on offer today.  After this session I’m off to the EPM demo grounds.  Here’s hoping I’ve got something geekycoolawesome to share.

Be seeing you.

18 September 2016

Oracle Open World, Day 1

The real start of the conference

Saturday is fun day.  Sunday through Thursday are work days.  I can’t decide which I prefer.  Sadly, I think I prefer the sessions.  Get a life, Cameron
And with that, let’s begin the geeking.  I’ll take you through the sessions one by one and update throughout the day.

UGF6169: Swipe Your Way to Better Analytics in the Cloud with Senior Lifestyle, Sarah Katz, Huron Consulting

Oh dear, I really wanted to attend this one.  I plead poor adjustment to time zone changes and hence I overslept.  Bugger.

UGF6308: Oracle Hyperion Planning Interface:  Simplified, Cindy Eichner, Finit Solutions

Cindy spoke on the SUI and how it’s the future and in fact is the present.  It’s a good overview of the functionality within the SUI focusing on PBCS.

UGF6421: Why Finance and IT Love Oracle Hyperion in the Cloud, Alex Leung, The Goal Getters

Excellent overview of the different kinds of cloud:  IaaS, PaaS, and SaaS by Alex Leung of The Goal Getters.  This is really good stuff because these terms are flying around OOW and for some of this (read me) it sounds alike but mean very different things.
The other thing I like is that it is very focused on Hyperion/Oracle EPM.  Alex is really walking us through the features of on-premises/IAAS, PaaS, and SaaS.
Why does Finance love the Cloud? There are some fairly compelling reasons:  control both technical and cost, focus on app not infrastructure, better support, better uptime.
Why does IT loves the Cloud?  Ibid on the compelling reasons plus  no commitment for capacity planning, better technical agility and focus, better security, allows deviation from internal standards support.

UGF7494: Implementing the World’s Largest Oracle Exalytics Program, Gary Crisci, GE

Gary’s repeating his Best Speaker presentation from Kscope16 on Exalytics.
Big boxes.  Big.  And big implementation – the largest in the world.  And big savings.
I think I could fit one in my garage.  Maybe.
As always, Gary is a fantastic speaker.

Conclusion for now

I’ll leave it for now but have more to come.

Oracle Open World 2016, Day 0

Another year, another week of live blogging madness…

…that goes by the name Oracle Open World.  Yes, it’s here – here being San Francisco, the City By The Bay.
I struggled on numbering this post – is it day -1 (OOW opens tomorrow) or day -3 (I was here for the ACED briefings on Thursday and Friday last) – and reasoned that the whole number before 1 is 0 and as tomorrow is the 1st today must be day 0.  I know we’ve all enjoyed this lesson in 2nd grade math.
Unlike Kscope, OOW is in SF every year.  Beyond actually being able to find restaurants, coffee shops, etc. year over year, it is a fun place to be for a conference.  If one were to measure the suitability of a city based on the number of songs written about it – yes this is a strange metric but bear with me – the Great American Songbook proves it although they are all strangely downbeat.  Chicago it ain’t but I note ODTUG managed to have a conference there on the basis of only two songs.  Actually, this may not be a good criteria.  No matter.

I hear music

What are the (well at least my) songs about the city?  San Francisco Blues – Peggy Lee, Got My Gate on the Golden Gate – Mel Tormé, and of course I Left My Heart in San Francisco – Dean Martin, Perry Como, and of course Tony Bennett. Yes, three singers on the last song but is the iconic SF song and besides Dino in concert is hilarious, Mr. C is sooo relaxing,  and it’s Tony Bennett’s song.
There’s others as well.  On a slightly more positive note, but not really because it’s about the Big One:  San Francisco – Judy Garland.  And then a really cool police procedural one, The Streets of San Francisco.
Alas, while there are many songs about San Francisco, there are no songs about Oracle Open World.  Of course there is that Wednesday night event although I am likely hoping in vain for Anthony Dominick Benedetto as the star attraction next year.
No matter, the world+dog doesn’t share my taste in music, and this blog isn’t likely to change anyone’s mind.  But I have (maybe) encouraged you to open your mind to new horizons.

Speaking of new horizons

As noted, I attended the ACE Director briefing two days before the show and then the EPM Partner meeting yesterday (I’m sort of astounded that Yr. Obt. Svt. is invited to either one but I never argue with good fortune) and the NDA and even CDA is, to put it mildly, restrictive.  
Given that, I can’t really provide any details but I’ll give you the whole Magillah in a one word speech:  Cloud.
Yup, that was a tease.  Sorry.
Seriously, if you’re involved in EPM in any way, get your head around cloud, cloud, cloud.  Buy me a cup of coffee some time and I’ll give you my 2¢ on whether that’s a good, bad, or indifferent idea.  From a product perspective, funding coming to these products is beyond fantastic.
But what won’t be a tease will be what I’m going to try to do this year.  To wit: daily blogging, hopefully live, and lots of coverage on Twitter at @CameronLackpour.  With this kind of event, Twitter occurs as it happens, the blog happens at night.
Be seeing you.
P.S.  Be happy:  I have shot my bolt re musical links from your grandparent’s years.  
P.P.S.  I actually did write this yesterday but forgot about it.  Yes, the madness is upon me.

08 September 2016

A Smart View survey? Oh yes. Or no. You decide.

A time of surveys

No, not a political one for those of you in the States.  Gah, I have enough of those from the eleventy-billion polling firms in this Great and Glorious country.  A pox upon them.  Yr. Obt. Svt. only goes this far in mentioning politics in his blog but he does wish that said eleventy-billion polling companies would please stop calling his abode.  It’s not like I answer the phone in any case.  And if I did, I’d lie just to confound them.

Having just (gently) savaged the American voting system, is this survey to be assiduously avoided the way many of us don’t answer the telephone?  No, because it addresses one of the key components in Oracle’s EPM technology stack – Smart View.

Smart View! Yes or No?

Is that a fair or even a valid question?  I’m not sure that it is.  After all, it isn’t as though there are many viable alternatives to Smart View except for Applied OLAP’s Dodeca and Dodeca Excel Add-In for Essbase.

Think of this poll as a message to Oracle as the results go to Oracle’s product management team for review.  Continuing the political theme, how you vote is anonymous, just like being at the ballot box so feel free to praise or damn Smart View without fear.

For the record

There are those who claim I am no fan of Smart View.  I will answer that by saying that this poll was my idea.  I like the concept of Smart View – one tool, many providers.  It’s what set Essbase apart in 1992 with its then revolutionary Excel add-in and I very much want Smart View to continue in that tradition.

At the same time no product is perfect.  This is your (and my) chance to let Oracle know what you think of it, what you use, and where you’d like to see the product be available (there is more to life than Excel).

Some of the guilty

Speaking of blame, as always there are many who contributed to this effort.  

If you don’t vote, don’t complain

Vote, won’t you?  This is your chance to make your opinion heard and it’s a pity not to take advantage of it.

Be seeing you.

02 September 2016

Tim and Cameron's most excellent 00W 16 EPM meetup

Uninsanity

Uninsanity?  Why not?  After all, unlikely, unwieldly, and undesirable are all words in good old American (or, if you’re not from the States the Queen’s English).  As Humpty Dumpty once said, “When I use a word, it means just what I choose it to mean – neither more nor less.”  And why not?  All I am doing is casting off the chains of restrictive and prescriptive grammar that are nothing but the dead hands of those of long ago who had this crazy notion of clear and concise writing and replacing them with words of my own making but clear(ish) intent.  I should note that this did not work as a strategy in English class – I commonly got A/F grades, i.e. A for content and an F for grammar.*  So sort of like this blog then.  

The old saw that defines insanity as doing the same act again and expecting different results most definitlely does not apply to at least one aspect of my professional life – Tim’s and Cameron’s Most Excellent Oracle OpenWorld EPM Meetup.  We’ve done it every year since 2012 and it has gone from strength to strength, a prime example of uninsanity.

I’ve written about that success in 2012, 2013, 2014, and 2015.  See, I told you so.  Unpossible.  Who are you gonna believe, me or your lying eyes?  

2016 is no exception:  Tim’s and Cameron’s Most Excellent Oracle OpenWorld EPM Meetup is coming to an Oracle Open World near you on 20 September 2016, 7:00 pm till whenever, at the Hyatt Regency Eclipse Lounge.  To quote the meetup.com invitation:
This is where the elite EPM geeks eat, meet, and greet. Tim and Cameron are hosting this unique networking and (fun, really fun) meetup for the fourth year in a row. Let's make 2016 the best yet!

It is a lot of fun.  You’ll meet like minded geeks, a gaggle of ACEs, maybe Oracle product management, and Tim Tow (yay!) and Yr. Obt. Svt. (groan!).

If you’re at Open World 2016 or are local to San Francisco, this is your chance to do all of the above.

Join us, won’t you?

Unpossible

*Seriously, I hated English class.  And it was “gifted” as well.  If there was ever an example of ungifted, I was it.  I’d love to send my various long-suffering English teachers a copy of my Developing Essbase books; I’ll bet they’d be as surprised as I am.  Also, Mr. Minsky my parallel English teacher, wherever you are, thanks for teaching me the three point essay.  You cannot imagine how many times I went back to your lessons.  There’s a message there about teaching styles and learning.  Who knows if anyone has ever learnt anything reading this blog but a man can hope.

30 August 2016

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.