.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 "Keep Only Selected Items" is SLOW against cubes

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server

When connected to an AS database, we can use the Excel pivot table filter technique “Keep Only Selected Items” on some dimensions but not others.  I have a fairly large dimension that can handle it no problem ( < 1 second ) and another smaller dimension that takes several minutes to return a result.  During that time, there is very little SQL Profiler activity on the AS Server and very high processor use on the Excel machine.  In both cases there is very little time logged on SQL Profiler between Query Begin and Query End – the query is fast.  It is also quick to run the same MDX query in Mgt Studio. 

Is this a solve-able problem?  The analysts here use this function pretty heavily. 


View Complete Post

More Related Resource Links

Performace point/Excel services OLAP cubes

Running Sharepoint 2010 Ent fresh install.  I have created a new site and performance point service as instructed in the following tutorial. http://blogs.msdn.com/b/performancepoint/archive/2009/11/24/deploying-performancepoint-2010-soup-to-nuts.aspx I have then uploaded an Excel document which pulls data from OLAP cubes.  The Excel doc as a standalone works fine.  After it has been uploaded users can open it as normal etc but when changing slicers and trying to pull info from the cubes I get the following error.. An error occurred durning an attempt to establish a connection to the external data source.  The following connections failed to refresh: "Name of out cubes here" I have been playing with the secure store and generating keys etc to no avail.  I have tried exporting the .odc file from the Excel document and uploading it to the data connections library/dashboard site but this doesnt help The anoying thing is that I had it working once and then the next day it failed for no apparent reason and even after a fresh install I cant get it to work again.  Any pointers or tutorials would be greatly appreciated! Andy

Excel 2007/2010 - SSAS 2008 R2 Offline cubes


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?

Report exported to Excel 2007 is EXTREMELY slow once a filter is applied.


I am exporting a 12,000 row, 20 column report to Excel from SSRS 2008.  Once opened in Excel, everything is fine.  If I further filter the data through Excel (no external data connections) performance degrades.  If I filter down to say 20 rows, it nearly completely bogs down my entire PC.  I've emailed this spreadsheet to several others, who then experience the same issue.

If I export this same set of data from a different BI tool, such as Microstrategy, there are no performance issues what so ever.

SSAS cubes processing slow



Can i use Process data option in SSIS Analysis services task for sheduling SSAS cubes.

Now im using Process Full option, It is taking 1 hour for processing 44 lakhs records.

If i use the Process data option whether it will reduce process timings..

or tell me the ways to reduce the process timing in scheduling.

If i use Process data instead of Process full any disadvantages there??

Please guide me..



Export Visio Database Table Names to Excel

If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

Excel Automation in .Net

Excel Automation is a buzz word in both webapps and winapps. In the programming life (like Mr.Anderson neo) I guess almost every one could have come across this word. For others let me go further in detail. Excel Automation is automating some or all of the process involved in creating or updating excel worsheets.

display data into GridView from excel sheet

I have to display data from excel sheet into my gridview and also I need to figure out a way to store data into excel files from my web page. I am using C# 3.5, VS 2008.

Export SharePoint List to Excel Spreadsheet Programmatically using C#

In SharePoint applications, Custom Lists are used to store business data and Document Libraries to store the documents. But for data manupulation and analysis, Microsoft Excel provides very rich features as compared to SharePoint Lists. That's why people still loves to work on Microsoft Excel Sheets.

Creating an Excel Spreadsheet programmatically using VB.NET

The Interoperability services make it very easy to work with COM Capable Applications such as Word and Excel. This article reveals using Excel from a managed application. Excel is the spreadsheet component of Microsoft Office 2000. The majority of Excel programmatic functionality is exposed through Automation via the type library Excel9.olb. The intention of this article is to express that a managed application can interrelate with Excel as a COM server.

Create And Run Excel Macro At Runtime in C#

