.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Excel 2007/2010 - SSAS 2008 R2 Offline cubes

Posted By:      Posted Date: September 27, 2010    Points: 0   Category :Sql Server

Hi all. I have a question regarding offline cubes (offline data files) in Excel 2007/2010 connecting to SSAS 2008 R2. When I try to make an offline data file in Excel I get a OLE DB error. The server I am trying to connect to is at a hosting partner on a different (but trusted) domain but I have admin rights on it. I then took a copy of the SSAS database and moved it to my own pc and could then create offline data file. I thought it might be a firewall issue on the server and tried turning it off, but the OLE DB error came again. I therefore think that there is something that happens over the network and is blocked by the hosting partner. I probably should tell that I am also SSAS admin, so it is not because of lack of rights on the SSAS that I get the error.

What I like to know if there is anyone who knows this problem or knows what happens when you try to create offline data file against SSAS in Excel? Specifically what is transferred between the client and the server, what ports and are there any programs run or code executed to create the offline cube on the client that might be blocked by typical network/domain settings/rules?

View Complete Post

More Related Resource Links

Excel 2007 Pivot table OLAP- SSAS 2008 Error

Hi, I do have a strange issue with Excel 2007 pivot table report; the data source is SSAS 2008 cube. When refresh the excel 2007 report data using the 'Refresh All' or 'Refresh' button available under the Data menu, the below error is thrown. "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples." After some analysis, the report works well on the below workaround, 1. Remove all the calculated measures from report (3 measures in this scenario) 2. Remove one row label (dimension attribute) from report But the measures, attributes are valid one from cube and it is required fields in the report. So, understand that removing few items is not at all a solution. The below workaround also works. 1. Check the 'Defer Layout Update' option from the Pivot Table Field List window. 2. Click on the Update button After that Unchecked the 'Defer Layout Update' option and apply the filter, etc in the report. The report works very well. But every time, we need to use the above work around after open the excel report. I have NO clue on this issue. Please help to resolve this issue. Thanks, Jey

SSAS 2008 R2 Cube being queried by Excel 2010 Pivot Table - 60x slower than with Excel 2003!


SQLServer 2008 R2

Excel 2010 x64

Hi All,

I have a fairly complex Excel report where a raw data sheet is updated from 10 or so PivotTables pulling from an Analysis Services Cube, (2008 R2), with a VBA script that refreshes all pivottables, and then does some post-processing, formatting etc.

This was originally developed in Excel 2003, and the updating of all the pivot tables ran in under 10 seconds

I've now recreated in Excel 2010 and *each table* is taking up to a minute to refresh!

I've also observed that I can get the refresh time of a single table back down to 1sec, if I delete all other pivottables from the worksheet/book.

Its deeply frustrating, as I'm touting 2010 as being the way for our business to go...it won't help the cause if I deliver something with a x60 increase in runtime!

I've run traces of both the 2003 and 2010 versions executing against the SQL Server, the only thing I can see is that the 2003 version passes 1 single MDX query, whilst the 2010 version appears to be firing it in sections.

I've posted this in the Excel forum too.

Please can anyone help?

Many thanks,





SSAS 2008 data refresh problem in Excel 2007



My problem sounds like that:

I have some cube created with SSAS 2008 and I'm using Excel 2007 as browser to view cube data.

I made pivot table with nessesary data, everything works fine till I save excel file and sent it to my client.

When client uses some filter, some row measures disapear. Dimension data are shown, measures are blank cells, but calculations from those measures is filled.

e.g. I see difference of sales between years in percent, but I can't see measures, which were used to count that difference, because in excel I see blank cells.

Any ideas?

Connection problems: Excel 2010 SSAS 2008 R2


I am using SQL 2008 Analysis Server 10.0.2740.0 on a Windows 2008 R2 Enterprise 64 bit OS.  I followed the instructions on setting up the msmdpump for IIS7.  My IIS7 box is also a Windows 2008 R2 Enterprise 64 bit OS.

I have an Excel 2010 64 bit spreadsheet on a Windows 7 SP1 64 bit OS, that currently has this as a connection string:
Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=PointBridge;Data Source=chipbcsql08;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

When I changed the connection string to Data Source=http://cube.mydomain.com/olap/msmdpump.dll instead of the servername of chipbcsql08 I get an Excel error dialog box:  The HTTP server returned the following error:  Method Not Allowed

I then click ok and get a 2nd Excel error dialog box that says:  Errors in the OLE DB provider.  An error occurred while loading the connection dialog box component for prompting.

Ideas on what I need to change?  I think I am about to make this happen!!  haha  Helpful thoughts will be rgeatly aprpeciated!!


In sharepoint excel working fine in office 2007 but not in office 2010


Hi All,

we receive error  while trying to create excel sheet using the template we have uploaded on the SharePoint 2007.


This occurs on a machine that has Office 2010 installed. Earlier the machine had Office 2007 and everything was working fine.


Can you let me know why is problem is coming and how to resolve this.



Sanjeev Tiwari


Sharepoint 2007 and Excel 2010 features


Does Sharepoint 2007 Server support the new Excel 2010 feaures like slicers? Or do i have to have Sharepoint 2010 to get that working (considering all other components besides Sharepoint 2010 are available in my Sharepoint 2007 environment) ?

- Thanks



Will the old Excel 2007 Datamining Solution Work With SQL 2008 Express?

Or does it need the full version?  Is there a new version of the Datamining add-in for 2008 Thanks Peter

Cannot List SSAS Catalogs in Excel 2007

