Death of a thousand cuts

It is no secret

I have no love for Essbase Load Rules.  Mostly because I have comprehensively, completely, and utterly tied myself into knots using them.  And I should be clear about this – they are dangerous in my mind because it is easy to crude ETL in them that you (or at least I) will promptly forget about.  When it comes time to change things, or data changes, it is a complete Pain In The You-Know-What to figure out what was done in the rule.  Yes, they were Hot Stuff when invented, but that was almost 20 years ago (yes, really) and it is time for their closed nature to go, go, go.  Well, at least that’s my opinion.  I don’t really hate the things, but I do think that ETL via Load Rules is just asking for trouble and I wonder why there is no other lightweight way of sending data in an automated fashion.  Read on, gentle reader (all three or four of you), and yr. obdnt. srvnt. will reveal all.

Four exceptions to Load Rules

Actually, there are at least four different ways of sending data to Essbase without using Load Rules.

Load in free form

Have you ever locked and sent (BSO and the classic add-in)/sent (ASO and the classic add-in) or submitted (BSO or ASO and Smart View)?  Then you have used Essbase’s free form data format and yes, you can load it directly into Essbase sans Load Rule.  Want an example?  Take a look at CALCDAT.txt in Sample.Basic.  It is a free form data file with level zero and consolidated data values.  Why Arbor (yes, it is that old) did that, I cannot say, but they did.  

Load via ODI

ODI has an option to load data directly into Essbase without a Load Rule.  However, there seem to be lots of issues with that approach (go on, search for it and you will note that most ODI practitioners use a Load Rule because it is cleaner) and of course you have to buy into ODI in the first place.  I am a huge fan of the tool but its learning curve has a very cliff-like look to it.

Load via Studio

Essbase Studio is a great way to explore data, model it, and ultimately express it in Essbase.  And yes, there is nary a Load Rule to be seen in the tool.  But, at the end of the day, Load Rules are used to get metadata and data into Essbase.  I am inclined to cut Studio a break as Studio developers don’t so much as dirty their fingernails with Load Rules.  Nevertheless, programmatically derived Load Rules (that use ultra-secret API calls) are still part of the equation as they are generated by Studio so it is still tarred with that brush.

Load via MaxL

Did you know that MaxL allowed you to load data points to Essbase?  No?  Did you know import data did this?  It’s really pretty easy.

Before

 

MaxL

 

After

 

Pretty cool, eh?  You will note that this is essentially a free form data load which is the same as a submit (see, I am divorcing myself, reluctantly, from the classic add-in in every way and manner) from the Microsoft Office tool of your choice.  But this approach isn’t really suitable for more than a small amount of information.  

Four exceptions, but none necessarily right

Free form simply is not a practical solution, ODI is a great tool, but you have to be 100% committed to it and it has its caveats, Studio, while cool, is also not a simple solution and it resolves to the dreaded Load Rules in the end, and loading small bits of data via MaxL is interesting, but ultimately not practical.

What is needed is a lightweight way to load data, from SQL, without any of the above approaches and of course without Load Rules.  How oh how oh how can this be done?

HyperPipe to the rescue

Well, I didn’t write it

I was whining/complaining/ranting (I can do all of this at the same time and it is difficult to distinguish one from the other) to Jason Jones about Load Rules (yeah, this is one of the biggest bees that buzz around my bonnet) and he said (NB – Artistic License ahead), “Really?  You’d like to load data to Essbase without using Load Rules?  Give me a couple of days.”  And so it was.  It is great knowing people who are smarter than you.  

So what is it?

It’s a command line way to load from files or SQL to Essbase.  Note the words that are missing from that sentence – Load Rules.  <insert evil laugh>  This example will show loading to MVFEDINTWWW, aka Sample.Basic step by step.
Data in SQLOh, did I mention that the Merant ODBC drivers are no more?  What you see below is the open source H2 Java database.  Did I mention the client is running on a Mac?  It made me laugh out loud.
I should note that the above is not implying a Mac version of Essbase, but instead shows that with a web application orientation, one can access data and processes across platforms and machines.

Data target

Here’s the data target.  Jason has not yet made the switch to Smart View.  I understand his pain.

Where does it run?

