Using Lead MDX function for dimensions other than Time - Surprises in Essbase Part VIII

October 18th, that was the day I completed 10 years in IT. Never thought I'll last this long :).
Never thought that someone can stick with me for 5 years (my wife), just kidding.

Life is about all those little surprises isn't it :), but what about the EPM ones.

I agree with Tim German - "They’re opportunities.  Infuriating, head-pounding, driving-to-drink opportunities."

I was working on an ASO cube where I had to create a 27 month rolling view.

I got the MDX formula and was looking at the results and to my surprise none of the values are correct.


During troubleshooting I thought of checking whether I can jump years and updated my formula to the one above. Guess what it is not working!!!!!, so there lies our problem.

Note: You can use ([Base],[Years].CurrentMember.Lead(1)), I just wanted to see if LEVEL will work or not.

Next step was to see whether I can move in time, and lead on time dimension was working fine. I started thinking "Does Lead only work on Time dimension?"

Let's look at what TechRef says about Lead - nothing about just on Time dimension, so it must work with any other dimension!!!

If you look carefully there is a note about "Multiple Hierarchies Enabled"
When multiple hierarchies are enabled, this function returns NULL when the source member is in one hierarchy and the result member belongs to a different hierarchy.
This is my Year dimension.


My result and source are in the same hierarchy!!!, so that is not the issue.

I tried to look at query tracking and found this.


By the way MaxL is executed from MDX Editor :), it tells us that there were no "Stored Queries" executed - Lead didn't work.

Just for kicks, I thought of making Years as Dynamic and guess what it started working!!!!

Changed Years to Stored (by removing the formula member) it works there too.

So Lead (maybe the rest of the MDX like Lag, NextMember, PrevMember) doesn't work with multiple hierarchies. Now could that be true, well yes and no :)

I started looking at all possible explanations and found one hidden in the DBAG - Look under Hierarchies in Aggregate Storage Applications, Databases, and Outlines

It says
The first hierarchy in a multiple hierarchies enabled dimension must be a stored hierarchy.
I updated my Years dimension as below.


It works!!!!

To summarize this surprise :)

Lead/NextMember works with "Multiple Hierarchies Enabled" if and only if the Parent is stored/dynamic hierarchy.

Selecting multiple data file and rules file in EAS

Learning never stops :)

I'm sure that many of you are already aware of the trick that I'm going to share with you.

How I can select multiple data files and rules files in EAS?

Well I always used to add multiple rows using the "Insert" button and then selected each data file and rule file. (Now that I know this trick, kind of feels dumb (sigh))

I've 6 data files to be loaded to a cube and they use 3 load rules. I can perform the data with just 7 button clicks (Yes including the "OK" button).

How many of you are aware that Ctrl and Shift button work when selecting rules and data files? (well I was not)


Navigate to the folder where data files are kept, use "Shift"/"Ctrl" to select multiple files.


It adds each data files in separate row.

Now I've Actual, Budget and Forecast rule files and I can do the same multiple selection with rule files.

If you look carefully, I've selected the rows (Act.txt, Bud.txt and Fcst.txt). My rule files are also in the same order.


Now that was my 4th click.

After selection


I did perform the same steps with next set.

End result


7th click will start the load :)

The order (data files and rule files) must match.

Isn't it cool? As I said learning never stops :)

Calculation Manager 11.1.2.3.502 New Custom Functions Part I


Many of you know this already that Calc Manager 11.1.2.3.502 patch was released, however what you don't know and may not be able to find out (well eventually you'll) is about those Custom Functions in Calc Manager.

There are 60 CDF functions included in this patch (opposed to the 16 in .500 release)!!!! that is a huge number.

It is quite a huge task to talk about all those functions, however I'll try to cover some of the interesting ones.

Some of the time functions and string to number that I built as a CDF are now there in Calc Manager.

I guess the Calc Manager team is one of best teams that I had a chance to work with. They are ready to hear your feedback, ready to implement those and always look for opportunities to improve the tool.

3 kudos to Sree Menon and Kim Reeve and the Calc Manager team.

The function that I'm going to talk about is an interesting one. I did blog about this earlier, and was not sure whether it'll be removed from the next release. Calc Manager team was able to see the value and they made it better.

To run the new MDX functions (there are two @CalcMgrMDXDataCopy and @CalcMgrMDXExport), you'll have to encrypt the username and password using a key. Have a look here to see how it is done.

