Kscope15 -- The Essbase sessions I want to attend

What am I talking about?

Why Kscope15, of course.  Could there be any other conference?  Thought not.  So why am I writing this?

To bore you to death share with you the sessions, events, and activities that make up the very (really) best EPM conference anywhere.  It really is that good.

I should note that these are the sessions I’m interested in.  You may have a completely different take on what’s worth seeing but I’ll bet our tastes overlap.

I should also note that I will never get to attend most of these sessions because of all of the Oracle and ODTUG obligations I have at the conference.  But a man can dream, right?

And finally I should also note that this post will only cover Essbase as it’s just too long to put all of the other tracks together into one post.  Look for multiple posts on this during the week.

OMG, one last, last note.  Check out Ludovic de Paz’ post on things to do in South Florida

when you’re not being a sad, pathetic, but awfully keen Essbase geek.  I hear there is life beyond Kscope although I couldn’t tell you what that actually is like.  Perhaps Ludovic can help.

And with that, we’re off.

All of the Essbase sessions I would like to attend but probably won’t

I seem to make a living more off of Planning and (gasp) ODI, but I do get the odd “pure” Essbase (It’s an interesting choice of words, isn’t it?  I’m not the only one who refers to Essbase that way.  Does that mean that the other tools are adulterated?  But I digress yet again.) project from time to time and I really enjoy them.  I really enjoy the puzzle solving nature of “How do I analyze this” almost as much as the “How do I optimize this” part of Essbase projects.  

Here’s what I’m looking forward to:

Monday, 23 June 2015

Unappreciated and Maligned Core Essbase Features

George Cooper , Gap

Co-presenter(s): Doug Pearce, Analysis Team

When: Jun 22, 2015, Session 2, 9:45 am - 10:45 am

Anyone who can write, “What deranged person would do that?“, has to be a good presenter.  George has been around in the Essbase world seemingly forever and really knows his stuff.

Pushing Essbase to the Limit: A Load and Stress Testing Exercise

Tim German , Qubix

Co-presenter(s): John A Booth, Infratects

When: Jun 22, 2015, Session 4, 2:00 pm - 3:00 pm

My fellow co-authors Tim and John are bringing more rigor to Essbase, cf. George Cooper’s session.  Given that so much of what we do is rule of thumb, and how incredibly inadequate that can sometimes be, I am really looking forward to this session.  I should also note that this session touches on some of the topics Tim wrote about in Developing Essbase Applications:  Hybrid Techniques and Practices.

Drillbridge: The Easy and Free Way to Implement Relational Drillthrough

Jason Jones , Key Performance Ideas

When: Jun 22, 2015, Session 4, 2:00 pm - 3:00 pm

So cool  So very, very cool.  It’s drillthrough from Essbase to relational but without Studio.  So cool.

Essbase: Advanced Design Patterns from the Trenches

George Cooper , Gap

Co-presenter(s): Bill Kamal Reddy, Analysis Team

When: Jun 23, 2015, Session 8, 2:00 pm - 3:00 pm

This is another one by George.  I’m interested in design patterns (I am trying not to write crap code; this is one way to design nirvana.) and what George has on offer looks like interesting stuff.

Tuesday, 23 June 2015

The Vision for Essbase and How It Should Already Change How You Do Things

Gabby Rubin , Oracle Corporation

Co-presenter(s): William Bradstreet, Bradstreet Business Services, LLC

When: Jun 23, 2015, Session 5, 8:30 am - 9:30 am

Bill participated on a Hybrid Essbase OTN Archbeat podcast with me and a handful of the Best and Brightest on Hybrid Essbase; he has actually for real and truly implemented Hybrid at a customer.  AFAIK, he may be the first in the world.

Enjoy Better MDX for Financial Metrics: From Design to Online Help

Ted Hollenberg , Time Warner Cable

Co-presenter(s): Ron Moore, TopDown Consulting

When: Jun 23, 2015, Session 7, 11:15 am - 12:15 pm

Ted is a real, live, honest-to-God customer who really knows his stuff.  Ron is a long time Essbase consultant and instructor.  I cannot imagine not learning something (or an awful lot) from this session.

I Develop in Essbase: Why Do I Need to Know SQL?

Cameron Lackpour , CL Solve

Co-presenter(s): Glenn Schwartzberg, interRel Consulting

When: Jun 23, 2015, Session 8, 2:00 pm - 3:00 pm

