Problems connecting to SSAS instance over HTTP in Excel 2003

Our Biz users are all running Excel 2003, and we've recently set up a bunch of cubes that we'd like to open up to them.

I followed the steps here: http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx
to set up HTTP access to SSAS2005. It works great from my machine, I figured because I had SQL 05 installed locally and all the requisite drivers, etc.

On our biz users' desktops, however, it wasn't as peachy.

I first figured out I had to install MSXML6 and the SSAS 9.0 driver on their machine to connect to the cube. I downloaded those from here:

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

Once I was able to set up the connection to the cube, and about to create a pivot table in Excel, the Query wizard failed, with this error:

"XML parsing failed at line 1, column 9: DTD is prohibited"

Googling didn't seem to uncover anything useful, has anyone seen this before?

Not sure how google works, but using Windows Live Search (www.live.com) discovered the following thread:

http://www.topxml.com/MS-XML-Analysis/rn-132023_HTTP-XML/A-access-to-AS2005-cause-an-error.aspx

In the thread user Jeje attached the HTTP trace, which is pretty helpful - it shows that first client components tried to use 9.0 driver, but then falled back to 8.0 driver, which is probably the reason for the error. Why did it fall back to 8.0 - perhaps a permission issue ?

|||Whoops, now I feel bad for not using Live . I'll have to be sure to use both now!

Mosha - security sounds like it could be a probable cause. I'll try experimenting with permission issues tomorrow. Also, on a related thread in the same forum a MS employee noted:

Sent date: 01/06/2006
From: "Akshai Mirchandani [MS]" <(email address - cut out)>
Message:
This is a known issue -- a hotfix should be available soon from support.

Thanks,

Akshai

