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














Monday, January 23, 2012

Dusting off this blog

Well - it has been a while since I have posted to my blog and figured it was time I get something new out there. I've been pretty busy lately, which puts me in the paradox of having lots to share on my blog, but not enough time to write blog posts. Ironically when I have time to blog, that usually means I don't have anything to talk about. But this is an exception and I am motivated to publish some information given a number of things going on, particularly on the heels of my recent trip to San Antonio Texas to check out the site of this year's ODTUG KScope12 conference.

But before I get into that, first I would like to share some news that will also explain my hiatus from blogging. Over the past year I have been working on a book project with a number of well known and highly respected Oracle Essbase professionals. If I am to be judged by the company I keep, then I have managed to work my way into something special. This project is headed up by friend, colleague, and fellow ODTUG'r and Oracle Ace Cameron Lackpour. Cameron is a top notch Essbase guy and apparently the only one among us who was brave enough to take on the project manager task and actually secure a publisher for our content. For that we are all eternally indebted to Cameron.

The book is a compilation of various Essbase content that even the most seasoned Essbase developer should be able to pick up some tips from. I had the privilege of authoring the MDX chapter. So for anyone who reads my blog, you can now get the same information I provide here for free buy purchasing the book :) I know that doesn't seem like the best deal, but I assure you I compiled a good amount of information on MDX that has not made its way to my blog or OTN posts. Beyond a fascinating chapter on MDX, my co-authors have written on various topics from ASO optimization to how to perform a successful Essbase implementation and various other topics that any Essbase developer would be interested in. The book is in final drafts and should go to print soon. I will be sure to update more on that as dates become available.

In addition to working on the book project and my full time job I have also had the opportunity and privilege to be a part of the ODTUG KScope12 conference committee this year. My role is Vendor chair and I am excited to be working with such a top notch organization as ODTUG and their conference partner YCC. This year's conference is shaping up to be the best so far and while I know I say that every year, so far I have not been wrong. Each year this conference manages to surpass itself, when it seems impossible to do so.

For starters, interest in the conference this year is the greatest it has been as evident by the record number of content abstracts received and the number of registrations to date. It is clear that the message is out there that ODTUG KScope is an amazing conference opportunity for people in the Oracle community, particularly in the EPM space, to get fresh up to date information on product functionality and future direction directly from Oracle product managers, along with the best in-depth training opportunities. In the past I raved about the technical deep dive sessions and the hands on labs. This year will be no different, but add in the fact that we have broken through the tech-manager barrier and will have a good amount of business content as well. One thing we know in the former Hyperion world is that many of the people using the products are not tech people. They should not feel out of place at KScope, there is a place for them too with sessions devoted specifically to the challenges and solutions end users in the business face. This years content is going to be amazing and for anyone who is a fan of my MDX sessions, I will presenting again this year and you can be sure after spending the last year writing about MDX for the book project, the content will be spot on.

If I sound particularly excited about the conference and you are wondering why am I babbling on about it now a full five months before the conference, it's because I just got back from San Antonio where I had an on-site meeting with the ODTUG board and the rest of the conference committee. Normally I don't too excited about the location of the conference. When I go to a KScope conference I don't tend to see much daylight, so aside from a few social events, the conference could be in a basement and it wouldn't be all that much different to me. Despite my lack of appreciation for location, ODTUG has consistently chosen unique and enjoyable locations for their conferences. Staying off the typical conference belt, ODTUG prides themselves on finding facilities that allow the conference attendee the opportunity to go somewhere they might not typically visit on their own, and leave with an experience that fits the location. I still here people talk about the Queen Mary event in Long Beach California last year, it certainly was a night to remember.

I suppose I also tend to downplay the conference location as I am sensitive to the current state of the economy and how employers are not keen on throwing money towards employees going on a week long junket to some exotic location. It is tough to balance between the two, but I think it is important to keep perspective and recognize that just because the location looks like a good time, that doesn't take away from the exceptional training and networking opportunities available. This year is a bit tougher to sell that since the location chosen for the conference this year is absolutely amazing. San Antonio is a very nice city, I was surprised to learn it is the 7th largest in the U.S. and it is nicely located regardless if you are coming from either coast or anywhere in the middle of the country. However, what really makes this year exceptional is the facility. This year the conference will be held at the JW Marriot Hill Country. I strongly recommend checking out the website to try and get an idea of how nice this property is, although I have to be honest, the website as nice as it is, just can't do it justice compared to being there. The place is huge, modern, clean, with unbelievable amenities. For those of you who would consider bringing your family with you, this is the year to do it. I'm planning to bring my wife and kids and spend a few days after the conference enjoying the location with them.

