Essbase Studio issue and work around

I was working with data loads in Essbase Studio and ran into some odd behavior (OK working with Studio IS odd behavior). I had a data load that kept failing with a data item found before all members error. . I took the generated SQL and ran it through a SQL tool and it returned properly. SO what could it be. From Studio 11.1.1.1 I remembered you could not edit dimension or load rule files, so the first thing I did was try to open the data load rule (I never learn). To my surprise it opened. For fun I tried dimension build rules and they fail with a message that you can't open file created in Studio, oh well). I went and looked at the SQL in the rule and it was the same as the SQL I pulled from the Essbase Model. Since the SQL should work, I tried to retrieve it into my load rule to see what the data looked like there. Below is what I got:

Notice the two columns. It turns out these were manually created dimensions that I had set the default bindings to set. In my SQL (Oracle based) The two columns looked like:

'MTD' as View,

'Actual' as Scenario.

In one case (don't ask we why) it took MTD and only returned the "M" for Actual it returned nothing! Huh??? To fix this I got creative. remembering back to old old old problems with certain data types from SQL, I modified the code to look like

Cast('MTD' as Varchar2(10)) as View,

Cast('Actual' as Varchar2(25)) as Scenario



And it worked like a champ. There is something in how it is handling the literals that does not work right.



I have spent about two months working with Oracle support showing them the issue (Three different web conferences with three different people and sent them my code) but they have not been able to recreate the issue on the support environment. So after a lot of time, I gave up and closed the ticket. This means we won't have a fix for what I consider a bug, but support doesn't recognize, but at least now when you run into it, you know how to work around it.


There is a lot of potential in Essbase Studio and I look forward to the 11.1.2 release where a lot is supposed to be fixed. When 11.1.2 comes out, I'll look it over and let you all know all the cool stuff it will do.

One item that will not be fixed (that I know of) is with Drill-through. There is an issue I found where if you do not have access to an intersection of data from Smart View, you can still drill-through to detail. I don't know if this is a Smart View issue or a Studio Drill-through issue, but The Smart View team has taken ownership of the issue (Thank you Matt) and is looking for a fix to this. I commend the Smart View team for their consistent willingness to listen to problems and react.

Quiz 3 Answer

Amarnath had the quiz answer about 99% there.
Note, I moved hie paragrapgs around a little to make it more meaningful but his reply was:

Since boolean function results in 0 or 1, if the current member has UDA as "Major Market" then the boolean function will result in 1, if not it will result in 0 and dividing by 0 results in #Missing. By this method it is not required to use an IF statement.


FIX(@levmbrs("Year",0),"Actual",@Relative("All Products",0),@Relative("Market",0))

"Sales" (
"Major Market Sales"->"Market"->"Product" = "Major Market Sales"->"Market"->"Product" + "Sales"/@ISUDA(Market,"Major Market");
"Small Market Sales"->"Market"->"Product" = "Small Market Sales"->"Market"->"Product" + "Sales"/@ISUDA(Market,"Small Market");

)
ENDFIX


The problem with his answer is he needs to do a fix before his existing fix to clear out the values as he is writing to an upper level intersection and the code would not be rerunnable (is that a word?). It is nice that he tried to use the last quiz's answer as part of his response, but I was not bright enough to think about having that happen. It shows he actually read my solution :)

Clementine's modified version of his code is:

FIX(@levmbrs("Year",0),"Actual",@Relative("All Products",0),@Relative("Market",0))

"Sales" (
"Major Market Sales" = "Sales"/@ISUDA(Market,"Major Market");
"Small Market Sales= "Sales"/@ISUDA(Market,"Small Market");

)
ENDFIX

AGG(PRODUCTS,MARKET);


In this case, Clementine would have to have the agg statement but gets the ability to drill into Market to see what Markets made up each of the calculated members.

If Clementine was not so picky about Fix statements she could have done a fix around the Agg to only do the Aggs in the Actualscenario, but that would have broken her rule.(And added efficiency.). If the requirement was not to use the UDA, you could have also used the attribute dimension and would not have needed to calculate at all or you could replace the @ISUDA with @ISATTRIBUTE. There are a number of other solutions available and Jared had the beginnings of one, but his solution had a few problems.But is was a really good try. A little modification and it would most likely work. If you have another solution, please post it, In Calc scripts, like many other things in Essbase, there is more than one way to accomplish things.

Only two people tried to answer the quiz. Do I need to make the easier or harder, keep them as they are, forget about them or just post them as tips instead of quizes so you don't have to think too hard?