Excel XP - Cannot connect to OLAP 9 - Analysis Services 2005

MS Excel XP on Windows 2000 fails to connect to OLAP 2005 Cube with the OLAP 9 Provider. Excel gives the error: <initialisering datasource failed>. The same error appears when I try to connect to OLAP 8 cubes. Before I could access OLap 8 cube, I had to reinstall Office XP.

Windows XP with Office 2003 works fine. It can access both OLAP 8.0 and OLAP 9.0 cubes.

Does the new OLAP 9.0 provider only works with Office 2003?

Best regards,

Jakobsgaard

I have been investigating a bit more in this matter...The PC which is giving os all this trouble are running Windows 2000 with Office XP SP3.

If I registre msolap90.dll, both MS Olap 8.0 and MS Olap 9.0 fails with the error: <initialisering datasource failed> when I connect to a cube from MS Excel.

But if I only registre msolap80.dll, MS Olap 8.0 works fine again.

Best regards,

Jakobsgaard
-
regsvr32 "c:\program files\common files\system\ole db\msolap90.dll"
regsvr32 "c:\program files\common files\system\ole db\msolap80.dll"
|||

Windows 2000 does not have MSXML6 by default and Office XP does not install it (Office 2003 does install MSXML5 which the 9.0 provider is able to use, in the absence of MSXML6).

Installing MSXML6 solved the problem.

|||We are receiving a similiar error but are using Office 2003.

Did you hear anything from anyone else?

Mark e. Johnson|||Suresh Bansal Stated the following in a further post:

"ok i found the solution. we need to reregister the olap 9.0 provider before we can use it to connect SQL Server 2005 Analysis Service Cube with Excel Pivot Table :
regsvr32 "c:\program files\common files\system\ole db\msolap90.dll"
"

This did the trick for us.

|||THANKS A LOT THAT SOLVED MY PROBLEM!!

Excel pivot table. Any language you like as long as its US English

Hi,
I have created an OLAP Cube in SQL Server 2005 CTP2 and can browse it fine in the Analysis Studio / Browse tab. When I use the pivot table feature in Excel (2003) I get:
XML for Analysis parser: The LocaleIdentifier property is not overwritable and cannot be assigned a value.
Any help is appreciated.
Cheers,
Adam
I have the same problem.
I'm using the September ctp on Win XP Us

My current windows user is a french user.

I have no idea, I try 3 installations and I always have the same pb: Excel returns me the message, and the BI Visual Studio ML Browser doesn't work.
|||Same problem...no clue at all
win xp sp2, ctp sept....|||I have the same problem - sept. ctp Windows 2003 Server with SP1
Czeslaw|||Yup, me too.... WinXP sp2, SS2005 sept ctp, Office 2003. Any help would be appreciated.

Regards
Kjetil|||Hi.
As you can see on my other post, I had the same problems but after scratcing my head for a few hours, I tried to change the regional settings in Windows controlpanel from Norwegian to English (United States) and VOILA - up popped the pivottable in Excel.

There might be a way to change the settings in SSAS to concur with the regional settings in Windows but I haven't gotten that far yet.

PS: I first tried to remove Norwegian from the MS Office 2003 Language settings but that didn't help...

Hope this will solve your problems too.

Regards
Kjetil |||I can confirm that this fix works for me. Good work Kjetil.|||

I believe you'll find that the problem is actually a missing locale message table. The problem is with the OLEDB driver. Here's how to check:

a) naviagate to the location of the oledb driver. It should be in: C:\Program Files\Common Files\System\Ole DB.

b) check the entries in msmdsrv.log. You should see that it's complaining about a missing message manager. You should notice that the locale ID matches the value set in Control panel.

Now a workarround that I applied, without having to change the control panel was to copy the directory in .\resources\1033 (US english) to .\resources\4105 (Canadian English). This uses the US english entries until Microsoft releases the fix.

Note: remember, this would have to be done on every client PC, accessing AS.

|||

I use Japanese version.
I can solve the problem.

September CTP
Windows XP Pro. with SP2
Office 2000 with SP3

[controlPanel]-[regional settings]
Japanese To English (United States)

thanks!

|||Hi all,

Thank you for all the suggestions, only one worked was changing the regional settings.

This problem means that this CTP has effectively failed before even starting, not very good!!!

