Why I created a blog

Its been many years since I first created this blog. It has remained true to Essbase and related information over those years. Hopefully it has answered questions and given you insight over those years. I will continue to provide my observations and comments on the ever changing world of EPM. Don't be surprised if the scope of the blog changes and brings in other Hyperion topics.


Thursday, December 14, 2017

Defeating the Phantom Value Menace

Even old dogs can learn new tricks. I was recently teaching a calc script class and was discussing the Clearblock command. I mentioned the options,  All, Upper, NonInput, Dynamic and Empty and was describing what each one did. At this point, one of the students; I'll call her Fred, asked if this would solve her issue. She described the issue, their automation process rebuilds the dimensions and in doing so, quite often moves members from one parent to another. Often all of the children are moved from one parent making that parent now a level 0 member. The member would retain the data and therefor double count it. This is an issue that is called phantom data,  I and other have known about for a long time and I never had a good way to deal with it. She asked if the NonInput option would get rid of the data?  I gave her a great reply "I have no idea. It should work, but I've never tried it". I told her I would test it and get back to her.

I tested it yesterday and it works.  Here are the steps I did to test it. If anyone wants to shoot holes, I am not against it, I am happy for you to tell me what I'm missing.

I took a copy of sample basic and exported the level 0 data. Typically one would just use the calcdata.txt file to load, but that loads all levels so everything would be an input value. I then cleared the database, reloaded the level 0 extract and ran the default calc.  As you can see the result is as expected.  I have data for all of the intersections



Next I went into the outline and removed the children of Colas (100)

 
I saved the outline retaining all data. I then refreshed the data in my spreadsheet.


As you can see, I have phantom data in Colas and the retrieval no longer recognizes its children.

I created the following calc script. Notice I fixed on level 0 members of my sparse dimensions. This way the script would run quickly and only impact the those members that are newly designated level 0 members (by removing their children).  I did the agg at the end so it would propagate the "Fix" up to the ancestors.

     SET UPDATECALC  OFF;
            SET AGGMISSG ON;

      FIX(@relative("Product",0),@relative("Market",0))
           CLEARBLOCK  NONINPUT ;
      ENDFIX

       agg("Product","Market");

I ran the script and verified the results

IT WORKED !!!! Colas data was removed but not any other.  I shared the results with "Fred" and mentioned that were I doing this, I would add this calc right after my  dimension build to clear out the members before a user had a chance to submit data there.

There you have it, a problem I have been struggling with for years, easily solved but a simple question. Thanks "Fred".  Others may have stumbled upon this before, but I've never seen anyone post it.

Friday, August 18, 2017

Fast and Furious - OAC Updates