Yr. obt. svt. is presenting this along with MMIC.  If you don’t know SQL and you use Essbase, you’re really missing out.  SQL is the Swiss army knife of corporate computing.  Glenn and I have put together a moderately advanced presentation with a lot of techniques and code.  It should be good stuff, despite my participation.

ASO, BSO, and Hybrid Calculations: What's Fastest, How Hard Is It, and Which One Should You Use?

Cameron Lackpour , CL Solve

Co-presenter(s): Tim German, Qubix

When: Jun 23, 2015, Session 9, 3:30 pm - 4:30 pm

I am very, very, very excited about this one.  Tim and I are going to both present on different techniques and (and this is even more exciting) provide a method for determining what should be selected based on objective measures; there will be a subjective feasibility component as well.  It’s actual real honest-to-God consulting.  In some ways it’s an extension of the evaluation engagements I sometimes now do.  I plan on using this approach with those customers.  Come to think of it, I’m going to use it on my own approaches as well.

Customer Panel: A Panoramic View of Exalytics

Gary Crisci , General Electric

When: Jun 23, 2015, Session 10, 4:45 pm - 5:45 pm

A real, live, breathing, red blooded, Exalytics customer.  What will Gary say?  Knowing him, not a single punch or praise will be pulled.  It should be both enlightening and entertaining.

Wednesday, 24 June 2015

Designer Aggregation Scripts for ASO: Make Them Fit Without Being Too Tight

Ted Hollenberg , Time Warner Cable

When: Jun 24, 2015, Session 11, 8:30 am - 9:30 am

Ibid.

Essbase New Features and Roadmap Update: 2015 Edition

Steve Liebermensch , Oracle Corporation

Co-presenter(s): Gabby Rubin, Oracle Corporation

When: Jun 24, 2015, Session 11, 8:30 am - 9:30 am

The future, as presented by the Oracle Essbase product manager.  Do you care about the future of Essbase?  You should.

The Essbase of Things: Essbase on the Cloud, Essbase with OBIEE, and What It All Means

Steve Liebermensch , Oracle Corporation

When: Jun 24, 2015, Session 13, 11:15 am - 12:15 pm

The future of the future, as presented by the Oracle Essbase product manager.  Do you care about the future of Essbase?  You should.  Again.

Automation of Focused Aggregations Creation

Dmitry Kryuk , Independent Consultant

When: Jun 24, 2015, Session 14, 1:45 pm - 2:45 pm

I’ve blogged about focused aggregations in Planning.  Would you believe analytical and algorithmic?  Again, more rigor applied to Essbase.  It looks like I need to up my game yet again.

BYO MDX Editor: BMGS (Become More Groovy-Savvy) in the Process

William Hodges , The Hackett Group

When: Jun 24, 2015, Session 14, 1:45 pm - 2:45 pm

William’s session is based on his chapter in DEA Hybrid (I am not going to type this out again and again and again).  This is a really cool concept and if you dabble or live in MDX and Excel, it is highly recommended.

Hybrid: We Have a New Essbase Engine, and Its Fuel Is Data

John Booth , Infratects

When: Jun 24, 2015, Session 16, 4:15 pm - 5:15 pm

Hybrid, Hybrid, Hybrid.  It’s almost like Hybrid is the future of Essbase.  It is, actually.  John always does excellent work and I’m super curious to see what’s come up with, particularly wrt Planning.

Exalytics - Creating the Best Platform for Essbase, EPM and BI

Gabby Rubin , Oracle Corporation

When: Jun 24, 2015, Session 15, 3:00 pm - 4:00 pm

There’s little chance of me ever working on an Exalytics project given my company size of exactly one, but what happens in Exalytics sooner or later mostly trickles down to standard Essbase.  

Why Didn't I Think of That? How Can I Do It in Essbase?

Glenn Schwartzberg , interRel Consulting

When: Jun 24, 2015, Session 14, 1:45 pm - 2:45 pm

Glenn aka MMIC always surprises me with the weird and wonderful things he knows about Essbase.  I think he’s even sadder of a geek than me, which is saying something.  I think that’s a positive attribute.  Maybe.

A Simple Solution to Performing Complex Eliminations in Planning and Essbase

Joseph Watkins , Huron Consulting Group

When: Jun 24, 2015, Session 16, 4:15 pm - 5:15 pm

Joe does some fascinating work.  I am super interested to see how he accomplishes it-can-only-be-done-in-HFM in Essbase itself.

