27 October 2016

Stupid Programming Tricks No. 30 -- FIXing Stupid Programming Tricks No. 15 with @SHARE and @REMOVE

For the love of Mike

I just wrestled with an Essbase calc script that humbled me because:  I couldn’t figure the !#$%ing thing out and I already dealt with this FOUR YEARS AGO (just about) in November 2012.  And I forgot that I did.  And I found out only when I searched for it on the web.  How embarrassing on several levels.  I am however happy to relate that I did figure this out (hence this post) and would ask you what exactly you were doing professionally four years ago.  Share your immediately referenced memories with me care of this site and please include estimated time between the previous sentence and recollection.
Yr. Obt. Svt. at work and play

So what didn’t work?

As I wrote back in Stupid Programming Tricks No. 15 @SHARE the pain with FIX, there is a bug (feature?) with FIX statements when allied to @REMOVE and shared member hierarchies.  To wit:  @REMOVEing shared members from within a FIX simply doesn’t work.  

To recap, here is Good Old Sample Basic aka MVFEDITWWW completely cleared out:

What I want to NOT do is write to the level zero descendants of Diet:  100-20, 200-20, and 300-30 but nowhere else.

One would think using FIX, @REMOVE, and @RELATIVE as follows would provide the desired result.  The idea being that if I use a @REMOVE function to remove the leaf Diet members – which are all shared – I will write to everything but 100-20, 200-20, and 300-30.  I will be disappointed.

By disappointed I mean this:

Ah.  That isn’t exactly what I wanted:  Essbase just wrote everywhere.  What went wrong other than it obviously isn’t working?

Why it went pear shaped