(http://cc.msnscache.com/cache.aspx?q=4937159748319&lang=en-US&mkt=en-US&FORM=CVRE2)

Are you aware of any related hotfixes that address this issue?|||Argh, I tried completely loosening security on the olap/ folder and its children, but this doesn't seem to help at all. I checked our IIS logs and like in Mosha's link the client seems to be attempting the 9.0 driver and then falling back to the 8.0 driver, which of course doesn't work.

Anyone know if there's a hotfix for this?|||Interesting. Changing IIS auth to anonymous seems like it completely solves this problem...although I'm unsure of the security implications -

Client -> IIS (msmdpump.dll) -> SSAS

If IIS is not using windows auth, does SSAS just use the anonymous user's credentials (ie the IUSR_ acct) to authenticate?|||

If you run Profiler against the Analysis Server, you can see which user attempted to connect -- if it was the IUSR_xxx or similar user then you know it is a security issue and the Pump is not able to delegate the user credentials to the Analysis Server for some reason.

I believe the hotfix was about using Basic authentication which doesn't seem to be your scenario.

Thanks,

Akshai

|||

Hi all,

I've also been encountering issues with HTTP access to SSAS 2005, but with excel 2007.

Although it's working from excel 2003 (but only if the option to save the username/password is checked when creating the data source for the pivot table), it won't let me access it from excel 2007. I always get an access denied-like message when i try to set up the connection to the data source.

We haven't been able to identify where is the issue. We set the HTTP access as via the link mentionned before, but that won't let us access from excel 2007.

We've also been facing some backward compatibility issues using excel 2003 with OLE DB provider for analysis services 9.0 installed.

With it we can not connect to our SSAS 2000 cubes (again via http) anymore, the error is the response returned via the HTTP server is not valid (even though we try using the OPAL 8.0 driver).

Any idea how we could solve that issue? did we miss a component to install to ensure compatibility with both version of SSAS?

thanks in advance for your help.

Cheers, and happy new year!

sql

Problems after installing SP2

I'm having some major problems with SSAS after installing SP2 (I never had an SP2 CTP installed, just SP1).

When I bring up BI Studio and select Open/Analysis Service Database, put in my server name (which is on the same machine that I'm running BI Studio), the click the "Database" drop down to select a database, it shows the following error in the bottom of the dialog:

Errors in the OLAP storage engine: The linked measure group with the ID of 'Sales', Name of 'Sales' cannot be processed because it contains MG dimension with the ID of 'Time', Name of 'Time' with different granularity attribute than its source object.
Errors in the metadata manager. An error occurred when loading the Sales measure group, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Data\FoodMart.0.db\Trained Cube.0.cub\Sales.1.det.xml'.
Errors in the metadata manager. An error occurred when loading the Trained Cube cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Data\FoodMart.0.db\Trained Cube.1.cub.xml'.

At this point, there doesn't seem to be any way to open any database in BI Studio. I always get that error, even if I try typing in another database name.

SQL Management Studio seems slightly better - I can at least connect there. But it only shows a couple of the databases that were on the machine - a bunch are missing. Any attempt to process or browse any of the databases results in the same error as above.

Any ideas?

Quoting the SP2 Readme: http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/ReadmeSQL2005SP2.htm

5.3.2 Analysis Services Databases that Contain a Linked Measure Group Might Become Unusable After Upgrade

Introduced in SQL Server 2005 SP2.

Analysis Services now requires that all dimensions contained in a linked measure group have the same granularity as the dimension in the source measure group. For example, if a time dimension in the source measure group has the day attribute as the granularity attribute, the linked measure group must also use the day attribute as the granularity attribute. The most common scenario for an Analysis Services database that violates this rule is a SQL Server 2000 Analysis Services database that was migrated by using a pre-release version of SQL Server 2005.

If you have a database that violates this granularity rule, you must modify the database containing the linked measure group before applying SP2. If you don't, the database will become invalid after SP2 is applied. Such invalid databases must be deleted.

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

|||Thanks Edward. RTFM, eh? Actually, I doubt it would have helped - this is a sample DB that I found somewhere, I wouldn't have realized it had this issue.

The problem is, I don't see a way to delete the database. The admin tools all barf with that error when I try to do anything, including deleting a DB. Is there some other way to nuke it?|||Unless you can go back to pre-SP2 build, you can stop the service, and manually delete all the files and folders in the Data directory. Then you will have to redeploy and reprocess the rest of your databases, unless you have backups in which case you would simply restore them.|||Thanks Mosha. I deleted the .0.db directory and the 0.db.xml file for the bad database (which I didn't need anyway, it was just for playing with), and have now regained access to my server. Any other files I should delete to clean things up?|||

This should be enough.

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

|||

I've recently installed SP2. All was fine initially but I'm now getting the error listed above having not changed anything significant (changed the membersname unique property of an attribute and then changed it back again). I'd been making changes all day to the attribute relationships and deploying without a problem.

I'm not using a linked measure group but the measure group it's reporting an error on has a different granularity key to the main measure group. Apart from changing the granularity key of the group that has a problem what can I do to fix this?

Mark

|||

I got the same error message which is posted in the first post here.

On a server which didn't had the foodmart database installed, I didn't get any problems after installing SP2. When I tossed the foodmart files away as described above, all problems vanished. It seems the problem here is the foodmart database. At the end of it Im glad I got my DB back, but "they" managed to scare the living daylight out of me with this, because I thought the whole AS had been turned into a wreck. Ironically the only database left in the management studio was the foodmart db. If you don't use it I would recommend to delete it before installing SP2.

Regards, Jeroen

Problems after installing SP2

I'm having some major problems with SSAS after installing SP2 (I never had an SP2 CTP installed, just SP1).

When I bring up BI Studio and select Open/Analysis Service Database, put in my server name (which is on the same machine that I'm running BI Studio), the click the "Database" drop down to select a database, it shows the following error in the bottom of the dialog:

Errors in the OLAP storage engine: The linked measure group with the ID of 'Sales', Name of 'Sales' cannot be processed because it contains MG dimension with the ID of 'Time', Name of 'Time' with different granularity attribute than its source object.
Errors in the metadata manager. An error occurred when loading the Sales measure group, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Data\FoodMart.0.db\Trained Cube.0.cub\Sales.1.det.xml'.
Errors in the metadata manager. An error occurred when loading the Trained Cube cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Data\FoodMart.0.db\Trained Cube.1.cub.xml'.

At this point, there doesn't seem to be any way to open any database in BI Studio. I always get that error, even if I try typing in another database name.

SQL Management Studio seems slightly better - I can at least connect there. But it only shows a couple of the databases that were on the machine - a bunch are missing. Any attempt to process or browse any of the databases results in the same error as above.

Any ideas?

Quoting the SP2 Readme: http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/ReadmeSQL2005SP2.htm

5.3.2 Analysis Services Databases that Contain a Linked Measure Group Might Become Unusable After Upgrade

Introduced in SQL Server 2005 SP2.

Analysis Services now requires that all dimensions contained in a linked measure group have the same granularity as the dimension in the source measure group. For example, if a time dimension in the source measure group has the day attribute as the granularity attribute, the linked measure group must also use the day attribute as the granularity attribute. The most common scenario for an Analysis Services database that violates this rule is a SQL Server 2000 Analysis Services database that was migrated by using a pre-release version of SQL Server 2005.

If you have a database that violates this granularity rule, you must modify the database containing the linked measure group before applying SP2. If you don't, the database will become invalid after SP2 is applied. Such invalid databases must be deleted.

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

|||Thanks Edward. RTFM, eh? Actually, I doubt it would have helped - this is a sample DB that I found somewhere, I wouldn't have realized it had this issue.

The problem is, I don't see a way to delete the database. The admin tools all barf with that error when I try to do anything, including deleting a DB. Is there some other way to nuke it?|||Unless you can go back to pre-SP2 build, you can stop the service, and manually delete all the files and folders in the Data directory. Then you will have to redeploy and reprocess the rest of your databases, unless you have backups in which case you would simply restore them.|||Thanks Mosha. I deleted the .0.db directory and the 0.db.xml file for the bad database (which I didn't need anyway, it was just for playing with), and have now regained access to my server. Any other files I should delete to clean things up?|||

This should be enough.

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

|||

I've recently installed SP2. All was fine initially but I'm now getting the error listed above having not changed anything significant (changed the membersname unique property of an attribute and then changed it back again). I'd been making changes all day to the attribute relationships and deploying without a problem.

I'm not using a linked measure group but the measure group it's reporting an error on has a different granularity key to the main measure group. Apart from changing the granularity key of the group that has a problem what can I do to fix this?

Mark

|||

I got the same error message which is posted in the first post here.

On a server which didn't had the foodmart database installed, I didn't get any problems after installing SP2. When I tossed the foodmart files away as described above, all problems vanished. It seems the problem here is the foodmart database. At the end of it Im glad I got my DB back, but "they" managed to scare the living daylight out of me with this, because I thought the whole AS had been turned into a wreck. Ironically the only database left in the management studio was the foodmart db. If you don't use it I would recommend to delete it before installing SP2.

Regards, Jeroen

Problems after installing SP2

I'm having some major problems with SSAS after installing SP2 (I never had an SP2 CTP installed, just SP1).

When I bring up BI Studio and select Open/Analysis Service Database, put in my server name (which is on the same machine that I'm running BI Studio), the click the "Database" drop down to select a database, it shows the following error in the bottom of the dialog:

Errors in the OLAP storage engine: The linked measure group with the ID of 'Sales', Name of 'Sales' cannot be processed because it contains MG dimension with the ID of 'Time', Name of 'Time' with different granularity attribute than its source object.
Errors in the metadata manager. An error occurred when loading the Sales measure group, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Data\FoodMart.0.db\Trained Cube.0.cub\Sales.1.det.xml'.
Errors in the metadata manager. An error occurred when loading the Trained Cube cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Data\FoodMart.0.db\Trained Cube.1.cub.xml'.

At this point, there doesn't seem to be any way to open any database in BI Studio. I always get that error, even if I try typing in another database name.

SQL Management Studio seems slightly better - I can at least connect there. But it only shows a couple of the databases that were on the machine - a bunch are missing. Any attempt to process or browse any of the databases results in the same error as above.

Any ideas?

Quoting the SP2 Readme: http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/ReadmeSQL2005SP2.htm

5.3.2 Analysis Services Databases that Contain a Linked Measure Group Might Become Unusable After Upgrade

Introduced in SQL Server 2005 SP2.

Analysis Services now requires that all dimensions contained in a linked measure group have the same granularity as the dimension in the source measure group. For example, if a time dimension in the source measure group has the day attribute as the granularity attribute, the linked measure group must also use the day attribute as the granularity attribute. The most common scenario for an Analysis Services database that violates this rule is a SQL Server 2000 Analysis Services database that was migrated by using a pre-release version of SQL Server 2005.

If you have a database that violates this granularity rule, you must modify the database containing the linked measure group before applying SP2. If you don't, the database will become invalid after SP2 is applied. Such invalid databases must be deleted.

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

|||Thanks Edward. RTFM, eh? Actually, I doubt it would have helped - this is a sample DB that I found somewhere, I wouldn't have realized it had this issue.

The problem is, I don't see a way to delete the database. The admin tools all barf with that error when I try to do anything, including deleting a DB. Is there some other way to nuke it?
|||Unless you can go back to pre-SP2 build, you can stop the service, and manually delete all the files and folders in the Data directory. Then you will have to redeploy and reprocess the rest of your databases, unless you have backups in which case you would simply restore them.|||Thanks Mosha. I deleted the .0.db directory and the 0.db.xml file for the bad database (which I didn't need anyway, it was just for playing with), and have now regained access to my server. Any other files I should delete to clean things up?
|||

This should be enough.

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

|||

I've recently installed SP2. All was fine initially but I'm now getting the error listed above having not changed anything significant (changed the membersname unique property of an attribute and then changed it back again). I'd been making changes all day to the attribute relationships and deploying without a problem.

I'm not using a linked measure group but the measure group it's reporting an error on has a different granularity key to the main measure group. Apart from changing the granularity key of the group that has a problem what can I do to fix this?

Mark

|||

I got the same error message which is posted in the first post here.

On a server which didn't had the foodmart database installed, I didn't get any problems after installing SP2. When I tossed the foodmart files away as described above, all problems vanished. It seems the problem here is the foodmart database. At the end of it Im glad I got my DB back, but "they" managed to scare the living daylight out of me with this, because I thought the whole AS had been turned into a wreck. Ironically the only database left in the management studio was the foodmart db. If you don't use it I would recommend to delete it before installing SP2.

Regards, Jeroen