CalcMgrMDXDataCopy

Now what is so exciting about this function. Well think about updating an ASO reporting cube from a Planning application in real time.

Yes you can do it by implementing custom functions, custom applications and what not, however What about a single function which extracts data and then loads that for you :)

You don't believe me, let's see it.



That is a rule which uses RUNJAVA command. (I like using it and yes you can use the function which does a syntax check for you ;))

RUNJAVA com.hyperion.calcmgr.common.cdf.MDXDataCopy 
"fhe34i5hsdudoi3fhkjhds" /* key */ 
"0K44GgAMKPRjHY1SqF6OcK38x7BWUbTzOfT4F28sPRncN0qDqGGE7Oc3NFolAPw4" /*user*/ 
"KsivsXGrvXKti62/+vuK3b42IkPjNZdntYyrASYV8/4EgOXSw/uWR14jjK8Y4yWF" /* password */ 
"Vision" /* from application */ 
"Plan1" /* from database */ 
"VsionASO" /* to application */ 
"Vision" /* to database */ 
"NON EMPTY(crossjoin(crossjoin(crossjoin(crossjoin(crossjoin({DESCENDANTS([YearTotal],[Period].Levels(0))},{[Plan]}),{[FY14]}),{[Working]}),{[000]}),{[P_000]}))" /* MDX that defines the column members */ 
"NON EMPTY({Children([Capital Expenditures]), Children([7300])})" /* MDX that defines the row members */ 
"" /* source member mappings, can be empty */ 
"" /* target member mappings, can be empty */ 
"No_Country" /* Target POV columns, members from dimensions that do not exist on the source*/
 "-1" /* rows per page */ 
"c:\\Temp\\CapExpenditure.log"; /* log file , can be empty */

Everything is self explanatory except source/target member mappings and rows per page.

Source/Target member mapping - This can be used when you have different member names let's say you source has Apr and Target has April then you can add Apr in source and April in target and the function will replace it for you. 

It is similar to the report script RENAME function.

Rows per page - If you define -1 it'll load the entire output, if you says 1000 then it'll only load 1000 rows.

I can now setup a form which uses this business rule


Now when a planner saves the form, tadaaa your reporting application will be updated in real time.



That's my ASO pull :)

Now issues (oh yeah there are some).

You cannot use Run Time prompts in Planning (if you are running this from Essbase you can use RTPs), MDX is treated as a string and Planning doesn't replace RTPs if they come as a string.

Since this is done from a Webform, you'll have to hard code your column and row into the MDX. (Once the issue with RTP is fixed you can use your user variable here. Thanks Christian, that was an awesome hack)

Things to keep in mind

You'll have to check the log file and see if function executed successfully or not, Planning web and Essbase logs will say that it was successful :(

If you are thinking about implementing this do not use NON EMPTY statements in your MDX query, what if the Planner realized that an entry was wrong and decides to clear it. Yes it might take time to run the query without NON EMPTY, however I'll take correct results over wrong one :)

If you are using Function (not RUNJAVA) selection it'll still show you the option to select member in MDX row and column axis, do not use that option. Also you'll have to make sure that the strings are enclosed in "s. It is a simple fix which you can do. I know you are smart :)

Improvements

Good logging mechanism - Planning/Essbase can look at the execution and tell you that it failed.

I would like to see if we can apply a where/page clause in the functions, so that I'm not pulling those members which can essentially my POV/Page in Planning.

CalcMgrMDXExport

For years Essbase folks were looking for a way to get a clean export using MDX and finally we've something from Oracle.


RUNJAVA com.hyperion.calcmgr.common.cdf.MDXExport
            "fhe34i5hsdudoi3fhkjhds"/* key */
            "0K44GgAMKPRjHY1SqF6OcK38x7BWUbTzOfT4F28sPRncN0qDqGGE7Oc3NFolAPw4" /* user */
            "KsivsXGrvXKti62/+vuK3b42IkPjNZdntYyrASYV8/4EgOXSw/uWR14jjK8Y4yWF" /* password */
"C:\\Temp\\MDXExport.txt" /* export file */
            "ASOSamp" /* from application */
            "Sample"  /* from database */
            "Azgard" /* server name */
