Gary Crisci's BI Blog

Dusting off this blog

2012-01-23T15:12:00.003-05:00

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

SQL queries for Essbase developers

2011-02-17T14:21:00.002-05:00

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

2011-02-17T14:01:00.003-05:00

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/

Kaleidoscope is NOT the new Solutions (and guess what, that's a good thing)

2010-03-24T21:23:00.001-04:00

I’ve heard over the last couple of years that this conference or that conference was going to be the “New Solutions”. It got me thinking back to when I went to the old Hyperion Solutions conference and what was so special about it. I remember having a lot of fun when I went, seeing old friends and colleagues, etc, but I was trying to remember what I really got out of the conference. Did I learn anything? Honestly, the answer is not really. At the end of the day, Solutions was a Sales and Marketing conference. It served one main purpose – to get me to buy more software. It wasn’t about making me a better developer. Yes, there were a few technical sessions here and there, but they never got too far below the surface. Most of the time, the sessions were “success stories”. This is where someone who just spent a bunch of money implementing a product stands up in front of a room and describes all the obstacles they have overcome. Mind you, many of these individual’s experience with the software is limited to the implementation they just went through, not exactly “experts”. Of course, I’m not saying there isn’t value in learning from someone else’s experiences. But as a professional who has been through a number of implementations myself, I’m looking for something a little more advanced than the standard fluff you fit in a 45 minute session, where 20 minutes is devoted to telling me about your company. The reality is Solutions wasn’t much of a value. I still found myself paying for training throughout the year and I never brought back anything really amazing to provide value to my employer. Looking back, I am quite unhappy with myself for spending the amount of money I did those years to sit through a live infomercial. I think this is why I have embraced ODTUG’s Kaleidoscope conference as much as I have.

Kaleidoscope is distinctly different from Solutions. Solutions focused on marketing, Kaleidoscope focuses on training. At Solutions they talked about new functionality, at Kaleidoscope you are taught new functionality. A Solutions presentation usually amounted to pretty power point slides, at Kaleidoscope there are hands on labs and many of the presentations contain live demonstrations. The most notable distinction is that Solutions was about a company; Kaleidoscope is about developers.

Kaleidoscope focuses on how to improve the individual and provide value through training, networking opportunities, and a chance to hear from the product managers and developers first hand during the full day symposium. Speakers and presentations are vetted by a panel of industry experts. Speakers aren’t given a slot just because they bought something this year. Speakers and their presentations are chosen based on a mindset of what is going to provide the best educational opportunity for attendees. This mindset permeates throughout all activities that are planned. This ensures attendees are getting the most value for the money they are spending.

Kaleidoscope has become the one event I plan for every year where I know I will get value and improve myself for my career. So, regardless if my company’s budget has room for me to go, I know I will be there because I will pay out of pocket to attend if I have to. I’ve done it before. Now I know some people might not be able to afford that, but I don’t see how I could afford not to attend. In the past, I would have been willing to miss a Solutions conference; I’m not willing to miss a Kaleidoscope conference.

Kaleidoscope 2010 - Hyperion agenda publshed

2009-12-17T11:23:00.003-05:00

I am very pleased to announce that the Hyperion SIG board has finalized the 2010 Kaliedoscope Hyperion schedule. (Please click the link to view) Keep in mind some minor changes could be made in the cases where speakers are unable to attend and stuff like that.

This schedule is one of the best we have had and shows that Kaliedoscope is THE national conference for Hyperion users and developers.

Please check out the schedule and consider making plans to attend.

I would like to thank the entire board for there efforts in putting together such an awesome schedule and thanks to ODTUG for providing us with the forum to host such an event. Special thanks to Edward Roske for being the content coordinator.

although we are taking a short break for the holidays, the board will be reconvening in January to continue our planning sessions to ensure this year's conference exceeds attendee's expectations. Looking forward to seeing all of you there.

Please check out ODTUG Kaliedoscope for more information on the conference.

IsAncestor explanation

2009-10-31T15:20:00.002-04:00

Recently I answered a post on the user forum regarding a way to test if a member is a Descendent of another member using MDX member formula's in an ASO database. It took me a little while to figure it out. I think it's a little hard for Essbase people to understand because of the way we used to do it in BSO using the function @ISDESC. Below was my response and I thought it was worth posting on the blog.

--In calc script language you are saying @ISDESC("C009") meaning you want hierarchical members below "C2009". Now in Calc script we also have @ISANCEST and if we said @ISANCEST("C009") we would be looking for all members that are hierarchically above "C2009". MDX does not have ISDESC, MDX only has IsAncestor. The parameters are member1 and member2. Now depending on which way you ask the question, you get a different answer.
IsAncestor([C009], [Dimension].CurrentMember) is really saying "Is "C009" an ancestor of the current member"? The answer is only true for descendants of "C009". Now, if you were to say IsAncestor([Dimension].CurrentMember, [C009]) you would be asking "is the current member an ancestor of "C009""? The answer is only true for ancestors of C009.

Book Review: Oracle Essbase 9 Implementation Guide

2009-10-31T15:12:00.003-04:00

A while back, I was asked to do a book review for Oracle Essbase 9 Implementation Guide by Sarma Anantapantula and Joseph Gomez by the publisher, Packt Publishing. Despite terrible flashbacks of grade school book reports, I agreed and was shipped a copy of the book. When I first heard this book existed, I rolled my eyes and thought, “this probably won’t be very good”. Then I thought about it a bit, gave some thought to what goes into writing a book, and realized I was being unfair to prejudge. Despite the fact there has historically been very little published content on Essbase, maybe these people got it right and as an active member of the Essbase community, I should embrace the idea of our little world getting more notoriety. It was with this open mind that I began reading the book and hoped to write a positive review.

As I began reading the book, I was encouraged by what I saw. Aside from some minor misstatements in the preface (things like products called ‘Essbase Planning’ and ‘Hyperion Smart Office’) the opening was very good. The authors had an excellent segment defining a data warehouse, frankly, it was one of the best explanations I’ve read on a term that is extremely over used by people to describe many things that are not in fact data warehouses. After reading the preface, I thought to myself “this book is on the right track”. I even made a point of commenting early on that I thought it was a “pretty good book” on a user forum post on Network54. Unfortunately, as I continued to read, I became less and less impressed with the book and about half way through started to feel the book was not what I thought and hoped it would be.

I’m not one of those developers who has a book memory, when working with Essbase I always have a copy of the technical reference and database administrators guide close by, so I’m not going to dive into the technical inaccuracies in the book, although I believe there were many. For the most part, I was not comfortable with the way the book was written. The authors tended to speak very authoritatively about topics that are not absolute. “Essbase is more art than science” is a term repeated ad nauseam in the book, yet the authors took the position in many instances to speak in definitive statements without clarifying what they were saying. Statements like “While the dynamically calculated member occupies a place in the database outline, it does not affect the block size in the database, therefore, it does not affect performance”. They do not clarify what kind of performance they are talking about. Dynamically calculated members with member formulas referencing sparse member sets, most definitely have a performance impact. Referencing dynamically calculated members within a calculation script can also impact performance by engaging the dynamic calculator cache. I realize the book is for beginners and maybe these topics are not appropriate early on in the book, but they needed to be careful making such declarative statements, which they did quite often.

Much of the material in the book can be found in the database administrators’ guide, which has a more thorough explanation. I can accept that in a book of this nature because you would expect the product’s documentation would have all the technical content. What you are looking for in a book like this is the author’s particular point of view and words of wisdom to help understand the technical content. Often I felt the author’s viewpoints were very specific to their own experiences and the book seemed narrow. It was clear that industry experts had not proof read any of the chapters. At the end of the day, you have a book written by a couple of guys who have used Essbase for a while and decided to write a book about it. I don’t say that to take anything away from the effort they put into it, only that it doesn’t have the breadth of experience needed to make it useful to individuals learning how to use this product. The authors often created their own terminology and expressed it as accepted industry jargon. They presented concepts that many would not consider best practices, such as using aliases as the permanent name for a member and codes as the alias. The example given was something along the lines of having a member name as “Hood Esscar Best Dealers” and using an alias for the dealer ID ‘03030-USA’ They then state how you could change the member name and still load to the dealer ID because it is the alias. While this is technically correct, best practice in my experience would be to have the dealer ID as the member name and the name as the description (i.e. Alias). This is an example where I think, in their experience this is the way things are done, but it is not really the best practice.

There are many other examples of things I found to be inaccurate or misleading at best. I won’t get into all of them. Overall, the book was just not written very well, there are many cases when the context of a section will shift direction and it is not clear why, at times, I felt as if someone had accidently cut a paragraph out of the book. This leads me to my primary criticism of the book. The feeling I get was that the book was rushed. The quality was not good and in many cases, it affected the message the authors were trying to get across. This seemed very strange to me that the book would feel rushed because initially I thought it was odd that the book was based on Essbase 9, when Essbase 11 was already general release. I didn’t take this too much to heart because I knew from the Kaleidoscope conference that a large number of users were still on version 9. What I realized as I read was that while technically it was based on 9, often things the authors claimed were based on early releases of version 9, particularly the section on ASO, which was extremely disappointing. It was clear the author’s experience was almost entirely block storage and the feeling I got was that the bulk of what they talked about was relevant in version 6, not so much the newer functionality we have today, with the exception of their discussions on EAS.

Overall, I was not left with a good impression of the book. I would caution new users reading the book to be careful applying what they have learned at face value. While there are some good parts to the book, I found more about it I didn’t like.

Leaving Monterey - So long Kaleidoscope; see ya next year

2009-06-26T12:21:00.001-04:00

So I'm leaving on a jet plane, don't know when I'll be back again (that sounds familiar). Anyway, KScope (that's what the cool kids call it) was excellent this year, a worthy follow up to last year's conference and a great inspiration for next year's conference. I wanted to say thanks to the people who voted me back onto the board of directors, I will be sure to work with the rest of the board to pack as much great content into next year as we can. Of course that is partly based on what all of you send us, so please come up with some interesting abstracts for presentations and submit them when we make the call. I'm also hoping to get more hands on labs next year. there is no teaching experience like when you actually get to do it and the labs were a big success this year.
Have to run to the airport so I'll have to try and post some more at a later time after I settle back in and go through all the presentations. Speaking of hands on labs I am working with Mike Nader to get all his content posted on the ODTUG site, hopefully by early next week.

