I will be available on 31 May, 2014. Essbase forever! Or something like that. Contact me on LinkedIn if you're interested.

25 July 2014

Stupid Programming Trick No. 18 -- Modifying the Period dimension in Planning

Introduction

The genesis of this Stupid Programming Trick comes both from this post over on Network54 and because this has bugged me for, oh, ever (as long as ever = how long Planning has been a product so 13-odd years at this point).  It’s not that summary time periods in a custom time period Planning application is hard, it’s just that it’s all a bit unintuitive and I’ve never seen it documented anywhere.

For those reasons, what you will see is how to manipulate custom periods in a Planning Period dimension to get quarter totals.  I will also show how custom Period members outside of YearTotal.

Create the application

Note that I am picking Custom as my base time period.

Do I have a custom time period Planning application?  It sure looks like it.  Let’s create the application.

Working…working…working

Glorious 12th +1


So now I have a custom Period Planning application.  How do I add quarters into my YearTotal hierarchy?

Let’s add those quarters

Don’t do this

If you want to add siblings to BegBalance and YearTotal, click on Add Child.  If you want to add children to BegBalance or YearTotal, don’t click on Add Child.  That makes sense, does it not?  No, I suppose not.

Do this

Click on the first level zero period under YearTotal and then click on Add Summary Time Period.

Nothing new to see here

Name it, define its storage property, and click on Save.

13 Periods in a quarter?

Not forever, but for now I have now added the first summary time period under YearTotal.  I will need to do this three more times.

Now add the next one

Select the next first child of Q2.  If Q1 is P1 through P3, the first child of Q2 will be P4.

Easy peasy lemon squeezy

Nothing new to see here, folks.  Move along.

Here’s Q2

Weird enough for you?  Yet it works.  And it happens again in Q3 and Q4.

Do it again

You should know the drill by now.  Click on P7 and then Add Summary Time Period.
Ta da

One more time

Q4 is almost done.

It’s done

Why does Period behave this way?  Ask the Oracle Product Manager, but I wouldn’t blame him – it has been this way since I first laid eyes on Planning 1.5.

Adding a sibling to YearTotal

So that’s the weirdness when it comes to quarters.  What about other custom time periods?  More weirdness, of course.

Note how you do not click on YearTotal and Add Sibling to add, oh, a sibling to YearTotal as that would likely be too intuitive.  

Nope, instead you click on Period and then click on the toolbar button Add Child.

Put in a value.

Ta-da, now you have a sibling to YearTotal


Add a child to P1YTD.
Now you are (or at least I am) done.

Don’t do this at home, kids

Oooo, scary

All done

Conclusion

What exactly can we conclude from this?
  • Adding siblings and parents in Period is just…weird
  • It ain’t intuitive, but much of life is that way
  • You can add siblings and parents so long as you follow the heretofore undocumented steps
  • Yr. obt. svt. answers pleas for help even whilst he is on vacation.  Am I nuts?  Probably.

Be seeing you.

21 July 2014

Enhanced Planning Validations: Part 1

Introduction


What you are about to read is not my work.  No, I didn’t steal it, but instead I somehow recruited Tyler Feddersen of Performance Architects to write about this really clever technique to enhance validations in Hyperion Planning.  Also, that will likely mean that the quality of what you are about to read is several levels of awesomeness above my usual drivel, so pay attention, this is good stuff.  

Does this mean I work for Performance Architects now?  Nope, I am still my one man band.  

I know Tyler (and Chuck Persky and Jonathan Etkin) because they were incredibly gracious, helpful, and just all around awesome when I was writing my Kscope14 ASO Planning presentation with Tim German and needed some real world feedback on what that tool is like.  Sometimes people think that consulting companies are ruthless competitors (okay, I am not exactly a competitor to that firm unless they’ve been fooling me and have just Tyler as an employee but in some sense we do compete) that don’t share.  The hour long conference call I had with Performance Architects and the multiple emails back and forth show me that they “get it”.  What a nice bunch of guys.  I’m sure they treat their customers with the same generosity of spirt and technical acumen if they spent all that time and energy with me for free.  You should hire them when you aren’t hiring me.   ;)

