Wednesday, June 1, 2016

Big feature comes to Essbase in standard PSU

Introduction

Back when Essbase version 7 came out and we were introduced to ASO, Hyperion also introduced us to MDX.  It was added as the calculation language for member formulas in ASO cubes but we also were able to use it as a query language against the database.  As a bonus we were not limited to just ASO we could write queries against BSO databases too.  Having a powerful query language similar to SQL had tremendous potential for Essbase.  We could write complex scripts to pull very specific data from our cubes and even had the ability to create custom members and sets in the queries alleviating the need to modify the actual outline just to get a calculated result.

Overtime the MDX query language has been improved and Oracle has done some very cool things with it behind the scenes to drive things like OBIEE.  For the average power user though, MDX for Essbase had one major limitation; there was no good way to get the results of the query in a useable format.  When using MDX against Essbase you had a few options to get data.

  1. You could pull the data through EAS using an MDX script.  
    1. While EAS could be useful for writing a script, the result set was returned into the EAS window with no way to get it out to Excel or even Notepad.  Large MDX scripts could time out and sometimes even take EAS down completely.
  2. You could copy and paste your MDX script into the Smartview query window.  
    1. This worked well enough for small to medium size queries.  Large queries would tend to cause issues with APS and Excel.  In addition, Excel became unresponsive while the query was running, so once you kicked it off you had to wait for it to complete.  If it was a 20 minute query, you weren't doing anything else in Excel in that time.  This also meant you had to run the query manually with no way to automate or run multiple queries as a batch.
  3. Running the queries via MaxL.  
    1. MaxL had the benefit of running the query in the background allowing you to run larger queries that typically didn't time out.  You could pull back really large data sets and you could write a script to run multiple queries in a batch.  The problem with MaxL was the output was not useable (see samples below).  MaxL did not allow for a delimiter so results were fixed width; that itself was not a show stopper but it was often problematic.  The real issue however was that MaxL would echo additional info in the result set about the query itself.  It would echo the query before the result set and then insert other info about the process.  If your intention was to have a user run these queries or to feed this data to another system you found that no one was going to be happy because you would need to clean up the result set before it became useable.  This meant a one off query could be done, but you weren't going to be able to use this as a standard solution.
  4. Use a third party tool.  
    1. While a few have been developed getting access to them usually involved additional licensing fees and adding another component to the system.
So for me looking at all of those options, MaxL would often seem to be a good choice except for the formatting issues.  At least that was the case up until now!

The Fix

I've been a vocal proponent of fixing this functionality for a long time, often discussing it at conferences with product and development managers, and submitting enhancement requests.  Recently I had the opportunity to work with the Essbase development team on some features and the issue of MDX output came up.  We talked through the short list of what was needed to get over this hump and the development team was very responsive.

With this latest release of Essbase 11.1.2.4.010, Oracle has added a few key enhancements to MaxL that will allow you to modify the MDX output as a clean and useful data set.  The rest of this post will be dedicated to these new features and how to use them.  I will also discuss the use of some existing MaxL features that will be needed to get the output the way you want it.

One more very important point to mention is that these enhancements are not limited to just MDX output, they also work for DISPLAY and QUERY commands. 

11.1.2.4.010 new features in Maxl

  • set column_separator
    • Allows you to define a delimiter to separate your columns.  This could be a comma, tab, vertical bar, or pretty much any other characters.  As a nice bonus the dev team made this a multi-byte delimiter, meaning you can specify a sequence of characters as your delimiter.  This comes in real handy for when you have that brilliant financial analyst who thought he was being so cool when he decided to use a pipe (|) in his member descriptions and inadvertently broke all the exports going from one system to another.
    • Examples:
      • Pipe - set column_separator "|";
      • Comma - set column_separator ",";
      • Tab - set column_separator " ";
      • Multi character - set column_separator "~#";
  • set column_header
    • This setting has an ON | OFF switch allowing you to have column headers in your result set or suppress them.  In most cases you will want them on, but turning them off can be useful particularly when passing the output to another system.  In case you were wondering, it shows the column heading for dimensions in the rows as well, even when using complex Crossjoins.
    • Examples: 
      • On - set column_header ON;
      • Off - set column_header OFF;
  • set echo_mode
    • This setting has an ON | OFF switch that allows you to suppress all that excess content I was describing above.  By setting this to OFF, you will no longer get the query repeated back in the result set or any of the other system messages that you would find in prior versions.
    • Examples: 
      • On - set echo_mode OFF;
      • Off - set echo_mode ON;