Anyone know if there is a fix yet?

Iain

|||

Hello again.

I finally found the solution to this problem: Update to the latest version of Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider found at :

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

This also solved a memory problem I had earlier with the error message:

"Memory error: While attempting to store a string, a string was found that was larger than the page size selected. The operation cannot be completed."

Thanx to Dave Wickert who gave the tip!

Regards

Kjetil

|||

I installed the latest version of Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider , but still have the "LocalIdentifier" problem as soon as I change the regional settings from US English to something else.

Any more ideas ?

Thanks.

|||

Was: Excel pivot table. Any language you like as long as its US English

I have this message appearing in Excel when connecting to Analysis Services 2000 and 2005 cubes. I have never used languages other than English in the application and the locale settings were NOT changed.

Installing the the latest version of Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider does NOT solve the issue which appears to be intermittent.

Immediately prior to the error I was trying (in vain) to use scripts to control the behaviour and "protection" of OWC web published components.

A fix is needed urgently.

|||

Something you may want to try.

a) Had a similar issue, & narrowed down to a conflict between AS_OLEDB Version 8 & 9 installed on same PC. Seems the upgrade to 9 doesn't clean up the old version. To resolve this, run uninstall of the existing oledb driver, go to C:\Program Files\Common Files\System\Ole DB & delete all files in this directory if they exist, re-install AS_OLEDB 9.0.

P.S. Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider (OLEDB 9.0) available at www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc

b) Check analysis server default language.

i.e. SQL Management studio, connect to Analysis Server, Properties (right click on server), Language/Collation, set to US English.

|||

Hi,

I realise that this is an old post but as we had this issue for some of our users january 2006 it might help someone...

We had an old version of IntelligentApps installed (4.5.0.29) on the clients where we had the XMLA-LocaleIdentifier issue. When we disabled that plugin the problem went away.

If you need to use that plugin to access 2000-cubes and don't want to upgrade it is an easy task to disable/enable the plugin (you will find a shortcut under "start->program->intelligentapps->excel edition->Enable or disable excel addins".

Thanks

Gabriel

Excel pivot table. Any language you like as long as its US English

Hi,
I have created an OLAP Cube in SQL Server 2005 CTP2 and can browse it fine in the Analysis Studio / Browse tab. When I use the pivot table feature in Excel (2003) I get:
XML for Analysis parser: The LocaleIdentifier property is not overwritable and cannot be assigned a value.
Any help is appreciated.
Cheers,
Adam
I have the same problem.
I'm using the September ctp on Win XP Us

My current windows user is a french user.

I have no idea, I try 3 installations and I always have the same pb: Excel returns me the message, and the BI Visual Studio ML Browser doesn't work.
|||Same problem...no clue at all
win xp sp2, ctp sept....

|||I have the same problem - sept. ctp Windows 2003 Server with SP1
Czeslaw|||Yup, me too.... WinXP sp2, SS2005 sept ctp, Office 2003. Any help would be appreciated.

Regards
Kjetil|||Hi.
As you can see on my other post, I had the same problems but after scratcing my head for a few hours, I tried to change the regional settings in Windows controlpanel from Norwegian to English (United States) and VOILA - up popped the pivottable in Excel.

There might be a way to change the settings in SSAS to concur with the regional settings in Windows but I haven't gotten that far yet.

PS: I first tried to remove Norwegian from the MS Office 2003 Language settings but that didn't help...

Hope this will solve your problems too.

Regards
Kjetil |||I can confirm that this fix works for me. Good work Kjetil.|||

I believe you'll find that the problem is actually a missing locale message table. The problem is with the OLEDB driver. Here's how to check:

a) naviagate to the location of the oledb driver. It should be in: C:\Program Files\Common Files\System\Ole DB.

b) check the entries in msmdsrv.log. You should see that it's complaining about a missing message manager. You should notice that the locale ID matches the value set in Control panel.

Now a workarround that I applied, without having to change the control panel was to copy the directory in .\resources\1033 (US english) to .\resources\4105 (Canadian English). This uses the US english entries until Microsoft releases the fix.

Note: remember, this would have to be done on every client PC, accessing AS.

|||

I use Japanese version.
I can solve the problem.

September CTP
Windows XP Pro. with SP2
Office 2000 with SP3

[controlPanel]-[regional settings]
Japanese To English (United States)