To all my fellow villagers I say "see ya next year"

Always a villager - Gary

ODTUG Kaliedoscope 2009

2009-06-22T17:49:00.002-04:00

So I finally have a chance to get in a little blog time.

I rolled into Monterey for Kaleidoscope on Saturday, overall not a terrible trip although they did "misplace" my luggage. you'd think if United Airlines was going to charge me extra to check a bag, they might do a better job of not losing it, perhaps I'm asking too much. At least my bags showed up later that evening, so not too bad.

K-Scope is at the Hyatt Regency in Monterey CA this year. The hotel is pretty nice. My first room was a little weird, very clean and nice, but it was at the farthest end of the property and outside my door was a highway. The door had a gap under it that a small animal could sneak in and you could see people's feet as they walked by. Then on Sunday morning I had a visitor. I'm not going to say it was definitely a black Widow spider in the bathroom, because I didn't get close enough to check, but I'm told there in this area and it sure looked like one, at least it did before I killed it. Taking all that into consideration I stayed in the room, it wasn't until today when the wireless internet wasn't working for the third day I asked them to change my room. I think there is something wrong with me if I am willing to put up with potentially poisonous spiders, but draw the line at no internet, but hey, I needed to get on Facebook.

So new room is a lot nicer and "better" internet connection. You'd think if you were going to host a group of developers at a technology conference, you would ensure your network could handle all the traffic, apparently the Hyatt can't. We've brought their network to a halt and no one is thrilled about it. Weather has been holding out pretty well and the food has been decent, but not as good as last year. To be fair though, I didn't expect Monterey to compare with New Orleans when it came to food. I'm heading into town this evening to the Chart House with Interel consulting. If the Chart house is as good out here as the others I've been to, it should be good.