Existing features in MaxL you will need or want to get this working

  • set column_width
    • The set column width feature for this use case is being used as a kind of a safety feature.  If you don't set it large enough there is a chance some data can get cut off.  This is especially true in some of the DISPLAY result sets.  So to be safe we set it really high since it has no downside impact when using a delimiter
    • Example: 
      • set column_width 10000;
  • set message level
    • Even with echo_mode off certain messages will show up in your result set if you allow them to.  To be safe, I suggest setting this to only allow FATAL messages through.  Basically if you have a fatal error your query is not going to finish anyway.
    • Example:
      • set message level fatal;
  • alter session set dml_output
    • dml_output allows you to apply some formatting to your query results.  Note that MDX output is not a replacement for the fully featured report script.  However there are a few key alters you will want to consider when generating an MDX output set.
      • Turning aliases on and off.  Often I will get requests from users to either turn aliases on or off when running a query.
        • Examples:
          • alter session set dml_output alias off;
          • alter session set dml_output alias on;
      • Formatting numbers (i.e. decimal places) is often useful when running a query
        • Examples:
          • alter session set dml_output numerical_display fixed_decimal;
          • alter session set dml_output precision 2;
      • Turn off cell status.  Cell status will return additional information about each cell.  In addition to providing extra data you don't want, on really large queries it could even impact performance.
        • Examples
          • alter session set dml_output cell_status off;

Samples

Sample 1

This is an example of a basic MDX query and the result set generated in 11.1.2.4.009.  In other words this is a "Before" example.

Script

login 'user' 'pwd' on 'host';
set message level fatal;
alter session set dml_output alias off;
alter session set dml_output cell_status off;
alter session set dml_output numerical_display fixed_decimal;
alter session set dml_output precision 2;
spool on to 'c:\Temp\CO\output.txt';

select {[May],[Jun]} on AXIS(0), 
{[100],[200]} on AXIS(1) 
from [Sample].[Basic];

spool off;
logout;
exit;

Output

MAXL > ; select {[May],[Jun]} on AXIS(0), 
     > ; {[100],[200]} on AXIS(1) 
     > ; from [Sample].[Basic];

 Axis-1              (May)               (Jun)              
+-------------------+-------------------+-------------------
 (100)                           2571.00             2859.00
 (200)                           2302.00             2445.00


Sample 2

This is the same query run in 11.1.2.4.010 with new features

Script

login 'user' 'pwd' on 'host';
set column_width 10000;
set message level fatal;
set column_separator "|";
set column_header on;
set echo_mode off;
alter session set dml_output alias off;
alter session set dml_output cell_status on;
alter session set dml_output numerical_display fixed_decimal;
alter session set dml_output precision 2;
spool on to 'c:\Temp\CO\output.txt';

select {[May],[Jun]} on AXIS(0), 
{[100],[200]} on AXIS(1) 
from [Sample].[Basic];

spool off;
logout;
exit;

Output

Product|May|Jun
100|2571.00|2859.00
200|2302.00|2445.00

Notes

You will see that in the second query we do not get the actual query returned to us in the spool.  We do not get mention of "AXIS(1)" as a column header, we get the actual dimension name for the rows.  We do not get the repetitive dashes as a line break -------------.  You will also note that column headers are not enclosed in parenthesis.  This output can be provided to any user to open in Excel or import into another application.

Sample 3

This is the same query run in Sample 2 but with column headers turned off

Script

login 'user' 'pwd' on 'host';
set column_width 10000;
set message level fatal;
set column_separator "|";
set column_header off;
set echo_mode off;
alter session set dml_output alias off;
alter session set dml_output cell_status on;
alter session set dml_output numerical_display fixed_decimal;
alter session set dml_output precision 2;
spool on to 'c:\Temp\CO\output.txt';

select {[May],[Jun]} on AXIS(0), 
{[100],[200]} on AXIS(1) 
from [Sample].[Basic];

spool off;
logout;
exit;

Output

100|2571.00|2859.00
200|2302.00|2445.00


Sample 4

This is the same query run in Sample 1 but a tab delimiter

Script

