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 for AS2005

I try to use the Excel Pivot Table to retrieve the data from AS2005 cube data. I have install the Microsoft OLE DB Provider for Analsysis Services 9.0", but I get the following error message: Insitialzaiton of the data source failed. However, when I use the SQL Server Management Studio, I can browse the cube data. As a result, I know I have permission the access the cube data. Why the Excel Pivot Table is not working for me?

This schenario is pretty common and should be working. There got to be something wrong with components installed.

Try applying the latest version of AS OLEDB provider from http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

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

Hi all,

I have upgrade my AS 2000 cube to As 2005, but there has some thing wrong with the Excel Pivot Table. In the cube I have one Time dimension with three attributes, Year, Month, Day. Then I create a Hierachies with three levels, Year, Month, and Day. Finally I retrieve the data though Excel Pivot Table. I put the Time Hierachies in the page field, and put the Year attribtues in the column filed. But no matter how I filter in the page fields, ex select January 2006 and 2007, the column field will display the whole year of data for 2006 and 2007. I didn't have this problem in AS 2000, anyone know the reason.

Thanks,

Tomas

My first guess is the relationships between the attribute hierarchies may not be correct. Could you describe the attribute relationships explicitly defined in the dimension? Could you also clarify if Month is modeled as January, February, etc. or January 2006, February 2006, ..., January 2007, February 2007, etc.?

Thanks,
Bryan

|||

The attributes relationship as follow:

Date(Usage: key)

|__Calendar Month

ex, 2007-1-1

Calendar Month(Usage: Regular)

|__Calendar Year

ex, January 2007

Calendar Year(Usage: Regular)

ex, 2007

Hierachies

*Calendar Year

**Calendar Month

***Date

Thanks,

Tomas

|||

Thomas,

This post was marked as answered. Has the problem been resolved?

Thanks,
Bryan

|||

sorry my mistake to marked as answered. The problem has not been resolved.

Thanks

|||

Not really sure what's going on with this. I'd suggest seeing if this is a problem in other browsers. If it is, try replacing the dimension and see if the problem persists. You may also want to open profiler to snag the query being submitted by Excel to see if it is just assembling a weird statement.

B.

|||

Here is what Excel generate:

WITH MEMBER [Time].[Year - Quarter - Month - Date].[XL_QZX] AS 'Aggregate ( { [Time].[Year - Quarter - Month - Date].[Quarter].&[2005-01-01T00:00:00] , [Time].[Year - Quarter - Month - Date].[Quarter].&[2004-01-01T00:00:00] , [Time].[Year - Quarter - Month - Date].[Quarter].&[2003-01-01T00:00:00] } )' SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Time].[Year].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [MaxMinSales] WHERE ([Measures].[Store Sales], [Time].[Year - Quarter - Month - Date].[XL_QZX])

But the result is the whole year, instead of Quarter 1.

|||

I'm not sure exactly what the problem is, but quarter wasn't in the hierarchy description from above. Take a look at the relationship from month to quarter and then quarter to year. I'm thiking that's where the problem is likely at.

B.

Excel Pivot

Hi,

I have developed a cube in AS 2005. I am accesing the Cube from Excel. In Cube Design I have grouped the Measures under different folders with 'DisplayFolder' Feature as I have about 100 measures.

When I access the measures from Excel, the folders are not displayed but the entire list of 100 measures. Is there anyway to group the measures together in folders such as sales, assets, profit etc.?

Thanks for the help.

Regards

Josh

Hello! If you are using Excel2003 i do not think that there is a solution.

If you use Excel2007 as a client they will show up in the display folders. Calculated members, however, will have to be mapped to display folders in the calculations properties dialoge. It is a small property button in the calculations tab in the cube editor.

HTH

Thomas Ivarsson

|||

Hi Thomas,

Thanks for your quick reply.

I am using Excel 2002 ;(

I will check with the upgrade and see what happens. BTW, I have couple more questions related to the same.:

- How can I use suppress values=0 (without using Autofilters)

- Is it possible to insert columns or rows in the pivot (which are not measures or dimensions)? The user wants to insert a column to do some calculations.

I would appreciate your reply on the same. Thanks a lot in advance.

Regards

Josh

|||

Hello.

To supress values = 0, I think you can only use FILTER() fór that. NON EMPTY will only suppress nulls.

Mosha have a good post here: http://www.sqljunkies.com/WebLog/mosha/archive/2007/03/07/averages_ratios_division_by_zero_non_empty_behavior.aspx

, that recommend you to aviod zeros in the cube.

You can insert columns and rows only if you do not use the pivot tables(In Excel2007). You will have to convert the pivot table to formulas. It is not possible to have both.

HTH

Thomas Ivarsson