thanks!

|||Hi all,

Thank you for all the suggestions, only one worked was changing the regional settings.

This problem means that this CTP has effectively failed before even starting, not very good!!!

Anyone know if there is a fix yet?

Iain|||

Hello again.

I finally found the solution to this problem: Update to the latest version of Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider found at :

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

This also solved a memory problem I had earlier with the error message:

"Memory error: While attempting to store a string, a string was found that was larger than the page size selected. The operation cannot be completed."

Thanx to Dave Wickert who gave the tip!

Regards

Kjetil

|||

I installed the latest version of Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider , but still have the "LocalIdentifier" problem as soon as I change the regional settings from US English to something else.

Any more ideas ?

Thanks.

|||

Was: Excel pivot table. Any language you like as long as its US English

I have this message appearing in Excel when connecting to Analysis Services 2000 and 2005 cubes. I have never used languages other than English in the application and the locale settings were NOT changed.

Installing the the latest version of Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider does NOT solve the issue which appears to be intermittent.

Immediately prior to the error I was trying (in vain) to use scripts to control the behaviour and "protection" of OWC web published components.

A fix is needed urgently.

|||

Something you may want to try.

a) Had a similar issue, & narrowed down to a conflict between AS_OLEDB Version 8 & 9 installed on same PC. Seems the upgrade to 9 doesn't clean up the old version. To resolve this, run uninstall of the existing oledb driver, go to C:\Program Files\Common Files\System\Ole DB & delete all files in this directory if they exist, re-install AS_OLEDB 9.0.

P.S. Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider (OLEDB 9.0) available at www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc

b) Check analysis server default language.

i.e. SQL Management studio, connect to Analysis Server, Properties (right click on server), Language/Collation, set to US English.

|||

Hi,

I realise that this is an old post but as we had this issue for some of our users january 2006 it might help someone...

We had an old version of IntelligentApps installed (4.5.0.29) on the clients where we had the XMLA-LocaleIdentifier issue. When we disabled that plugin the problem went away.

If you need to use that plugin to access 2000-cubes and don't want to upgrade it is an easy task to disable/enable the plugin (you will find a shortcut under "start->program->intelligentapps->excel edition->Enable or disable excel addins".

Thanks

Gabriel

Excel pivot table. Any language you like as long as its US English

Hi,
I have created an OLAP Cube in SQL Server 2005 CTP2 and can browse it fine in the Analysis Studio / Browse tab. When I use the pivot table feature in Excel (2003) I get:
XML for Analysis parser: The LocaleIdentifier property is not overwritable and cannot be assigned a value.
Any help is appreciated.
Cheers,
Adam
I have the same problem.
I'm using the September ctp on Win XP Us

My current windows user is a french user.

I have no idea, I try 3 installations and I always have the same pb: Excel returns me the message, and the BI Visual Studio ML Browser doesn't work.
|||Same problem...no clue at all
win xp sp2, ctp sept....

|||I have the same problem - sept. ctp Windows 2003 Server with SP1
Czeslaw|||Yup, me too.... WinXP sp2, SS2005 sept ctp, Office 2003. Any help would be appreciated.

Regards
Kjetil|||Hi.
As you can see on my other post, I had the same problems but after scratcing my head for a few hours, I tried to change the regional settings in Windows controlpanel from Norwegian to English (United States) and VOILA - up popped the pivottable in Excel.

There might be a way to change the settings in SSAS to concur with the regional settings in Windows but I haven't gotten that far yet.

PS: I first tried to remove Norwegian from the MS Office 2003 Language settings but that didn't help...

Hope this will solve your problems too.

Regards
Kjetil |||I can confirm that this fix works for me. Good work Kjetil.|||

I believe you'll find that the problem is actually a missing locale message table. The problem is with the OLEDB driver. Here's how to check:

a) naviagate to the location of the oledb driver. It should be in: C:\Program Files\Common Files\System\Ole DB.

b) check the entries in msmdsrv.log. You should see that it's complaining about a missing message manager. You should notice that the locale ID matches the value set in Control panel.

Now a workarround that I applied, without having to change the control panel was to copy the directory in .\resources\1033 (US english) to .\resources\4105 (Canadian English). This uses the US english entries until Microsoft releases the fix.

Note: remember, this would have to be done on every client PC, accessing AS.

|||