Jason is a Java guy, so the code to run this is in the hyper-pipe (HyperPipe?  hyper-pipe?  Tomatoe, tomatoh) jar file through the bash Unix shell.
Are you catching all of the properties?  Just about everything one could possibly think of wrt a data load to Essbase.  You can read the screen as well as I but I draw your attention to two parameters.

sqlDriver

JDBC to the rescue, ODBC goes bye-bye.  Neat, huh?

sqlInitQuery

Want to manipulate columns and data?  Do it in SQL, as Zeus declared on Mount Olympus.  At least I’m pretty sure he said that in Clash of the Titans.  Just watch the good (original) one -- I think me mentions it right after, “Release the Kraken”.  

You may fire when you are ready, Gridley

And that’s it.  A load to Essbase, from SQL, with Load Rules being conspicuously absent.  <insert big grin>

Data in Essbase

How does it work?

Jason described it as akin to the MaxL data load process.  And that is all I know of it as I am no Java programmer.  However, the good news is that Jason is going to release this as an open source project so you can download it and tear it apart.  Have fun.

So is that the end of Load Rules?

Alas and alack, no.  They will be around, quite possibly forever (if you define forever for as long as there is an Essbase, then yes, definitely forever) as there are simply too many people with too much code in them.

On the other hand, Oracle took the bold step of giving the chop to Hyperion Business Rules to be replaced in 11.1.2.2 onwards with Calculation Manager and the same is true for the Classic Excel add-in for Essbase, so there is hope.  

As of this writing, Jason has not released HyperPipe but as I noted, he is an open-source kind of guy.  Contact him via his blog and look there for a much more in depth review of the tool.

And let me also give a hearty thanks to Jason for figuring out how to do this.  Now if only a certain absolutely humongous software company takes this concept and runs with it.  <insert the biggest grin thus far>  As the immortal Robbie Burns wrote, “Ah, but a man's reach should exceed his grasp, Or what's a heaven for?”

Be seeing you.

Dodeca dynamic reports

Introduction

One of the greatest strengths of Dodeca are the many, many, many kinds of functionality that would require scads of code are simply not needed because Applied OLAP has already done all the work and built it into the tool.  Whether it be SQL drill through, frighteningly awesome member selectors that you can drive from dimensions, members, SQL, delimited lists, the ability to quickly convert complex Excel workbooks into highly-functional Essbase reporting decks – I could go on and on and I have.

But what happens when you need to do something outside of those great pieces of functionality?  Yes, Dodeca does many, many, many things, but what about custom stuff?  Why thank you for asking, because that is where Workbook Scripts come into play.

What the heck are Workbook Scripts?

That is a very interesting question, and not an entirely easy one to answer.  Okay, part of the answer is straightforward -- WS (I am not typing out Workbook Scripts any more in this post) are how you  customize a Dodeca View (for the uninitiated, Views are reports/forms/etc., usually, but definitely not always a marriage of Essbase and Excel workbooks as hosted in Dodeca).  But WS are not some new age Visual Basic for Applications for Dodeca.  They are more akin to Excel’s original Macros (this is pre-VBA) but with multiple twists – WS have their own Essbase/SQL/Excel language, are tied intimately into Dodeca’s event model, and can even directly execute Excel formulas.  Did I mention that all of this happens in unison with Dodeca events occurring on the sheet AND all of the functionality and formulas that are intrinsic to Excel AND the Essbase API AND Essbase report scripts AND/OR MDX AND well, you get the idea?  It is, in a word, cool.

Crawl before you walk

If the above sounds overwhelming, I am doing WS a disservice – they are actually pretty easy to use (that is sort of their point) and a whole bunch of coding that you might have to do in another tool is simply not an issue as Dodeca does it all for you.  Probably the best way to show this is an example and per the title of this blog post, I am going to show you how to create a report that dynamically sets the rows to the level zero descendant of whatever off grid/POV Product is selected.  To do this I will write an extremely simple two step WS that will:

1. Read the member selection from the Product treeview POV control
2. Run a report script to get the inclusive level zero descendants of that Product
3. Dynamically increase or reduce the no. of Products on the sheet
4. Retrieve the data from the sheet

A very simple report script