Rahul S. figured this out in his blog post FIX, REMOVE, and CLEARBLOCK, You may end up with No data ;).  Essentially (basically?) trying to remove shared members from a FIX statement using @RELATIVE results in a union of the two halves of the statement, i.e:  FIX(@REMOVE(@RELATIVE("Product", 0), @RELATIVE("Diet", 0)).  The first @RELATIVE selects all of the level 0 descendants of Product, the second @RELATIVE selects the shared 100-20, 200-20, and 300-30.  When these two @RELATIVE functions are surrounded by the @REMOVE the second set is removed from the first and then unioned with the now-reduced first set.  In other words, the shared members are removed and then they are added back in.  Go Read The Whole Thing but at the end you’ll join me in a despairing “Bugger”.  Double Bugger because this isn’t in any way, shape, manner, or form how Essbase deals with @REMOVE when the scope doesn’t address shared members; that works exactly as expected.  Triple Bugger.

As I noted in my blog post four years ago, there are three ways round this:
  1. Explicitly list all of the members to be excluded in that second set.
  2. Use a UDA to exclude the members in that second set.
  3. Use the EXCLUDE..ENDEXCLUDE grammar.

I’m rejecting all three approaches on the grounds of OMG-you-have-to-be-kidding-me (do I really need to detail why doing this for three members is annoying but for 30 or 120 is utter madness?), OMG-this-is-redundant, and OMG-it-doesn’t-freaking-work.

In order:
  1. Listing shared members in code is just dumb on effort, maintenance, and ascetic grounds.
  2. Assigning members to an alternate hierarchy and then assigning a UDA to those members as well is also dumb from an effort, maintenance, and ascetic perspective.
  3. EXCLUDE..ENDEXCLUDE doesn’t work.

The first two points are arguably subjective but the issue around EXCLUDE..ENDEXCLUDE one is not.  I am here to tell you that it flat out doesn’t work when applied to large data sets.  Don’t believe me?  What does this tell you when EXCLUDE..ENDEXCLUDE is applied to a real database?

Full system utilization or crap code?  I vote for the latter and the blame is squarely laid at the feet of EXCLUDE.  Oh EXCLUDE..ENDEXCLUDE, don’t you know you’ve broken my heart?  Such wasted promise.

So what are my choices?
  1. Give up.  All life is an illusion.  Seemingly insoluble FIX logic binds one to the Wheel of Things.
  2. Figure out another way round this.  Surely there has to be a way.

Fix it or ignore it

It occurred to me that if FIX was the issue, there might be another way to address members.  Of course there is but it is not typically used in the context of selecting sparse members:  I am speaking of an IF formula in a calculation block.  Before you start with, “Always FIX on sparse, IF on dense” I’m here to tell you that particular rule of thumb sucks eggs.  The only things that matter are:  does it work and the how does it perform.  The latter measure in BSO is always driven by the number of blocks accessed (assuming EXCLUDE..ENDEXCLUDE isn’t driving you round the bend).  Fast code that doesn’t work is trumped by maybe-slightly-slower code that does.

Let’s look at my “wrong” code:
The code still focuses on level 0 members of Product but instead of using FIX to identify members in the Diet hieararchy I’ve used an outline test to see if a member is a descendant of Diet or not.  To that point about an IF statement on sparse being bad by addressing every block without constraint this code doesn’t allow that because the containing FIX on level 0 Product defines the absolute limit of members to be tested; the IF statement just performs an outline query within that FIX.

And what do we get?

Ah, relief.  This really does the trick:  exclude the shared members within the hierarchy Diet from operations against the overall hierarchy of Product.

Sample.Basic is one thing.  What about a real database?  I am happy to share that it is fast as it doesn’t significantly address more blocks than the hoped-for @REMOVE statement would.  Yes, it is arguable that FIXing on everything and then testing for inclusion in a shared hierarchy is slower than never touching that hierarchy’s members but cf. that comment about fast code that doesn’t work.

What, if anything, have we (I) learnt?

  1. Try to remember what you did in the past.  I actually (now) remember writing that post.  It was a real stinker figuring out what didn’t work.  
  2. Has this unintuitive behavior been documented by Oracle?  Has it been fixed (this is not what any reasonable geek expects)?  Has EXCLUDE..ENDEXCLUDE been changed to actually work?  No, no, and no.
  3. Don’t focus on, “It has to work, it just has to.”  No it doesn’t.  Think Of Another Way.  There often is one in Essbase-land if not always in life.

Also, I have solved this !@$%ing problem.  And closed down one of my more painful Stupid Trick posts.

Be seeing you.

24 October 2016

The Compleat Idiot's Guide to PBCS, No. 17 -- License Compliance

This is never a fun topic, is it?

With the caveat that IANAL nor am I the guy that actually signs the contract nor do I work for Oracle, it has been pointed out to me that PBCS – which was once Planning-in-the-Cloud-but-better – is now a platform that supports multiple products such as PBCS, EPBCS, FCCS, the soon to be released (or is it out now?) PCMCS, and I would daresay a number of future products.  Multiple products equals (potentially) multiple contracts; certainly that is true if you follow the message of this post which is a note of warning because:
  • Said products are not in your company’s contract unless you’ve specifically bought them.
  • There’s not a (technical) thing in the world to prevent you from using what you haven’t paid.
  • Oracle can see everything you’re doing on their pods.  You are after all paying to have Oracle manage the software.
  • Oracle has a reputation for pursuing license non-compliance with gusto, verve, and tenacity.
  • It really isn’t cricket to use what you’ve not paid for.

So can we say that playing with this sort of thing is as dangerous as A-Bomb testing?  Almost.

Like eating cookies in the supermarket before you get to the checkout

OMG that drives me crazy.  Do you have the money?  Is it yours?  Will you put it back if you don’t like it?  Maybe, no, and who knows but look at what you’re doing right now.  While I digress once again my philosophy on paying for things when one ought to should be clear.

Careening wildly back to the point of this post, the rules about using what you’ve paid for in the Cloud are actually no different than what happens in the on-premises world (or, arguably, the supermarket checkout line).   It isn’t yours if you (actually your company) haven’t paid for it.

It’s easy-peasy-no-big-deasy to navigate to eDelivery, download whatever on-premises EPM product your heart desires (sans patches as that is covered by your access at My Oracle Support), and – if you’re way smarter than me – flawlessly and quickly install the software in your environment.  Installed or not, if you haven’t paid for it, you’re in violation.  Yikes.

As just about everyone knows I am completely and comprehensively infrastructure allergic and moreover incompetent (I am incompetent in all sorts of areas; this one is infrastructure).  Contract violation is miles easier in the Cloud because you/I don’t have to install anything so even a profound allergy isn’t prophylactic.

Get thee behind me, Satan.  

Although I am a fan of the dramatic, I am not actually equating Oracle license violation with Luke 4:8 and Matthew 16:22-24 but I am making the point that temptation is staring you right there in the face.  Don’t be weak.

Here’s what we see on login to the service.  I feel like Eve in the Garden of Eden.  

I’m going to resist FCCS temptation not because I am morally superior but because I am not a beancounter.  I am going to go to Planning and Budgeting ‘cos that’s what I do.

With that warning, ooooooh, lookit that.  Would I like to click on EPBCS?  Why yes I would.  And what happens?

The wizard to build an EPBCS application fires right up.  And that is where I stop for now.

You Have Been Warned

I actually am too chicken to see what happens if I click on FCCS as a billet-doux from Oracle Contracts is not very high on my To Do list.  I’d ask who amongst the Best and Brightest of my many (ahem) readers have violated their license to see what happens but that would be self-incrimination.  I’m not here to tempt (there’s that theme again) you to throw away your 5th Amendment rights.

But seriously, folks, if you know what happens (I am guessing off you go on an FCCS application wizard and subsequent contract violation(s)) and you shouldn’t be doing it, there is a way to anonymously comment to this blog.  I’m curious to hear what you have to say.  And no, I don’t actually expect a response but who knows.

Also seriously, don’t do it.  If you’re a partner, sign up for that pod (these are precious things within consulting companies) internally; if you’re a customer, I understand Oracle has sales reps that are just itching to sell you all of these products.  I’ll bet they’ll figure out a way to make you happy.

Be seeing you.

Postscript

I really am going out on a bit of a limb with this post because I simply haven’t navigated into a product I shouldn’t have access to.  Typically (always, actually), I for real and for true do whatever I write about because how else could I write about it.  This is different.  Call me Scaredy Cat Cameron, but as noted I’ve got lots of other things to do with my time.

My point is, I am unusually anxious to correct whatever’s wrong in this post.  Credit will be given no matter how embarrassing it will be for me.  Besides, mild embarrassment is my normal state.

09 October 2016

The Compleat Idiot's Guide to PBCS, No.16 -- Activity Reports

What’s happening?

In the traditional on-premises world, if you or your IT department can figure out how to monitor Planning (and Essbase and FR and FDMEE and who knows what else), the world is your oyster at least when it comes to server and application statistics.  Tracking Planning itself is, at best, an art and I’ve never seen anyone convincingly capture its statistics.  It’s hard.

At least in the on-premises world we can get our figurative fingers around the servers.  In the Brave New World of SaaS that simply isn’t possible.  In fact there is no access to server(s) behind PBCS.  And that’s not by accident but in fact is the whole point behind SaaS.  No muss, no fuss, just Planning in the cloud.  Seriously, is there anyone out there that likes owning Hyperion infrastructure except consulting companies with rapidly-declining infrastructure practices?  No, thought not.

No Essbase or Planning web application logs, no IP reports, no nothing.  How on earth do we geeks know what’s going on within our PBCS application?  Remember, this isn’t like on-premises Planning where you can look at servers while running Planning, looking at log files, or calling up and abusing your woefully overworked BOFH because things have gone FOOM!

So what what happens when your BOFH or even you, oh Gentle Reader, wants to know what on earth is going on in within your PBCS app?  Is it sclerotic in nature?  Was it formerly Zip-a-Dee-Doo-Dah fast before but now is like molasses on a cold New England morn?  Would you just like to know who, what, when, where, and how your system is being used?  You are in luck for PBCS now has an activity report that will give you deep insight into your pod.  And – cos’ it’s SaaS – the whole thing is there for the asking.  No IT, no fancy consultants, no nothing.  Or is that something?  You decide.

Steps to performance Nirvana

Who moved my Navigator?

Yes, the Cloud is ever changing.  Sometimes that’s a good thing, other times not so much.  I hadn’t logged into this instance in over a month and my beloved Navigator was nowhere to be seen.  After the mild panic attack, here it is and it’s the first step in getting to that activity report.

Look over it all with Overview

As noted above, everything changes if you haven’t looked at PBCS for a while.  Continue on, brave geek, and go to Overview to see the magic.  Honestly, it is really cool.

Check out the Activity

We’re getting really close.  The most recent day’s activity is available as is data going back 60 days.  

And what happens when a date is clicked on?  Magic, that’s what.

Let’s look at what we get

What we get is a window into PBCS’ soul.  Fair enough, that may be a bit of an exaggeration but if you think about all of the metrics that can be captured in an on-premises system and all of the metrics that cannot be captured in a SaaS offering (which is, um, everything), the fact that PBCS offers this up is really kind of gratifying.  Remember, this isn’t idle curiosity but instead how we Planning geeks understand what works and what does not work in our implementation.

I’ll put my comments into the grey boxes.  I’m pretty sure Oracle doesn’t supply running commentary on the report but if you ask really nicely, perhaps they will.

Activity Report
All Times in Pacific Standard Time

Number of Users
This is more than just the number of users – it’s the users over the last five days, how long they used it per day, how many users there have been over the last 7 and even 30 days.  Wowzers.

Metric
09/16
09/17
09/18
09/19
09/20
09/21
Today
Users
267
257
34
72
333
348
396
Usage Duration in Hours
121
124
44
58
159
186
248
Users Last 7 Days
478
479
466
483
574
608
653
Users Last 30 Days
865
861
808
776
825
868
890


Do you think the application is slow?  Or are you just (like me) incredibly impatient?  Numbers don’t lie and this report tells me if it’s the UI or calcs provide the most pain.  As one might imagine, it’s the calcs and they’re detailed by user, location, time of day, form (or Smart View), and time.  Must Write Better Code.

Percentage of UI Requests over 10 Seconds (1.03%)
Top 30 Worst Performing User Interface Actions over 10 Seconds
Duration (Min:Sec)
User
Time
Screen
Action
Object
Durations (Min:Sec)
373:34
xx250059
20:52:49
Smart View
Adhoc Get Default Grid

Essbase=373:33
216:46
xx250460
18:32:37
Smart View
Adhoc Get Default Grid

Essbase=216:45
188:58
xx250253
18:38:45
Smart View
Adhoc Get Default Grid

Essbase=187:52
188:32
xx185095
19:59:32
Planning
SmartView


185:24
xx200008
05:43:39
Planning
SmartView


184:21
xx185020
16:05:31
Smart View
Adhoc Get Default Grid

Essbase=184:20
174:14
xx250036
15:20:52
Smart View
Adhoc Get Default Grid

Essbase=173:09
143:15
xx250180
05:24:46
Planning
SmartView


99:17
xx129565
05:43:39
Planning
SmartView


01:25
btchadmin
16:36:30
EPM Automate
Download File Zip
File Name=EXP_Sec.zip

00:59
xx185067
10:03:50
Smart View
Save Form
40.0 Revenue (USD),
Essbase=00:01
User Experience=01:08
Client=00:07
Data Validation=00:00
SmartPush=00:00
Business Rules=00:56
Network=00:02
00:45
xx185091
13:00:02
Smart View
Save Form
40.0 Revenue (USD),
Essbase=00:01
User Experience=00:54
Client=00:08
Data Validation=00:00
SmartPush=00:00
Business Rules=00:42
Network=00:01
00:40
xx185126
12:26:21
Smart View
Save Form
10.A Travel,
Essbase=00:01
User Experience=00:46
Client=00:05
Data Validation=00:00
SmartPush=00:00
Business Rules=00:38
Network=00:00
00:38
xx185126
12:03:19
Smart View
Save Form
10.A Travel,
Essbase=00:00
User Experience=00:50
Client=00:10
Data Validation=00:00
SmartPush=00:00
Business Rules=00:36
Network=00:00
00:38
xx185126
11:42:56
Smart View
Save Form
10.A Travel,
Essbase=00:00
User Experience=00:55
Client=00:11
Data Validation=00:00
SmartPush=00:00
Business Rules=00:35
Network=00:06
00:36
xx185135
13:10:28
Application Management
User Login Report
Access During=120,

00:32
xx185085
02:43:53
Planning
PlanningCentral


00:31
xx185154
08:11:51
Smart View
Save Form
10.A Travel,
Essbase=00:00
User Experience=01:25
Client=00:52
Data Validation=00:00
SmartPush=00:00
Business Rules=00:28
Network=00:01
00:31
xx185099
20:19:18
Smart View
Save Form
10.A Travel
Essbase=00:01
00:29
xx250289
10:48:24
Smart View
Save Form
9.D Software (Owned Leased),
Essbase=00:00
User Experience=00:52
Client=00:21
Data Validation=00:00
SmartPush=00:00
Business Rules=00:28
Network=00:01
00:28
xx185126
12:23:13
Smart View
Save Form
10.A Travel,
Essbase=00:00
User Experience=00:36
Client=00:07
Data Validation=00:00
SmartPush=00:00
Business Rules=00:27
Network=00:00
00:28
xx185126
12:06:31
Smart View
Save Form
10.A Travel,
Essbase=00:00
User Experience=00:44
Client=00:12
Data Validation=00:00
SmartPush=00:00
Business Rules=00:25
Network=00:04
00:27
xx185091
12:58:23
Smart View
Save Form
40.0 Revenue (USD),
Essbase=00:01
User Experience=00:46
Client=00:17
Data Validation=00:00
SmartPush=00:00
Business Rules=00:24
Network=00:01
00:27
xx185154
08:24:21
Smart View
Save Form
10.A Travel,
Essbase=00:00
User Experience=01:26
Client=00:51
Data Validation=00:00
SmartPush=00:00
Business Rules=00:24
Network=00:07
00:27
xx185091
04:47:50
Smart View
Save Form
40.0 Revenue (USD),
Essbase=00:00
User Experience=00:49
Client=00:14
Data Validation=00:00
SmartPush=00:00
Business Rules=00:25
Network=00:08
00:26
xx185013
01:34:31
Smart View
Save Form
10.A Travel - Summary,
Essbase=00:11
User Experience=03:20
Client=02:49
Data Validation=00:00
SmartPush=00:00
Business Rules=00:05
Network=00:04
00:26
xx185013
01:19:19
Smart View
Save Form
10.A Travel - Summary,
Essbase=00:10
User Experience=03:17
Client=02:50
Data Validation=00:00
SmartPush=00:00
Business Rules=00:04
Network=00:01
00:26
xx185091
07:48:50
Smart View
Save Form
40.0 Revenue (USD),
Essbase=00:01
User Experience=00:55
Client=00:25
Data Validation=00:01
SmartPush=00:00
Business Rules=00:22
Network=00:03
00:26
xx185091
07:50:55
Smart View
Save Form
40.0 Revenue (USD),
Essbase=00:01
User Experience=00:48
Client=00:20
Data Validation=00:00
SmartPush=00:00
Business Rules=00:23
Network=00:02
00:25
xx185154
08:28:58
Smart View
Save Form
10.A Travel,
Essbase=00:00
User Experience=01:13
Client=00:45
Data Validation=00:00
SmartPush=00:00
Business Rules=00:22
Network=00:03

How many Planners use the system?  How long do they use it?  And when?  PBCS tells all.

Users by Hour
Number of Users by Usage Duration

Who uses it the most?  Who uses it the least?  

Top 10 Most Active Users by Usage Duration
User
Usage Duration (Min:Sec)
a12345.btchadmin
1321:10
a12345.xx250110
310:03
a12345.xx185157
289:05
a12345.xx185398
237:53
a12345.xx210023
229:28
a12345.xx111085
223:30
a12345.xx185095
205:40
a12345.xx185265
204:48
a12345.xx185135
203:41
a12345.xx250118
189:02

10 Least Active Users by Usage Duration
User
Usage Duration (Min:Sec)
a12345.xx185113
00:00
a12345.xx185082
00:00
a12345.xx250330
00:00
a12345.xx185072
00:00
a12345.xx162800
00:01
a12345.xx185057
00:06
a12345.xx185005
00:11
a12345.xx250243
00:16
a12345.xx185066
00:17
a12345.xx250173
00:19

Browser problems are a fact of life in Planning-land.  Think of all of the pain that surrounds IE11 and Enterprise Mode.  Ugh.  Perhaps you can convince your Planners to go with something a bit less old fashioned.  At least you’ll know what they use.

Browser Version Usage
Browser Version
Usage Count
Microsoft Internet Explorer 11.0
3
Chrome 53.0.2785.116
2
Firefox 45.0
1
Firefox 48.0
1


Is that enough?  It should be.

Inquiring minds want to know

What I’ve illustrated is what’s available today in PBCS.  Is that available in out-of-the-box on-premises Planning?  Do I really have to ask?  No.

Putting aside the smugness that comes with PBCS’ features vis-à-vis on-premises, what else might PBCS provide to us in the way of an activity report?  I for one would like to know:
  • What, on average, are my longest Business Rules by form?  Slow “saves” – we know these are really form calculations – cause users to lose their Planning minds.
  • How long are my longest Business Rules, attached to a form or not?
  • Perceived performance can come from a slow UI, or a slow calc, or a slow save to Planning.  But a bad network connection also makes things slow.  That’s not an application fault.
  • What’s been changed in the application.  You know what you’ve changed, but what about other developers.  Actually, if you’re like me, you have no idea what you’ve changed, oh, 15 minutes after you’ve done it.  Projects can be crazy…

So what would you like to see when it comes to Planning diagnostics?

Be seeing you.