In the past our team was critisized in the newsgroups, that whenever we release new CTP, the release notes for Analysis Services are pretty much empty. We will try to make it better, but in the meantime, I decided to write what I thought was interesting or important in the blog. Although there were many interesting and/or important changes in the last 2 months since April CTP14, I decided only to focus on MDX related ones.
MDX Script is executed/verified during cube deployment/processing
This is probably most significant change as far as users will be impacted. We quite hesitated before finally enabling this behavior. Previously, the errors in MDX Script were not raised during cube deployment, but instead any MDX query sent to the cube would fail. We felt that it is better to prevent the creation of the cube rather then let it get created, but useless, since all queries would fail. Also, the UI for processing and deploying the cube knows how to display multiple errors, since MDX Script can have more then single error - but client tools usually just show the last error from the IErrorRecords collection.
There is, of course, a drawback with this approach - what if it takes hours to process the cube, only to fail at the very last step because MDX Script contained small typo. Not an easy decision - and it will be up to the users to tell their feedback on this one. But we provided an escape door. The Cube object now has new property called ScriptErrorHandlingMode, which can be set to IgnoreAll value, in which case all the errors in MDX Script will simply be ignored. This property is important for backward compatibility as well. Often, when we investigate customer issues on AS2K, we discover that their MDX Scripts are full of errors, but AS2K just silently ignores them. This isn't a clean design, but we also cannot break these customers with upgrade to AS2005.
MDXMissingMembersMode
This property is somewhat related to the previous subject. In the previous CTPs, we always set it to value Ignore, since we wanted to emulate SQL semantics closer. However, we quickly discovered, that while it was fine for ad-hoc reporting queries, it was a problem for member names misspelling inside MDX Script. Therefore, we decided that by default MDXMissingMembersMode will apply only to queries, and inside scripts it will always raise an error. But as it usually happens, we then discovered that even in queries we want sometimes to raise errors, regardless of how cube metadata is set, so we also introduced now connection string property “MDX Missing Members Mode“ with values Default, Ignore and Error. Default will respect cube metadata, and Ignore and Error will either always ignore or always raise error on unrecognized member names respectively.
New MDX functions
- Exists function lost the possiblity to specify arbitrary number of filters. All the filters must be specified now as second parameter. Obviously there is no loss of functionality, since all filters can be crossjoined anyway. But Exists didn't only lose, it also gained ! It gained third parameter, which can be measure group name. What this does is it forces Exists like behavior, but on the fact table instead of dimension table. One important point, is that records in measure group are considered even if all the measures are NULL, which is different from NonEmpty function
- CustomData function and connection string property enable application to use from MDX any custom data passed through connection string. This feature will be most useful in controlled middle-tier scenarios
- MeasureGroupMeasures function enables to get list of all measures in the measure group. This sometimes comes handy in writing MDX Scripts which need to SCOPE on specific measure group, as well as in reporting scenarios
- MemberValue function, together with DDL binding for MemberValue, mostly oriented for reporting. Other MDX functions, such as Filter are optimized to work more efficiently with it.
Changes in behavior for Root and Leaves
The changes can be described as simply “they make more sense now”. For example, Leaves now is going to lower granularity only, without reseting slice on the current subcube. This is very intuitive, since if you wrote in the script
SCOPE USA;
SCOPE Leaves();
everybody expects to be on leaves of USA, but before CTP15, you would end up on leaves of everything, because Leaves function overwrites all measure group attributes. With CTP15 it got “weaker” semantics, of changing granularity only.
Aggregate over semiadditive measures
This was one of the tougher problems, but I beleive we now have very elegant solution. Regardless, whether user does multiselect using sets in the WHERE clause, or old-fashioned way with Aggregate function - it will now make total sense over semiadditive measures. Even (especially!) when set crosses non-additive Time dimension. I.e. if measure was LastChild, and set to aggregate over was {[2002], [2005]} - it will go to [2005]. It works even for ByAccount semiadditive measure, when set crosses both Accounts and Time ! Or at least I think it works :)
Better support for empty WHERE clause and subselects
This is yet another change oriented for reporting scenarios. In SQL when WHERE clause evaluates to FALSE, the result is empty rowset. In MDX it used to be that when WHERE clause was empty - we raised an error. Not anymore - it simply will return empty cellset (or rowset). Same with subselects (which actually are closer to SQL's WHERE clause than MDX's WHERE clause is). Additionally, we now also automatically reset current members (default members) inside outer subselect to fall inside inner subselect.
ImpersonationMode for stored procedures
BTW - same is done for Data Source. It is possible now to specify that stored procedure should execute under specific account, or under server account (only server admin can say that), or as current user etc. We even support Windows Server 2003's protocol transition with S4U if ActiveDirectory is set up correctly. And if it is set up correctly, then even EffectiveUserName property will work fine with stored procedures ImpersonationMode. Also, since I mentioned it, EffectiveUserName is much more robust in CTP15, and works correctly even if ActiveDirectory is locked down.
Performance improvements in MDX
There are just too many of them to list everything here. Changes in performance are across the board, but the main focus is on complex calculations. On the related note, CTP15 introduces MDX related perfmon counters. While they are somewhat rudimentary - it is still better then nothing.
Other changes
Well, obviously there were ton of bug fixes - again it is not possible to mention all of them. Areas such as Parent-Child calculations, VisualTotals, Aggregate, calculation precedence, subselects will see most of the improvements.