"{[{rtp_Promo}]}" /* column axis */
"NON EMPTY Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin({[Units]},{[Curr Year]}),{DESCENDANTS([MTD],[Time].Levels(0))}),{[Sale]}),{[Cash]}),[Age].Levels(0).members),[Income Level].Levels(0).members),{DESCENDANTS([All Merchandise],[Products].Levels(0))}),{[Online]}),{DESCENDANTS([North East],[Geography].Levels(0))})" /* row axis */
            "|" /* seperator */
            "0" /* #Missing replacement */
            "false" /* use unique name, if you have duplicate names enabled*/
            "false" /* use alias name */
    "true" /* suppress zeros */
    "true"; /* row headers, similar to DataExportDimHeader ON + DataExportColHeader */

Again a self explanatory function.

I really like the option of using unique name and alias(keep in mind that DATAEXPORT cannot export alias)

I can use runtimeprompts here, because this is an Essbase Calc Manager rule and Calc Manager launches this (I told you Calc Manager DEV team is awesome)


Output


Now that is a really neat output and you can change your MDX query to change the position of the dimensions.

Things to keep in mind

The issue with this one is if your query is wrong, if Essbase cannot allocate memory, if it exceeds 2^64 limit (well that is a lot of ifs), the script will still tell you that it finished successfully. It won't log the output to the file or to the Essbase app log. Only way to find out is where the file is created/updated with new timestamp.

I always use MDX script editor to see whether my MDX query is working or not, so that way you can make sure everything is working. It is using Grid API and I guess will have some performance impact. Well that can be another blog :)

If you are using Function (not RUNJAVA) selection, do not use Member selection in MDX query. You've to type the MDX query there.

Improvements