Advanced Calculations in Calc Manager

Ron Moore , TopDown Consulting

When: Jun 24, 2015, Session 13, 11:15 am - 12:15 pm

I know that Ron has been working with Calculation Manager product management and development.  This ought to be super interesting and a chance for Essbase rather than Planning use cases.

Thursday, 25 June 2015

Thursday Deep Dive - Essbase: What's New in 11.1.2.4?

Cameron Lackpour , CL Solve

Co-presenter(s): Gabby Rubin, Oracle Corporation

When: Jun 25, 2015, Deep Dive Session , 9:00 am - 11:00 am

I swear this is the last of the lot that involves yr. obt. svt., which given that it’s the last session of the conference (sob) is pretty predicatable.  What, you’re not sticking around for the after party?  The good news is that I’m solely the interlocutor and Gabby Rubin, Steve Liebermensch, and MMIC Glenn Schwartzberg will be actual brains.  This is similar to what I did last year with the Planning Deep Dive.  I’m very excited about this opportunity to put the screws to Oracle product management and you should be too.  :)

And that’s the end of the Essbase sessions

If you read through the notes and see that many of the sessions I want to attend are simultaneously schedule, that is the nature of a conference only a few days long.  Happily, all sessions are recorded and Kscope attendees can view them at their leisure.

My next post either tomorrow or the day after will be on Planning.

Be seeing you.

I don’t ordinarily write about conferences other than Kscope, but when I do…

…I write about the Rittman Mead BI Forum 2015

Tim German and I just spoke at Rittman Mead’s annual BI Forum.  Alas, we didn’t go to the one in Brighton (I attended the Comshare EMEA conference there in 1992 but I cannot find any links to it – I think it was in the hotel the IRA tried to assassinate Margaret Thatcher in although I note that RM’s conference was in a different hotel).  Instead we went to the one in Atlanta (come think of it, I believe I attended a Comshare conference there as well – maybe 1991?  1993?  I can’t remember for the life of me.) and it was absolutely brilliant.

Why?  I find technology just fascinating (erm, you would imagine that based on this blog) and the presented technology at this level is all new to me.  Fascinating.

NB – For those of you who think this is a sycophantic recounting of their conference, it isn’t.  I’m pretty hard to impress and I was just blown away by the conference.  Think of this post as enthusiasm and well deserved at that.

NB #2 – Mark went out of his way to let the two token Essbase guys feel at home.  Many thanks, Mark.

Day by day

Day 0

The conference started out with a master class on Oracle Big Data.  We’ve all heard a lot of hype and hyperbole about Big Data.  I have to say that after watching Jordan Meyer and Mark present on it, I think Big Data might just deserve it all.  I also have to admit this is the first time I’ve had a bird’s eye view of R, EDA, Oracle Big Data SQL, and I-don’t-know-how-many-other-techologies, but the kinds of data, the statistical analyses, and reporting just blew me away, as did the rest of the conference.

Day 1 & 2

Rather than try to give my impressions of each one of the sessions, which would be difficult ‘cos I was mostly completely at sea, the conference covered subjects and technologies that are not in the toolkit of at least this Essbase hacker.  I’ll list it and try to give you a feel of my overall impression of the conference.

The list

• Brian MacDonald or Chris Lynskey, Oracle Corporation : “Looking Ahead to Oracle BI 12c and Visual Analyzer”
• Robin Moffatt, Rittman Mead : “Smarter Regression Testing for OBIEE”
• Bill Creekbaum and Gabby Rubin, Oracle Corporation : “Solid Standing for Analytics in the Cloud”
• Hasso Schaap, Qualogy : “Developing strategic analytics applications on OBICS PaaS”
• Tim German / Cameron Lackpour, Qubix / CLSolve : “Hybrid Mode – An Essbase Revolution”
• Kevin McGinley, Red Pill Analytics, “Agile BI Applications: A Case Study”
• Stewart Bryson, Red Pill Analytics, “Supercharge BI Delivery with Continuous Integration”
• Ryan Stark or Chris Lynskey, Oracle Corporation : “Big Data Discovery”
• Andy Rocha & Pete Tamisin, Rittman Mead : “OBIEE Can Help You Achieve Your GOOOOOOOOOALS!”
• Christian Screen, Sierra-Cedar : “10 Tenants for Making Your Oracle BI Applications Project Succeed Like a Boss”
• Sumit Sarkar, Progress Software : “Make sense of NoSQL data using OBIEE”