login 'user' 'pwd' on 'host';
set column_width 10000;
set message level fatal;
set column_separator " ";
set column_header on;
set echo_mode off;
alter session set dml_output alias off;
alter session set dml_output cell_status off;
alter session set dml_output numerical_display fixed_decimal;
alter session set dml_output precision 2;
spool on to 'c:\Temp\CO\output.txt';

select {[May],[Jun]} on AXIS(0), 
{[100],[200]} on AXIS(1) 
from [Sample].[Basic];

spool off;
logout;
exit;

Output

Product May Jun
100 2571.00 2859.00
200 2302.00 2445.00

Notes

You will see that to get tab delimiter I simply hit the tab key between two double quotes.


Sample 5

This is a similar query but now adding additional dimensions to the rows with the use of a Crossjoin

Script

login 'user' 'pwd' on 'host';
set column_width 10000;
set message level fatal;
set column_separator "|";
set column_header on;
set echo_mode off;
alter session set dml_output alias off;
alter session set dml_output cell_status off;
alter session set dml_output numerical_display fixed_decimal;
alter session set dml_output precision 2;
alter session set dml_output get_missing_cells off
spool on to 'c:\Temp\CO\output.txt';

select NON EMPTY 
{[Year].children} on AXIS(0), 
{CrossJoin([Market].children, [Product].children)} on AXIS(1) 
from [Sample].[Basic];

spool off;
logout;
exit;

Output

Market|Product|Qtr1|Qtr2|Qtr3|Qtr4
East|100|2747.00|3352.00|3740.00|2817.00
East|200|562.00|610.00|372.00|990.00
East|300|591.00|922.00|522.00|592.00
East|400|1480.00|1615.00|1712.00|1537.00
East|Diet|555.00|652.00|644.00|557.00
West|100|1042.00|849.00|899.00|759.00
West|200|2325.00|2423.00|2540.00|2439.00
West|300|2363.00|2739.00|2937.00|2692.00
West|400|1407.00|1504.00|1563.00|1380.00
West|Diet|2025.00|1975.00|2100.00|1987.00
South|100|1051.00|1198.00|1312.00|1212.00
South|200|1465.00|1540.00|1612.00|1498.00
South|300|561.00|529.00|591.00|669.00
South|400|#Missing|#Missing|#Missing|#Missing
South|Diet|1146.00|1289.00|1310.00|1167.00
Central|100|2208.00|2473.00|2560.00|2249.00
Central|200|2369.00|2457.00|2481.00|2271.00
Central|300|2414.00|2579.00|2648.00|2450.00
Central|400|2118.00|2317.00|2423.00|2245.00
Central|Diet|3291.00|3420.00|3478.00|3230.00

Sample 6

This is a similar query to Sample 5 but now using multiple axis vs. Crossjoin.

Script

login 'user' 'pwd' on 'host';
set column_width 10000;
set message level fatal;
set column_separator "|";
set column_header on;
set echo_mode off;
alter session set dml_output alias off;
alter session set dml_output cell_status off;
alter session set dml_output numerical_display fixed_decimal;
alter session set dml_output precision 2;
alter session set dml_output get_missing_cells off
spool on to 'c:\Temp\CO\output.txt';

select NON EMPTY 
{[Year].children} on AXIS(0), 
{[Market].children} on AXIS(1),
{[Product].children} on AXIS(2)
from [Sample].[Basic];

spool off;
logout;
exit;

Output

Market|Product|Qtr1|Qtr2|Qtr3|Qtr4
East|100|2747.00|3352.00|3740.00|2817.00
East|200|562.00|610.00|372.00|990.00
East|300|591.00|922.00|522.00|592.00
East|400|1480.00|1615.00|1712.00|1537.00
East|Diet|555.00|652.00|644.00|557.00
West|100|1042.00|849.00|899.00|759.00
West|200|2325.00|2423.00|2540.00|2439.00
West|300|2363.00|2739.00|2937.00|2692.00
West|400|1407.00|1504.00|1563.00|1380.00
West|Diet|2025.00|1975.00|2100.00|1987.00
South|100|1051.00|1198.00|1312.00|1212.00
South|200|1465.00|1540.00|1612.00|1498.00
South|300|561.00|529.00|591.00|669.00
South|400|#Missing|#Missing|#Missing|#Missing
South|Diet|1146.00|1289.00|1310.00|1167.00
Central|100|2208.00|2473.00|2560.00|2249.00
Central|200|2369.00|2457.00|2481.00|2271.00
Central|300|2414.00|2579.00|2648.00|2450.00
Central|400|2118.00|2317.00|2423.00|2245.00
Central|Diet|3291.00|3420.00|3478.00|3230.00