So enough about the hotel and me complaining, let's talk content.

This year the content has again been extremely good so far, the Symposium for Oracle EPM (Essbase) talked about some good stuff coming down the line, most notably procedural scripts for MDX in ASO cubes (ie calc scripts for ASO) also the ability to run MDX against Essbase via PL/SQL. Some people have said they were not as impressed this year with the symposium as last year, but to be fair I think that is more about the fact that last year there was just more really cool new stuff for them to talk about. This year the Essbase updates just weren't as "knock you off your seat" as they were last year when we learned about slowly changing attribute dimensions and storing text values in a cube.

The Smartview session went really well and I think Oracle walked away with a lot of good information to take back with them. Some of the OBIEE stuff looks like it is just about where it needs to be for companies to start looking at it as a serious option.

The welcome reception was very nice last night. The exibitor hall is small when compared with the old Solutions conference, but bigger and better than last year. Interel has Rock Band set up at their booth and you can't walk by without seeing a "group" rocking out.

This morning I gave my first presentation on Calc Scripts with Ed Roske. Overall we got some good feedback. It was a "base" level presentation, but I think we had some good dialog going on with the audience and answered some good questions. I'm hoping we finally solved the myth of "If on Dense/Fix on Sparse".

Later I sat through Matt Millela's CDF presentation which was pretty awesome. He has some great code available on his blog essbaselabs.blogspot.com. Who knew you could have your Essbase database tweet you with updates.

