Endeca Journey begins

Let's just say that I got courageous :) or someone convinced me to look beyond Essbase and Planning.

Now don't assume that you are going to see me talking all about Big Data and Data discovery, This is a post which I thought of sharing about my experience while playing with the installation and the issues I found while playing with it. (I could be because of my ignorance, I'm just two days old and there is a lot to learn)

You've to follow a certain sequence for installing Endeca Server, Studio and ETL server. This is well described in the installation guides, so I'm not going to cover that.

After following the instructions I installed Endeca Server, ETL Designer, ETL Server, and was happy about it. I started Endeca Server service and to add to the frustration it didn't start. (Here it comes)

A quick note - None of these are installed as a Windows service, I had to write the scripts to install them as Windows services. If the installer can handle this I would take that any time of the day.

Endeca Server issue

Endeca Server log file just keep growing every minute (or seconds) and the below given message is repeatdly recorded.



Mine is not a clustered  environment, and it was still trying to open the port associated with the cluster. After playing with EndecaServer.properties file I gave up and searched for the error in OTN and found that someone else had the exact same issue and the fix was to uninstall and re-install. I'm not a fan of fixing issues with uninstall and re-install, however I had a doubt that this could be something related to the install sequence.

I did uninstall and re-install Endeca server and guess what that solved the issue. 1 issue down.

Endeca Studio issue

Yeahhhhh, I got into Endeca studio and was customizing admin@oracle.com user (this is the default admin user that gets created). I did change the password and the email address. I continued with rest of the installation and guess what I forgot the email address I gave to admin user :(

For the life of me I cannot figure out what email address I gave. I do remember the password. If you end up in such a situation; here is what you can do.

Navigate to Endeca Studio domain (my domain location is given below)

E:\Oracle\Middleware\user_projects\domains\AzgardEndecaStudio\eid\studio\data\hsql

There is a file called lportal.script, open this file with a text editor and navigate to the section where it says INSERT INTO USER_VALUES



You can see the username, email address and other details here. 2nd issue down :)

Endeca ETL Server issue

It's raining issues.......... :(

After installing ETL server, the license file needs to be updated. In order to do this you must create a property file (cloverServer.properties)and add this to the startWeblogic.cmd. I did add this as per install guide.

Tried to start the server and got the below given error.


Demi God Google has an answer to this.

It is not filed against Endeca. However if you see that error, it just means that something is wrong. Well at-least this is similar to Cannot send data, Receive data error in Essbase ;)

I went back and looked at the properties file and realized the mistake. I was using \ instead of /.
\ is considered as an escape character.

File corrected and started again. This time it complaining about DB connection!!!!

java.sql.SQLException: Cannot get working DB connection!

What!!! It never asked me to create one and it is complaining about a working DB connection. This one drove me crazy, however the answer was there in DataIntegratorServer.pdf

If you look under Examples of DB Connection Configuration, it says if you are using a properties file you should add the following entry. 

jdbc.driverClassName=org.apache.derby.jdbc.EmbeddedDriver
jdbc.url=jdbc:derby:databases/cloverDb;create=true
jdbc.username=clover
jdbc.password=clover
jdbc.dialect=org.hibernate.dialect.DerbyDialect

Well that should be there in the install guide.

Look under E:\Oracle\Middleware\user_projects\domains\AzgardEndecaETLServer\databases and use the name of the Derby database (instead of cloverDb)created there. (This one is created by default)

Issues 3 and 4 down :)

Endeca IAS issue

If you are installing Integrator Acquisition System (IAS) then you'll see that ias-setup.bat went away after sometime, I was under an impression that it configured the domain. However it didn't. When I tried to start IAS using startWeblogic.cmd it complained about the usage of wrong garbage collection.

Unknown option or illegal argument: -XX:+UseParallelGC.
Please check for incorrect spelling or review documentation of startup options.

ias-setup.bat uses jrockit as the JDK and for jrockit you must use -Xgc:genpar instead of XX:+UseParallelOldGC

This can be changed in setIasEnv.cmd under E:\Oracle\Middleware\Endeca\IAS\3.1.0\bin

Issue 5 down.

Success!!!! I got my first ETL graph created, Sample data loaded to a data domain.

Hope it helps someone

Essbase MDX Optimization NONEMPTYTUPLE - Surprises in Essbase Part XI

This blog post had 3 different titles and ended up as a Surprise :)

I was working at an engagement, where I had to load data from Source to Target - we do this all the time.

What was different was how some products were graded. The data coming from source was missing some key dimensions.

Let's say that data from source has Dimension A and Dimension B combinations. However for the calculation logic (MDX) to work it should sit at Dimension ADimension BDimension CDimension D and Dimension E. Now to achieve this I created a flagging member.

I can then move the data when it matches A and B from flag.

I replicated the same in ASOSamp and the above said logic can be achieved as depicted below.

Load Data


Above given is the format of the source data.

Flag


Above given is the format of the flag, you can see that it has more detail

Column G and H matches Column B and Column C of data load.

