Home > SSAS > SSAS Locale Identifier Bug

 

SSAS Locale Identifier Bug

January 29th, 2012

These days I have two little problems with SSAS. One is really small – I really don’t like how the Process dialog in BIDS (2008 R2) stays blank during the process operation and how I need to click on “Stop” after it finishes. The fact that such a bug had gotten into the product and has survived for so long when it happens 80-90% of the time doesn’t speak very well for the QA process at Microsoft. However, I can understand that the impact of this bug would have been deemed very insignificant as it impacts developers only and does not prevent them from doing their job. By the way, if you are also annoyed by the blank process dialog in the latest version of SQL Server, you will have to wait until the next release (2012) until you get a fix:

http://connect.microsoft.com/SQLServer/feedback/details/536543/ssas-process-progress-window-blank-no-details

The other bug is far more significant. It not only impair developers’ ability to build some features, but is also highly visible to all Excel users. However, it is hard(er) to reproduce:

http://connect.microsoft.com/SQLServer/feedback/details/484146/getting-an-error-when-trying-to-browse-a-cube

Still, it seems like developers from {[World].[Countries].Members}-{[World].[Countries].[USA]} hit it all the time. I am speaking about the Locale Identifier bug. The most common occurrence is when drilling through to detail in Excel. After the drill-through action has been initiated, Excel shows a message box with a message talking about the XML Parser and how the Locale Identifier cannot be overwritten -

“XML for Analysis parser: The LocaleIdentifier property is not overwritable and cannot be assigned a new value.”

The cause is simple (as confirmed by the SSAS team at Microsoft and Akshai Mirchandani in particular): once we open a session we cannot overwrite the locale. The mystery is around the cause for Excel to do something like that. Noting that Excel is not the only offender, as I have also seen the same error message thrown by SQL Server Profiler, and Chris Webb has seen it with the Cube Browser in BIDS:

http://connect.microsoft.com/SQLServer/feedback/details/484146/getting-an-error-when-trying-to-browse-a-cube

Since the bug has been reported to Microsoft, we are now hopeful that a fix will appear at some point. Until then you can try the following workaround, courtesy of my friends and former colleagues Matthew Ward and Paul Hales:

- Switch the Windows Region and Language Format in Control Panel to English (United States):

- Switch it back to whatever it was before

Now the “bug” should be fixed for that machine. For example, I had my new Windows 7 workstation configured to use Australian formats. After I got the Locale Identifier error message in Profiler, I switched the formats to USA. The bug disappeared and I could profile SSAS. After that I switched Windows back to the original English (Australia) format…and nothing broke. I could still use Profiler and drill-through in Excel.

Another notice. Greg Galloway has released a new version (0.7.4) of the OLAP Pivot Extensions add-in for Excel. In case you have been experiencing a Locale Identifier problem in SSAS while using older versions of the add-in, please download the new one and let Greg know (e.g. on the discussions page on CodePlex) if the new release fies your problem.

Thanks to everyone involved in confirming and testing different fixes. Ideally, I would like to see Microsoft fixing this on the server side of things which would allow us to easily patch up all existing systems exhibiting the problem.

Since all Microsoft Connect item related to this bug have been closed, I opened a new one, so you can vote in order to prioritise this issue:

https://connect.microsoft.com/SQLServer/feedback/details/721372/locale-identifier-bug

Note (2012-03-21): The issue seems to be with Windows Vista+ as confirmed by Michael Kaplan – http://blogs.msdn.com/b/michkap/archive/2010/03/19/9980203.aspx

If you enjoyed this post, make sure you subscribe to my RSS feed!

 

SSAS ,

  1. Anna Anchova
    | #1

    Very useful post.
    This morning I spent an hour to solve Excel error when refreshing pivot with SSAS cube source. The error was “The query did not run or the database table could not be opened”
    Changing Region and Language Format to English and back to Bulgarian worked like a charm.
    Thanks for sharing this info.

  2. | #2

    Hi Anna,

    Thanks for confirming it works!

  3. Iman
    | #3

    Hey Boyan, it works well for me too. Thanks for the great post!

  4. | #4

    Cool! The more people comment here and vote on Connect, the higher the chance of getting this fixed..hopefully not only in SQL 2012+.

  5. Shaun Baggett
    | #5

    Hi Boyan
    This doesn’t always work in my experience.
    Another workaround is to add the following to the client connection string;
    Locale Identifier=1033

  6. Fede Gonzalez
    | #6

    Great! It´s working fine now!

  7. | #7

    Hi Shaun,

    Do you know when it does or does not work? So far I’ve had only positive responses…

  8. | #8

    This is partially a bug in Windows 7 and affects a lot of apps that relied on the Windows registry being set a certain way to identify the machine’s locale.
    See Michael Kaplan’s blog post (the man who knows everything about multi-language stuff @ Microsoft): http://blogs.msdn.com/b/michkap/archive/2010/03/19/9980203.aspx
    It affected our Delphi v7-based app – I put a fix in our code to work around the issue. Excel (and a few other apps) suffer from a similar flaw.
    As you suggested, switching the user’s region away and back fixes the issue – as an Australian-based ISV I just couldn’t bring myself to suggest that as a solution to our users :)

  9. | #9

    @Ian Yates

    Thanks Ian. I have made the SSAS and the Excel teams within Microsoft aware of this issue and since we have a concrete case in which this breaks dependencies between their own products I hope they will do something about it. Preferrably they’ll fix the Windows “bug”, not just Excel or SSAS.

    Not the prettiest workaround, I agree, but it seems to be working.. Thanks for the link – it’s good to know the “fix” has been confirmed.

Comments are closed.