The first step is to write a report script for MVFEDITWWW (aka Sample.Basic) that mimics a drilldown in Excel.  I have been writing Essbase report scripts for longer than I care to think, so this was dead easy.  You could make this report as simple or as complex as you like – data is not the point, just metadata.  Here it is:
 

And the output (yes, there is data, no, I don’t care about the numbers, yet):

Tokens to the rescue

If I were writing this report to only do Colas I guess I could stop, but I have a mad lust for power, uh, flexibility, and who knows, I may share this report with someone who is responsible for Cream or Fruit Soda pop, so I am going to remove the member “100” and replace it with the string [T.Product].  See my other posts on Dodeca to explain how tokenization works.  

Tokens via ScriptText

What matters now is that whatever Product member is selected is what gets put into the report script.  To do that, I will change the report and in the WS BuildRangeFromScript method’s ScriptText property I will put in a WS function called TokenValue or @TVal (see what I mean about this looking like Excel 4 and before Macros?).  The bits in yellow are the token string [T.Product].  The bits in a sort of horrible brown are the function @TVal(<TokenName>) which make Dodeca value the token before the report script is executed.  

Also note that the whole thing is enclosed in double quotes – this makes sense as the Product name could be Fruit Soda instead of Colas.  Remember, Dodeca can pass either the name or the alias, depending on what the user has selected for the dimension control, so the report script has to be accomodate either.

Just to be clear, to make Product tokenized, I put in the WS function and token string of:
@TVal([T.Product]) and encase that within double quotes so that it looks like:
"@TVal([T.Product])"

Ranges R Us

There are three ranges that must be defined for this process to work:

1. An Essbase retrieve range – where Essbase will retrieve the data (remember, this is the range that we want to dynamically make larger or smaller).  This range will get larger or smaller depending on the output of the report script.  NB – Make this range one row (or column if this is a column build) longer than it needs to be to get Dodeca to insert rows correctly.
2. A template range – a range that is repeated for each member in the output of the script.  Formatting, formulas, etc. go in here.  If the range is one row in height (which is the usual approach) then just one row will be inserted.  If the range is two or ten rows, then that many rows and their contents will be inserted when the row is repeated.  Think of this as a quick and dirty method to do multiple dimension drilldowns where the inner dimension is a fixed set of members.
3. An Essbase target range – A single cell that tells Dodeca where to stick the copied template range.

Two simple range rules

1. The target range must be inside the retrieve range
2. The target range must be outside of the template range

Follow the above two rules and you will be happy.  Ignore them at your peril – I will illustrate later what happens to Essbase/Dodeca geeks that do not follow those two pretty simple strictures.  It’s ugly.

So what does this all look like?

Here’s the View template.  Note that there are no predefined members in the row headers – that will be the output of the report script.  You will also note the three ranges that I have named (Ess.Retrieve.Range.1 is fixed, but I could have named the other two Platypus and Orange if I wanted to but as I am not totally insane, yet, I chose meaningful names; you should too.)  Just so everyone is clear (are you getting the idea that the position of these ranges is important?), the ranges are as follows:

1. In green in cell A7 is the target row for the inserted Products.  Note that the Insert.Marker range is within Ess.Retrieve.Range.1.
2. In yellow from A4 to P8 is the Essbase retrieve range Ess.Retrieve.Range.1.  Note that it is one row longer than needed – this is to get the insert function to grow the range, just like in Excel (go on, try it).  If you don’t make Ess.Retrieve.Range.1 one row longer than necessary, you’ll get this when everything fires:

Note that rows have been inserted onto the sheet, but the range Ess.Retrieve.Range.1 has not been expanded.  You Have Been Warned but I am jumping ahead of the narrative.

3. In blue from A3 to P3 is the row template range Row.Template – this is what will get repeated for each selected Product.

Again, note the application of the two range rules:

1. Insert.Marker is within Ess.Retrieve.Range.1
2. Insert.Marker is outside of Row.Template

Setting the properties

Dodeca helpfully supplies an event called OnAfterWorkbookOpen and just like the name suggests, this is after the workbook is open but before anything has been retrieved.  This is the time and the place to set the contents of the row.  To do that, this View must have nine properties set – it looks a little overwhelming at first but honestly it isn’t very hard.