All of the sessions were good (somehow I think even the session Tim and I presented on Hybrid was at least adequate – sorry Tim), but the ones that I found most fascinating were the conceptual ones that could apply to EPM as well.  Robin’s presentation in particular really hit home as it captured the kind of structure and rigor that we should be bringing to testing in EPM projects.  Stewart also almost made me believe in Agile, which is really quite an accomplishment.  Even the more BI-specific technologies and approaches were absolutely fascinating.

A data visualization bake off allowed BI geeks to take a known dataset around public school classroom projects that need funding and presented their solution at the conference.  Sorry, I can’t tell you who won as I was continuing my OMG-this-is-so-f’ing-cool bit of shock and awe, but both of the solutions were really great.

There was also a debate on self-service BI vs. the traditional IT-oriented and controlled top down BI.  Oddly, there was quite a bit of discussion about buffets vs. Michelin three star restaurants (it was a metaphor for the different kinds of BI but it all left me a bit puckish).  I have to say that I was on the side of the former – this is where Essbase got its start and where my heart with the product still is.  Empowering people to take control of their data is one of the things that makes Essbase so great.  Self-service won.  :)

In action

If you want to download the presentations have a look here.

Want to see us in action?  Check us out on Flickr.

One other thing

This sounds kind of ridiculous, but the food at this conference was just fantastic.  Perhaps that’s down to the hotel, or perhaps the small size (60-ish) which allowed a different kind of buffet

Wrapping it all up

Will I go again?  If I can afford it (remember, yr. obt. svt. funds himself) yes, I will.  It was that good.

Thanks again to RM for inviting Tim and I to present and hopefully we didn’t let the Essbase side down.

Be seeing you.

EPM documentation – The little things mean a lot

The problem

If you’ve ever needed to send a link to a particular subject within the Essbase Technical Reference, henceforth called Tech Ref ‘cos I am lazy), you may have had a frustrating experience.  Why?

Copying and pasting of said links don’t work

What do I mean?  Watch and (not) marvel.

Filter MDX function documentation

Yr. obt. svt.’s attempt to copy and paste

The copy

The paste

The url copied is http://docs.oracle.com/cd/E57185_01/epm.1112/essbase_tech_ref/frameset.htm?launch.html.  That ain’t right.

Alas and alack

We’re right back where we started from, as you can see.

Getting round this

I whined (whinged for you Commonwealth readers) about this to the EPM Information Development team and they, perhaps in an effort get me to shut up or perhaps in an effort to help the general EPM community out – I prefer to think of the latter but the former is likely the case – showed me how to get to the right place.

It’s just this little chromium switch

Instead of copying the frame url, instead look at the frame’s information.  Here are the steps in Firefox.

Here’s looking at you, kid

Grab it and go

This copy is going to result in the url http://docs.oracle.com/cd/E57185_01/epm.1112/essbase_tech_ref/mdx_filter.html.  Could this be right?

Oh yes it is

Isn’t that nice?

So is this important?

If you share links from EPM documentation, yes, absolutely.  I know it seems trivial but I think each and every one of we Essbase Hackers have been frustrated by this.  

Again, my thanks to the great people at the EPM documentation team for showing how this works.  If you’re not taking advantage of their offerings beyond the base documentation page, you really ought to.

Here they are on YouTube, Google+, Twitter, Facebook, and LinkedIn.  You can’t say they aren’t trying to reach out to us.   

Be seeing you.

Stupid Programming Trick No. 24 – Sub Vars to tables via Calc Scripts

Introduction

This is another in a series of good ideas that have been shamelessly stolen inspired by Martin Slack.  I’m learning an awful lot from Martin; I’m not entirely sure that he is from me.  Such are the fortunes of war.

The problem

I needed a way to get the current month value from an Essbase substitution variable so I could do all sorts of weird and wonderful data integration things with it.  How to do it?

The idea that springs to mind for most of us (or at least yr. obt. svt.) is some way of getting it out from MaxL using the display variable syntax, piping it to a log file, and then face the somewhat unpleasant task of reading in a text file, parsing it via some language (VBScript, Perl, Powershell, etc;) and then from that getting it into a table.  

Ugh.

Stolen glory

Martin said something to the effect of, “What if you could just get it from a calc script?”  A calc script?  Hmmm.  Calc scripts, even using DATAEXPORT, write data, not metadata.  Or do they?