I use Japanese version.
I can solve the problem.

September CTP
Windows XP Pro. with SP2
Office 2000 with SP3

[controlPanel]-[regional settings]
Japanese To English (United States)

thanks!

|||Hi all,

Thank you for all the suggestions, only one worked was changing the regional settings.

This problem means that this CTP has effectively failed before even starting, not very good!!!

Anyone know if there is a fix yet?

Iain|||

Hello again.

I finally found the solution to this problem: Update to the latest version of Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider found at :

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

This also solved a memory problem I had earlier with the error message:

"Memory error: While attempting to store a string, a string was found that was larger than the page size selected. The operation cannot be completed."

Thanx to Dave Wickert who gave the tip!

Regards

Kjetil

|||

I installed the latest version of Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider , but still have the "LocalIdentifier" problem as soon as I change the regional settings from US English to something else.

Any more ideas ?

Thanks.

|||

Was: Excel pivot table. Any language you like as long as its US English

I have this message appearing in Excel when connecting to Analysis Services 2000 and 2005 cubes. I have never used languages other than English in the application and the locale settings were NOT changed.

Installing the the latest version of Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider does NOT solve the issue which appears to be intermittent.

Immediately prior to the error I was trying (in vain) to use scripts to control the behaviour and "protection" of OWC web published components.

A fix is needed urgently.

|||

Something you may want to try.

a) Had a similar issue, & narrowed down to a conflict between AS_OLEDB Version 8 & 9 installed on same PC. Seems the upgrade to 9 doesn't clean up the old version. To resolve this, run uninstall of the existing oledb driver, go to C:\Program Files\Common Files\System\Ole DB & delete all files in this directory if they exist, re-install AS_OLEDB 9.0.

P.S. Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider (OLEDB 9.0) available at www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc

b) Check analysis server default language.

i.e. SQL Management studio, connect to Analysis Server, Properties (right click on server), Language/Collation, set to US English.

|||

Hi,

I realise that this is an old post but as we had this issue for some of our users january 2006 it might help someone...

We had an old version of IntelligentApps installed (4.5.0.29) on the clients where we had the XMLA-LocaleIdentifier issue. When we disabled that plugin the problem went away.

If you need to use that plugin to access 2000-cubes and don't want to upgrade it is an easy task to disable/enable the plugin (you will find a shortcut under "start->program->intelligentapps->excel edition->Enable or disable excel addins".

Thanks

Gabriel

Excel Pivot Table and Time Dimension

I’m using the OLEDB Provider for Analysis Services 9.0 in Excel 2003 to get to my OLAP cubes in SQL Server 2005 Standard SP1.When I use Time as a Page filter in an Excel Pivot table, the dropdown looks like this (May_2006 is the default period):

-2006
-Qtr2_2006

+May_2006

+Apr_2006

+Jun_2006

+Qtr1_2006

+Qtr3_2006

+Qtr4_2006

+2004

+2005

I checked the field settings in Excel and it is using the data source order option.When I browse the dimension, the Time members are shown in chronological order.I’ve concluded that this behavior is a result of setting the default month in the Time dimension.Anyone know if this is simply the way it is when working with a defaulted Time dimension in Excel?

Moving to SQL Server Analysis Services forum.

Excel pivot table

Hi,
I have created an OLAP Cube in SQL Server 2005 CTP2 and can browse it fine in the Analysis Studio / Browse tab. When I use the pivot table feature in Excel (2003) I get:
XML for Analysis parser: The LocaleIdentifier property is not overwritable and cannot be assigned a value.
Any help is appreciated.
Cheers,
Adam
I have the same problem.
I'm using the September ctp on Win XP Us

My current windows user is a french user.

I have no idea, I try 3 installations and I always have the same pb: Excel returns me the message, and the BI Visual Studio ML Browser doesn't work.
|||Same problem...no clue at all
win xp sp2, ctp sept....

|||I have the same problem - sept. ctp Windows 2003 Server with SP1
Czeslaw|||Yup, me too.... WinXP sp2, SS2005 sept ctp, Office 2003. Any help would be appreciated.

Regards
Kjetil|||Hi.
As you can see on my other post, I had the same problems but after scratcing my head for a few hours, I tried to change the regional settings in Windows controlpanel from Norwegian to English (United States) and VOILA - up popped the pivottable in Excel.