1. BuildRangeFromScript -- You must decide how you are to build the row set.  I have an older version (I am too lazy/overwhelmed-with-so-many-things-to-do-it-scares-me to download the latest and greatest but I should) of Dodeca, so I am missing the MDX script option.  In any case, I want the EssbaseReportScript type.

2. ScriptText – The report script text as shown in the ScriptText editor a few sections above must be entered here.  Tokens, btw, are not a requirement and indeed when I was proving that this WS worked, I just used the Essbase report script with Product 100.  Once I had that working, I tokenized it.
3. StartCell – Range name of the repeated rows that are tied to the output from ScriptText.  This is the green range.
4. Rows – This report has dynamic rows; it could just as easily be columns.
5. EnterNumbersAsText – Just in case member names such as 100 are used, treat them as text.
6. CopyFromRange – The name of the range to be repeated.  This is the blue range.
7. Insert – Set to TRUE as I want the output of ScriptText to be reflected in the sheet.
8. OutputRangeName – The name of the rows that are built during the insert process.
9. OutputMap – The column that receives the output of ScriptText.

Attaching the WS to the View

Once you have written the WS and committed it, simply assign it to the View in the WS property:

Let’s run the view

Diet Drinks

Colas

 

Cola

 

All Products

You get the idea from the colored ranges, right?  The Row.Template range in blue is repeated and inserted into Ess.Retrieve.Range.1 as many times as there are Products coming out of the ScriptText property.  Also note that Insert.Marker gets pushed down as the rows get inserted.  Lastly, the Row.Tempate range is no longer in the sheet – I put in a second step into the WS to delete that range once the retrieve was complete.
 

What users would actually see

And lets look at the same report as the above but without the colors to show the addresses of the ranges post retrieve.

 
WS can be assigned to multiple views – think of it as a way to build a library of functionality within an application to be used over and over again.

That was easy, wasn’t it?

Think about how you would do that outside of Dodeca with a spreadsheet.  Think about all of the things Dodeca is giving you – connections, grids, selectors, tokenization, and now WS.  Think about the effort on your part to code all of that.  It’s sort of a consultant’s dream, isn’t it, so long as the dream consists of writing enormous amounts of code.  I’ve done it in the Classic (nope, now it is Legacy) Essbase Excel add-in toolkit and I don’t ever, ever, ever want to do it again coz I have way more important things to do with my life, like blog, or post on OTN or Network54 or work on “special projects” or prepare content for my three Kscope13 presentations or I dunno, try to have a life.  Yep, plenty of other things to do and not one of them includes writing tons of code.

Addendum – two examples of what not to do

I could have ended this post right up above but I thought I would save anyone who tries this approach from the errors I committed.  And more than that, it really illustrates how this technique works.  So with the thought that errors can be fun and educational, and the reminder that you should follow the below two rules at all times, let’s begin.

For the record once again (I think this is the third time I’ve written this, so yes, it is important) make your life simple and follow these two rules:

1. Insert.Marker is within Ess.Retrieve.Range.1
2. Insert.Marker is outside of Row.Template

What happens when you don’t follow those rules?  Or, in my case, had the rules explained to you by an ever-patient Tim Tow but then completely ignored them?  Pain, that’s what.  Let me show you what happens when Insert.Marker is not within Ess.Retrieve.Range.1.

Btw, we can only view this by invoking the WS debugger and then stepping the process.  I should note that the only way to see what is on the sheet when there are WS errors  is to run the WS in debug mode and then use the CoverView button to display the View.  Otherwise, all you get is this:

The CoverView button is on the top toolbar of the debugger.

Error no. 1 – Insert.Marker outside of Ess.Retrieve.Range.1

Assuming that the WS is being stepped, and that the CoverView button has been selected, here’s the sheet before the ScriptText is applied.  Note how Insert.Marker is outside of Ess.Retrieve.Range.1.

And we get…KABOOM!
 
Do you see what happened?  The Row.Template got repeated correctly, but the Ess.Retrieve.Range.1 did not get expanded.  When that retrieve fires, there will be no Product within the range.  The error message is a bit cryptic although if you look at the above range, you’ll realize it makes perfect sense.
Error no. 2 – Insert.Marker within Row.Template
What happens if the Insert.Marker is within the Row.Template?  It’s actually quite logical – the ScriptText returns lots of rows one for each product, but doesn’t put the right members in, Dodeca inserts them but they are now within the expanded Row.Template range which gets expanded.  Ess.Retrieve.Range.1 is what gets retrieved but it doesn’t have any Products and so you get the below result.
And a nasty error message of:
It’s the same error message (it is the same error from an Essbase perspective) but a completely different looking sheet.  Don’t do as I did and all will be well.  And use that debug function and even color the ranges if you are confused as to what is where and why.
And now really the end 
Beyond user-initiated error, i.e., I screwed up, this is dead easy and takes much longer to read about than it does to set up and run.  I think practically every Dodeca customer out there uses WS in one form or another, but like all powerful tools, there is definitely a learning curve.  Hopefully I’ve brought you a bit along the way with WS in Dodeca and showed you some of the power.  In case you can’t tell, I really like this tool.

Be seeing you.

Stupid Programming Tricks #16 -- Special characters in Substitution Variables with MaxL

Shouldn’t I be able to find this?

I was working on an upgrade project this week from Essbase 9.3.1 and as part of this process I needed to add a bunch of Essbase substitution variables to my test server.  This should have been No Big Deal, especially as the client was going to give me a MaxL script with the variables and their values all ready to go.  Simply change the username, password, and server and everything will be tickety-boo, right?

So how do you add a variable, anyway?

I don’t know how the variables got onto the client’s server, but I suspect they have been there for a good long time, given that this is a 9.3.1 instance of Essbase.  What I got from the client was code that looked like this:

alter database appname.dbname set variable variablename value ;

What oh what is wrong with this approach when said variable (I am calling it CLTest) doesn’t exist?  Simply this:

After a bit (okay, a lot) of headscratching it made sense – the variable has to exist before it can be set.  In other words, set variable only works when the variable already exists.  Uh oh, as I had 76 variables to value.  For sure I didn’t want to set that in EAS.  So, I thought in my sometimes-logical mind, surely if I can set it, I can add it, right?

However, a review of the alter command’s 11.1.2.2 (and yes, I am finally on an 11.1.2.2 project, only eight months or so after its release, Huzzah!) documentation at the system, application, and database level, I couldn’t find how to add (I hope with the repetition of that word I have clued you in by now) a variable.

So I went to the great false god Google and searched for how to do this and got lots of hits but got…nothing.  Lots and lots and lots of ways to set the values, but not to add them.

And then it hit me

What oh what oh what would happen if I modified the alter command, took out set and put in add, as adding a variable is what I want to do?

And so it was:

A bozo is what I am for struggling with that for 15 minutes.

MaxL functionality through the ages

I don’t have a 9.3.1 instance to prove this out on, but the client said this (see below) worked and it most definitely did not in 11.1.2.2, so I am going to guess that it changed.  In any case, this stuff is not all that well documented, so I thought I’d illustrate these so you don’t have to go out of your mind (Question:  If I am already out of my mind, and I claim that these things drive me out of my mind, does that – make it worse, who can tell, or, and  most likely, does anyone care?  But I digress.) trying to figure it all out.

Doesn’t work #1 – variable as a numeric value

I got this (changed to something generic lest I get sued) and it supposedly (I have no reason to doubt them, I just can’t see it for myself) works in 9.3.1:

alter database sample.basic set variable CLTest 3 ;

In 11.1.2.2, that resolves to:
Hmm, I can’t remember setting a substation variable in any version of Essbase where the variable value is a number.  How oh how oh how do we do it in 11.1.2.x?  Careful (well, lucky) observation shows single quotes around the 3 above.  Could that be the answer?  Why yes it could.  

alter database sample.basic set variable CLTest '3 ' ;


And yes, you can reference CLTest as a value in a calc script.  A very interesting approach and one that I’ve never seen nor thought of.

Doesn’t work #2 – double quotes around the member name

Again, I don’t know how this works in 9.3.1; I suspect maybe not given the date of this Network54 thread that shows the solution.  In 11.1.2.2, simply sticking double quotes around a value will result in “success”, but it will also mean that the variable value will not resolve in BSO (or ASO, for that matter, although there [ and ] need to surround the member name) as BSO Essbase needs that " and " around the variable value.  What do I mean?
 

