I was working on a new SSAS 2008 cube today, and came across an error which Google was unable to help with. I thought I’d post the solution here to help anyone else who may encounter it.
The cube in question will be primarily be accessed using Excel 2007, so I’d been dutifully testing it along the way to ensure all was well. And then, after a number of changes the following error appeared when connecting to the cube from Excel to create a pivot table.
Excel was unable to get necessary information about this cube. The cube might have been reorganized or changed on the server.
Contact the OLAP cube administrator and, if necessary, set up a new data source to connect to the cube
Connecting and querying the cube via SSMS or BIDS worked without error (hense I didn’t spot the error sooner!).
A quick Google revealed a number of posts regarding this error, but they all related to attributes containing invalid characters when accessed from Excel 2000 Or problems with translations and locale settings in the .oqy file. Neither of these was the cause here, so I had to go back and recreate every change I had made step by step to track the problem.
Well, I’m please to report that in the end it was nothing more that a simple spelling mistake in a named set. One of the dynamic named sets in the cube calculations referred to a specific member of a dimension, which was spelled slightly incorrectly. (Simplified example..)
CREATE DYNAMIC SET CURRENTCUBE.[Set1]
AS {[Dimension].[Attribute].[Value1],
[Dimension].[Attribute].[Value2WithTypo]
};
When querying calculated measures through MDX in SSMS, the MDX parser just ignored the problem and only uses the valid members, however it appears as though Excel 2007 is slightly more picky with its cubes.
Useful to know, and even more useful when used as a tool to double check for any errors in the MDX calculations.