And with that, every single word below except the conclusion  is Tyler’s.  Tyler, thanks for writing this and I look forward to part two.

Tyler Feddersen, Performance Architects   (Big thanks as well to Mohan Chanila for making this readable)

What’s the big deal?

Data form validations offered in Hyperion Planning 11.1.2+ were a nice step in the right direction for easily identifying data entry errors. Additionally, a lesser known functionality that was introduced simultaneously was the ability to hook up these data form validations with Process Management. Unfortunately, there are a few caveats. First off, there are very few of us out there using Process Management. Secondly, even if you do…I’ve found the validation processing to be less than stellar if there are too many validations.

Facing similar issues, a fellow co-worker of mine, Alex Rodriguez, introduced the initial idea to use business rules as an alternative, which turned out to be quite efficient. Since then, I’ve expanded on the idea to create a full validation solution. I have to say…I like the outcome. In fact, I’ve used it in multiple recent Planning solutions. The result does not involve anything like code manipulation but rather is an innovative take on pieces of functionality that are already available. Part 1 of this solution will go through the creation of a process to identify errorneous data entry.

A Look Ahead

Below is an overview of what this blog will be attempting to accomplish. Some of the members may seem a little different since this was done on a PSPB application.

A correct data set.

After saving new data, an error is displayed due to an invalid entry. Note: The cell is highlighted with “red” by using the data form validations. If the data were to be saved correctly again, the form would again appear as the initial image.

Additionally, the error appears in a consolidated list of errors for administrators and/or users. 

How It Was Done

Step 1: The Validation Member

The first step is to create a new member specific to validations. The sole use of this member is going to be for storing validation “flags” (SmartList values). This validation member should be created in a SPARSE dimension that will not be part of the validation logic. For example, I used the Budget Item dimension (sparse) in my application because I wanted the errors to be stored at a specific Employee/Position/Entity combination, but I did not care to see errors by specific Budget Item members. By using a Sparse member, the validation intersection will be able to load much faster on a data form using “Suppress Missing Blocks” since it has its own block.

Note: In the images below, you will see that two members were actually used, one in the Budget Item dimension (Validate) and one in the Account dimension (AllocValidate). This was done for two reasons. One, it allowed me to assign the SmartList (coming up) to an Account and keep the Evaluation Settings untouched. Two, it allowed the form to display the error with a complete new column/row combination. However, similar functionality can be achieved by simply adding the single sparse member.


Hold up…is block suppression really THAT important?

Thanks for asking. Yes. The use of block suppression with this solution is incredibly important in getting an efficient final product . As we already know (or now you will), a block is needed for data in each sparse, stored member combination. If we create a validation member in the dense dimension and don’t trim down the possible sparse combinations, the validation form is going to need to process all blocks that exist in the entire system to return the validation errors. Meanwhile, by choosing one of the sparse dimensions and only using a single member within it (a validation member or other random member) , the number of evaluated blocks is knocked down to only those affected by the actual validations. Using the “Suppress Missing Blocks” option, Planning will suppress all blocks that contain no data prior to sending the query to Essbase.

For example, the image below shows the processed cells when a sparse member was utilized. Only two entire cells were processed when the Planning form was opened, due to one validation error existing.


Now, the image below shows the number of processed cells when the sparse member strategy was not used. Notice that the number of cells increased exponentially, even though there was still only one validation error existing.


While 10,168 cells may not seem like much relative to larger data sets, it represents every block in a fairly small and sparse data set. In a heavier model, the differences could go from Planning crashing to the form opening in a matter of seconds.

Step 2: The SmartList of Errors

Next, we need to create a SmartList with entries for each error to be tested. “AllocValidate_Menu” has been created in the image below.  