You've been tasked with writing a Windows Form application using the .NET Framework that interacts with Microsoft Excel. If you are new to working with Excel in .NET and are having trouble learning how to format certain cells or cell ranges, then this little tip will come in handy. Rather than scour the internet hoping to find the correct set of constants or method syntax for formatting your spreadsheet, use the built in tools in Excel to write the familiar VBA code for you. Then, you can paste the VBA code generated by Excel into the sample C# code below and run it dynamically at runtime.

Creating an Excel Spreadsheet and Adding Data to It Programmatically

The Interoperability services make it veryeasy to work with COM Capable Applications such as Word and Excel. Thisarticle reveals using Excel from a managed application. Excel is thespreadsheet component of Microsoft Office 2000. The majority of Excelprogrammatic functionality is exposed through Automation via the typelibrary Excel9.olb. The intention of this article is to express that amanaged application can interrelate with Excel as a COM server.

Creating Excel file from C#

I used below coding for create a excel sheet.I got from on web site.
In below coding there is no problem.Its create the excel file.If i run second time,its ask to overwrite the current excel file.If I said "Yes" then it ok ,but If I select cancel button,its generate the Error message.The exception is

Gridview that exports selected rows to Excel

his seems like a messy workaround, but I ended up using two Gridviews - one to display my data and show my checkboxes, and the other which was invisible amd would eventually end up as my Excel spreadsheet.

PrintPocketCE Print Pocket Excel, Pocket Word and email files

Version 3.560 (May 15, 2009):

Important bug fix: a slight difference in how some devices create fonts was causing a few devices to have significantly longer print times for large print jobs.

Adjusted PocketJet printer paper feed commands

Other minor fixes and enhancements.

Version 3.559 (Mar 5, 2009):

Added support for Martel MCP78xx printers

Version 3.558 (Aug 20, 2008):

Added support for Martel MCP78xx printers

Version 3.557 (June 27, 2008):

Fixed error in Canon printer support

Fixed COM0 port selection

Version 3.556 (June 15, 2008):

Added support for Brother MW-260

Added support for Sato MB400

Version 3.555 (Aug 8, 2007):

Added support for Pentax RuggedJet 3 and RuggedJet 4 printers

Changed Epson TM-P60 support to maximum page width of 1200 dots

Version 3.551 (Aug 1, 2006):

Added Peripheral Nomad printer support

Version 3.550 (Mar 23, 2006):

Added Panasonic JT-H200PR printer support

Added Pocket Spectrum printer support

Fixed problem with WM5.0 "inverted image" problem

Response.Redirect slow


I'm in the middle of migrating servers and on the new server Response.Redirect is extremely slow. It takes around 20 seconds for the browser to get the redirect. This is something that was quick on the existing server. It's an ASP.NET 2.0 application, both servers are running IIS6.

Additionally, if I "manually" do the redirect, it's fast. For example:

Existing way:


takes around 20 seconds on the new server

New way:

Response.Status = "302 Moved Temporarily";
Response.AddHeader("Location", url);

is fast on the new server.

Any ideas why Response.Redirect would be so slow on the new server?

VS 2010 Pro is slow



I was wondering if anybody else is finding VS 2010 extremely slow loading a solution - and it pretty much renders the rest of the machine useless until loaded. Everything is slowed to a halt.

I upgraded from VS 2005 - now I think I made a terrible mistake. I cannot afford to try and load 2 solutions simultaneously.

The machine is a Core2 Duo E8400, 3GB of RAM, WinXP SP3. Max I can go to is 4GB. Would upgrading to a 64-bit OS really be worth it (actually only to get 8GB of RAM)?

I'm hoping someone else has found a solution to this particular problem.



Convert excel file into xml


Hai, I have two columns with id,name in the excel sheet.I want to convert into xml file in the below format

<?xml version="1.0" encoding="utf-8"?>

<guide xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <Boat id="1"> <name> Copper Kettle </name> </Boat> <Boat id="2"> <name> Copper Penny </name> </Boat> <Boat id="3"> <name> Copperhead </name> </Boat> <Boat id="4"> <name> Coppertone </name> </Boat> <Boat id="5"> <name> Copy Boy </name> </Boat> </guide>

suggest me with sample code in ASP.NET. Thanks

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