Notes

You will see that even with multiple Axis the column headings line up nicely.

Sample 7

The handling of #Missing poses some issues as there is still no way to remove #Missing from an MDX result set.  Structuring the query a certain way and making use of the NON EMPTY directive can usually overcome this.  In most cases you can find a dimension where you are only selecting a single member (think year, scenario, view, etc.) using a single member from one of those dimensions would allow you to place it as the sole member on the column axis effectively making it a "Data" column.  In some cases you could even use an Attribute dimension, although use of attribute could impact performance.

In this example I format the query in such a way to get a level 0 export from Sample.Basic for Actual and Budget.  Note that the if you run this without the NON EMPTY you will get #Missing values in the "Data" column but with NON EMPTY those rows are suppressed.

login 'user' 'pwd' on 'host';
set column_width 10000;
set message level fatal;
set column_separator "|";
set column_header on;
set echo_mode off;
alter session set dml_output alias off;
alter session set dml_output cell_status off;
alter session set dml_output numerical_display fixed_decimal;
alter session set dml_output precision 2;
alter session set dml_output get_missing_cells off;
spool on to 'c:\Temp\CO\output.txt';

WITH
SET [_Year] AS '{Descendants([Year], [Year].levels(0))}'
SET [_Measures] AS '{Descendants([Measures], [Measures].levels(0))}'
SET [_Product] AS '{Descendants([Product], [Product].levels(0))}'
SET [_Market] AS '{Descendants([Market], [Market].levels(0))}'
SET [_Scenario] AS '{[Actual], [Budget]}'
MEMBER [Caffeinated].[Data] AS '[Caffeinated]'

SELECT
{[Caffeinated].[Data]} ON AXIS(0),
NON EMPTY
CrossJoin([_Year], 
Crossjoin([_Measures], 
Crossjoin([_Product], 
Crossjoin([_Market], [_Scenario])
)
)
)
ON AXIS(1) 
FROM [Sample].[Basic]
;

Output (only partial output due to size)

Year|Measures|Product|Market|Scenario|Data
Jan|Sales|100-10|New York|Actual|678.00
Jan|Sales|100-10|New York|Budget|640.00
Jan|Sales|100-10|Massachusetts|Actual|494.00
Jan|Sales|100-10|Massachusetts|Budget|460.00
Jan|Sales|100-10|Florida|Actual|210.00
Jan|Sales|100-10|Florida|Budget|190.00
Jan|Sales|100-10|Connecticut|Actual|310.00
Jan|Sales|100-10|Connecticut|Budget|290.00
Jan|Sales|100-10|New Hampshire|Actual|120.00
Jan|Sales|100-10|New Hampshire|Budget|110.00
Jan|Sales|100-10|California|Actual|678.00

Closing

So there you have it, a simple enhancement with tremendous potential.  With clean formatted output, users can run complex queries and work with the results in a format they expect.

I want to give credit to the Essbase Development and Product management team for working on this and turning it around so quickly.  They really did a great job with this and I am looking forward as a customer to continued collaboration with our primary vendor/partner.

16 comments:

amarnath said...

This really looks awesome

Does the column_seperator works only for the MDX output (or) it will work for any MaxL query that is run

Many times, we execute MaxL statements and parse the output in order to make it more formatted to be able to load and store somewhere

Gary Crisci, Oracle Ace said...

Hi Amarnath
I mentioned in the post that this also applies to any export using the DISPLAY and QUERY verbs.

That is one of the drivers for the large column width setting. When you run an export using DISPLAY or QUERY, the amount of info in the column could exceed the column width if you don't set it high enough.

Joe said...

Rad. Thanks for the full writeup with great examples.

bi_expert said...

This is great.. We can now replace our report scripts using this..

anky_10 said...
This comment has been removed by the author.
anky_10 said...

Hi Gary,
Really helpful post. We just upgraded to 11.1.2.4.010. SO that prompted me to try your post. I tried basic query but I do get query in spool file.

