Just seen a link to a paper on what’s new in SP2 on http://www.sqlis.com:
Here’s the bits relevant to Analysis Services:
Analysis Services
- Microsoft Office 2007 requires the installation of SQL Server 2005 Analysis Services SP2 to support all of its business intelligence features. Features of Microsoft Office 2007 that require SP2 will be disabled when running against an instance of Analysis Services that does not have SP2 installed.
- The performance of local cubes, grouping, and subselects have been substantially improved.
- MDX query performance has been improved with respect to subselects, arbitrary shapes, running sum calculations, visual totals, ROLAP dimensions, cell writeback, many-to-many dimensions, 64-bit NUMA hardware, semi-additive measures and unary operators.
- The functionality of subselects has changed.
- A warning message now appears when a user-defined hierarchy is not defined as a natural hierarchy.
- The MDX Drilldown* functions have a new argument that allows you to specify drilldown on specified tuples only.
- The SCOPE_ISOLATION property has been added to the MDX CREATE MEMBER function.
This property enables session-scoped and query-defined calculations to be resolved before calculations in the cube rather than after.
- Numerous functionality and performance-related bugs have been incorporated.
Specifically, improvements have been made to incremental processing, usage-based aggregation design algorithms, backward and forward compatibility, parent-child security, partition query scalability, cell writeback and the Time Intelligence Wizard.
- The performance and functionality of the neural network viewer has been improved and support for multiple nested tables has been added.
- The performance of naïve bayes predictions have been improved through caching of commonly used attributes.
- Neural network training has been improved through better utilization of memory with sparse training data sets and better utilization of multiple threads during error computation (SQL Server 2005 Enterprise Edition feature).
- Limited support for data mining viewers with local mining models has been added.
- The redistribution of data mining viewer controls is now dependent upon ADOMD.NET.
All very vague… I hope they’re planning on releasing some more detailed documentation on each of these. I know some people in Redmond are aware of how poor the documentation for AS has been in the past; let’s hope they’ve been listened to.
UPDATE: you can download the CTP here:
UPDATE: ok, I take it back… at least, some of it. Here’s the KB article containing a list of all of the bugs fixed in the CTP:
But there aren’t just bug fixes, there are some quite important changes in behaviour too. At least Mosha is blogging about them.
I\’m especially interested in "The functionality of subselects has changed.". I wonder if sub-selects now set the current coordinate? I will probably be testing this today.
Unfortunately, the changes in the november CTP of SP2 do not include sub-selects setting the current coordinate. The following query on the AW database indicates this.
WITH MEMBER [Measures].[LY] AS([Measures].[Internet Order Count], [Date].[Calendar Year].prevmember)SELECT {[Measures].[Internet Order Count], [Measures].[LY]} ON 0FROM (SELECT [Date].[Calendar Year].&[2003] ON 0 FROM [Direct Sales])
NULL is returned for the measure LY. If the coordinate had been set by the subselect, the behavior should have been equal to that of the following query, where 2,677 is returned for LY:
WITH MEMBER [Measures].[LY] AS([Measures].[Internet Order Count], [Date].[Calendar Year].prevmember)SELECT {[Measures].[Internet Order Count], [Measures].[LY]} ON 0FROM [Direct Sales]WHERE [Date].[Calendar Year].&[2003]
Hmm, have you (or anyone else) tested Excel 2007 RTM to see if it works yet?
to MichaelBarrett:
Use MDX(1) listed below to get your expected result ,
and use MDX(2) to get the reason.
Do not mix up between the from and where clause.
=============================
//MDX(1)
WITH MEMBER [Measures].[LY] AS( [Measures].[Internet Order Count], [Date].[Calendar Year].prevmember)member measures.AAA as [Date].[Calendar Year].currentmember.name SELECT { [Measures].[Internet Order Count], [Measures].[LY] , measures.AAA } ON 0 , {[Date].[Calendar Year].&[2003]} on axis(1) FROM (SELECT {[Date].[Calendar Year].&[2003]} ON 0 FROM [Direct Sales])
===================
//MDX(2)
WITH MEMBER [Measures].[LY] AS( [Measures].[Internet Order Count], [Date].[Calendar Year].prevmember)member measures.AAA as [Date].[Calendar Year].currentmember.name SELECT { [Measures].[Internet Order Count], [Measures].[LY] , measures.AAA } ON 0 FROM ( SELECT {[Date].[Calendar Year].&[2003]} ON 0 FROM [Direct Sales])
use the folloing mdx to see more clearly.
(To explore the where clause ,keep in the similar way.)
I think microsoft is just in the right way.
Just my private opinion.
========================
WITH MEMBER [Measures].[LY] AS( [Measures].[Internet Order Count], [Date].[Calendar Year].prevmember)member measures.AAA as [Date].[Calendar Year].currentmember.name SELECT { [Measures].[Internet Order Count], [Measures].[LY] , measures.AAA } ON 0 , {[Date].[Calendar Year].members} on axis(1) FROM ( SELECT {[Date].[Calendar Year].&[2002] , [Date].[Calendar Year].&[2003]} ON 0 FROM [Direct Sales])