I'm about to head out to an Essbase Studio hands on class with one of my favorite Essbase guys Mike Nader. I have a lot of respect for Mike, especially since he taught me Essbase back when he was a trainer on the east coast. I'm also co-presenting an MDX presentation with him tomorrow. That one is in between my two other sessions "I knew how to do it in BSO now how do I do it in ASO" and my social experiment "Politics of Hyperion". The politics one is going to be either a big hit or a total bust. It's a non-technical presentation focusing on some history of Hyperion and how Essbase has evolved from a "finance" tool to an Enterprise class database system. The interesting thing about the presentation is the angle I am approaching it from. While I have always had Finance and IT positions, my education is actually in Organizational Behavior and so I'm approaching the topic from that kind of academic angle. We'll see how that goes!

Some tips for Kaleidoscope 2009

2009-02-04T15:29:00.003-05:00

I'm not going to post a whole lot about the content being delivered this year at the ODTUG Kaleidoscope conference June 21-25 in Monterey, CA (http://www.odtugkaleidoscope.com/), my fellow Oracle Ace's, Ed Roske, Tim Tow, and Glenn Schwartzberg have each devoted a substantial amount of time on their respective blogs to what is going to be presented at this years conference. Simply put, there is no other conference this year that will match the quality and quantity of content being presented. Check out the blogs and the Kaleidoscope website to get an idea.

Instead, I have decided to post something I believe a lot of people need help with this year in order to attend the conference, which is justifying the cost to their employers. I am hearing a lot of people say they doubt they will be able to get the funds to go this year due to the economy. Therefore, I thought I would put together a list of items you should talk about with your manager/employer to help you build your case why you should go.

1. First and foremost, I think it is important to frame this conference in its proper context. Unlike some other conferences where you spend your day wandering around a conference center from one exhibitor's booth to another collecting as many tee shirts, pens, and squishy things as you can find, or a conference where you go from one session to the next looking at marketing slides, Kaleidoscope is a developer's training conference. This is an important distinction and one you should really drive home with your manager. This is a hard core, "deep dive", hands on week of training. Dollar for dollar you cannot get a better deal on this much training. Even if your company has cut back on Travel and Entertainment expenses, they may still have money put aside for training and you should fight to get that money if you can. Getting your employer to break up your expenses for the trip between T&E and Training could help you get your trip approved.

