Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Archive for December 12th, 2007

Microsoft Office 2007 SP1 has been released!

Posted by denglishbi on December 12, 2007

SP1 for Office 2007 was released this week and if you have Office 2007 installed this should have been downloaded with your Windows Updates (checked my applications and it doesn’t appear that this has happened yet).

Office 2007 Service Pack 1

Download the new service packs for your Office 2007 products.

· Office 2007 Service Pack 1

· Visio 2007 Service Pack 1

· Project 2007 Service Pack 1

Not sure if you have SP1 installed yet, then check out this link – How to determine which version of a 2007 Office product is installed

Posted in Downloads | Leave a Comment »

Microsoft Business Intelligence VPC Release 5.1

Posted by denglishbi on December 12, 2007

Microsoft has posted a BI VPC that is fully loaded with all of the BI components on their download site.  There are two links and a couple of files that you will need to download to extract the VPC.  The total download is a little over 4GB and the extracted VHD is around 15GB.  According to the readme file the OS is setup to expire on 3/5/2009. 

Here is what is included on the VPC:

Microsoft Windows Server 2003 R2 Service Pack 2
Internet Explorer 7
Microsoft Office Enterprise 2007
Microsoft Office Visio 2007
Microsoft Office Project 2007
Microsoft Office SharePoint Designer 2007
Microsoft SharePoint Portal Server 2007
Microsoft Visual Studio 2005 w/ Service Pack 1
Microsoft SQL Server 2005 w/ Service Pack 2 (w/ SSIS, SSAS, SSRS)
Microsoft SQL Server 2005 Data Mining Add-ins
Microsoft SQL Server 2005 SharePoint Integration Add-in
ProClarity Analytics Server 6.3
ProClarity Dashboard Server 6.3
ProClarity Desktop Professional 6.3
ProClarity Web Professional 6.3
Microsoft PerformancePoint Planning Server 2007
Microsoft PerformancePoint Monitoring Server 2007
Microsoft PerformancePoint Dashboard Designer
Microsoft PerformancePoint Planning Add-In for Excel
Microsoft PerformancePoint Planning Business Modeler
Project REAL Reference Implementation
SoftArtisans OfficeWriter 3.8.1 and Designer
BI Demo Files
Microsoft SQL Server 2005 Samples
Microsoft SQL Server BI Metadata Samples
Performance Point Planning Samples/Labs
Visual Studio 2005 Tools for Office Second Edition

Here are the links for the downloads: Link1 and Link2.  Enjoy!

Posted in Downloads | Leave a Comment »

SSAS returning wrong results (cache related)

Posted by denglishbi on December 12, 2007

Ran into a rather odd issue today.  End-user reported that the chart being displayed to them was returning the wrong results and different filters were returning the same results.  Here is the example of two queries that I used to validate the results and I was able to reproduce the issue:

SELECT NON EMPTY { { [Date].[Quarter Year].&[2007]&[3], [Date].[Quarter Year].&[2007]&[4], [Date].[Quarter Year].&[2008]&[1] } } ON COLUMNS,

NON EMPTY { Crossjoin({ [Brand].[Brand Label].[Brand1]}, { [Measures].[TBA], [Measures].[TBA(Base)] } ) } ON ROWS

FROM [cube] WHERE ( ( [Market].[Market].[USS] ), { [State].[State].&[Georgia], [State].[State].&[Florida], [State].[State].&[South Carolina] } )

AND

select {[Measures].[TBA], [Measures].[TBA(Base)]} on columns,

non empty([Brand].[Brand Label].[Brand1]) on rows

from cube where ([Market].[Market].&[USS], [Date].[Quarter Year].&[2007]&[3])

If I ran these queries in this order the results for 2007 Q3 were 56% and 1082.  This was correct for the first one based on the WHERE clause, but not for the second MDX statement.  I was really puzzled, but I decided to clear the cache and try again.  This time I ran the queries in reverse order and I got the correct values (second query was 49.6% and 3312.  I found a posting about setting the Cache Policy in the connection string to 9 or setting this at the server level in the msmdsrv.ini file (setting CalculationCoverPolicy to 9 instead of 0 which is the default).  When I did this modification and restarted SSAS everything worked properly no matter which order I ran the MDX statements in.  Really odd behavior still since both queries have different filters for the WHERE clause.  Luckily I saw the Cache Policy setting in Mosha’s posting  and Chris Webb also had a similar posting about this type of issue.  There was also a posting in the Microsoft Forums that I used for reference.

I tested this in SQL 2008 Nov CTP5 and similar results.  When I ran them in this order the second query actually returned 151.9% instead of 56% even though the correct result was supposed to be 49.6%.  The second value was the same in SQL 2005.  The same fix to the msmdsrv.ini file fixes the issue though.  I will have to make a call ticket with MS Support and make them aware of this bug.  I will let you know if they tell me to make a different fix to resolve this issue.

UPDATE (12/19/2007):

Just wanted to provide an update in regards to this issue with the cache reuse in Analysis Services.  I did test both these MDX statements in Mosha’s MDX Studio and when the 2nd statement is executed it will only return the results from the cache and it will not perform any calculations.  If you clear the cache and run them in the reverse order then it will perform the necessary calculations and everything is fine.  Making the ‘Cache Policy’ setting change does resolve the issue and you can run the statements in either order. 

MS Support got back to me and they did state that there is an issue with the cache reuse with the current product (and actually the next version of the product) and they have filed the issue with the development team.  The interesting thing is that another way that they reported that this can be resolved was swapping out the function being used in one of the calculations.  The current calculation is using the Aggregate function and they switched it to the Sum function and this fixed the problem also.  Really odd since these are basically setup to do the same thing.  They stated that it can be problematic using the Aggregate function to aggregate a calculated member.

In regards to the Cache Policy setting here is what they had to say:

The cache policy setting (9 instead of 0) you were using as a workaround is intended to change how the formula engine answers queries; it will cause the formula engine to lean towards using block evaluation mode more, instead of cell-by-cell mode. Generally, this can result in faster queries, but it can also slow down some queries, so there is a risk involved with it. Microsoft CSS does not usually recommend this setting as a general approach/resolution.

It’s usually a better idea to find ways to improve the queries without using cache policy; it should only be used as a last resort, and only under the direction of CSS. We generally do not make the details of the cache policy settings public, because they do carry risks, and they are intended only for special cases that should be determined by customer support.

Makes sense, but how would I have ever come to the conclusion that the issue was actually with the function we were using to perform the calculation?  Extremely odd behavior if you ask me and this is pretty scary.  Hopefully they will come up with a solution to resolve the incorrect cache reuse.  How do I know that this is the only scenario that is going to return incorrect results?

Posted in Analysis Services | Tagged: | Leave a Comment »