It took a little while for the first Essbase OAC patch to appear. It did  on July 29th and I blogged about all the new features. Well a mere two weeks later and we have the next big release (what I'll call 106). When I saw the pace it reminded me of the following quote (watch the video link for the full effect)
Round the world and home again
That’s the sailor’s way
Faster faster, faster faster
There’s no earthly way of knowing
Which direction we are going
There’s no knowing where we’re rowing
Or which way the river’s flowing
Is it raining, is it snowing
Is a hurricane a-blowing
Not a speck of light is showing
So the danger must be growing
Are the fires of Hell a-glowing
Is the grisly reaper mowing
Yes, the danger must be growing
For the rowers keep on rowing
And they’re certainly not showing
Any signs that they are slowing
‘Wondrous Boat Ride’, from Willy Wonka and the Chocolate Factory (1971)

Yes the OAC dev team is delivering features and functionality at a tremendous rate. If you are looking for what is included with bug fixes and features, it is a bit difficult as the readmes still don't seem to work and after you install the parch, you can't even get to the (broken) link.  So I won't talk about bug fixes here although I understand there were a bunch. I'll talk about the new features.  I have to thank my friends at OAC dev for providing me with this info. Most of the screen shots are theirs.

Calc Trace

The first thing I want to talk about is a new feature called Calc Trace. It is amazing that once Product Management has to do their own testing we get features we have wanted for a long time. That is the case here. It was difficult for them to figure out what a calculation script was doing. So out of it came this. You set a application config setting CalcTrace On and from Smart View you can see the numbers that make up any calculation. In your script, you provide the intersection you want to trace



then when you run it you get a pop-up in Smart View with what contributed to the calculation
 
There are a lot more details, but this should be enough to get you excited.
 

Audit Trails

Next,Audit trails have been added to track data changes. To enable it there is a config setting "Audittrail Data" The audit trail is captured in a .csv file on the server. This will track Smart View data changes, LRO , URL File and cell notes. Access to the results is based on user security , You can see the results either in Smart View for a selected intersection or by exporting the file and openin (in excel preferably).  UPDATE, it has been confirmed that this only works for BSO and Hybrid cubes.
 
 

Partitions and XREf/XWrite

Want more? How about a UI editor for replicated partitions. In the last release we got transparent partitions, now to complete it we get the replicated as well. Remember for partitions, if you go across OAC instances you have to be licensed for Enterprise edition, but within an instance is ok.
 
Talking about partitions, there are enhancements to partitions in general

  • Validation/Save errors have been made clearer
  • There is ample scope for partition definition becoming stale. For ex:
    • cube deleted/renamed,
    • Essbase host/port changes,
    • user credential changes etc.
  • Consequences of such stale partitions could manifest in multiple ways.
    • Incorrect query/calculation results for slices with partition overlapping .
    • Presence of orphan partition between a pair of cubes prevents creation of fresh partition of same type.
    • There was no cleanup/recovery mechanism from such stale metadata in partition definitions without manually deleting the ddb files.
  • Solution
    • Invalidation & Periodic auto cleanup on servers.
    • Invalidate orphan partitions at first discovery.
    • Cleanup all orphan partitions on the cube at startup.
 

 
Similar to Partitions, there have been a lot of changes to Xref and location aliases mostly to allow cross instanceand  on-preXREf . XRef now hastwo flavors:

@XREF(LocationAlias [, mbrList])

Recommended to be used to access remote cube from a different essbase instance. For ex: Local cube on cloud & remote cube on-prem
 
and
 
@XREF(App,Db [, mbrList])
Meant to be used only in single essbase instance models.
Facilitates easy usability through simple DBX import of multiple dependent cubes
 
For location Aliases to define a connection to another server, you define the connection then use that connection in the location alias
 
Scenario Management has been improved. In prior versions if you added sandboxing to an existing cube you lost all data. I guess you still do, but now if you export the data prior to creating the sandbox, you can re-import it and it will automatically be saved to the base member. No need to build a load rule.
 

Hybrid cube automated solve order.

This is enabled by setting the config setting “HYBRIDOPTIMIZEDSOLVEORDER TRUE”

Doing so, sets up  bunch of automatic solve order settings
  • If the formula contains even one ratio operator (/, %), then set the solve order to 51
  • If the formula contains even one “*”, then set the solve order to 5  
  • Rest operator or combinations between operators of different types will get their default solve orders (10-50)
  • If consolidation type of formula member is ‘^’ (never aggregate) then set the solve order to 51
  • If dynamic member doesn’t have formula (aggregation) , then no changes to solver order are currently performed
Note this is based on the operators within the formula not the unary operator on the member
 

ESSCLI

 
The next item is something I requested for a long time. It is encryption for EssCLI. In the early versions, the ID and password were clear text in the file. Now an encrypted file is created that stores the password. The first time you log in you are prompted for the password and it is saved in a file, the next time, you just specify the user and the password is used from the file
 
 
EssCLI has also added support for remote data sources. This means you can load on-prem data relational data sources from within the companies firewall without opening ports. To use it you create a connection.properties file the EssCLI uses. It uses JDBC drivers for the connection.
you can then use it in the CLI

Dimension build

esscs dimbuild -application Sample -db Basic -rule Dim_Product.rul -stream -properties connection.properties -restructureoption ALL_DATA

Data load

esscs dataload -application Sample -db Basic -rule Basic.rul –stream -properties connection.properties

Drill Through Reporting

I have to say I'm not thrilled about this feature as I think this first generation is very limited, but at least it is a start. In Enterprise edition, you can drill back to a source. The reason I'm not thrilled is the dril through definition is tied to the generation names you would assign in the outline. I think it limits the functionality and I've expressed this to development. It is a multiple step process to set this up and would take too much space in this post to go through it. I'll create a separate post to walk you through the steps.


Cube Designer


 This Smart View extension gets better with each release and the new features include:
  • Improved Server Error Messages handling
  • Allows Export of Application from within Cube Designer
    • Include Data
    • Includes Calc Scripts
  • Data Export supports size up to 400 MB
  • Text Lists Support in the Cube Designer Wizard
  • Text Lists supports up to 64k entries

 

There are also enhancements for formulas in unstructured data loads

  • Allow for Hints for Measures
  • Can refer to specific column names as formulae
  • Provide Excel based Column References as formulae
  • Mix of both
  • Support for new implementation of XREF/XWRITE
  • Allows for migration of applications from one instance to another without the need for location aliases

LCM Improvements

I've saved one of the big changes for last. You can now use an LCM export from PBCS and FCCS to build an Essbase cube

  • Support for LCM Extracts from EPM using EPMAutomate
    • The OAC Essbase LCM Import using CLI extracts the Essbase Cube contents from the Export done using EPMAutomate
      • PBCS
      • FCCS…
Other general LCM Enhancements include:
  • Support for Partitions (Use Trusted Connections, within the same instance) – Earlier required manual intervention of changing partitions
  • Calc Associations – Users are manually added (Cloud to Cloud)
  • Support Audit Trail – preserved and imported (Cloud to Cloud)
  • Roles are migrated (Cloud to Cloud)
  • LRO (Not Supported) – Can be done manually using MAXL
There are some other minor things like a MaxL script library and template support.

It is amazing how far OAC/Essbase has come in such a short time. I look forward to the next round of enhancements.







Wednesday, August 9, 2017

OAC - The first patch


The honeymoon is over. Well not for my Boss, Edward Roske, he got married in Paris last week. So for him it is just starting. Congratulations Edward. For us using OAC, it is over because the first patch has arrived. It is not a virgin version any more.  That is a good thing for us.  While a lot of things in the patch were talked about at KScope as “coming soon” and was put in to the “Look Smarter than you are with Oracle Analytics Cloud” book, I feel it is good to go over what was released in the patch on 7/29/2017. The version is 17. Interestingly enough, one thing that did not work was getting the readme for the update.

There are a number of enhancements in this release. First, we can now see the logs generated without having to FTP onto the server and search for them. If you are at the Server level (having not selected an application or database) Select Administration and you will see the logs structure.


A couple of things to note.
1. You can only download the logs, they re not interactive on the screen
2. It is best to use something like Notepad ++ to view the output. Since this comes from a Linux box, notepad does not put display it well.
 
Second, A Rules file editor has been added so you can maintain and create rules files. When you select a database, you can go into the scripts card and click on the second 
"Tab"
 
Once there, we can create a new rule or edit an existing one.
 
Currently, you can't see the data like you can in EAS, but this is much better than the JSON editor shipped in the initial release (Although, I wish they kept that as an option as in some cases it would be nice)
 
Next, a UI has been added for creating transparent partitions.  I believe they are working on replicated partitions, but don't quote me on that.
In addition, you can now export Data and calc scripts from the UI
 
And here is what it looks like in the exported DBX file in excel. Notice if the file is over 1 meg, it saves it to a file instead.
 
 
another change to the UI is you can now set Location Aliases for using XRef (which also means you can use XRef)
 
Non-UI changes include:
  • The ability to use MaxL files in EssCLI (there will be a whole blog post on this)
  • Application timeout settings. If an application is idle for 15 minutes it will stop. In the future this is supposed to be configurable. (for 3 minutes to 2 weeks)
  • A new Sub-select for MDX in BSO (yes I said BSO) cubes.  The information I got is that it can be sued as a filter mechanism to reduce processing that requires partial aggregation. I've not had a change to delve into this further or see the benefits or ramifications. Once I do I'll talk about it.
  • Finally CalcLockBlock has been deprecated in this release. 
BUT wait, it you buy into this now, you also get at no additional charge, updates to the Cube Designer extension in Smart View. Alas, I don't have screen shots as I haven't had time to download the latest version but the improvements include:

  • Admin capabilities -> Delete App/Cube
  • Search Capability in Hierarchy viewer
  • Support for Generation Names
  • Support for asynchronous jobs
  • File Options - Setting the file delimiter, Skip Rows and Ignore Columns
  • Flip the sign for members on load (selecting the dimension and UDA)
  • Support for Text Lists 
While I'm sure there are bug fixes as well, (I know the corrected a problem with CDFs) until I get the actual readme, I'm not sure what fixed..
I expect the next release will occur soon, and when it does I'll blog about some other cool features that are supposed to be in it.


Friday, July 28, 2017

The Hidden Dimension

Let me digress, Oh wait, I have to tell you something before I digress from it. The other day on OTN (or was it Network 54?) there was a question posted on how a user could add commentary to Essbase in OAC.  I had mentioned Linked Reporting Objects(LROs) and verified that it worked in OAC.

When the user tried, they got an error. I was able to recreate it:

In my original test, I was using a non-sandbox cube. When getting the error, I had switched to a Sandbox enabled cube. it was not making sense to me, then I remembered to look at the dimensions. Here is what I saw:
You will notice in addition to the Sandbox dimension another dimension was added "CellProperties".  This is not an attribute dimension but a real "Hidden" dimension in that it acts like an attribute and does not appear as part of a standard retrieval.
This dimension is only added when Sandboxing is enabled. Looking at the member I saw there were three children:
When I added the member EssValue to my sheet LROs could be added. So what is this dimension and what is used for? I put all three members on my retrieval sheet and did a refresh.
EssValue returned the data value that is part of a normal retrieval. This means to be that "CellProperties" must default to this value. EssStatus gave me a the number 1. Hmm what does that mean? If I cleared out the member both the EssStatus and EssID went to #Missing. Entering a number and I got :



 
It changed to a 2. I remember in the beta talking about this. If I remember correctly, 1 means it was loaded from a dataload. 2 means user input (Submit). I believe a 3 means it is the result of a calculation.
 
The EssID, is really meant for relationships in sandboxing and approvals and really has no value to an enduser.
 
So you get two tips in this post.
1. How to get LROs to work in a Sandbox enabled cube
2. What that mysterious hidden dimension is and what it is used for.
OK, now I can digress