There might be a way to change the settings in SSAS to concur with the regional settings in Windows but I haven't gotten that far yet.

PS: I first tried to remove Norwegian from the MS Office 2003 Language settings but that didn't help...

Hope this will solve your problems too.

Regards
Kjetil |||I can confirm that this fix works for me. Good work Kjetil.|||

I believe you'll find that the problem is actually a missing locale message table. The problem is with the OLEDB driver. Here's how to check:

a) naviagate to the location of the oledb driver. It should be in: C:\Program Files\Common Files\System\Ole DB.

b) check the entries in msmdsrv.log. You should see that it's complaining about a missing message manager. You should notice that the locale ID matches the value set in Control panel.

Now a workarround that I applied, without having to change the control panel was to copy the directory in .\resources\1033 (US english) to .\resources\4105 (Canadian English). This uses the US english entries until Microsoft releases the fix.

Note: remember, this would have to be done on every client PC, accessing AS.

|||

I use Japanese version.
I can solve the problem.

September CTP
Windows XP Pro. with SP2
Office 2000 with SP3

[controlPanel]-[regional settings]
Japanese To English (United States)

thanks!

|||Hi all,

Thank you for all the suggestions, only one worked was changing the regional settings.

This problem means that this CTP has effectively failed before even starting, not very good!!!

Anyone know if there is a fix yet?

Iain|||

Hello again.

I finally found the solution to this problem: Update to the latest version of Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider found at :

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

This also solved a memory problem I had earlier with the error message:

"Memory error: While attempting to store a string, a string was found that was larger than the page size selected. The operation cannot be completed."

Thanx to Dave Wickert who gave the tip!

Regards

Kjetil

|||

I installed the latest version of Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider , but still have the "LocalIdentifier" problem as soon as I change the regional settings from US English to something else.

Any more ideas ?

Thanks.

|||

Was: Excel pivot table. Any language you like as long as its US English

I have this message appearing in Excel when connecting to Analysis Services 2000 and 2005 cubes. I have never used languages other than English in the application and the locale settings were NOT changed.

Installing the the latest version of Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider does NOT solve the issue which appears to be intermittent.

Immediately prior to the error I was trying (in vain) to use scripts to control the behaviour and "protection" of OWC web published components.

A fix is needed urgently.

|||

Something you may want to try.

a) Had a similar issue, & narrowed down to a conflict between AS_OLEDB Version 8 & 9 installed on same PC. Seems the upgrade to 9 doesn't clean up the old version. To resolve this, run uninstall of the existing oledb driver, go to C:\Program Files\Common Files\System\Ole DB & delete all files in this directory if they exist, re-install AS_OLEDB 9.0.

P.S. Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider (OLEDB 9.0) available at www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc

b) Check analysis server default language.

i.e. SQL Management studio, connect to Analysis Server, Properties (right click on server), Language/Collation, set to US English.

|||

Hi,

I realise that this is an old post but as we had this issue for some of our users january 2006 it might help someone...

We had an old version of IntelligentApps installed (4.5.0.29) on the clients where we had the XMLA-LocaleIdentifier issue. When we disabled that plugin the problem went away.

If you need to use that plugin to access 2000-cubes and don't want to upgrade it is an easy task to disable/enable the plugin (you will find a shortcut under "start->program->intelligentapps->excel edition->Enable or disable excel addins".

Thanks

Gabriel

Excel pivot table

Hi,
I have created an OLAP Cube in SQL Server 2005 CTP2 and can browse it fine in the Analysis Studio / Browse tab. When I use the pivot table feature in Excel (2003) I get:
XML for Analysis parser: The LocaleIdentifier property is not overwritable and cannot be assigned a value.
Any help is appreciated.
Cheers,
Adam
I have the same problem.
I'm using the September ctp on Win XP Us

My current windows user is a french user.

I have no idea, I try 3 installations and I always have the same pb: Excel returns me the message, and the BI Visual Studio ML Browser doesn't work.
|||Same problem...no clue at all
win xp sp2, ctp sept....|||I have the same problem - sept. ctp Windows 2003 Server with SP1
Czeslaw|||Yup, me too.... WinXP sp2, SS2005 sept ctp, Office 2003. Any help would be appreciated.