An entry should be put in for each desired error. In the image below, there are 13 different errors that can be flagged. For example, the second entry is used when a specific assignment contains over a 100% allocation.


Lastly, assign the SmartList to the Validation member that was previously created. In the example below, it is being assigned to the AllocValidate member, which exists in the Account dimension.

Step 3: Create the Business Rule

This step can either be accomplished by creating a new rule or by adding to an existing script. In the example below, the script is meant to be run on a data form with the Scenario, Version, Organization, Position, and Employee in the POV or Page. The rule then checks the total Percentage Allocation for the specified intersection. If the rule confirms an error, the following intersection will be assigned the error flag of having a high percentage:

{Scenario}->{Version}->{Organization}->{Position_Dim}->{Employee}->”BegBalance”->”Unspecified Element”->”No Year”->”AllocValidate”->”Validate”

Step 4: Create the Data Form

As with the business rule, this can be done by either creating a new form or adding to an existing one. In the examples provided at the beginning, both of these techniques were used.  The example below also shows the addition of a menu item to the form. The menu item “Run Validation” will kick off the rule that was created in the third step to populate the data form.


Voila! A fully functioning error handling process is at your disposal. The complexity of this error handling can go as far as Essbase/Planning will take you. Since all of the error handling has been merged with the rest of the Planning solution, there are plenty of ways to explore additional features to this solution. Tune in for Part 2 to see a few ways on how this process can be expanded further, including the creation of a “locking” procedure to prevent business rule execution and an integration with Process Management.  

Conclusion by Cameron

And there you have it – a really clever way to handle validations in Planning using the functionality built into the tool.  Thanks again Tyler for sharing this.

Be seeing you.

14 July 2014

Calculation Manager, BSO Planning, and ASO Planning combine for an awesome ASO Essbase procedural calculation hack -- Part 1

A bit of a tease

I’m not talking about me.  If anything I am more than a bit too straightforward.  I try not think of all of the own goals I have scored in my Cameron-tells-it-like-it-is way when a bit of discretion would have been better employed.  At least I am learning as I enter my dotage.

Nope, what I’m talking about is this first part of a three part blog post on how to achieve awesomeness in ASO Planning and beyond.  I really wanted to do this all in one shot but it simply became overwhelming in its length and complexity, so I am reluctantly going to break it into three parts with this first one simply laying the ground work.

Here’s how I plan on putting it out in over the next three weeks:
  • Part 1 – You’re reading it; an introduction to the CDFs you likely don’t know about
  • Part 2 – The genius of Joe Watkins
  • Part 3 – Tying it all together across three different Oracle EPM products with of course a glorious hack

What’s the mystery

As you read this, remember what’s in the title:  Calculation Manager, ASO Planning, ASO Essbase procedural calculations, and BSO Planning.  If you came to the Kscope14 session Tim German and I gave on ASO Planning I went a bit into this but there was simply no time to go into detail.  You won’t remember what I am about to show you because it got cut.  And, as I noted, this is just the introduction.  Hopefully that will keep you coming back for the next two installments because what I stumbled into is pretty damn cool.

Here are the clews

Beginning with, I think, Planning 11.1.2.2, standard Essbase (Custom Defined Functions) CDFs are installed with every copy of Essbase.  Surprised?  They’re definitely there in Essbase 11.1.2.3.500 and they actually differ between what’s available to Essbase and what is installed with each Planning application.  Even more surprised?  Oh the things we can find if we but search.  Of course as I am in the “Even a blind pig occasionally finds an acorn” set, I am putting this down to luck.

Two ways to find them

The first (and boring/safe/approved) way to figure out what these CDFs is to be a good little boy (or girl) and use Calculation Manager to insert functions:

Create and insert

Create a rule, insert a script object, and then click on the Insert Function toolbar button.  It’s the very first button – it’s like Oracle wants you to use it or something crazy like that.

Waddayasee?

I hope you see all kinds of functions.  We’re going to be focusing on the Custom Defined Functions (CDFs).

