33 posts from: Microsoft OLAP by Mosha Pasumansky
MDX and partitioning
(Indexed 2007-12-19):
The up-to-date version of this post with comments enabled is at sqlblogPartitions have been a built-in feature of Analysis Services ever since the first version. Naturally, all the engine subsystems, including the MDX query optimizer,work well with partitions. There are rare cases, however, when over-aggressive prefetching can generate query plan which is not optimal with respect to the partitioning scheme. Today we will review one such scenario and see how MDX could be rewritten to suit the par...(truncated)...
Optimizing Count(Filter(...)) expressions in MDX
(Indexed 2007-11-23):
As the readers of my blog know, bulk evaluation mode (called "block computation mode" in Katmai) delivers much better performance in MDX than the cell-by-cell evaluation mode. Therefore the most important optimization technique with MDX in Analysis Services is to rewrite MDX in such a way that makes block computations possible. Easy to say, but not always easy to do. With the release of Katmai's CTP5, Microsoft published the BOL article outlining conditions when block computations are and are no...(truncated)...
How to detect subselect inside MDX calculations (aka Multiselect in Excel 2007)
(Indexed 2007-09-27):
The subject of multiselect friendly calculations is a popular one in forums. I have written about it beforehere, here and here. In February 2008 I will write a blog which will give a definite answer on how AS2008 will deal with it (which is follow up on this post). But in the meantime, questions about AS2005 keep coming. By now, most people realize that it is possible to detect presence of set in WHERE clause by using EXISTING operator, which takes care of multiselect queries generated by Excel ...(truncated)...
Announcing MDX Studio CTP1 (v0.1 Alpha)
(Indexed 2007-09-19):
Redmond, WA September 18, 2007 - Mosha Pasumansky today announced the immediate availability of MDX Studio Community Technology Preview (CTP) 1, version 0.1 Alpha. MDX Studio is tool which helps users of Microsoft Analysis Services to analyze complex MDX expressions,monitor performance characteristics of MDX queries and get insight into how MDX interacts with other UDM features such as attribute relationships. MDX Studio provides unique visualizationrepresentation of MDX expression and allows th...(truncated)...
Outline of the 'Deep dive to MDX' PASS presentation
(Indexed 2007-09-06):
My PASS presentation is quickly approaching. Currently there are about twice as many people registered for it than I thought there will be. I am getting a little nervous about it. After all, 7 hours is a lot of time, and I am not sure what people expect from this session. Since there is still time to make changes, I decided to publish the tentative outline of the presentation here, in the hope to get more feedback. The outline is not final, the order of subjects will change for sure, but this sh...(truncated)...
Moving averages in MDX
(Indexed 2007-09-04):
Moving averagesis an important statistical technique for analyzing time series data. It is often used to do forecasting or trend estimations, and it is especially popular for analyzing financial data. There are many different types of moving averages, from simple to weighted to exponential etc. "Moving average" article in Wikipedia is a good starting point to learn about them. In this article we will discuss the implementation of moving averages in MDX. Simple Moving Average The classic way to c...(truncated)...
What is new in OLAP engine in Katmai CTP
(Indexed 2007-08-26):
After writingmy previous blog post about dynamic named sets, I received feedback from multiple people saying the same thing: "How come nobody knew about this great new feature for the last 3 months". Now, one of the constant complains about AS2005 was inadequate documentation. Almost two years after AS2005 release, the documentation improved greatly with several BOL Web releases. In the upcoming AS2008 release, all the new features are properly documented in CTP BOL. However, the problem seems t...(truncated)...
MDX in Katmai: Dynamic named sets
(Indexed 2007-08-25):
The September CTP of Katmai (speculated to bereleased during PASS) is expected to be the first Katmai CTP to contain significant improvements in the OLAP engine. The most important one (which is, in my opinion, is also the most important AS Katmai feature) is changes to the query optimizer, whichare nicknamed "Block computation mode" in the whitepapers and webcasts. I can't wait for it to be released to startblogging about newexecution plans. But while we all wait for this CTP, perhaps it is a g...(truncated)...
AS Query Performance Hands-On-Lab during PASS
(Indexed 2007-08-16):
More information about upcoming SQL PASS summit. We just got confirmation that we will have 3 spots for the"SQL Server Analysis Services Query Performance Comparison Lab". Richard Tkachuk (of www.sqlserveranalysisservices.com fame) is hosting these labs, and I will join himduring Wednesday session. The idea behind it is the following. We will have computers in the lab running the yet-not-released CTP build of Analysis Services 2008 side by side with Analysis Services 2005. People will bring thei...(truncated)...
PASS Presentation"Deep Dive into MDX"
(Indexed 2007-08-14):
SQL PASS organization holds the Summit 2007 this year in Denver, CO from Sep 17 to Sep 21. I always liked attending PASS summits, but this year I have been honored by the PASS committee who offered me to present at pre-conference seminar on the subject of my choosing. Pre-conference seminars don't have the glory of the conference presentation, because they are, well, pre-conference, so the attendance is light, as most people arrive only to the conference itself. However, the pre-conference semin...(truncated)...
BI Survey 7
(Indexed 2007-08-12):
Nigel Pendse has been running "The OLAP Survey" for the last 6 years. It has quickly became the most important and insightful survey of major OLAP tools. Microsoft has purchased every copy of the survey and deeply studied it. We sure learned a lot. The data points collected in the survey made it into the internal specs, presentations and whitepapers. This year the survey is running again, but this time it changed the name to "BI Survey". Despite name change, the essence is the same. I encourage ...(truncated)...
BI Survey 6
(Indexed 2007-07-17):
Nigel Pendse has been running "The OLAP Survey" for the last 5 years. It has quickly became the most important and insightful survey of major OLAP tools. Microsoft has purchased every copy of the survey and deeply studied it. We sure learned a lot. The data points collected in the survey made it into the internal specs, presentations and whitepapers. This year the survey is running again, but this time it changed the name to "BI Survey". Despite name change, the essence is the same. I encourage ...(truncated)...
MDX is like chess. Are you ready to play against Grandmaster ?
(Indexed 2007-06-09):
Warning: This post carries no or very little practical value. It is mostly obscure. However, if you are MDX junkie, you may find it interesting.I read this post by Panorama employee Oz Shal-Bar. It is very well written - it talks about non-trivial issues with MDX interop between different providers, yet it is very entertaining. It isn't written in a dull boring technical staff, but in a captive style typical to detective stories. I wish I could write like that ! And while I don't sympathize with...(truncated)...
Katmai June CTP - Attribute Relationships tab
(Indexed 2007-06-08):
As everybody knows by now, the first widely public CTP of Katmai has been released on Microsoft connect site. It is accompanied by documentation about what are the new features expected in SQL 2008. But this documentation is a little bit too high level, and also it doesn't tell which piece of the planned functionality is included in the June CTP and which one isn't. I am going to cover one feature for the Analysis Services. Since none of the interesting OLAP engine features and performance impro...(truncated)...
Counting 'in-flight' events in MDX
(Indexed 2007-06-02):
Time dimension has special meaning in OLAP and DW. The classic problems involve looking at previous period, parallel period, computing moving averages and running sums. Today we will look into less common, but nevertheless interesting problem of 'in-flight' events. This scenario arises when there is more than one date/time attribute associated with the fact. For example, for the marketing campaign we can have creation date, start and end date for the campaign; for airplanes there is departure an...(truncated)...
Notes about Notes from Microsoft BI Conference
(Indexed 2007-06-02):
I was reading the blog post "My notes from Microsoft BI Conference" by Vidas Matelis. It is a collection of notes recorded at different presentations, chalk talks etc. I felt that I agree with some of them, disagree with some, and have no opinion about some. I felt like it would be useful to write down my thoughts about these notes. Of course, when I don't agree with them - it doesn't mean that I am right - I am merely expressing my opinion on a matter, which is subjective. I also apologize upfr...(truncated)...
Counting Days in MDX
(Indexed 2007-05-28):
Time dimension is special in OLAP. Many MDX functions usually only make sense when applied to Time dimension (PrevMember, Lag, ParallelPeriod, PeriodsToDate, ClosingPeriod etc); semiadditive measures work differently with Time etc. Today, however, we will talk about much simpler subject - counting number of days in the currently selected period. There are all kinds of uses for this metric, for example computing averages over time (this is usually interesting in inventory applications to get aver...(truncated)...
I need your help or how to make multiselect work seamlessly in MDX
(Indexed 2007-05-24):
This post is unusual for me. In this post I am not going to give information to my readers. I am going to ask my readers to give information to me. For quite a some time now I have been looking into problem of interaction between MDX calculations and multiselect. Multiselect is a feature of pretty much every single OLAP client tool, where the user decides to slice not by single hierarchy member, but by several members. The way different client tool generate MDX for the multiselect differs (query...(truncated)...
How to get the today's date in MDX
(Indexed 2007-05-22):
The question in the title of this post is a popular one. There are many scenarios, where it is desirable to navigate to the member of the Time hierarchy which corresponds to the current day, month or year. Sometimes, there is a desire to set the default member to be aligned with the current date. It is especially relevant, when the Time dimension contains non aggregatable attribute, such as Year (i.e. there is no member 'All Years'). By default, Analysis Services sets the default member to one o...(truncated)...
Best practices for server ADOMD.NET stored procedures
(Indexed 2007-04-19):
In this article we will discuss some of the best practices around writing efficient Analysis Services stored procedures using server ADOMD.NET. These days there is plenty of information about writing efficient MDX - in books, whitepapers, blogs, forums etc. Just recently Microsoft released two documents - Performance Guide and Design Best Practices (and I have seen another one, still in the works, which goes deeper into MDX internals than anything else previously published). But there is no simi...(truncated)...
Averages, ratios, division by zero and NON_EMPTY_BEHAVIOR
(Indexed 2007-03-08):
How to get averages and other ratio based MDX calculations demonstrate great performance ? I thought I covered this subject in great depth before, but I probably wasn't concise enough. Last week I was asked to help with performance to very big and important customer (who shall remain unnamed). When I arrived on site, I found the project staff to be very knowledgeable about Analysis Services and MDX. Themodel was designed following all the best practices, they have studied the Performance Guide a...(truncated)...
OLAP Market Shares for 2006 - Microsoft is clear leader
(Indexed 2007-02-22):
Nigel Pendse published his analysis for the OLAP market shares in 2006. The market overall grew at about 16%, but Microsoft OLAP again grew faster than the market. This time much faster - at about 30%, and as a result the Microsoft's share jumped up 4 points, (while pretty much everybody else lost market share, except for Microstrategy and Applix). Microsoft's numbers include all OLAP related income,such as portion ofExcel's PivotTable andBusiness Scorecard Manager, but it is clear, that the mai...(truncated)...
Different forms of CrossJoin
(Indexed 2007-02-21):
This article is about different syntactical ways to write CrossJoin in MDX, all of which are completely equivalent from the functional and performance standpoint. Therefore, the article doesn't convey any practical information, and can be ignored. Readers curious about history of MDX can keep reading, however. CrossJoin is one of the most fundamental MDX functions. According to this survey, it is fifth most popular MDX function. But probably it is underrepresented there, because it counts only u...(truncated)...
You can help in search of Jim Gray
(Indexed 2007-02-03):
Since the Monday, February 29th, the computer science community was in shock. The renowned scientist Jim Gray was missing in the sea after what was supposed to be one day solo sail on his yacht. Like countless others, I always admired Jim. I have reread his classic book "Transaction Processing: Concepts and Techniques" several times, and learned tons from it. For example, I implemented Lock Manager in Analysis Services almost to the letter the way it is described in this book. I first met Jim in...(truncated)...
Analysis Services 2005 Performance Guide is live
(Indexed 2007-01-31):
Finally, after many iterations, discussions, heating debates, editings, reviews etc - it is finally done. Analysis Services 2005 Performance Guide, sporting 121 pages in the dense Word document is officially published today. The Performance Guide for AS2000 proved to be very popular and useful resource. With so many important changes in Analysis Services 2005, and with adoption numbers much bigger than AS2000 ever saw - the similar guide for 2005 version was badly needed. Well, the wait is over ...(truncated)...
Performance of IIF function in MDX
(Indexed 2007-01-29):
IIF is one of the most popular MDX functions,(this was proved by these surveys). Yet, it can cause significant performance degradation, which is often blamed on other parts of the system. Many times it is simple to rewrite the MDX expression to get rid of IIF altogether, and other times it is possible to slightly change the IIF to increase performance. While I and others mentioned this fact several times in blog entries, forum postings etc, people keep using and using it (and using it wrong). Wh...(truncated)...
New book about MDX ?
(Indexed 2007-01-26):
Couple of days ago I received email from Rob Zare about the upcoming book on MDX that he spotted on amazon, called "Problem solving for MDX for SQL Server 2005". Since books dedicated to MDX are rare, I was eager to check it out. Imagine my amazement, when I discovered, that I am listed as one of the authors, along with Rob Zare and Mark Whitehorn. Neither one of us knew anything about it. It's true that about a year and half ago we were thinking about sequel to "Fast Track to MDX", and we even ...(truncated)...
Five things about myself
(Indexed 2007-01-23):
Darren Gosbell has tagged me with "5 things that people don't know about me" game. Here is my version: I was born in Leningrad, USSR as "Misha Pasumansky". My cousin, who was born 3 months later got the same name - "Misha Pasumansky", because both of us were named after our late grandfather, whose name was Moses (Moisey in Russian, but it wasn't very safe name to give to child in Soviet Union). Anyway, two of us eventually ended up in the same high school (although in different classes), and la...(truncated)...
How to aggregate values as a product (multiplication) in MDX
(Indexed 2006-12-19):
MDX has some built-in aggregation functions - Sum, Count, Min and Max. There is also an ubiquitous Aggregate function, which will aggregate values according to the current measure aggregation function, which also adds Distinct Count and all of the semiadditive measures. But what if we need to compute not the Sum, but rather the Product (Multiplication) of values. The example that we will look here is somewhat artificial, but it is done on Adventure Works cube, so it can be repeated. Let's assume...(truncated)...
Display Folders
(Indexed 2006-12-13):
[Image]Display Folders is one of those little features of Analysis Services 2005 which get many people excited. While I personally don't share this excitement, I can understand how display folders can make information more accessible by organizing different metadata objects such as hierarchies, KPIs, measures etc into logical structures. So we are going to take a deeper look into how display folders work, and what does it mean when the client tool claims support for display folders. Let's start ...(truncated)...
What are the most popular MDX functions in AS2005
(Indexed 2006-11-30):
Almost two years ago, in January 2005, I have done a little research on the popularity of MDX functions. The methodology was to search the Analysis Services newsgroup for the names of MDX functions. Since some of the MDX function names are also very common English words - they were excluded from the statistics. But there were enough function left to do interesting analysis. Back in January 2005 most of the posts on the newsgroup were about Analysis Services 2000. About June 2005, Microsoft switc...(truncated)...
Subselects in SP2
(Indexed 2006-11-24):
The list of changes for SP2 contains the following line:The functionality of subselects has changed.Few people have commented that subselects are very important piece of functionality in Analysis Services 2005, and the simply saying "functionality changed" is not enough - they need to understand exactly what changed and what the new behavior is. I will try to take a shot at it in this post. There were actually couple of changes, and we will go from simpler ones to the more interesting ones. Spec...(truncated)...
Performance of running sum calculations in SP2
(Indexed 2006-11-18):
Since I started a series of blogs about AS specific changes in SP2, few people asked me to explain in more details some of the things covered in the SP2 list of changes. The most popular request so far was to explain performance optimizations for running sum calculations. I tried to delay that, because I am writing a article especially about running sums, but I decided not to wait until it is ready for two reasons. First, running sum can mean many different things - there are certainly many tech...(truncated)...