So fix it with special escape characters (this one I did not think up on my own as per above, but I thought I would illustrate it anyway).

alter database sample.basic set variable CLTest "\"Opening Inventory\"" ;


Btw, have did you notice how the leading and trailing double quotes are different?  Why?  Why doesn’t this work?
 
alter database sample.basic set variable CLTest "\"Opening Inventory"\" ;

Dunno, but this is the result.  
Go on try it.  You won’t get MaxL to execute that line.  My guess is that once "\" starts off a string, it needs to get closed with \"".  Or at least it appears that way.

Woot, woot, woot!  Geek alert, jump to the bottom to understand why the above is true.  Thank  Jason Jones for the full explanation.  A summary of the conversation (I hope you are enjoying the Christmas-y colors) is that the double quotes  in green are MaxL’s and the red \" is the escaped Substitution Variable’s  double quote.

alter database sample.basic set variable CLTest "\"Opening Inventory\"" ;

And that’s the end

I have to admit that I debated (I do actually think, or what passes for thinking for me) about writing this one but then I was consoled by the thought that this is called a “Stupid Trick”, so it isn’t as though you weren’t warned.  

OTOH, the add functionality isn’t documented anywhere, nor are the single and double quote approaches, so I think this one is worthy of a “Stupid Trick”.

Be seeing you.

The totally geeky cool addendum
Jason and I were IM chatting as we are wont to do, and this blog post came up.  Follow along and excuse the misspelt words that are the product of geeky enthusiasm.  He’s giving me a pretty good lesson in why MaxL works the way it does.  See, this blog is educational.  

Herewith the conversation:

[15:53] Jason Jones: btw, just saw your newest blog article
[15:53] Jason Jones: seriously, hit me up if you have quotes issues -- its a programming thing that i know inside and out :)
[15:53] CameronLackpour: I was writing it.
[15:53] CameronLackpour: So why does the leading quote look like "/" and the trailing quote look like /""
[15:53] CameronLackpour: That is super weird.
[15:53] Jason Jones: not at all
[15:54] Jason Jones: man, i get to school you in this quotes stuff left and right
[15:54] Jason Jones: :)
[15:54] CameronLackpour: Doesn't bother me.
[15:54] CameronLackpour: Btw, those escape codes are 100% undocumented.
[15:54] Jason Jones: i've actually run into this same thing with sub vars
[15:54] Jason Jones: well, they are undocumented, kind of
[15:54] CameronLackpour: Oh, everyone has.
[15:54] Jason Jones: if you have, say, programmed in perl it's sort of implicit
[15:54] CameronLackpour: Hmm, but MaxL isn't perl.
[15:55] Jason Jones: it's not but it has Perl/PHPish quoting semantics
[15:55] Jason Jones: so the basic idea is this
[15:55] Jason Jones: sub vars are strings
[15:55] Jason Jones: right?
[15:55] Jason Jones: they don't have a type like numeric or whatever, they are arbitrary text -- a string
[15:55] CameronLackpour: Except of course when ' and ' surround it.  Then it's a (if numeric) value.
[15:55] CameronLackpour: I verified that the '3' worked as an assign in a calc script -- it does.
[15:55] Jason Jones: well that's sort of another issue you are talking about
[15:56] Jason Jones: so in scripting languages you can quote things in different ways
[15:56] CameronLackpour: I am seeing a JJ Essbase blog post, but go on.
[15:56] Jason Jones: :)
[15:56] Jason Jones: let's invent a scripting language right now called CLScript
[15:56] Jason Jones: and in CL script you can put values into variables and you can print things
[15:56] Jason Jones: variables shall be prefixed with a dollar sign
[15:57] Jason Jones: $favoriteColor = "Blue"
[15:57] Jason Jones: print $favoriteColor
[15:57] Jason Jones: output is Blue, with no quotes
[15:57] Jason Jones: so I'll denote that as this: > Blue
[15:57] Jason Jones: now, CLScript also allows us to use single quotes to denote variable values as well
[15:57] Jason Jones: $favoriteColor = 'Blue'
[15:57] Jason Jones: print $favoriteColor
[15:58] Jason Jones: > Blue
[15:58] Jason Jones: fair enough?
[15:58] CameronLackpour: Sure
[15:58] Jason Jones: $favoriteColor = "Dark Blue"
[15:58] Jason Jones: print $favoriteColor
[15:58] Jason Jones: > Dark Blue
[15:58] Jason Jones: we have a space in our value but that's okay because as far as the CLScript interpreter is concerned, it just takes everthing between the quotes and thats the value of the variable
[15:59] Jason Jones: so it doesn't care
[15:59] Jason Jones: let's also say that in CLScript we can write this:
[15:59] Jason Jones: $favoriteColor = Blue
[15:59] Jason Jones: print $favoriteColor
[15:59] Jason Jones: > Blue
[15:59] Jason Jones: it *works* but is kind of frowned upon
[15:59] Jason Jones: CLScript decided to be nice and said "well, I don't like it but there's only one thing there so I'll pretend it has quotes around it"
[15:59] Jason Jones: but saying $favoriteColor = Dark Blue doesn't work
[16:00] Jason Jones: because the CLScript interpreter is like: Well, it looks like shit but i'll either not take it or I'll just take Dark since that's the first thing I see
[16:00] Jason Jones: so that's a toss up
[16:01] Jason Jones: CLScript is smart and takes absolutely everything in between the double quotes as its value… but what if you want a double quote inside the double quotes?!
[16:01] Jason Jones: $favoriteColor = "Dark "Navy" Blue"
[16:01] Jason Jones: it can't figure it out
[16:01] Jason Jones: so for ease of use, CLScript says, okay, if you want double quotes INSIDE your value I will let you put them into single quotes
[16:01] Jason Jones: $favoriteColor = 'Dark "Navy" Blue'
[16:02] Jason Jones: print $favoriteColor
[16:02] Jason Jones: > Dark "Navy" Blue
[16:02] Jason Jones: but what if we really have to use double quotes to quote a string that has double quotes in it?
[16:02] CameronLackpour: Like "Beginning Inventory"
[16:03] Jason Jones: CLScript says, okay, you can "escape" the quote -- put a backslash in front of a double quote and I'll now that that isn't a double quote to indicate the start and stop of your value, but literally you want a double quote
[16:03] Jason Jones: (that's another issue actually -- one sec)
[16:03] Jason Jones: so we can "escape our quotes
[16:03] Jason Jones: $favoriteColor = "Dark \"Navy\" Blue";
[16:03] Jason Jones: print $favoriteColor
[16:03] Jason Jones: > Dark "Navy" Blue
[16:04] Jason Jones: so it's not that the backslash double quote combination was the special character sequence on the outside of the value we want, it is for all the internal occurrences of a double quote
[16:04] Jason Jones: and that's why you have "\"Opening Inventory\"" instead of "\"Opening Inventory"\"
[16:04] Jason Jones: in fact, if using the latter form is not an error, it results in a value of this:
[16:04] CameronLackpour: Ooooh, I see
[16:04] Jason Jones: "Opening Inventory
[16:05] Jason Jones: with no end quote
[16:05] Jason Jones: now, all that being said there is yet one more wrinkle here
[16:05] Jason Jones: which is probably what's really biting you
[16:05] Jason Jones: aside from the double quote thign
[16:05] Jason Jones: so, we can go into EAS itself and edit our sub vars
[16:05] Jason Jones: &CurrMeasure, for example
[16:05] Jason Jones: i can go into EAS and set the value to this: Opening Inventory
[16:05] Jason Jones: notice no quotes
[16:06] CameronLackpour: hmm, would just \"Opening Inventory\" work?  I wonder why not.  Ah, because the interpreter needs the outer " and " around the internal \" Opening Inventory \".
[16:06] Jason Jones: what you say would not work
[16:06] Jason Jones: because you have two issues here
[16:06] Jason Jones: issue 1: MaxL recognizing the value and 2: the value itself from an Essbase perspective
[16:06] CameronLackpour: No, I think i get it.  MaxL pukes when there is a value with spaces.
[16:06] CameronLackpour: It needs "
[16:06] CameronLackpour:  and "
[16:06] Jason Jones: right
[16:07] Jason Jones: but here's the rub
[16:07] Jason Jones: if you put quotes around Opening Inventory and MaxL reads that, MaxL goes "okay, the thing between the quotes is the variable value"
[16:07] Jason Jones: BUT
[16:07] Jason Jones: here's the rub
[16:07] Jason Jones: and why it's more complex than that
[16:07] CameronLackpour: But then to get the escaped " and " -- that needs to go inside.  So "\"Operating Income\""
[16:07] Jason Jones: from an ESSBASE SUBSTITUTION VARIABLE STANDPOINT -- you need quotes on the subvar itself!
[16:08] Jason Jones: that's the difference between going into EAS and looking at sub vars and seeing one of these
[16:08] Jason Jones: CurrMeasure --> Opening Inventory
[16:08] Jason Jones: or
[16:08] Jason Jones: CurrMeasure --> "Opening Inventory"
[16:08] Jason Jones: if the value of the substitution variable itself doesn't have the quotes on it, it wont work!
[16:08] Jason Jones: because it looks like this when the code goes to execute
[16:08] Jason Jones: FIX(Opening Inventory)
[16:08] CameronLackpour: It'll get assigned, but just won't work./
[16:08] Jason Jones: rightio
[16:08] Jason Jones: FIX (&CurrMeasure)
[16:09] Jason Jones: straightup text replacement
[16:09] CameronLackpour: I will amend my text and give you educational credit.
[16:09] Jason Jones: and you can't put the quotes in yourself because then the FIX wouldn't be able to interpolate the variable since it wouldnt see it
[16:09] Jason Jones: ie
[16:09] Jason Jones: FIX ("&CurrMeasure")
[16:09] CameronLackpour: I know that FIX("&CurrMeasure") doesn't work
[16:09] Jason Jones: doesn't work
[16:09] Jason Jones: however
[16:09] CameronLackpour: Right, it tries to read it as a member name
[16:10] Jason Jones: and this is due to this measure having a space in the name
[16:10] Jason Jones: if it was OpeningInventory you'd be fine and not notice the issue
[16:10] Jason Jones: so probably how this manifests itself every now and then is that someone ends up googling "Error when using substiution variable with space in the name" or something to that affect
[16:10] CameronLackpour: Yes, that makes sense.  As I noted, I will amend it before Glenn jumps on it.  :)  I will give you all credit.
[16:11] Jason Jones: it's just a doubly gnarly issue when you add in the MaxL piece since you gotta get the quotes exactly right
[16:11] Jason Jones: :)
[16:11] Jason Jones: and then very, very lastly to really beat the issue to death, note that only double quoted strings interpolate variables
[16:11] CameronLackpour: What is interesting is that the client stated that "Opening Inventory" worked perfectly.  And yet when we looked in EAS we say "Opening Inventory", not Opening Inventory.
[16:11] Jason Jones: ie, we have an environemnt variable of NAME or something
[16:11] Jason Jones: so you might be able to do alter server set whatever "Name_$NAME"
[16:12] Jason Jones: and $NAME is replaced with the value of NAME
[16:12] Jason Jones: but if you do single quotes
[16:12] Jason Jones: 'Name_$NAME'
[16:12] Jason Jones: then the value of the variable is literally the dollar sign
[16:12] Jason Jones: that's why you might have to escape double quotes
[16:12] Jason Jones: to get variable interpolation as well as the quotes in your variable
[16:12] Jason Jones: alternatively in your example you could to this:
[16:12] Jason Jones: '"Opening Inventory"'
[16:12] CameronLackpour: Yes, I have seen this in MaxL with parameters.  Generally, I can throw a $1 or $2 into a string (like for file names) and so long as there are no spaces, everything works.
[16:12] Jason Jones: (single quote, double quote, value, double quote, single quote)
[16:13] CameronLackpour: But I have to encapsulate all in double quotes.
[16:13] Jason Jones: well, now you know how to do it with spaces
[16:13] Jason Jones: so if you wanted to say, pass in a command line parameter to the script that is used in the variable, like say $1 = March, you could do this:
[16:13] CameronLackpour: Thanks.  Hey, I have to run (not to hide my face in shame, but to clean out a gutter before it gets dark), but thank you.  You can correct me any time.
[16:13] Jason Jones: alter foo set to "\"Opening Inventory for Month of $1\""
[16:14] Jason Jones: no problem
[16:14] Jason Jones: glad to help a fellow nerd :)