NB – I am doing this in a BSO Planning application.  As we will see in a bit, there are nuances between what’s available to Planning Plan Types and pure Essbase applications.

Here’s the list of functions.  Nothing much new here except the fourth and third from the bottom although for the purposes of this post I will ignore Custom Defined Macros and leave that as an exploratory exercise for you, Gentle Reader.

What’s in the CDFs?

Lots and lots of interesting stuff is what.  Here’s most of what’s available to a Planning application.

Naming standards

I’m not going to cover most of these, but if you look at the above, you will note that there are CDFs that start with @CalcMgr and ones that start with @Hsp.  Telling you that if it starts with @CalcMgr it has to do with Calculation Manager and if it starts with @Hsp it has to do with Planning is not giving away the game.

So what does that mean for Essbase?

The @Hsp functions are only usable in Planning applications.  Don’t believe me?  Create a BSO Essbase Calculation Manager script and go through the same insert functionality.  What do you see?
This makes sense, right?  You’re not in Planning so the CDFs that Oracle wrote to support Planning aren’t there.

There’s more to Calculation Manager than Calculation Manager

Go into good old EAS and create a BSO calc script.  Now look over in the lower left hand side of the script window and select Categorical->User Defined Funcitons – they are the same @CalcMgr functions.  Here’s another hint (possibly a bit of a red herring) to the puzzle – those functions say @CalcMgr but they’re available in plain old calc scripts as well.  
The plot thickens, eh?  :)  What oh what oh what would you do with all of this?

An alternate, and cooler, and faster way to get to all of the above

I know, I know, Cameron has been a good little boy and painted within the lines.  As Donald Fauntleroy Duck would say, Ah, Phooey!

A much faster, and hackier, and more informative, and thus more awesome way to do this is to simply go looking for essfunc.xml on your Essbase server.  Oh, the things you will see.  

Lookatthat

There’s lots of files with that name.  How oh how oh how do they differ?

Two differing properties

Size matters
Discounting the Windows shortcuts, note the spread in size from 7K to 10K.  What do you suppose is in the 7K one?
Nothing but @CalcMgr functions.

And the 9K one?
Nothing but @Hsp functions.

And the 10K one?
A mix of @CalcMgr and @Hsp CDFs.  Curiouser and curiouser.
Location, location, location
Where you live matters, and where essfunc.xml domiciles matters as well.  

The @CalcMgr functions that are available to Plannnig and Essbase live in c:\oracle\middleware\user_projects\epmsystem1\EssbaseServer\essbaseserver1\java\essfunc.xml.

Planning applications get (it appears) dynamically generated essfunc.xml files that are stored in each Plan Type’s Essbase application folder.  Even more interestingly, it looks like @CalcMgr functions are added to the Planning/Essbase application’s essfunc.xml file on an as-needed basis.  That accounts for the mix of @Hsp (always there in Planning) and @CalcMgr (but only a handful) CDFs in the 10K file.

Why essfunc.xml is better than Calculation Manager’s insert functions

Although you can get the parameters in both Calculation Manager and EAS’ calc script editor, I personally find that many parameters hard to follow and the interface itself is kind of kludgy.  It turns out that the function dialog box in both tools simply reads the examples from essfunc.xml.  
A difference in parameters
Planning
It’s probably a bug, but param1, param2, param3, etc are not particularly helpful when it comes to figuring out what and how the @CalcMgrExecuteEncryptMaxLFile.
Essbase
Ah, that’s a bit better.  Now we can see that the private key, maxlFileName, arguments, and asynchronous parameters are used with @CalcMgrExecuteEncryptMaxLFile although there is still no way to know what the arguments and asynchronous parameters are.

Wouldn’t it simply be easier to read the file?  Why yes it would.