2. Down play the location. With a name like Hyatt Regency Monterey Resort & Spa your boss will imagine you sitting by the pool, getting massages, and playing golf all week. Show them the schedule, last year I hardly saw the light of day. The sessions run from early in the morning to late in the day. This is not a “put your feet up and relax” conference. Make sure you get the point across.

3. Point out that breakfast and lunch are part of your conference pass. This means your daily cost for meals will be substantially lower than a regular trip where you expense three meals per day. There is also at least one scheduled evening event where food will be served. Sure, we all like to have a good time when we travel, but times are tough, so commit to your manager that you will be extremely reasonable on the nights when you have to buy your own meals.

4. Transportation (This is a big one)

Air - There's not too much you can do about your flights. If you have airline points saved up, this would be a good time to use them. I know you were planning to use them for vacation this year, but what's more important your career growth or flying to someplace you probably shouldn't be spending so much money on anyway? Do what I'm doing, take the family camping this year and use those points to offset the cost of your flight. Your manager will be impressed at your commitment.
If you don't have points, then normally the rule of thumb is buy tickets early to get the best rate, but these days there's no telling with fuel costs whether prices are going to go up or down. I recommend purchasing sooner rather than later. Shop around, use some different websites and find the best deal. Also consider layovers; yes, it is inconvenient but sometimes you can get a lower fare than a direct flight. The weather should be good, so you have less likelihood of having trouble making your connecting flights.

Ground transportation - Make arrangements to have someone drop you off and pick you up at the airport, and save on taxi or parking fees. Last year this was my biggest expense; my company paid more for me to get to and from the airport than my flight. That won't happen this year. On the other end, I confirmed with the Hyatt Regency Monterey Resort & Spa that they have an airport shuttle and get this there's no charge. So skip the taxi fare to and from the hotel. (I should probably mention I posted this on my blog to give them a heads up)

5. If all else fails, consider self-funding the trip. I know that's a tough pill to swallow; believe me I know because last year that's exactly what I did. I also know some people just can't swing it. Nevertheless, if you can manage it, you should. Remember that this is your career and no one is going to look out for you as much as you will so put a pencil to it and see if you can make it happen. As I said, last year I funded my trip to Kaleidoscope because I had already burned up my annual funds on Collaborate (that was a big mistake). I'm really glad I went to Kaleidoscope. The content was unbelievable and I learned more in that week than I had at all other conferences and user group meetings I had ever attended. I am not exaggerating. Therefore, if you can do it you should, using some of my tips above the cost shouldn't hit you too hard and you should be able to write off your trip as a business expense at the end of the year.

So those are my tips, feel free to comment if you have any other tips I might have missed. I hope to see you at the conference this year. I will be giving a few presentations along with a number of other professionals and experts. Remember that it’s the quality of the attendees, which really makes this a great conference. Attendee participation is very high at this conference and there is a wealth of knowledge in these rooms to be tapped. I hope to see you there.

MDX Resources

2008-12-08T15:33:00.004-05:00

Today I received an email from someone asking what resource material I recommend for MDX with Essbase. After I wrote up and sent the response, I thought it would be something good to post, so below is the body of the email I sent:

I would recommend the following reference material for working with MDX for Essbase

If you have block storage experience then the following PDF is very helpful. You can find a copy at http://www.oracle.com/technology/products/bi/epm/pdf/4395_Calc_to_MDX_WP.pdf

This content can also be found in the technical reference under the MDX node; there is a section called Aggregate Storage Topics.

http://download.oracle.com/docs/cd/E10530_01/doc/epm.931/html_esb_techref/techref.htm

Beyond the material provided by Hyperion, there are two books I recommend:

Fast Track to MDX – Whitehorn, Zare, and Pasumansky.

