G’day All,
So I’ll skip the shenanigans of Gaullic invasions and move straight to the good stuff – but this is basically a copy of a post I guest wrote for Cameron after being horrified that VBA was his go-to for dealing with MDX.
The agony and the ecstasy
Upon first reading Cameron’s blog post on allocations in Hybrid I was horrified, disgusted, appalled, gobsmacked bemused regarding the machinations required to achieve something so simple. I believe there may have also been the words, “VBA??? That is the sound when doves cry.”, although to be honest I haven’t directly heard that but imagine that would be the sound of doves in their cognitive programming agony if they could code in VBA. But I digress. Surely, there is a better option!
Here are the issues to consider:
- All upper level members of a Hybrid cube are dynamic
- If we calculate against those upper members, we will throw the cube out of Hybrid mode. and into traditional BSO sparse dynamic calculations…at which point we’ll probably beat it in calculation using an abacus. Or Roman numerals. Or counting matchsticks. You get the idea. This is a Bad Thing To Be Avoided.
- Therefore we have to work out a method of getting the calculated upper level data to a stored level zero member.
- The only way to pull the data out at the upper level of a Hybrid cube is MDX.
- MDX is…not very elegant when run out using MAXL (hence Cameron’s everything-is-a-nail-if-VBA-is-your-hammer strategy). Cameron notes that this analogy is applicable to screwdrivers, sledgehammers, and ODI when used to build Planning dimensions.
Back to the Future I – A history lesson
Okay – so the underlying problem is pretty simple. Let us now jump back to an earlier time, an easier time, a simpler time. Hyperion version 11.1.2.2. Before all of this Hybrid nonsense. When BSO cubes ruled the world and performance was lacklustre but we liked it that way.
One of the brand new and world-breaking features in 11.1.2.2 was the advent of the @XWRITE statement. The counterpoint to the @XREF – this would allow us to write data from one cube to the other and had some amazing potential! Its benefits were easily recognisable – because you were only writing from active blocks, performance was significantly increased, and @XWRITE also had one key advantage in its ability to create the blocks on load! Gone were the days on needing to put CREATENONMISSINGBLK above your @XREF statements and waiting hours.
One of the items slipped in the documentation for this function is as follows: “For @XWRITE only, a reserved keyword @LOOPBACK can be used to write to the same database.” Curious – but actually had some utility in its ability to create blocks. The very talented David Ambler turned this into an elegant strategy to create blocks for a balance sheet in future years – pushing opening balances from the closing balances of the prior year.
Now I bet I know what you’re all thinking – that’s great Peter, so you’re just going to use @XWRITE from the parent to write to the stored level-0 member. Well done for reusing a four year old function. Except…that doesn’t work. @XWRITE is one of those top-down formulas that doesn’t work in Hybrid (and I did try). The cube drops out of Hybrid mode and we’re back to the abacus.
Still it’s a nice idea. Use a function to write back to the database directly, rather than export the data, map it and reload it. Now all we need is a function that natively uses MDX.
Back to the Future II – Where’s my hoverboard
Fortunately Oracle has provided one! The new (from 11.1.2.3.502 at least) CDFs of MDXDataCopy & MDXDataExport. Perhaps even more fortunately, Celvin Kattookaran has already written up how it all works! (whom despite never having met, I feel indebted to a significant number of drinks for all of the assistance he’s provided over the years) .
So, we’ve got a function that can export data using MDX thus keeping the database in Hybrid mode as well as the ability to map data from one member to another.
Where it’s at
Using good ol’ Sample.Basic, albeit a Hybridized/bastardized version of it, the code is as follows:
//ESS_LOCALE English_UnitedStates.Latin1@Binary
RUNJAVA com.hyperion.calcmgr.common.cdf.MDXDataCopy
"whynotzoidbergasAESkey==" /* key */
"pemO6ZElxHY7m570TfvhDB8H4WMcxP53wjWPlB26SwETEj/so3WuzB8ZBjnJBhUJ" /*user*/
"nqkqpUXwhhAywRPnn/ZFxOy5kKpb7iZoZWf6bA1NOJVR1hBzj15k2hpzjcgIwidC" /* password */
"Hybrid" /* from application */
"Hybrid" /* from database */
"Hybrid" /* to application (can be the same as the source) */
"Hybrid" /* to database (can be the same as the source)*/
"{[Scenario].[Actual]}" /* MDX that defines the column members */
"crossjoin(crossjoin(crossjoin({[Year].Levels(0).Members},{[Measures].[Sales]}),{[Market].[Market]}),{[Product].[Product]})" /* MDX that defines the row members */
"Sales,Market,Product" /* source member mappings, can be empty */
"Sales Driver,No Market,No Product" /* target member mappings, can be empty */
"" /* Target POV columns, members from dimensions that do not exist on the source*/
"-1" /* rows per page */
"e:\\Ifthisworkscameronowespeterabeer.log"; /* log file , can be empty */
RUNJAVA com.hyperion.calcmgr.common.cdf.MDXDataCopy Using the RUNJAVA command because we only want to run this command once and it’s all defined. There is also the function @CdfMDXDataCopy which could be used, but is not necessary here.
"whynotzoidbergasAESkey==" /* key */ "pemO6ZElxHY7m570TfvhDB8H4WMcxP53wjWPlB26SwETEj/so3WuzB8ZBjnJBhUJ" /*user*/ "nqkqpUXwhhAywRPnn/ZFxOy5kKpb7iZoZWf6bA1NOJVR1hBzj15k2hpzjcgIwidC" /* password */
This command requires a fully encrypted username and password and an associated AES key. In order to encrypt the key you need to use the calcmgrCmdLine.jar utility which can be found Essbase server. The command is as follows:
java -jar calcmgrCmdLine.jar -encrypt -key java -jar calcmgrCmdLine.jar -encrypt -key
I had a number of problems with this because the error handling of the AES string length isn’t…great. Basically you need a 24 character key with enough entropy in it – it doesn’t actually matter what it is (see my example) but you’ll need to test a few options (or just hammer the keyboard to get 24 chars).
"Hybrid" /* from application */ "Hybrid" /* from database */ "Hybrid" /* to application (can be the same as the source) */ "Hybrid" /* to database (can be the same as the source)*/
This one is pretty self-explanatory. The standard use of this function is to write to other databases – but as you’ll see, you can replicate our @LoopBack function from earlier and write back to the source.
"{[Scenario].[Actual]}" /* MDX that defines the column members */
"Crossjoin(Crossjoin(Crossjoin({[Year].Levels(0).Members},{[Measures].[Sales]}),{[Market].[Market]}),{[Product].[Product]})" /* MDX that defines the row members */
Okay: so this is one of the limitations of this process – the MDX query is simply a Column and Row definition. No Where clauses etc. Still, for what we’re trying to do here it’s reasonably functional.
"Sales,Market,Product" /* source member mappings, can be empty */ "Sales Driver,No Market,No Product" /* target member mappings, can be empty */
Here is where some of the fanciness lies. Really simple mapping (it’s actually just a basic string replace function) but allows us to write from a parent to a child to move the data to a stored member.
"" /* Target POV columns, members from dimensions that do not exist on the source*/
This is blank because we have no additional dimensions (the target IS the source). However, this is potentially useful when you’re mapping to an ASO reporting with additional dimensions.
"-1" /* rows per page */
As described in the documentation – this is a function to allow for a limiter of queries. If it’s a positive number only the number of rows listed will be extracted from the source and mapped. Useful only if you’re concerned about an out-of-control MDX query. Setting it to -1 will extract and load everything (and is obviously recommended
"e:\\Ifthisworkscameronowespeterabeer.log"; /* log file, can be empty */
A log file that will be generated on the essbase server. Incredibly useful for troubleshooting as it actually shows the processes and the outputs!
2015-08-28 10:09 > Used Memory (Mb):8 Free Memory (Mb):237 Total Memory (Mb):245 Max Memory (Mb):245
Query:SELECT {[Scenario].[Actual]}on columns,crossjoin(crossjoin(crossjoin({[Year].Levels(0).Members},{[Measures].[Sales]}),{[Market].[Market]}),{[Product].[Product]}) on rows FROM Hybrid.Hybrid
User:admin
Source:Hybrid/Hybrid
Target:Hybrid/Hybrid
Rows Per Page:-1
Signed in User:admin
Grid Size: Rows(13) Cols(5)
Actual
Jan Sales Driver No Market No Product 134590.0
Feb Sales Driver No Market No Product 130233.0
Mar Sales Driver No Market No Product 132922.0
Apr Sales Driver No Market No Product 128346.0
May Sales Driver No Market No Product 131673.0
Jun Sales Driver No Market No Product 135427.0
Jul Sales Driver No Market No Product 132977.0
Aug Sales Driver No Market No Product 137744.0
Sep Sales Driver No Market No Product 122903.0
Oct Sales Driver No Market No Product 127760.0
Nov Sales Driver No Market No Product 127837.0
Dec Sales Driver No Market No Product 130086.0
Once you’ve got it working this should be set to missing for performance reasons.
So how’s it perform then?
On the topic of performance – how fast is it? On a fully loaded (admittedly a customised/hybridefied Sample.Basic) database of 9 dimensions with the biggest being 14k and 9k members and with the data logging turned on:
Total Calc Elapsed Time for [Test.csc] : [0.263] seconds
Without the data logging:
Total Calc Elapsed Time for [Test.csc] : [0.061] seconds
Given that you could put this step immediately after a data load (it’s Hybrid sonny – ain’t no aggregation time) that’s pretty ridiculous in an absolutely awesome way.
What have we learnt today boys and girls?
Four key takeaways from this:
- MDX is an awesome language – as long as you can work out a method of parsing it, or just not needing to.
- Hybrid is going to take some getting used to in order to come up new methods of calculating things
- Sree and his team in the Oracle Calculation Manager group are coming up with some fantastic CDFs to drive a whole stack of functionality going forward. Definitely worth looking at some of them if you haven’t already.
- Cameron really needs to step up his game ‘cos he ought to have figured this out on his own.
Bonus For Timf
So after Cameron posted this up, TimF in the comments section pleaded asked if anyone had tested it sourcing from an ASO cube.
So yes – for Tim – I tested it. And it’s awesome.
//ESS_LOCALE English_UnitedStates.Latin1@Binary
RUNJAVA com.hyperion.calcmgr.common.cdf.MDXDataCopy
"whynotzoidberglngthAES===" /* key */
"EmUPiLFGiSufeNHa1zQ43smFThCX4BL1HFfX/IjDJFVhNePVyAZucdLI93fH0ZpA" /*user*/
"FiYG8Gk4GuafZ3PJqCpcpfLsXqQyEASsjGORAYjhTW0hBez9jTksnPlZT65iwYPT" /* password */
"ASO1" /* from application */
"ASO1" /* from database */
"HybBSO" /* to application */
"Plan3" /* to database */
"Crossjoin(crossjoin({[Account].[Test_Acc]},{[Version].[Final]}),{[Scenario].[Rolling]})"/* MDX that defines the column members */
"crossjoin(crossjoin({[Years].Levels(0).Members},{[Entity].Levels(0).Members}),{[Period].Levels(0).Members})" /* MDX that defines the row members */
"" /* source member mappings, can be empty */
"" /* target member mappings, can be empty */
"" /* Target POV columns, members from dimensions that do not exist on the source*/
"-1" /* rows per page */
"c:\\ifthisworkstimowesmeabeer.log"; /* log file , can be empty */
Finally we’ve got an “xref” function for ASO to BSO data movements. And it’s lightning quick, creates the blocks on the way in, and could be variable driven (see Celvin’s recent posts)
But what if you’ve got more dimensions in ASO
//ESS_LOCALE English_UnitedStates.Latin1@Binary
RUNJAVA com.hyperion.calcmgr.common.cdf.MDXDataCopy
"whynotzoidberglngthAES===" /* key */
"EmUPiLFGiSufeNHa1zQ43smFThCX4BL1HFfX/IjDJFVhNePVyAZucdLI93fH0ZpA" /*user*/
"FiYG8Gk4GuafZ3PJqCpcpfLsXqQyEASsjGORAYjhTW0hBez9jTksnPlZT65iwYPT" /* password */
"ASO1" /* from application */
"ASO1" /* from database */
"RMITPOC" /* to application */
"Plan3" /* to database */
"Crossjoin(crossjoin(crossjoin({[Account].[Test_Acc]},{[Version].[Final]}),{[Scenario].[Rolling]}),{[New_ASO_Dim].[Test_New_ASO_Dim_Member]})"/* MDX that defines the column members */
"crossjoin(crossjoin({[Years].Levels(0).Members},{[Entity].Levels(0).Members}),{[Period].Levels(0).Members})" /* MDX that defines the row members */
"Test_New_ASO_Dim_Member" /* source member mappings, can be empty */
"" /* target member mappings, can be empty, */
"" /* Target POV columns, members from dimensions that do not exist on the source*/
"-1" /* rows per page */
"c:\\ifthisworkstimowesmeabeer.log"; /* log file , can be empty */
Catch you all later!
Cheers
Pete