SQL PASS in Seattle: What has the UDM Done For You Lately by Dan Bulos
SQL PASS conference is in progress in Seattle right now. Lots of interesting sessions to choose from. I went down there yesterday, and here are some of my notes. The first presentation I really wanted to get on was "SSAS 2005: Planning for Enterprise - Scalability and Performance" by TK Anand and Cristian Petculescu. These guys are on AS product team, they know this staff inside and out. Unfortunately it wasn't meant for me to listen to them. First, I had meeting with Steve Ballmer and Ray Ozzie in the morning. Few selected engineers met with Steve and Ray and discussed the company strategy and things like that (sorry - cannot be more specific for obvious reasons). I was lucky to be included on the list - and this is kind of meeting that you don't want to miss! I rushed to Seattle right after that, but our normal Seattle rain was unusually hard yesterday, with strong winds knocking out electricity all over the place, traffic lights didn't work - horrible traffic everywhere - so I was late for TK and Cristian's presentation. I have the slides, of course, but there is nothing like live presentation. Anyway, I had couple of my (non-Microsoft) friends in the audience who came for the conference, and they were very very very excited. They told me that just that single presentation was already worth all the money and hassle flying to Seattle.
So, I missed that one, but I got into next one, which I also really wanted to go - "What has the UDM Done For You Lately" by Dan Bulos. Now, Dan is a well known figure in BI industry. He built his first cube 28 years ago (right about the time, when I was still a little kid, and to me the word "cube" meant nothing else but one of my toys). Dan has been working with Microsoft OLAP since 7.0 went into first Beta. He really knows his staff. On top of that his presentations are always entertaining, which keeps the audience alert. Very first slide immediately got my attention. Dan was asking a question: "Is UDM a real new innovative thing, or is it just a marketing term ?" Certainly this question has been raised before by Chris Webb, and with all my respect to Chris, I really disagreed with him on that one. So what does Dan think about it ? We went through the presentation, and as we were moving along, I was grinning more and more. Slide after slide, Dan was showing to the audience that UDM is a real deal. He was pointing out that not all things in UDM are groundbreaking innovations, most of them were done before, many are incremental improvements, but things like attribute model, autoexist and other don't have precedence in OLAP space. I almost reached for my laptop and because I wanted to write email to Chris saying - "You should really come and listen to Dan's presentation - this will convince you that you were wrong". By that time we were getting to the last slide, which posed the question again, but this time with the answer. You can imagine how shocked I was, after all these slides, to hear from Dan - "Well, in my opinion UDM is mostly a marketing trick". Quite a surprise for me I guess. Anyway, marketing or not, he did acknowledge that UDM has a potential to be the groundbreaking thing which will turn BI space upside down, but warned that the jury is still out whether it is going to happen or not. I caught up with him later in the evening during vendor demo and we had a little chat. I like to talk to Dan, because I always learn something new from him - he has some refreshing views on the industry and technology. We, of course, argued about the UDM thing, and either one of us failed to convince the other :) There were also some specific things about the presentation that I wanted to clarify, and I think for the benefit of those who were present, here are some of the things we discussed:
1. Dan mentioned, that in calculation scripts the SCOPE combined with assignment is just a syntax sugar on top of CREATE CELL CALCULATION functionality that existed in AS2000. I disagreed. First, AS2005 greatly relaxed restrictions on the expression to be used in the SCOPE. In AS2000, it could either be a single member, or Descendants of single member or all members from hierarchy or level. So, for example, you couldn't SCOPE on two members from the same level ! In AS2005 you can SCOPE pretty much on any set, as long as it doesn't reduce to arbitrary shape subcube. But that's not even the most important change. What is really important about SCOPEs is the fact how you can nest them redefining the subcube they apply to. For example:
SCOPE Root();
this = top-level-budget-whatever;
SCOPE Date.Month.Month.MEMBERS;
this = allocate-down-to-months;
SCOPE (Date.Month.January, {USA, Canada});
First we SCOPE on the Root of the cube (usually this is single cell if all attributes are aggregatable). Then we expand on months to do our allocation. But SCOPEs don't just expand, they can also shrink, so the next SCOPE goes just to January, but expands countries to USA and Canada etc. How would you do this with CREATE CELL CALCULATION which didn't support nesting.
2. Conditions are possible in MDX Scripts. There is an IF statement for that. Now, I always recommend using SCOPE instead, but for scenarios where SCOPE cannot be used, because condition is on cell values - IF statement, or IIF function inside MDX expression will do the job.
3. I absolutely loved how Dan emphasized that all the business logic must be on the server, inside the cube - as part of the data model and in MDX script. I totally sign with him when he said "If you use WITH MEMBER in your query - something is wrong". I can only add, that on top of that - WITH MEMBER is a bad performance choice, since it forces all the caching to happen at the query level, where as if calculated member is defined in the cube - all the caching happens across all users and all sessions and all queries.
4. Probably the funniest moment for me was when Dan discussed what he called "Twists" - which are tricks to transform one dimension into another in MDX. In one particular twist he was showing how to do join between dimensions without going through facts - purely on their keys. He showed a little snippet of MDX Script, and said something along these lines: "When Yukon came out, Mosha wrote a blog about 'bad' MDX functions for AS2005. StrToMember is one of them - but we have to use it here, because there is no other choice". He probably didn't see that I was in the audience. I didn't plan to make comments during presentation, because I know that it can be really distracting and intimidating, but since my name came up I couldn't just shut up. So I asked "What about LinkMember and MemberValue for this example". This caught Dan off-guard and he couldn't find an answer on spot. When we talked about this moment afterwards, we agreed that in that particular scenario the script indeed could've been rewritten to use LinkMember and MemberValue, but there are other cases where it wasn't immediately obvious how to do it. And, BTW, LinkMember was listed in my blog as another 'bad' function to use, so I think the real solution to dimension joins must be some new feature in next version of AS.
5. I also disagreed when he called Parent Child dimensions as not being treated as "First Class Objects". I agree that there are all kinds of issues around Parent Child - like you cannot have two PC hierarchies in the dimension, autoexists doesn't work quite correct in PC, the attribute decoding also doesn't work quite right from Key to other attributes etc. Parent Child is hard. But there are also some things which are designed primarily for Parent Child - like Unary Operators and Custom Member Formulas (although they also work for non PC as well).
6. It isn't a problem that calculated measures don't aggregate up. If the designer need to add calculated measure which has aggregation semantics of a real measure, than there is very simple way to do it. Add a real measure to the measure group, and specify NULL as its column binding. It will cost nothing during processing, the partition size is going to be exactly the same, because AS will easily detect that measure value is constant and will compress it to 0 bits. But then in MDX Script, when expressions are assigned to that measure - they will aggregate up using measure's aggregation function - and it should work very efficiently too.
7. Performance problems with allocations - are things of the past. AS2000 had those performance problems, but I believe that if the allocations are done properly in AS2005 cube, they will be lighting fast. Dan gave me an example of AS2000 scenario, where allocation was taking up to 20 minutes on a specific cube. He teased me by saying that the same thing in TM1 or Essbase would' ve taken only seconds. I challenged him back, saying that I bet that if recreates this logic in AS2005 properly - it will take less than a second. So we made a bet, and I will be waiting for the results. But I am pretty confident about this one :)
Overall - that was a great presentation - it gives plenty of things to think about afterwards.
After that I went to the vendor expo, and had interesting conversations with Q4Bis and RSinteract teams - but perhaps I will write about it in another blog. I also met some people - like finally meeting Deepak Puri in person. Sorry Marco - but we couldn't find you on the floor :( I cannot make it to PASS today, but looking forward to Friday - when Rob Zare does MDX presentation !