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 ;))

Data Load Rules - Use of data load field names

This post is a proof of my laziness. I'm just kidding. You know that I'm not lazy :)

A friend of mine came up with a question, "How can I create a dynamic load rule". Well that's not the exact question, however let's pretend for sometime that it was and how we can solve it.(His question was to how can I export data from BSO and load into ASO).

I do know that there is(are) similar thread(s) in Oracle forums, and the obvious answer is to create a DATAEXPORT calc script.

Get Data out from Source and then use a load rule to Import it to the Target.

The question many of us had/have is how is it determining the Rows and Columns. If you don't specify DataExportColHeaderEssbase makes the first Dense dimension as Column Dimension. 

If you look at the explanation, you'll find that it needs a dense dimension. It is easier to use a static dimension like period as the Column Dimension as it won't change.

Static Dimension will be one where you are not adding members to it and the DATAEXPORT calc script is not referring to a member set which is changing.

For example if I've @RELATIVE(YearTotal,0) in my calc script, I still know that it is 12/13 members starting from BegBalance/Jan to Dec.

What if in my cube Period is not dense and I don't have a static Dimension to select.

Just for the context of this blog post think that Sample Basic has only one dense dimension - Measures and has a similar structure.


If I create a calc script similar to the one given below,

I'll get Measures as Column. 

Let's imagine that next month I did add more members to the Parent "Profit".


My data file will change and I'll have to change my load rule to reflect those changes.

Yes I can do that, but can I make it Dynamic so that I don't need to edit the load rule (or make sure that it is correct) all the time.

I know that there is an option in Load Rule where I can mention the record which contains the field names - "Record containing data load filed names".

Oh yeah, we know that one, but what are you going to do with that?


Let's look at the load rule. Well it's just like any other rule.


Now let's open the data file.


See how it moved line 1 as Field Names, however it is not correct, because those my Data fields.

How can I get the Row Dimensions to show up in export file? (see we asked you earlier, "Where are you going with that option?" ;) )

If you look at the SET DATAEXPORTOPTIONS, there is an option to get the dimension names as header - DataExportDimHeader ON;

Let's add that to our script and see how that works.


Hmm that's interesting I got that one, however I lost the column field names.

How can I get the headers come as a single line?

Not to worry, here come the friendly neighborhood Spiderman - sorry sed command :)

sed -i ":a;N;$!ba;s/\n/\t/;s/\"Measures\"\t//" C:\Temp\100-10.txt

If you notice Dimension name Measures also get displayed when we use DataExportDimHeader, we don't need that because we've the member names from Measures in Columns. What the sed command does is it moves 2nd line to 1st and then replaces Measures with a tab.

You can replace "Measures" with your dimension name and if the delimiter is , then change \t to ,

sed -i ":a;N;$!ba;s/\n/,/;s/\"Measures\",//" C:\Temp\100-10.txt

Open the data file in the load rule again.


Tada, A dynamic load rule!!!!

For the windows users, you can download sed editor from here. (Download both files)

Note: If you open the rule without the data file it'll not show the field names :)

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