Here’s the @CalcMgrExecuteEncryptMaxLFile function in essfunc.xml

   <function name="@CalcMgrExecuteEncryptMaxLFile" tssec="1378746290" tsmcs="206000" javaSpec="com.hyperion.calcmgr.common.cdf.MaxLFunctions.runMaxLEnFile(String,String,String[],String)">
     <flag value="RUNTIME"/>
     <spec>
       <![CDATA[@CalcMgrExecuteEnMaxLFile(privateKey, maxlFileName, arguments, asynchronous)]]>
     </spec>
     <comment>
       <![CDATA[Calc Manager CDF for running Encrypted MaxL file. RunJava  com.hyperion.calcmgr.common.cdf.MaxLFunctions true -D 2115028571,2505324337 c:/MaxL/maxl.mxl 906388712099924604712352658511 0893542980829559883146306518502837293210. First argument if false, will be an synchromous maxl task]]>
     </comment>
   </function>

Hmm, same ambiguous @CalcMgrExecuteMaxLFile syntax but there’s now something else here – a RUNJAVA command.  At least there’s an example, sort of, although it looks as though both the private key and the encrypted username and password need to passed with this method.

Confusing, isn’t it?

Let’s end the confusion

Whilst I would love to tell you that I figured all of this out, I must confess that I reached out to the Calculation Manager product manager, Sree Menon, and begged for help.  Sree and his associate, Kim Reeve, were beyond helpful.  I promised them that I would share what they taught me.

A brief foray into the ACE program

Btw, lest you think I know Sree because I am an ACE Director, in fact Sree reached out to me way back when I wrote my second post on Calculation Manager.  I don’t think he knew (or cared) about my ACE status.  I am not in any way putting down OTN’s ACE program, I am merely noting that Oracle reaches out to everyone who is an advocate of their products.  That’s a long-winded way of saying if yr. obt. svt. can do it (it being evangelizing, working with Oracle, and maybe becoming an ACE), you most certainly can too and I very much encourage you to do so.  I am on a personal crusade as of this writing to get four people recognized as ACEs because they love Oracle’s products and thus do a ridiculous amount of evangelizing for Oracle.  It will take a long time and a lot of work all round but I’m convinced they will get in – they deserve it, at least in my opinion.

Sorry for the digression, but sometimes I think people think becoming an ACE is an impossible task.  It is a difficult task, and I might note that the first time I was nominated, I was rejected, but now here I am.  Again, if I can do it, you can too.  Don’t be intimidated by the process as making it is incredibly rewarding; probably the most rewarding thing I’ve achieved in my career.  Note that the evangelism works both ways cf. Sree reaching out to me so aim high and you might just make it.

Back to the code

It’s very simple MaxL code.  All that it is doing is logging in to Sample.Basic and running a MDX query.

encryptest.mshs

spool on to "c:\\tempdir\\encryptest.log" ;

login $key 9404266461315012977165999794704691034001 $key 0307596931591918242060507329306599979470 on localhost;

/*    The below settings are right out of Developing Essbase Applications    */
alter session set dml_output alias off ;
alter session set dml_output numerical_display fixed_decimal ;
alter session set dml_output precision 4 ;
set column_width 40 ;
set timestamp on ;

SELECT {[Measures].$3}
    ON COLUMNS,
{[Year].Levels(0).Members}
ON ROWS
FROM [Sample].[Basic]
WHERE ([Scenario].[Actual], [Product].[Product], [Market].[Market]) ;

spool off ;

exit ;

Note that $3 command line parameter – yes, you can pass parameters from a calc script to MaxL.  Get your creative juices flowing over that.  I’ll try to demonstrate a few bits of it.

Running the code in EAS via CDF3.csc

It’s actually quite straightforward, although it didn’t seem so when I was figuring it out.

The key syntax notes are as follows:
  • Pass all strings in double quotes
  • Use forward slashes only in file paths
  • Wrap application, server, and any command line parameters in @LIST
  • Those arguments map to command line parameters, so server = $1, database = $2, the first paramter = $3, etc., etc.
  • Wrap any member names in @NAME

Two block issues to be aware of

