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.

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.