I can create a Member with MDX formula and it'll perform the seeding for me :) Ta da.

NONEMPTYTUPLE ([PCT_Flag],[Base])
([Graded_PCT],[&CurrYr],[Base],[No Sale],[Cash],[No Promotion],[Under 20,000],[No Store],[NA Age],[&CurrMth])

 I don't need to check whether Data is missing for a combination, just use the NONEMPTY clauses for Optimization. This is explained in Techref under MDX Optimization Properties.

With a big grin and boasting about writing a seeding code with a one liner, I retrieved the results.


Did it work? No it didn't. I was expecting 10 (Row 3) to appear only once at Jan, Row 4 to appear only for Feb (Flagging member drives this)

I thought of using NONEMPTMEMBER instead of NONEMPTYTUPLE (only difference is one uses only members and other a tuple)

NONEMPTYMEMBER [PCT_Flag]
([Graded_PCT],[&CurrYr],[Base],[No Sale],[Cash],[No Promotion],[Under 20,000],[No Store],[NA Age],[&CurrClosedMth])

Results


Well that was the result I was looking for. I knew that it is a "surprise" and thought of checking with Cameron, Dan and Tim. They are/were of the opinion that they both should behave the same. I went on a wild goose chase of what was happening to NONEMPTYTUPLE.

It was behaving as if NONEMPTYTUPLE has no effect.

Is it because I'm performing a data assignment? Or Is NONEMPTYTUPLE having no effect?

Well I know that there is some effect adding NONEMPTYTUPLE (is was giving a performance boost for my procedural calcs).

To test the first question, I created a dummy member (the calc doesn't make any sense, however it gave answers :))

NONEMPTYTUPLE([Base],[&CurrYr])
([IL],[Base],[Units])-([MT],[Base],[Units])

Results


What are those number, they don't make any sense. Well they do what the calc did was to get this tuple ([IL],[Base],[Jan]) irrespective of which month you are in and subtract from MT.

I was under impression that NONEMPTYTUPLE is looking at the first nonmissing (Jan in my case) and then running with that value. To prove this I cleared Jan values from IL and MT - well the calc won't even run. It posted #Missing everywhere.

So NONEMPTYTUPLE is looking at Jan's value and using that for the calculation. (atleast in my case)

How can I make this work? I can use NONEMPTYMEMBER and it'll work fine (we saw this early).

Or the below MDX

NONEMPTYTUPLE([Base],[&CurrYr],[IL])
([IL],[Base],[Units])-([MT],[Base],[Units])

Yes it works. Why I don't know :)


I've tested this on 11.1.2.3.500 and 11.1.2.3.501 and it behaves the same way.

Conclusion - Do not use NONEMTPTYTUPLE for optimization (at-least for now)

Hope you enjoyed the madness :)

Set Next Month, Previous Month using Current Month Substitution Variable

This is an interesting one, not directly related to EPM however a useful one.

I know many of you have used different techniques for setting Substitution variables.

  1. Getting them from a database
  2. Updating using an EXCEL macro
  3. Using System date
I have done couple of those and there was one interesting one where I gave the user the capability to load any month they wish using a batch file.

So this batch file, a user can define the month they want to run and then using that parameter it'll set a data in the datastore and views will be automatically build according this to month-year combination.

It then sets Essbase substitution variables using the variable set in the datastore.

While doing that I had to derive the number for the month that I'm loading so that I can populate a field. I'm not going to talk about that method - I'm talking about a similar one.

Business would like to set up a variable (not a bunch of them) which will tell them that Hyperion Actuals are closed. They will control this, and then build rest of the substitution variables based on that one.

So here is the batch script which does that.

How does it work

Run a MaxL which will display a Application substitution variable and spool the result to a file.

Use a for loop to get the value of the Sub Var. 3rd token is the value, since mine is an application sub var.

application         database            variable            value  

For loop is on the output of findstr command and we are finding the name of the sub var, output will contain two lines (one the maxl display command and the second with the value), skip 1 line and you get a single line for the for loop

Now comes the magic of Batch script, many people think that it is not powerful enough (I do agree with that on some, not it is not always).

I've mapped each month to their number.

Suppose HYPACTMOYR is Sep-14 then HYPACTMOYRNUM will be set to 9;Oct-10;Nov-11;Dec-12, using the rem command it'll delete everything after 9(; is the delimiter)

So now HYPACTMOYRNUM is 9 and I can now I can add one number.

Reverse map from number to month. RPTGMOYR will be set to Oct;11-Nov;12-Dec, using rem command it'll delete everything after Oct

Next step is to add YY, which can be derived from HYPACTMOYR (Start from 4th character and give 3 characters "-14")

You can do set /a RPTGMOYRNUM=%HYPACTMOYRNUM% -1 to get Previous Month

Hope you enjoy it ;)

Calculation Manager 11.1.2.3.502 New Custom Functions Part II - Date functions

Published a TechTips on new Date functions in Calc Manager on ODTUG NewsLetter. null

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.

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.