@CalcMgrExecuteEncryptMaxLFile runs as a member calculation block.  That means that if the FIX that contains the command defines more than one block, it will run that many times.  I only know this because I tried running the command without any FIX and it took (seemingly) forever.  That’s simply because it ran once for each block.  As my copy of Sample.Basic has 376 blocks, the code ran 376 times.  Imagine it in a real database.  Shudder.

Of course if there is no block then @CalcMgrExecuteEncryptMaxLFile won’t run.  Yep, block creation issues raise their ugly head once again.

So what happens when you run it?

Here’s encryptest.log’s output.  Note that $3 resolved to “Sales”.
MAXL> login $key 9404266461315012977165999794704691034001 $key 0307596931591918242060507329306599979470 on localhost;

OK/INFO - 1051034 - Logging in user [hypadmin@Native Directory].
OK/INFO - 1241001 - Logged in to Essbase.

MAXL> alter session set dml_output alias off ;

OK/INFO - 1056226 - Session altered for user [native://nvid=c9d9665628b8695a:6d29254d:1410456fa26:-7adf?USER].

MAXL> alter session set dml_output numerical_display fixed_decimal ;

OK/INFO - 1056226 - Session altered for user [native://nvid=c9d9665628b8695a:6d29254d:1410456fa26:-7adf?USER].

MAXL> alter session set dml_output precision 4 ;

OK/INFO - 1056226 - Session altered for user [native://nvid=c9d9665628b8695a:6d29254d:1410456fa26:-7adf?USER].


     essmsh timestamp: Mon Jul 14 16:50:59 2014

MAXL> SELECT {[Measures].Sales}
  2>     ON COLUMNS,
  3> {[Year].Levels(0).Members}
  4> ON ROWS
  5> FROM [Sample].[Basic]
  6> WHERE ([Scenario].[Actual], [Product].[Product], [Market].[Market]) ;

Axis-1                                  (Sales)                                
+---------------------------------------+---------------------------------------
(Jan)                                                                31538.0000
(Feb)                                                                32069.0000
(Mar)                                                                32213.0000
(Apr)                                                                32917.0000
(May)                                                                33674.0000
(Jun)                                                                35088.0000
(Jul)                                                                36134.0000
(Aug)                                                                36008.0000
(Sep)                                                                33073.0000
(Oct)                                                                32828.0000
(Nov)                                                                31971.0000
(Dec)                                                                33342.0000

OK/INFO - 1241150 - MDX Query execution completed.

     essmsh timestamp: Mon Jul 14 16:50:59 2014

So what about RUNJAVA, and why?

There’s an alternate way of running this MaxL file execute, and it is actually there in the essfunc.xml file although again, it is imperfectly documented.

The syntax (and even the properties) are totally different from @CalcMgrExecuteEncryptMaxLFile.

Start off RUNJAVA with the right object name, in this case com.hyperion.calcmgr.common.cdf.MaxLFunctions.

Then, per the above code, you must pass:
  • The asynchronous flag of false
  • A –D to decrypt the MaxL script (just like off of a command line)
  • The public key for decryption
  • The already encrypted username
  • The already encrypted password
  • The server name
  • Do not pass the application and database
  • Any command line prompts
    • The encryrpted username is command line parameter 1 (but you must still pass it this way)
    • The enrypted password is command line parameter 2
    • The server name is command line parameter 3
    • After that you can pass member names (or whatever) as command line parameters 4, 5, 6, etc.
  • Wrap all parameters in double quotes
  • @LIST and @NAME are not required

This is what the MaxL script looks like.  It’s the same except the $3 became a $4.
spool on to "c:\\tempdir\\encryptest.log" ;

login $key 9404266461315012977165999794704691034001 $key 0307596931591918242060507329306599979470 on localhost;

/*    The below settings are right out of Developing Essbase Applications    */
alter session set dml_output alias off ;
alter session set dml_output numerical_display fixed_decimal ;
alter session set dml_output precision 4 ;
set column_width 40 ;
set timestamp on ;

SELECT {[Measures].$4}
    ON COLUMNS,
{[Year].Levels(0).Members}
ON ROWS
FROM [Sample].[Basic]
WHERE ([Scenario].[Actual], [Product].[Product], [Market].[Market]) ;

spool off ;

exit ;

Two other things about RUNJAVA

Celvin Kattookaran wrote that he thought RUNJAVA was a better way to go than @CalcMgr*.  I’m not sure I agree with him because of the below.

OTOH, I am sure I love stalking him, just like Glenn Schwartzberg loves stalking me.  The cycle of abuse continues.  Celvin, find someone to pick on.  :)

Syntax checking is gone, gone, gone.  

This will syntax check:

For those of you old enough (sob) to remember taking typing drills in school (my grandmother was a business teacher, taught typing, and that meant when we visited her in the summer we could use a – ooooh, electric typewriter – bored geeks like yr. obt. svt. typed that again and again and again, and Grandma, I miss you), that’s from the man that invented touch typing and isn’t exactly part of Oracle EPM.  

In other words, you had best be sure your syntax is right because Essbase (nor Calculation Manager) will tell you that you’re wrong.

Blocks, what blocks?

RUNJAVA, because it is not a calculation block but in essence a command line, could care less about a block existing, or not.  It’ll run just fine with no blocks, e.g.:
MAXL> login $key 9404266461315012977165999794704691034001 $key 0307596931591918242060507329306599979470 on localhost;

OK/INFO - 1051034 - Logging in user [hypadmin@Native Directory].
OK/INFO - 1241001 - Logged in to Essbase.

MAXL> alter session set dml_output alias off ;

OK/INFO - 1056226 - Session altered for user [native://nvid=c9d9665628b8695a:6d29254d:1410456fa26:-7adf?USER].

MAXL> alter session set dml_output numerical_display fixed_decimal ;

OK/INFO - 1056226 - Session altered for user [native://nvid=c9d9665628b8695a:6d29254d:1410456fa26:-7adf?USER].

MAXL> alter session set dml_output precision 4 ;

OK/INFO - 1056226 - Session altered for user [native://nvid=c9d9665628b8695a:6d29254d:1410456fa26:-7adf?USER].


     essmsh timestamp: Mon Jul 14 19:20:02 2014

MAXL> SELECT {[Measures].Sales}
  2>     ON COLUMNS,
  3> {[Year].Levels(0).Members}
  4> ON ROWS
  5> FROM [Sample].[Basic]
  6> WHERE ([Scenario].[Actual], [Product].[Product], [Market].[Market]) ;

Axis-1                                  (Sales)                            
+---------------------------------------+---------------------------------------
(Jan)                                                                  #Missing
(Feb)                                                                  #Missing
(Mar)                                                                  #Missing
(Apr)                                                                  #Missing
(May)                                                                  #Missing
(Jun)                                                                  #Missing
(Jul)                                                                  #Missing
(Aug)                                                                  #Missing
(Sep)                                                                  #Missing
(Oct)                                                                  #Missing
(Nov)                                                                  #Missing
(Dec)                                                                  #Missing

OK/INFO - 1241150 - MDX Query execution completed.

     essmsh timestamp: Mon Jul 14 19:20:02 2014

Again, think about some of the ramifications of the above.  They’re pretty heady, aren’t they?  I’ll come back to them in part three of this series.

That’s all for today

Has your interest been piqued?  We haven’t even gotten to the use case for this and it’s already been a huge amount of work.  No kidding, I spent almost two full man weeks trying to figure all of the above (and a bit more) out for Kscope14.  It was fun and painful, all at the same time.

Have you guessed where I’m taking this?  Send me your comments care of the commend section below.  C’mon, I’m lonely.

Hopefully that was enough for you – that’s over 16 pages and over 2,700 words.  Given that the above is 100% free, surely you’ve gotten value for money.

Be seeing you.