Fast Track is not Essbase specific, it is MS Analysis Services focused, but it provides a good introduction to the basics of MDX that I found very useful. Moshe Pasumansky created the MDX language, so it stands to reason if you are serious about learning MDX you should read at least one book that he has authored or co-authored.

MDX Solutions with Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase, Second Edition – Spofford, Harinath, Webb, Huang, Civardi

Make sure you get the second edition of this book; the second edition is the one that focuses some chapters on Essbase. Again, the content is more on Analysis Services, but at least there are a couple of chapters dedicated to Essbase.

Beyond those resources, practice and experimentation, and leverage the user forums.

http://forums.oracle.com/forums/forum.jspa?forumID=405

http://www.network54.com/Forum/58296/

Dec Update

2008-12-05T17:55:00.002-05:00

Quick update since I haven't posted in a while. I actually have a topic to post on I just haven't gotten around to putting it up, but should have it up soon. Things have been very busy lately, working in the financial services industry is a challenge these days, actually I suppose working in any industry is a challenge these days. Fortunately I do have some distractions, one of which is my participation on the ODTUG Hyperion SIG Board of Directors. Currently the BOD is engaged in putting together the agenda for the Kaleidoscope user conference in Monterey, CA. June 2009. Over the past few weeks, we have been reviewing abstracts and structuring the different tracks for the five days of the conference. I am very pleased with the content we have and really like the way the program is coming together. I strongly urge you to check out http://www.odtugkaleidoscope.com/ and consider attending this year. While expenses are going to be very tight this year for most of us, I strongly suggest you try to attend this conference. Remember one of the main differentiators with this conference is the amount of training you will get. This conference will not be a typical wine and dine vendor junket. The bulk of the conference is devoted to technical training and you will get a lot of it, dollar for dollar there is no better deal on the amount of content you will learn for the price. So I hope to see many of you there and if you do attend, be sure to look for my presentations, I'd love to see you there.

Best Regards

Time Functionality

2008-07-16T16:14:00.006-04:00

Something I've worked on a number of times and even spoke about at the ODTUG Kaleidoscope conference is MDX and specifically how to use it to do time related calcs like period-to-date functions and Time Balancing. I've seen quite a few posts on the subject and figured it was worth putting up some examples here.

More recent releases of Essbase will do time balancing natively, but period-to-date is something you still have to work out. My preferred method of doing this is to add an analytic dimension to the database and set the formula in one of the calculated members. For those of you who aren't familiar with the term, an analytic dimension is one that you add to your database that has a single default load member, all other members are calculated. In the case of time functionality I usually call this dimension [View] and have a default member called [Periodic]. I will then have some other members as needed, like [Time Balance], [QTD], [YTD], etc.

For Example:

The period-to-date functionality is pretty easy using the PeriodsToDate MDX function, the key is remembering to wrap it in a SUM function because the PTD function returns a member set, not a value.

YTD:
SUM(PeriodsToDate([Time].Generations(1), [Time].CurrentMember), [View].[Per])

QTD is almost identical with a modification to the generation reference:
SUM(PeriodsToDate([Time].Generations(2), [Time].CurrentMember), [View].[Per])

Time Balancing is little more involved. First you need to flag the members you want time balanced with a User Defined Attribute (UDA). Once you have that set you have to decide what kind of time balancing you wan to do. If you just want to grab the last child member, that's fairly easy.

CASE WHEN IsUDA([Measures].CurrentMember, "TB") THEN IIF(IsLeaf([Time].CurrentMember), [View].[Per],
(ClosingPeriod ([Time].Levels(0), [Time].CurrentMember), [View].[Per]))
ELSE [View].[Per] END

If you want to show the last value with data, known as Time Balance Skip missing, then you would use something like this:

CASE WHEN IsUDA([Measures].CurrentMember, "TB_Last") THEN
IIF(IsLeaf([Time].CurrentMember), [View].[Per],
IIF(NonEmptyCount ([Time].CurrentMember.Children, [View].[Per]) > 0,
([View].[Per], Tail(Filter(Leaves([Time].CurrentMember),
Not IsEmpty ([View].[Per]))).Item(0).Item(0)), MISSING))
ELSE [View].[Per] END