2 questions,
1. Why my spool file still records query lines?
2. Why do I see records with #missing as I am using NON EMPTY

Can you please help me?

Essbase and MAXL version = ESB11.1.2.4.010B014


QUERY on MAXL :

login 'id' 'pwd' on 'ess-server';
set column_width 10000;
set message level fatal;
set column_separator "|";
set column_header on;
set echo_mode off;
alter session set dml_output alias off;
alter session set dml_output cell_status off;
alter session set dml_output numerical_display fixed_decimal;
alter session set dml_output precision 2;
alter session set dml_output get_missing_cells off;
spool on to 'Export.log';

select NON EMPTY
{[FY16]} on AXIS(0),
{[Account].children} on AXIS(1),
{[Period].children} on AXIS(2)
from [RPT].[DB];


spool off;

Output in spool file:

MAXL>
MAXL> select NON EMPTY
2> {[FY16]} on AXIS(0),
3> {[Account].children} on AXIS(1),
4> {[Period].children} on AXIS(2)
5> from [samp].[RPT];

Period|Account|FY16
BegBalance|NetIncome_T|#Missing
BegBalance|BalanceSheet|#Missing
YearTotal|NetIncome_T|29
YearTotal|BalanceSheet|#Missing

MAXL>
MAXL>
MAXL> spool off;

Mark Drayton said...

Hey Gary,

Thanks for your post, it provides another string to the data export bow!

I applied the 012 Patch to my environment and then ran the examples and got a slightly different output:

Product|May|Jun
100|2571.00 < DC , RO > |2859.00 < DC , RO >
200|2302.00 < DC , RO > |2445.00 < DC , RO >

Interestingly, I've had to add spaces into the <> otherwise they're hidden in the HTML.

Have you seen these characters before? Just wondering if a bug was introduced.

Thanks,
Mark

Chris Schulze said...

Thanks, love the new output control features.

Chris Schulze

Chris Schulze said...

anky_10, I have seen the same behavior happen with #Missing output, but only when I have multiple dimensions on the rows. Hope that helps.

rohit rawat said...

Gary,
Does this require 11.1.2.4.010 version of client if I am running maxl from other computer?

amarnath said...

Rohit_rawat

Yes. you would need to have a min of .010 patch (Server & client) to have the output formatted
. I tried with an older version client and the output is not formatted

Gary Crisci, Oracle Ace said...

Sorry I did not respond to these comments, for some reason I was not getting alerts they were here.

@anky_10 - You need to have both Essbase server and client patched to 11.1.2.4.010
As for seeing #MISSING, you would need to combine Account and Period to AXIS(1) and use NON EMPTY on AXIS(1) to get your desired result.

Mark - you are seeing member properties, I would need to see your entire script to resolve.

Vesko said...

Gary,
Thanks for great post.
It is nice that we can spool output to a file. I see one problem. I am not able to suppress output to show on the screen. That is very annoying in case that output is big.
Any suggestion?

Gary Crisci, Oracle Ace said...

I don't know of a way to do that, I believe that is nature of the way MaxL works; basically the spool command is just spitting everything out. In this case they are giving ability to suppress some of the content.

I tend to run these via batch, so I don't mind it as much, especially if it is scheduled to run on a server.

Chris Schulze said...

In Windows and Unix on the command line you can always code whatever.bat >nul or whatever.sh >nul, but that obscures all bat/shell script stdout output, not just MaxL output.

Sébastien ROUX said...

Hey Gary, this article you wrote is quite useful as you can see. I succeeded in implementing my MDX query however having this strange set of characters ()... something I am missing maybe?

Thank you and please keep on blogging Gary!

NN-00-0006000152 P-100101001010001010 24240.00 <> -373.62 -373.62
NN-00-0006000152 P-100101001010001030 2223.00 <> 6442.47 6442.47
NN-00-0006000152 P-100101001010001040 186447.00 <> -796.62 -796.62
NN-00-0006000152 P-100101001010001080 173517.00 <> -763.89 -763.89
NN-00-0006000152 P-100101001010001100 71298.00 <> 4631.75 4631.75
NN-00-0006000152 P-100101001010001140 41997.00 <> 1212.86 1212.86
NN-00-0006000152 P-100101001010001170 81728.00 <> 1025.16 1025.16