Wednesday, July 27, 2016

Firefox right click menu not working properly with Workspace

As more people continue to move away from IE and adopt Firefox as the default Oracle EPM browser, some run into an annoying little issue.

When performing a right click in Workspace, using Firefox browser, the Firefox shortcut menu overlays the Oracle Workspace menu making it difficult to access the Oracle menu items.




A quick way to get to the Oracle menu is to press the escape key, but having to do that all the time can be annoying.

There is a setting in Firefox that drives this behavior.  The solution to this is to change the Context Menu option in Firefox to True. 

Basic solution


1) Open Firefox and type 'about:config' in the search bar


2) Search for 'dom.event.contextmenu.enabled and change value to 'true'



You can now go into Workspace and the right click menu will work as expected.

Advanced solution

We actually had an interesting time with this for a while.  Once we figured out how to correct the issue we were getting complaints from users that it worked for a while but then would revert back to the old behavior.  This took some more digging and I finally figured out the issue.

Some enterprise deployments of Firefox will actually distribute a 'Firefox preferences configuration file'.  This file has various settings that the administrator wants for the browser by default.  When a user logs in, the file is read and Firefox settings are updated.  So in our case we were changing the option to true but the config file had it set to false, so the next time the users went back into Firefox the options were reseting.

The steps for updating the config file are as follows:

32 bit Windows
  1. Make sure Firefox is closed
  2. Go to C:\Program Files\Mozilla Firefox
  3. Scroll to find mozilla.cfg
  4. Make a copy of the file called mozilla.cfg_old (this is just so you have a backup of original)
  5. Open mozilla.cfg in text editor
  6. Scroll to pref("dom.event.contextmenu.enabled”,false);
  7. Change it to pref("dom.event.contextmenu.enabled",true);
  8. Save and close file
  9. Restart Firefox

64 bit Windows
  1. Make sure Firefox is closed
  2. Go to C:\Program Files (x86)\Mozilla Firefox
  3. Scroll to find mozilla.cfg
  4. Make a copy of the file called mozilla.cfg_old (this is just so you have a backup of original)
  5. Open mozilla.cfg in text editor
  6. Scroll to pref("dom.event.contextmenu.enabled”,false);
  7. Change it to pref("dom.event.contextmenu.enabled",true);
  8. Save and close file
  9. Restart Firefox

Now depending on how your administrator distributes this file you could still wind up with an issue if they overwrite your updated file, but at least now you know where to check.  You could discuss with the administrator and find out why they want it to false and have them change it.  In our case there was a concern this was a security risk, but the admin team agreed it wasn't and changed it back to true.  In the worst case scenario if the admin insists it needs to be false, depending on your willingness to break the rules, you could always create a batch script that runs on login and edits the file.  Of course depending on how strict your company is this could result in you getting fired so take that advice with a grain of salt and exercise your own discretion.  If you are stuck with the option set to false, refer to the beginning of this post where I suggested using the 'Escape' key!


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.

Friday, October 3, 2014

LLS Light The Night Walk, Norwalk CT, 10/24/2014

This year I am participating in the LLS Light the Night Walk in Norwalk, CT to raise money and awareness for the Leukemia and Lymphoma Society.

I am the captain of my corporate team.  We have committed to raising $5,000, and I personally have committed to raising $2,000 of that amount.

LLS is an amazing organization working for an extremely worthy cause. 

Would you please consider supporting me by making a donation at
 http://pages.lightthenight.org/ctwhv/Norwalk14/gcrisci

Thank you for your support!
Gary Crisci

Wednesday, September 17, 2014

ODTUG Board Nomination

I would like to announce that I am running for the ODTUG Board of Directors.  I submitted my nomination this past weekend and I am hopeful I will gain enough votes to be elected to the board.

Below is my campaign statement and Bio.

Voting opens October 7 - October 28 for ODTUG members with a full membership.

I hope you will please vote for me. Thank you.



Campaign Statement:
I have been involved with ODTUG since June 2008.   That year I knew ODTUG was special and my career has never been the same as a result of my involvement.  I would like to continue giving back by becoming a member of the ODTUG BOD.

I have had amazing opportunities working with ODTUG.  I was the president of the ODTUG Hyperion SIG, Vendor lead for KScope12/13, and this year I am the business content lead.  In the years I have been involved with ODTUG I have tried to promote the growth and well being of the organization.  I was instrumental as president of the Hyperion SIG in driving content expansion and I was a strong contributor to building out vendor participation.   I have worked closely with ODTUG, I understand what the organization stands for and how it operates, I am certain I can be a significant contributor as a member of the board.

As a member of the board I will

·      Dedicate myself to making sure the organization stays focused on users and provides an exceptional experience and level of service to each member. 
·      Promote the EPM community tirelessly, but not at the expense of other Oracle technology tracks.  I believe there is room for everyone and we can continue to grow the breadth and scope of the technology areas we focus on.
·      Ensure ODTUG remains the premier provider of in depth Oracle content.

Please vote for me to become a member of the ODTUG Board of Directors.

Thank you


Bio:
Gary Crisci is an Oracle ACE with over fifteen years of finance experience specializing in Oracle Hyperion Solutions software. Gary is a noted expert in the Oracle Essbase field. He has spoken at numerous Kscope conferences and is a co-author of Developing Essbase Applications. Gary will also be a presenter at this year’s Oracle Open World conference.  During his career, Gary has held various positions as a consultant and as an industry professional working for top-tier companies such as Siemens, Morgan Stanley, and General Electric. He is the former president of the ODTUG Hyperion SIG, and he was on the conference committee for Kscope12 and Kscope13. Gary has an MBA in Information Systems. His current role is Senior Hyperion Architect -- Data Relationship Management for General Electric Corporation in Fairfield, Connecticut.



Thursday, March 6, 2014

MDX queries to get Parent/Child and Gen/Lev format (sort of)

I recently stumbled across some interesting MDX functionality.  Unfortunately I have to put the disclaimer right off the bat that sadly this feature may not be of much use given the very poor format of the output, nonetheless this is an interesting way to go about extracting this data and if the user has no other method available to them, it might be helpful.  Perhaps someday Oracle will provide a better facility for formating MDX output and for those of you using MDX behind the scenes to feed a custom app, this should be right up your ally.

The feature I am referring to is the MDX Property Expression.  Basically what this function does is allow you to pull onto your result axis DIMENSION properties for the members you are returning.  The options for what you pull can be quite robust if you structure properly.

So for starters let's say you wanted to query a dimension and return parent child members

You could run the following query:

 SELECT {} ON AXIS(0),
Market.Members DIMENSION PROPERTIES
  PROPERTY_EXPR
   (
    Market,
    MEMBER_NAME,
    Parent(Currentaxismember()),
   "Parent"
   )
ON ROWS
FROM Sample.Basic;


And you would get the following results:

  Axis-1                                            Axis-1.properties                               
+-------------------------------------------------+-------------------------------------------------
 (Market)                                          (Parent = null, type: STRING, )                 
 (East)                                            (Parent = Market, type: STRING, )               
 (New York)                                        (Parent = East, type: STRING, )                 
 (Massachusetts)                                   (Parent = East, type: STRING, )                 
 (Florida)                                         (Parent = East, type: STRING, )                 
 (Connecticut)                                     (Parent = East, type: STRING, )                 
 (New Hampshire)                                   (Parent = East, type: STRING, )                 
 (West)                                            (Parent = Market, type: STRING, )               
 (California)                                      (Parent = West, type: STRING, )                 
 (Oregon)                                          (Parent = West, type: STRING, )                 
 (Washington)                                      (Parent = West, type: STRING, )                 
 (Utah)                                            (Parent = West, type: STRING, )                 
 (Nevada)                                          (Parent = West, type: STRING, )                 
 (South)                                           (Parent = Market, type: STRING, )               
 (Texas)                                           (Parent = South, type: STRING, )                
 (Oklahoma)                                        (Parent = South, type: STRING, )                
 (Louisiana)                                       (Parent = South, type: STRING, )                
 (New Mexico)                                      (Parent = South, type: STRING, )                
 (Central)                                         (Parent = Market, type: STRING, )               
 (Illinois)                                        (Parent = Central, type: STRING, )              
 (Ohio)                                            (Parent = Central, type: STRING, )              
 (Wisconsin)                                       (Parent = Central, type: STRING, )              
 (Missouri)                                        (Parent = Central, type: STRING, )              
 (Iowa)                                            (Parent = Central, type: STRING, )              
 (Colorado)                                        (Parent = Central, type: STRING, )


Some quick find and replace action in Excel (I smell a macro) could easily clean this up.

Let's say you wanted to query Generations

SELECT {} ON AXIS(0),
Market.Levels(0).Members DIMENSION PROPERTIES
  PROPERTY_EXPR
   (
    Market,
    MEMBER_NAME,
    Ancestor
     (
      Currentaxismember(),
      Currentaxismember().Dimension.Generations(1)
     ),
   "Parent_Generation_1"
   ),
  PROPERTY_EXPR
   (
    Market,
    MEMBER_NAME,
    Ancestor
     (
      Currentaxismember(),
      Currentaxismember().Dimension.Generations(2)
     ),
   "Parent_Generation_2"
   )
ON ROWS
FROM Sample.Basic;


Your results would look like this:

  Axis-1                                                                                              Axis-1.properties                                                                                
+---------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------
 (New York)                                                                                          (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = East, type: STRING, )         
 (Massachusetts)                                                                                     (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = East, type: STRING, )         
 (Florida)                                                                                           (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = East, type: STRING, )         
 (Connecticut)                                                                                       (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = East, type: STRING, )         
 (New Hampshire)                                                                                     (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = East, type: STRING, )         
 (California)                                                                                        (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = West, type: STRING, )         
 (Oregon)                                                                                            (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = West, type: STRING, )         
 (Washington)                                                                                        (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = West, type: STRING, )         
 (Utah)                                                                                              (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = West, type: STRING, )         
 (Nevada)                                                                                            (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = West, type: STRING, )         
 (Texas)                                                                                             (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = South, type: STRING, )        
 (Oklahoma)                                                                                          (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = South, type: STRING, )        
 (Louisiana)                                                                                         (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = South, type: STRING, )        
 (New Mexico)                                                                                        (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = South, type: STRING, )        
 (Illinois)                                                                                          (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = Central, type: STRING, )      
 (Ohio)                                                                                              (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = Central, type: STRING, )      
 (Wisconsin)                                                                                         (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = Central, type: STRING, )      
 (Missouri)                                                                                          (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = Central, type: STRING, )      
 (Iowa)                                                                                              (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = Central, type: STRING, )      
 (Colorado)                                                                                          (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = Central, type: STRING, ) 


Your challenge here is to manage the column width of your output.  There is a MaxL command to set the output column width

SET column_width 100;

Fortunately there is no maximum to the length of a column, but I would think a deep hierarchy might be a bit ugly!

Another challenge with the generation format is that you would have to know in advance how many generations your dimension has.

To export in level format, just replace Generations for Levels in the query.



I don't think this method replaces a nice tool like the outline extractor.  But if you are in a jam and wanted to extract a dim very quickly and see the hierarchy, this method could be useful.  Of course I have to wonder why outline extraction to a useful format is not native functionality in Essbase.  I think they made a nice step with the outline extraction feature in MaxL, however it dumps to an XML file that is not of much use unless you further process the file.  If anyone from Oracle reads this - please add functionality to extract an outline to a flat file in a format that you could easily push back via a load rule to build another outline.  We have needed this functionality for years.  And while your at it, please provide a client for running MDX queries and returning the results in a useful format.

Thanks, hope you enjoy!












Tuesday, February 19, 2013

ODTUG KScope comes full circle for me in New Orleans

I remember back in April 2008, it was the first spring following the end of the Hyperion Solutions conferences.  As an avid Essbase user I was nervous about the future of my dear friend.  I figured Oracle wouldn't kill the product and I was right, but in an inadvertent way they did kill a very important part of the Essbase community. 

The solutions conference was an annual event that brought together Hyperion people from all over the country (world actually) to meet and network around technology products that were core to our careers.  Sure the conference was a bit fluff, many of the presentations were marketing slides with maybe a couple of tidbits of useful information, something we affectionately referred to as "Tips and Tricks".  At the end of the conference you walked away with a few nuggets of information and mostly inspiration to try some new things.  It was also an opportunity to network; meeting up with people who you may have communicated with all year via a faceless user forum.  Still, it was like meeting up with an old friend even if it was the first time you ever met each other face to face.

So when Solutions was laid to rest many were left wondering "where do I go now?".  The answer that was put forth to us was OAUG Collaborate.  Communications from Oracle and OAUG threw around the Solutions name and it seemed like things would continue without skipping a beat.  I dutifully paid the price of admission and headed off to Denver with a colleague of mine excited to experience the newest incarnation of the Solutions conference.  Boy was I disappointed!

I'm not going to using the post to speak negatively about my experience at Collaborate, I've voiced that opinion in the past and what's in the past is done.  Today I know a number of top notch "Hyperion" people who are involved with OAUG and I respect the efforts they put forth to try and contribute to the community.

Despite the disappointing experience that was Collaborate 08, I do not regret taking that trip one bit.  The fact is that trip changed the trajectory of my career in many ways.  It was the last day of the conference and I was walking through the conference center when I came upon two of the most well known names in the Essbase community - Tim Tow and Edward Roske.  They asked me what I thought of the conference and I told them I was disappointed and would not be returning the next year.  They told me about this "Essbase conference" they were working on in June in New Orleans with ODTUG and suggested I go.  I explained there was no way I could get funding to attend another conference in two months given what I had just spent on Collaborate.  They explained a little more about what they were doing and said they really thought it would be worth it if I would come, then they asked if I had any good ideas for a presentation.  I explained I had been doing some stuff with MDX around time functionality (something that was significantly missing in early versions of ASO).  They told me if I could get there I could give my presentation.

So I headed home, gave it some thought, and had a conversation with my wife.  I explained that this was something that could potentially be good for my career (it never looks bad on a resume when you are a presenter at a national conference).  My wife agreed and I made the investment to self fund my trip to New Orleans hoping this wasn't going to be a bust.  In the back of my mind I figured what's the worst that could happen, the conference is in New Orleans.  Worst case scenario I spend the week in New Orleans - I could think of a lot worse things.  The fact is I love New Orleans from the food to the night life, the music and the culture, New Orleans is one of my favorite places to go.

Two months later I was in the Sheraton New Orleans reviewing the conference schedule.  I could tell immediately this was going to be an interesting event.  For starters all the sessions were in one room.  There was a single track for Essbase, it amounted to about 175 people all in the same room for the entire conference.  We started off with a symposium hosted by Oracle product managers who were former Hyperion people, then we had back to back technical sessions on Essbase from one highly skilled Essbase person after another.  I started to wonder what I was doing there as a speaker.  Luckily my presentation on MDX and the use of Analytic Dimensions was well received.  By the end of the conference my head was spinning with all I had learned in a matter of four days.  Things I could have never learned in any other setting.  There were no training classes offered like this, there were no local user group meetings like this, there was no other conference like this.  I was absolutely blown away by the experience.  I knew at that moment that I was part of something special and it was going to be huge, there was no way to contain it.  Word would get out and people would come.

I was not alone in my impression of KScope.  Other developers were also beaming about how awesome the conference was (have you ever read Cameron Lackpour's blog?).  Many of us knew this was something special but it wasn't going to sustain itself alone, ODTUG as we learned was a volunteer organization, essentially a user group on steroids (disclaimer: no one I know of within ODTUG has ever taken performance enhancing drugs).  Hyperbole aside this was a user group, so as a user in the community if I wanted to see this continue, I was going to have to put up some time and do my part.  I felt it was worth it and I volunteered to be part of the Hyperion Special Interest Group with ODTUG.

Over the past few years I have continued to be part of ODTUG in different ways.  For one year I served as the President of the Hyperion SIG and for the last two years I have been on the conference committee as Vendor Chair.  Working with ODTUG has been a wonderful experience.  Aside from being some of the nicest people I have met, they are a group of individuals genuinely concerned about their members.  There is such true dedication to the user and what is best for them.  I have witnessed on multiple occasions decisions being made that were not the easiest or most profitable, not the most popular or politically correct, but they were what was best for the users.  That principle consistently drives everything they do.  In a cynical world where it seems like someone is always trying to put one over on you, the strong principles of ODTUG have been refreshing and something I am proud to be associated with.

My investment in ODTUG has paid dividends many times over.  I have developed a network of some of the most talented and intelligent individuals in the industry, I have learned something new at every event, and I have made some good friends in the process.  Those relationships have resulted in projects that helped me get my work published and have increased my value within my own organization.  I owe a great deal to ODTUG and that is why I am proud to be part of the organization.

So here we are five years later returning to New Orleans.  Some things have certainly changed, we are a much larger conference to say the least.  Growth has been amazing over the past five years with each year exceeding the last. 
  • Five years ago we had a single track in one room, there are now seven tracks in EPM/BI plus five other tracks in the traditional Oracle space.  
  • Five years ago the Hyperion track was about Essbase alone, today we cover multiple EPM products including Essbase, Planning, HFM, and tracks focused on business use cases.  
  • Five years ago the vendor expo floor was a small room with a couple of kiosks, today the vendor expo is a force to be reckoned with.  
    • One of the main highlights of the conference the expo floor has grown to include all the major players in the space as well as the niche players who bring their unique personalized solutions for all to experience.  With over 30 Exhibitors and sponsors, the expo floor has become the cornerstone of the conference experience.
  • Five years ago, we had only a handful of presenters, today we have so many more each with unique valuable experiences they are looking to share.
    • We had more abstract submissions this year then ever and the content processing teams worked through a tremendous effort to select the best of the best.
  • Five years ago we only had lectures, today we also have hand on labs that get attendees in front of the software and learning skills by doing not just listening.
KScope has come a long way in five years and it is fun to look back on such a successful history.  What is even more fun is thinking about the future and  what it will be like in another five years.  I am very excited to be returning to New Orleans this June.  My old adage still holds true - worst case scenario I get to spend a week in New Orleans!  But knowing what I know now, I don't worry if the trip will be worth my time, I just worry about how to fit it all in!

Hope to see you there!
http://KScope13.com