Now you'll want to modify your QTD and YTD functions to work with your Time balancing

QTD
CASE WHEN IsUDA([Measures].CurrentMember, "TB_Last") THEN
IIF(IsLeaf([Time].CurrentMember), [View].[Per],
IIF(NonEmptyCount ([Time].CurrentMember.Children, [View].[Per]) > 0,
([View].[Per], Tail(Filter(Leaves([Time].CurrentMember),
Not IsEmpty ([View].[Per]))).Item(0).Item(0)), MISSING))
ELSE SUM(PeriodsToDate([Time].Generations(2), [Time].CurrentMember), [View].[Per]) END

YTD
CASE WHEN IsUDA([Measures].CurrentMember, "TB_Last") THEN
IIF(IsLeaf([Time].CurrentMember), [View].[Per],
IIF(NonEmptyCount ([Time].CurrentMember.Children, [View].[Per]) > 0,
([View].[Per], Tail(Filter(Leaves([Time].CurrentMember),
Not IsEmpty ([View].[Per]))).Item(0).Item(0)), MISSING))
ELSE SUM(PeriodsToDate([Time].Generations(1), [Time].CurrentMember), [View].[Per]) END

Alternate Time roll-up in ASO cube with EIS

2008-07-01T16:37:00.006-04:00

Ok, so for my first post I figured I would write about something that just happened the other day that I thought was pretty cool. By the way, this is pretty much going to be the format of this blog, when I do something that I think is cool, I'll write about it and if anyone can get any benefit out of it cool. So as I was saying, I came up against a little challenge the other day and the result came out pretty nicely. At the end of the day I had to create two different time roll-ups in my ASO cube. I thought about actually doing two different time dimensions (what the hell it's an ASO cube I can have as many dimensions as I want) I resisted this temptation and went back to my OLAP basics, would the two dimensions have any meaning if I cross tabbed them in a report? Answer: no; so I went back to alternate roll-up.

Now an Alternate roll-up is certainly nothing new, and probably not worth writing about in and of itself, but this one did pose a couple of challenges. (For those of you who are new, alternate roll-ups are pretty much what they sound like, an alternate way of rolling certain members up in a hierarchy. For example you might have a member called [New York] that rolls up to a member called [North East Region]. You could then share the member [New York] and roll it up under another member like [Major Markets]).

So first let me set the stage. For starters, this database goes down to the daily level. The data set in this application flows on what is called a Production calendar. Basically the Production calendar runs a few days short on each month, so the month of June ends on June 23th and the month of July begins on June 24th. Now we had already hashed this out with the users and had them provide the production calendar for the next two years so I could integrate it with my standard calendar. Now is a good time to mention that I've built my own little master data repository where I store approx 4 - 5 standard hierarchies that I use throughout multiple applications. I have a standard time dim (which also has built in the company's fiscal calendar) then I have a structure for the GL, a Cost Center roll-up, and another one for a Branch hierarchy. For most of these structures I have both Parent/Child and Gen/Level tables so I have a lot of flexibility in using with EIS or SQL load rules. Whenever I build a new app chances are one of these structures will be used so I just go grab them from the central repository. Perhaps I can post another time and get more in depth about what I am doing there. Getting back to this post, I pull down the time structure and I join it with the Prod calendar the users provided me and we built the Production calendar for the app. After a while the users kept coming up with issues they were having, it seems all internal reporting is on Production calendar but anything going external, especially reporting that needed to match up against other third parties was all done on regular calendar (i.e. June is 6/1-6/30). I gave it some thought and came up with a pretty quick way to do it. I already had the regular time roll-up and my time dim in EIS was pointing to a view so I could simply modify my view with a UNION query to bring in the regular time roll-up. I had to do some manipulation to keep the upper level members unique. All I had to do was prefix all Prod calendar members with 'PY' (i.e. [PY2008-Q1]) and then Fiscal year with 'FY' (i.e. [FY2008-Q1]).