Regards
Kjetil|||Hi.
As you can see on my other post, I had the same problems but after scratcing my head for a few hours, I tried to change the regional settings in Windows controlpanel from Norwegian to English (United States) and VOILA - up popped the pivottable in Excel.

There might be a way to change the settings in SSAS to concur with the regional settings in Windows but I haven't gotten that far yet.

PS: I first tried to remove Norwegian from the MS Office 2003 Language settings but that didn't help...

Hope this will solve your problems too.

Regards
Kjetil |||I can confirm that this fix works for me. Good work Kjetil.|||

I believe you'll find that the problem is actually a missing locale message table. The problem is with the OLEDB driver. Here's how to check:

a) naviagate to the location of the oledb driver. It should be in: C:\Program Files\Common Files\System\Ole DB.

b) check the entries in msmdsrv.log. You should see that it's complaining about a missing message manager. You should notice that the locale ID matches the value set in Control panel.

Now a workarround that I applied, without having to change the control panel was to copy the directory in .\resources\1033 (US english) to .\resources\4105 (Canadian English). This uses the US english entries until Microsoft releases the fix.

Note: remember, this would have to be done on every client PC, accessing AS.

|||

I use Japanese version.
I can solve the problem.

September CTP
Windows XP Pro. with SP2
Office 2000 with SP3

[controlPanel]-[regional settings]
Japanese To English (United States)

thanks!

|||Hi all,

Thank you for all the suggestions, only one worked was changing the regional settings.

This problem means that this CTP has effectively failed before even starting, not very good!!!

Anyone know if there is a fix yet?

Iain

|||

Hello again.

I finally found the solution to this problem: Update to the latest version of Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider found at :

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

This also solved a memory problem I had earlier with the error message:

"Memory error: While attempting to store a string, a string was found that was larger than the page size selected. The operation cannot be completed."

Thanx to Dave Wickert who gave the tip!

Regards

Kjetil

|||

I installed the latest version of Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider , but still have the "LocalIdentifier" problem as soon as I change the regional settings from US English to something else.

Any more ideas ?

Thanks.

|||

Was: Excel pivot table. Any language you like as long as its US English

I have this message appearing in Excel when connecting to Analysis Services 2000 and 2005 cubes. I have never used languages other than English in the application and the locale settings were NOT changed.

Installing the the latest version of Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider does NOT solve the issue which appears to be intermittent.

Immediately prior to the error I was trying (in vain) to use scripts to control the behaviour and "protection" of OWC web published components.

A fix is needed urgently.

|||

Something you may want to try.

a) Had a similar issue, & narrowed down to a conflict between AS_OLEDB Version 8 & 9 installed on same PC. Seems the upgrade to 9 doesn't clean up the old version. To resolve this, run uninstall of the existing oledb driver, go to C:\Program Files\Common Files\System\Ole DB & delete all files in this directory if they exist, re-install AS_OLEDB 9.0.

P.S. Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider (OLEDB 9.0) available at www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc

b) Check analysis server default language.

i.e. SQL Management studio, connect to Analysis Server, Properties (right click on server), Language/Collation, set to US English.

|||

Hi,

I realise that this is an old post but as we had this issue for some of our users january 2006 it might help someone...

We had an old version of IntelligentApps installed (4.5.0.29) on the clients where we had the XMLA-LocaleIdentifier issue. When we disabled that plugin the problem went away.

If you need to use that plugin to access 2000-cubes and don't want to upgrade it is an easy task to disable/enable the plugin (you will find a shortcut under "start->program->intelligentapps->excel edition->Enable or disable excel addins".

Thanks

Gabriel

Excel Pivot Table

When I use Excel 2002 Sp3 to connect to the AS2000. I can select multiple value in the Page field. But when I install the OLAP AS 9.0 provider to connect to the AS2005. The multiple value selection is disapper. How can I fix this?There is a 'select multiple items' checkbox in the bottom area of the dropdown box.

If this is not visible - I'd almost think that your Excel platform at that specific time is Excel 2000.
Try recreating your pivot table from the as2005 cube|||I am using Excel 2002. It is funny sometime the muti value check box appear, sometime it disappear. I also try to reinstall the Excel, but it doesn't help|||

Did you upgrade MSXML to 6.0 as well as upgrading the AS Provider ?

Must say I don't get this behaviour in 2003 having added both the upgrades