I am trying to access SSAS 2005 through excel on a client computer.  I get the following error message:  The data connection wizard cannot obtain a list of databases from the specified source.  It looks like I connect, but I cannot list any of the catalogs.  Both catalogs are migrated from a sql2000 database.    I ensured that the account has a browsable role through the roles in SSAS.  Interestingly if I create a UDL file with a connection to ssas and choose existing connections and specifiy the udl file I can access the specific ssas catalog.  In creating the udl file I cannot see the catalogs, but I can type in the catalog name which connects me and allows me to browse the cube using excel.  Unfortunatley, my users use other applications besides excel which don't allow access through udls.  Any help would be appreciated.

How to export data from SQL 2008 R2 to excel 2010

Hi, I am using microsoft sql 2008 R2 express server and I have window 7 64 bit and office 2010.  I would like to export data form sql to excel using a query in sql.  I tried a few examples I found on the web, but they don't work.  Please help. Thanks

Excel 2007 connectivity to SSAS 2005 cube

Hello - I am using an excel 2007 odc file to connect to an analysis services 2005 cube (on windows server 2003 R2 OS). I receive the following error message   "Excel was unable to get necessary information about this cube. The cube might have been reorganized or changed on the server" Have already attempted the following fixes but running out of ideas now 1. Added localeIdentifier to ODC file    <odc:ConnectionString>Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Data Source=bisql;Initial Catalog=SLAM ACT BUD; LocaleIdentifier = 1033 </odc:ConnectionString> 2. Ensured that all dimensional attributes which are used in MDX calculations (named sets, calculated members) are set to "AttributeHierarchyEnabled  = True" Running out of steam so any help is greatly appreciated.

Sharepoint 2010 and Excel 2007

i just installed sharepoing 2010 enterprise (not an upgrade) and sqlserver 2008 on another machine. everything is working well, however, i cannot open any excel documents saved in 2007 format. if i save the document in 2003 format, i can open and edit the file. this is not true for word, in which either 2003 or 2007 opens perfectly. not sure what the problem is. the error on the server is 5244. ExcelServerWebServiceApplication.Local: Could not get the web application associated with this context. This indicates that the Excel Server service is not properly registered or provisioned. is registered in event viewer.  

Range or List Filtering via SSAS cube in Excel 2007

Hi All, A client has posed an interesting question, as current users of business objects webi they can filter the results of a cube/universe by copy and pasting a range or list of values seperated by a ';' into a list filter that is availble. e.g. product2;product533,;product029;product8389 etc etc. The list can sometimes be hundreds long. How is that same function acheived in browsing a ssas cube in excel 2007, all I can see is that a user has to use the drop down list and individualy select the values they want. This does not seem like a great method! Any ideas? They are using SQL / SSAS/ SSIS/ SSRS 2008 R2 Cheers DC

Can multiple users connect simulateously to an offline cube created by SSAS 2008?


We have created an offline cube on a network drive from SSAS 2008 and users are able to successfully access this serially using the Excel 2003 cube add-in or Excel 2007, in concert with version 10 driver, but when a user is already connected, the second attempt to connect will get the message :

Connection to <filename> server, <dbname> database, <cubename> cube failed.  OLAP Server error: The following system error occurred:  The process cannot access the file because it is being used by another process. ., File system error: An error occurred while opening the '<filename>' local cube file.

This is regardless of whether users connect specifying read-only and we have found it impossible to connect to a cube if the file has the read-only attribute set.

Is there any way to solve this without users taking copies onto their workstations?



Cannot Export SQL 2008 Table to Excel 2010


I'm trying to use SSIS in Visual Studio 2008 to export a table to Excel 2010 and get the following error:


The table cannot be created. (Microsoft Visual Studio)


Syntax error in field definition. (Microsoft Office Access Database Engine)

Program Location:

   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
   at Microsoft.DataTransformationServices.Design.PipelineUtils.ExecuteSqlStatement(ConnectionManager connectionManager, String sqlStatement, IServiceProvider serviceProvider, Boolean getReader)
   at Microsoft.DataTransformationServices.Design.DtsTableCreator.okButton_Click(Object sender, EventArgs e)

What type of account is needed for deploying/maintaining SSAS cubes in SSAS 2008 R2


Hi All,

Can someone help in understanding what type of account is needed for deploying/maintaining SSAS cubes in SSAS 2008 R2?

Does one have to be in the local administrators group on the SSAS server? Can a domain group/user (non administrator) be assigned to any role that enables them to deploy cubes etc. If so, what Impersonation Mode will be used from within the dev studio while trying to depoly the project?

Can someone point to any documentation as well. I must not be searching right as I am not able to find an exact information.



SSAS KPI stauts in excel 2007


My question is about the useage of a KPI status icon in excel 2007.

I have created a KPI in SSAS which will calculate the difference of two measure which is Actual FH FC ratio and Average FH FC Ratio..
These two measures combined with the KPI value AND the Date dimension gives only those records back for that fact and Date. IN this examples it will return from the year 2006 thry 2009 facts (DimDate is from 19300101 thru 20301231) and this is what i want and get in excel. again the status of this KPI is unchecked from the PivotTable Field list.


Sharepoint 2010 and Excel 2007




I am creating a windows utility where user select list from a windows form combo box and inserting the excel data into the sharepoint list.

Issue that i am facing is, SharePoint runs on VS2010 project properties Platform Target as "Any CPU" while excel 2007 driver "Microsoft.ACE.OLEDB.12.0;" workins with Platform target as "x86".


Both are not working with each other, while if I am running them separately, they are working.


Kindly suggest.





ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend