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!