The conference committee is making special arrangements for family activities in addition to the normal packed itinerary of events to keep spouses and children well entertained during the trip. I can't stress how much I am looking forward to this. I hope you get as excited when you see what is going to be happening at this year's conference. I can tell you that it would be a good idea to sign up early because despite how large the conference facility is, I expect they will sell out. So take a look at the link I provided and be sure to look at the ODTUG KScope12 website to read about all the content and activities being provided this year. I believe this is going to be the best KScope to date and hope you won't miss it.

If anyone has any questions about ODTUG or KScope, please free to leave a comment on this blog.

Best regards - Gary

Thursday, February 17, 2011

SQL queries for Essbase developers

So when I first set up this blog I posted that I would not only write about Essbase all of the time and I would include other content as well. Looking back over my posts, I have not lived up to that too well, so here's one in the "other technology" category. Of course it still has an Essbase slant on it, but even if you are one of those non-believers that uses a different OLAP technology, some of these examples might still be of interest to you.

Recursive (Parent/Child) queries in SQL.
Let's say you are well organized and have your metadata structures in relational tables, rather than just living in some Excel files. Now suppose you want to spin off a cube from one of these structures, but you don't want the entire structure, maybe you just want a particular branch of the hierarchy (i.e. all descendants below a certain parent). Pulling that kind of query in a relational database used to be kind of tough. Here's an example of how to do it.

Let's say you start off with an Outline extract of the Sample Basic database for the Market dimension and you want to query the table to get all descendant of "East".


WITH Descendants (child,parent) AS
(SELECT [CHILD0,Market], [PARENT0,Market]
FROM dbo.Market
WHERE [PARENT0,Market] = 'East'
UNION ALL
SELECT dbo.Market.[CHILD0,Market],
dbo.Market.[PARENT0,Market]
FROM dbo.Market
INNER JOIN Descendants ON dbo.Market.[PARENT0,Market]= Descendants.child)

SELECT DISTINCT dbo.Market.[PARENT0,Market], dbo.Market.[CHILD0,Market]
FROM dbo.Market
INNER JOIN Descendants ON dbo.Market.[CHILD0,Market] = Descendants.child




Your results will be


PARENT0,Market CHILD0,Market
East Connecticut
East Florida
East Massachusetts
East New Hampshire
East New York



Play around with it and see what you can do with it.
Be forewarned, if the table is big this might take a while.
Also note that this "Descendants" feature in MS SQL Server didn't show up until SQL Server 2005.


Another one I have heard is how do I take a parent/child table and turn it into Generations or Levels.

Using a basic Parent/Child table going to Generations would look like this


SELECT a_1.Parent AS Gen1, b_1.Child AS Gen2, c_1.Child AS Gen3, d_1.Child AS Gen4, e_1.Child AS Gen5
FROM (SELECT Parent
FROM dbo.Test_table
WHERE (Parent NOT IN
(SELECT DISTINCT Child
FROM dbo.Test_table AS A))) AS a_1 INNER JOIN
(SELECT DISTINCT Child, Parent
FROM dbo.Test_table AS B) AS b_1 ON a_1.Parent = b_1.Parent LEFT OUTER JOIN
(SELECT DISTINCT Child, Parent
FROM dbo.Test_table AS C) AS c_1 LEFT OUTER JOIN
(SELECT DISTINCT Child, Parent
FROM dbo.Test_table AS D) AS d_1 LEFT OUTER JOIN
(SELECT DISTINCT Child, Parent
FROM dbo.Test_table AS E) AS e_1 ON d_1.Child = e_1.Parent ON c_1.Child = d_1.Parent ON b_1.Child = c_1.Parent
GROUP BY a_1.Parent, b_1.Child, c_1.Child, d_1.Child, e_1.Child



If you want to go to levels it would look like this.


SELECT a_1.Child AS lev0, b_1.Parent AS lev1, c_1.Parent AS lev2,
d_1.Parent AS lev3, e_1.Parent AS lev4
FROM (
SELECT Child
FROM dbo.Test_table
WHERE(Child NOT IN
(SELECT DISTINCT Parent
FROM dbo.Test_table AS A))) AS a_1 INNER JOIN
(SELECT DISTINCT Child, Parent
FROM dbo.Test_table AS B) AS b_1 ON a_1.Child = b_1.Child LEFT OUTER JOIN
(SELECT DISTINCT Child, Parent
FROM dbo.Test_table AS C) AS c_1 LEFT OUTER JOIN
(SELECT DISTINCT Child, Parent
FROM dbo.Test_table AS D) AS d_1 LEFT OUTER JOIN
(SELECT DISTINCT Child, Parent
FROM dbo.Test_table AS E) AS e_1 ON d_1.Parent = e_1.Child ON
c_1.Parent = d_1.Child ON b_1.Parent = c_1.Child



Lastly, let's say you have Generations and you want to go to Parent/Child.
It is important to have a sort key on your source table to maintain the proper order.

So assume the source table looks like

Sort_ID, Gen1, Gen2, Gen3, Gen4

You would create a target table then run some insert queries to cycle through the source table. Lastly I like to run a couple of update queries to tag lev0 members and upper level members.


CREATE TABLE [dbo].[Target] (
[ID] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
[parent] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[member] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[group_item_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]


INSERT INTO [dbo].[Target] ([parent], [member])
SELECT [Gen1], [Gen2]
FROM [dbo].[Source]
GROUP BY [Gen1], [Gen2]
HAVING (NOT ([Gen2] IS NULL))
ORDER BY MIN([sort_key])

INSERT INTO [dbo].[Target] ([parent], [member])
SELECT [Gen2], [Gen3]
FROM [dbo.[Source]
GROUP BY [Gen2], [Gen3]
HAVING (NOT ([Gen3] IS NULL))
ORDER BY MIN([sort_key])

INSERT INTO [dbo].[Target] ([parent], [member])
SELECT [Gen3], [Gen4]
FROM [dbo.[Source]
GROUP BY [Gen1], [Gen2]
HAVING (NOT ([Gen2] IS NULL))
ORDER BY MIN([sort_key])

UPDATE [dbo].[Target]
SET [group_item_flag] = 'G'
WHERE [member] IN (
SELECT DISTINCT [parent]
FROM [dbo].[Target])

UPDATE [dbo].[Target]
SET [group_item_flag] = 'I'
WHERE [member] NOT IN (
SELECT DISTINCT [parent]
FROM [dbo].[Target])



Let me know some of your favorites in the comments.

Been a long time between posts

Wow, it has been a long time between posts. It's not that I haven't wanted to but it just seems things have been so busy lately. I am living the cliche "do more with less". Actually it's just do more with what you got and more and more and more. Oh well, not that I'm complaining. Anyway it seems like Kaleidoscope is coming around the corner soon and that usually gets me to dust off my blog as I get excited about attending. This year will be particularly important to me since I had to miss last year when I had to cancel at the last minute due to personal reasons. I'm sorry to anyone who was planning to see my presentation, I made a point of forwarding all of my content and I know my session wasn't canceled, a fill in speaker took the slot for me and presented my material, so hopefully that made up for my last minute drop out. This year will hopefully not see anything unexpected. I'm working on a very cool MDX presentation for administrators. Two years ago at Kaleidoscope I was co-presenting an MDX presentation with Mike Nader. IN the banter back and forth some questions came up about how much could we really use MDX as a query language. We get that we use it for member formulas but as a stand alone query language how much would we use it? Essbase users are spoiled with our rockin addin and the idea of actually having to code our queries seems like a step backwards to us. Still you can do some pretty cool things with MDX and one thing in particular that came up was when I mentioned that as an admin, MDX had some great functionality for querying the member outline and understanding things about your dimensions and members. It got me thinking that it would make a pretty cool session. So this year that is what people who attend my session will get. I'm going to explore the ways you can use MDX as an administrator to understand your outline. We'll start off simple by returning members of a dimension, then looking at how to pull children or descendants. We'll go through how to pull a list of members with a particular attribute or UDA. Maybe you want to find all members with a member formula or count how many level 0 members you have in a particular dimension. I plan to have a lot of sample code which attendees will be able to take back and use in their own shops. And since MDX can query both BSO and ASO cubes, I'm going to show people how to run an MDX query that will guide them towards optimizing their BSO outlines when ordering their sparse dimensions.
Beyond my presentation, which should be enough to make anyone want to attend :) there are looking to be some amazing presentations this year. Kaleidoscope has grown considerably, there will be more Oracle presence, a larger variety of vendors and partners, the hands on labs are going to be awesome and the usual fun and networking events will be included. I think we are also finally past those few awkward years of vying amongst the different user groups about whose conference is the best or who is going to be the "next solutions" (please, see my previous post) These days I think people have come to recognize that if you are a Hyperion professional and you want the best Hyperion training for the best value, along with a front row seat to all the new product features coming down the line, there is no conference better than Kaleidoscope. I have no doubt this year will live up to and surpass previous years. I'm looking forward to seeing everyone in June. If you haven't registered yet, you really should. http://www.kscope11.com/