|||Yes MSXML 6.0 Parser and MS OLAP Provide For AS 9.0 have been installed in my computer. Now I find the tempoary solution. If I run another application such as IE6 to overlap the Excel, then I can see the multi value check box. It is really @.$%...?|||

It's good that you're getting somewhere!

I remember that in some cases, I've had to have MDAC 2.8 installed if it wasn't already present. You shouldn't be getting that far in excel w/o it, but it's worth a try.

http://www.microsoft.com/downloads/details.aspx?DisplayLang=en&FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c

Excel OLAP Report losing layout after migration

I have big numbers of OLAP Excel (Pivot tables) report and very complex.

Finally I am able to move my olap database from AS 2000 to AS 2005 after fixing some know issues some specific to my database.

Now the big challenge is all Excel 2002 reports should work un touched. I am using Excel Macro to update connection string in all Excel file but I can not afford to redesign all of them. Redesigning Excel report will almost kill my AS 2005 migration project.

After changing pivot table connection from 2000 database to 2005 database, when I refresh pivot table I am losing my reports layout. In my "Field List" all shared dimensions and measures has exact same names after refresh.

Looks it's ok for most of the dimensions but all shared dimensions had Hierarchy name like Company.Vendor , Company.Employee in AS 2000 has this issue in AS 2005.

After migration I do had to fix all Hierarchy shared dimensions because all came in AS 2005 as Company 1, Company 2.

Any idea what I should try to do fix them.

Thank you - Ashok

Macro to change Excel PivotTable connection from AS 2000 to AS 2005

With ActiveCell.PivotTable.PivotCache
Response = MsgBox("Current Connection: " & .Connection)
.Connection = Replace(.Connection, "MSOLAP.2", "MSOLAP.3")
.Connection = Replace(.Connection, "AS2000DB_Name", "AS2005DB_Name")
Response = MsgBox("New Connection: " & .Connection)
End With

Hello. Most respect to your situation but we have had this discusssion a few days ago here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1061202&SiteID=1

Kind regards

Thomas Ivarsson

|||

Thanks Thomas.

There is one way to fix this. It worked for me however still there are some open issues but may be it's in my case only.

Let's say we have Hierarchy shared dimensions in AS 2000

Company.Vendor

Company.Employee

Company.Location

Once we run the migration in AS 2005 we see dimensions like

Company (Hierarchy name Vendor)

Company 1 (Hierarchy name Employee)

Company 2 (Hierarchy name Location)

What I did before, I renamed them "Company Vendor", "Comapny Employee" and "Company Location". That created issues of losing layout in Excel Pivot table report.

This is what I tested and worked to fix Excel Pivot report losing layout issue.

- After migration create a dimension "Company" or user first one "Company" dimension, in that dimension create three Hierarchy "Vendor", "Employee" and "Location". If you use existing "Company" dimension then add two new "Employee" and "Location"

- Remove all Company dimensions from cubes

- Add new Company dimension which has more the one Hierarchy

- Process cube

- Open Old Excel Pivot table report which runs fine with AS 2000 cube

- Run macro to change connection to AS 2005

- Refresh report

You should see "Filed list" changed now "Company Vendor" DIM will be only "Vendor" but the big deal is you don't lose reports layout.

It worked for me I still have open issue that I have 5 cubes and one Cube with 5 Linked Cubes (like Virtual Cube in 2000). I don't know why I am not able to add my new "Company" dimension to my Virtual Cube.

|||

Hello Ashok. The cube migration wizard(AS2000 to SSAS2005) will only copy the structures in AS2000 and do its best to make it fit in SSAS2005. You are already aware of this as I can see.

Another problem that you might see is that some MDX will no longer work as expected.

If you have several customer dimensions in AS2000 you will still have several customer dimensions in SSAS2005. But one design fundamental is that you can have one customer dimension in SSAS2005 with several user hierarchies in that dimension.

Attribute relations is another fundamental thing in SSAS2005 that I do not think that the migration wizard will get right.

In SSAS2005 you no longer have virtual cubes but a logical cube with one or several measure groups(fact tables)

I have done some migration projects and I have always recommended my customers to avoid the migation wizard and redesign the AS2000 cubes according to what is best in SSAS2005. I think that this have been the best approach.

This is the best chance you will have to improve you AS2000 cubes.

Regards

Thomas Ivarsson