The Stupid Trick its own self

DATAEXPORT is an eponymous command – it exports data.  But what if there was no data to export?  Even more useless, right?  Here’s the Stupid Trick bit:  this is exactly what is needed – no data is fine because we’re going after metadata.  So how does metadata come from data?  And how does it get into a relational table?  Read on Gentle Reader and all will be revealed.

It’s all in your head in four parts

Part the first – the outline

Using my VFEDITWWW, I’ve added members that, at least in this blog, can never have data.  To wit, I have added the members No Measures, No Product, and No Market.  You will likely have different requirements unless you are The Beverage Company.

Part the second – the calc script

This member combination now gets exported to disk.  Ah, you say, yet another worthless bit of programming drivel from Cameron.  You’re wrong, for once, to think that.

Looking at the code below, note the structure of the export, with the DataExportColHeader option set to  Year.  When that is combined with an export of the Members That Can Never Have Data, look at what gets exported.

Part the third – the output

Brilliant, isn’t it?  And I mention that because the brilliance is all Martin’s.

I now have an output file with just the current month and nothing else.  This is a lot easier than trying to running a MaxL file and then parsing the output.  But how to get that into a relational data store?

Part the fourth – the SQL

The last problem now facing this approach is getting the value of Oct into a relational database.  Again, there are all sorts of complex and sophisticated ways of doing this:  ODI or SSIS spring to mind but there is a significantly easier way to do this, and to even do it within a SQL query.

Say hello to your new friend BULK INSERT

In SQL Server (in Oracle there is a concept called external tables that does much the same thing), there is a command called BULK INSERT.  While it is mostly used for doing just what it sounds like – a bulk insert of lots of data – in fact it can be used to bring any amount of data into a database from a file.  And if it can do that, and there is a file that has the current month, connecting the dots is pretty straightforward.

Here’s what my code looks like:

BULK INSERT Current_Period FROM

    '\\epm11123\C$\Tempdir\Current_Period.txt'

    WITH

    (

        CODEPAGE = 'ACP',

        DATAFILETYPE = 'char',

        FIRSTROW = 1,

        LASTROW = 1,

        FIELDTERMINATOR = '\t',

        ROWTERMINATOR = '\n'

    )

One consideration is that there’s no way to map the columns in the data source to the target.  Instead BULK INSERT is positional, e.g., the first field in the source file is loaded into the first field in the target table.  This is different from INSERT INTO…SELECT FROM syntax which allows an alteration of field order.

I used UNC naming not because I had to – c:\tempdir would have worked as well – but because mapped drives *must* use UNC naming, not a shared drive so it’s best to just get in the habit.   Also, this continues the tradition of Martin + UNC so what’s not to like?

Lastly, I defined the file by specifying the character set, file type, first and last row, the tab field terminator, and lastly DATAEXPORT’s line feed row terminator.  For giggles I threw in a first and last row but really everything other than the CODEPAGE and DATAFILETYPE settings are just a belt for the braces.

What does it look like?

Pretty good, actually, but there is a fly in the soup:

Do you see the issue?  The text file contents is “Oct” and that is exactly what BULK INSERT imports.  

There’s a pretty good chance that the double quotes are superfluous; the REPLACE function ought to take care of that:

There’s only one more issue to fix

This query will be run more than once, and that’s a problem as can be seen below when I ran the query twice:

The issue is that the query as it stands doesn’t clear out the table before inserting the Current_Period.txt file.  My brilliant data integration code expects one and only one record so perhaps it is not all that brilliant and will thus go KABOOM when two records are encountered.  Even worse is that this table could have multiple values, e.g., if this were to be run in both September and October the first record would be Sep which isn’t the current month.  

The solution is to run a TRUNCATE TABLE first, then the BULK INSERT, and finally the SELECT REPLACE command as below:

Now no matter how many times I run this, I get one and only one result.  Huzzah!

So what do we have?

A combination Stupid Trick that pulls out metadata, not data, via DATAEXPORT and then painlessly imports an evaluated Essbase Substitution variable into a table with nary a hint of MaxL script output or complex data import to SQL.  Take this query and stick it into a stored procedure, an ODI variable or procedure, etc.  The sky is the limit with this and it is dead easy.

Thanks again, Martin.  You keep thinking them up, I keep stealing them, and world+dog gets the benefit.  

Be seeing you.