A good logging mechanism, so see if the script was really successful or not.
A where/Page clause condition where I can specify my members - this way I can reduce the amount of time spend to write the output. (remember the less to write the faster it'll be)

These functions are really great (considering the fact that it is version 1). Hope to see those improvements coming in.



FIXPARALLEL and EMPTYMEMBERSET - Surprises in Essbase Part VIII

There are some issues which you just ignore and there are some where you tell the world that there is an issue.

Well then there is another set where you've to prove that you are not wrong :)

I really like the way Oracle adds new functionality and I did like FIXPARALLEL.

If you don't know what it is then have a look at Glenn's and Cameron's blog posts.

This post is about a big surprise with FIXPARALLEL!!!!! (Oh FIXPARALLEL is not available in Calc Manager 11.1.2.3.500)

I was writing an allocation script (in BSO) and thought of using FIXPARALLEL to see how much performance gain is there. I was amazed by the results (don't know the exact numbers, so don't want to quote it).

I started looking at the results and found that there is an issue with it. The calc did finish however nothing was calculated!!!!!!!

I was running like a chicken without a head. (Well I'm not Mike the headless chicken and I cannot live without my head ;))

I was thinking about all possibilities (I can think without head ;)) - Block creation, everything you can think about when data is wiped out.

Now let's see what really happened.

I modified Sample Basic to replicate the issue I had and these are the changes I made.

Adjustments made to Sample Basic to reflect our issue.

3 new members added to Measures as Children of Ratios

  • MajorRatio
  • SmallRatio
  • NewRatio
2 new UDAs added to Market

  • Direct – to all members under East
  • New Market – to all members under Central  
1 new member added to Market
  • NoMarket
This is how it looks like


Below given is the screenshot of the calc I used.


Now let's look at what I expect to see


The results


What just happened there, well every member in Market dimension got multiplied with NewRatio.


No pun intended

Oh don't wonder Why 
The answer is right there in the log.


It says that FIX statement evaluates to an empty set and FIX STATEMENT is ignored.

Then you'll see three statements which says "Calculating [ Scenario(Budget)] with fixed members". Why 3? It should have only done 2.

Look at the 3rd statement (there is no Market information there and that is our EMPTYSET.

Now you know what happened here. Now what if NewRatio was missing, (this is what happened earlier in realworld - where you'll think that it is a block creation or what not) yes you guessed it right all Budget data will be gone. pufffff!!!!!!

Now that I know that I've an EMPTYSET I wrote a different calc to prove that it can still work.


I hope that it get fixed soon.

Shared Member Drill down - Surprises in Essbase Part VII

This blog post is a surprise and I'm surprised why this change is not documented anywhere.

What is this about? Well I guess you'll have to read till the end :)

Conversion is a big deal. Oh no not the religious conversion :) I'm talking about converting Excel Add-in users to Smart View. (It was hard for me to use Smart View in the beginning)

Me and my friend, Kishore were discussing about this conversion where he had a big number of users who are still using EXCEL Add-in and like many of the discussions floating on web (on Smart View and Add-in) we also had a fair deal of - Smart View is better than Add-in and vice versa.

There were many questions that you'll get from the community where they find the change difficult and different.

So there was this question where he was wondering whether I knew about an option in Smart View to drill down on a shared member (parent member which is shared in an alternate hierarchy)

He said he can do that in EXCEL Add-in.

I was like "No you cannot do that in Add-in, it stops the drill down if the alternate hierarchy parent is there". I didn't say that aloud.

I'm like I don't know how to do that in Add-in and you are asking me "How can I still down on a shared member in Smart View?"

Even though I didn't know how to do that in Add-in I did give him the idea on how you can make it work in Smart View.

If you've not used it - There is an option in Smart View which will allow you to zoom in on formulas (The formula can be a member equation or a consolidation to the parent). 

This method won't give the correct results if there is a child which is ignored or never consolidating.

Later he was generous enough to show me how that works in EXCEL Add-in (I know you already know this :) )

You can use Free Form mode in EXCEL Add-in to perform the drill down on a shared member. The only place I've used Free Form mode is to run report scripts.

I did like that option in EXCEL Add-in and I thought of finding an option in Smart View which can really work.

I was trying to replicate this in Sample Basic (I had to modify it to add a alternate hierarchy) and this is how my Market dimension looks like.













What happens when you drill down on East, when you've Northeastern in the grid?








Yes, you guessed it right. Nothing it won't drill down.

No I don't have duplicate members in my database. This was the change that got introduced in 11.1.2.3.000

From the new features guide

Unique Names for Shared Members
In grid clients (for example, Oracle Hyperion Smart View for Office), you can reference shared members as unique from their base members by typing them with a qualified name (for example, [Parent].[Child]). Shared members can be referenced with qualified names even if you have not set the outline to enable duplicate member names.

EXCEL Add-in User guide on drill operations on Shared members

Product


   100
      150 (regular member)
         100-10
         100-20


   Test1
      150 (shared member)


Note that the regular member 150 contains children; the shared member 150 does not. Drilling up and down on 150 gives different results.
  • Drilling down on 150 returns nothing if interpreted as the shared member; or, return 100 10 and 100-20 if interpreted as the regular member.
  • Drilling up on 150 returns Test1 if interpreted as the shared member; or, return 100 if interpreted as the regular member.
The proximity of the shared member to the regular member gives different results when drilling down:
  • If Test1 is on the worksheet and very close to 150, Essbase Server understands that 150 is the shared member and drilling down does not result in the children, 100-10 and 100-20. 
  • If Test1 is not on the worksheet or members are in between them, Essbase Server understands 150 to be the regular member so drilling down results in the children 100-10 and 100-20.
This was what Kishore was trying to solve in Smart View (Use Zoom in - formulas on East)














As I said I was trying to find a perfect answer and tried this on 11.1.2.3.500 Essbase server. As I was playing around I accidentally double clicked on East.

Guess what (see the difference in the member names - East is East in 11.1.2.3.500), you can drill down on a Shared Member in 11.1.2.3.500.














I was first thinking that it could be a Smart View/Excel Add-in version issue (Kishore had an older and I'm using the latest).

I looked at my Software inventory ;) and was able to get an older version of Add-in and Smart View.

I could drill down on those versions also, which made me look at the Essbase version.

It is thus proven that on 11.1.2.3.000 I cannot drill down on a shared parent. So where did it all start, I was lucky that there are not many patches that got released between 11.1.2.3.000 and .500

I patched it to 11.1.2.3.001, just because this patch fixed the issue with the way Shared members were showing up in Smart View (shared shows up as unique member) and see whether this behavior started from that patch and no one noticed it. You can read more about the issue here.

Retrieve the same sheet in the patched version and zoom in on East voila. The long ask for drill on Shared members is here.

It is confirmed that from 11.1.2.3.001 onwards you can now drill down on a shared member.

I'm still not sure why this is not showing on any of the new features or why the change in behavior is not documented anywhere. Everyone + dog always asked for this feature and Oracle didn't bother to tell us that it is here. (or they don't know about it ;))

Views discussed in this blog are my personal views.
They do not represent the view of organizations, businesses or institutions that I'm part of.