The SQL query looked like this

SELECT 'Production Calendar' AS Hierarchy, 'PY' + CAST(prod_yr AS varchar) AS fiscal_year, 'PY' + CAST(prod_yr AS varchar) + '-Q' + CAST(prod_qtr_no AS varchar) AS fiscal_qtr, 'Q' + CAST(prod_qtr_no AS varchar) + '-' + 'PY' + CAST(prod_yr AS varchar) AS fiscal_qtr_desc, 'PY' + CAST(prod_yr AS varchar) + '-' + RIGHT('00' + CAST(prod_mo_no AS varchar), 2) AS month, prod_mo_short + '-' + 'PY' + CAST(prod_yr AS varchar) AS month_desc, CAST(cal_year AS varchar) + '-' + RIGHT('00' + CAST(cal_mo_no AS varchar), 2) + '-' + RIGHT('00' + CAST(cal_day_no AS varchar), 2) AS day, prod_mo_short AS month_attr, prod_mo_sort AS month_attr_sort, cal_day_short AS day_attr, cal_day_of_week_no AS day_attr_sort, weekday, sort_id, NULL AS alias
FROM dbo.Periods
WHERE (US_TRADE_DATE <> 0) AND (prod_yr IS NOT NULL)
UNION
SELECT TOP 100 PERCENT 'Fiscal Calendar' AS Hiearchy, 'FY' + CAST(fiscal_year AS varchar) AS fiscal_year, 'FY' + CAST(fiscal_year AS varchar) + '-Q' + CAST(fiscal_qtr_no AS varchar) AS fiscal_qtr, 'Q' + CAST(fiscal_qtr_no AS varchar) + '-' + 'FY' + CAST(fiscal_year AS varchar) AS fiscal_qtr_desc, 'FY' + CAST(cal_year AS varchar) + '-' + RIGHT('00' + CAST(cal_mo_no AS varchar), 2) AS month, cal_month_short + '-' + 'FY' + CAST(cal_year AS varchar) AS month_desc, CAST(cal_year AS varchar) + '-' + RIGHT('00' + CAST(cal_mo_no AS varchar), 2) + '-' + RIGHT('00' + CAST(cal_day_no AS varchar), 2) AS day, cal_month_short AS month_attr, prod_mo_sort AS month_attr_sort, cal_day_short AS day_attr, cal_day_of_week_no AS day_attr_sort, weekday, 1000000 + sort_id AS sort_id, NULL AS alias
FROM dbo.Periods AS Periods_1
WHERE (US_TRADE_DATE <> 0) AND (prod_yr IS NOT NULL) ORDER BY sort_id

The result set looked like this

Fiscal Calendar

FY2008

FY2008-Q3

Q3-FY2008

FY2008-06

Jun-FY2008

2008-06-30

Jun

Mon

1039627

NULL

Production Calendar

PY2008

PY2008-Q3

Q3-PY2008

PY2008-07

Jul-PY2008

2008-06-30

Jul

Mon

39627

NULL

The next step was to modify my EIS meta-outline to allow for multiple hierarchies. So I went to the properties for the dim and went to the Outline Build properties. I set it to 'Stored for All Alternate Hierarchies'. This setting tells Essbase when building this dimension to make the root member allow multiple hierarchies and set all Gen 2 members to Stored. This was ideal for this app, I didn't want to loose any performance with the alternate roll-up so by having both hierarchies stored I'm able to maximize performance on both hierarchies.

The end result worked out really well

Here's what the Prod Year looked like

And here's what the Fiscal year looked like

Once I had the Time roll-ups in place I loaded the data and ran an aggregation. I cranked it up to about 10x the initial data load size and it flies! Query response is very fast.
End users can now see data both ways.
This was a quick win, total development time was less than two hours.

Feel free to post comments.