.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 report reads data from all the partitions and it takes time

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :

I have a cube of size 300 GB and it has daily partitions and when refresh the report for the all the days (I have one year data so around 270 partitions)  and it takes data from each partitions so it reads all the partitions which i can see clearly from the profiler.

This is logically correct because i am having no data filter But looking for all the days in my rows from all the partitions BUT Users don't wanna wait everyday for 20 min just to keep waiting for the excel report to come.

I tried to do aggregations as well But it didn't help again it reads all the aggregations for each.

When we run for few days or even months of data its fine BUT when report is for the whole year it will read all the partition and eventually takes a lot of time. my dilemma is how to avoid this so that i can reduce the time

I also tried cache worming like create cache from my cube as  ({dimension.dimension.members}*{dimension.dateDimension.members} )
Seems like no effect .

Any one please have better idea remember i am admin and users are just user group so separate roles so cache can't be shared

Any idea how i can reduce this time



View Complete Post

More Related Resource Links

Export SSRS report to excel with data of pie chart



I am new to SSRS. I have a report with a pie chart . When I export the report in excel format, pie chart is displayed as picture. I want it to be displayed as editable pie chart with data. 

The requirement is, if user want to change the data in excel sheet after exporting, changes should reflect in pie chart of that excel sheet.

Please suggest me the way of achieving it.


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.

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.

Data Types - Date and Time in SqlServer

Date and time values can be stored with either the DATETIME or SMALLDATETIME data type. The difference between the two is that SMALLDATETIME supports a smaller range of dates and does not give the same level of precision when accounting for time. The DATETIME data type can hold values from January 1st of 1753 to December 31st of 9999. The time is stored to the 1 three hundredths of a second and each value takes up 8 bytes of storage. The SMALLDATETIME data type can hold values between January 1st 1900 and June 6th of 2079. The time is tracked to the minute and each value takes up 4 bytes of storage. The majority of business applications can live happily with SMALLDATETIME, however, if you are in an environment where each second matters or you need to make estimates to the distant future (or past) then you have to resort to DATETIME. If you fail to specify the time when inserting a value into a DATETIME or SMALLDATETIME column, a default of midnight is used. If you fail to specify the date portion the default of January 1, 1900 is used.

Date and Time Data Types and Functions

The following sections in this topic provide an overview of all Transact-SQL date and time data types and functions. For information and examples that are common to date and time data types and functions

huge number of data from database ... so how to minimize load time


Hi ...

I have used the above method to configure my crystal report ...

Its working fine , But i have huge number of data in database ....

So , it take long time to load the report ...

So , i have decided to show only the last 50 records inserted into the database , not all the 500 records to be loaded every time.

Plz help me with this issue  ...


Also , Plz explain me abt index legend in crystal report.

delete data in excel sheet



           I have write the code for deleting the data in excel sheet. but we have got the error" Deleting data in a linked table is not supported by this ISAM."

     below I paste some code snippest.

string strDelete = "Delete  from [" + strSheetName + "$]";

            cmdExcel.Connection = con;
            cmdExcel.CommandType = CommandType.Text;
            cmdExcel.CommandText = strDelete;


Please can anybody this answer.It is urgent

Export Gridview data to Excel sheet


Hi ,

     I have a requirement where i need to export the gridview data to excel.My code export the data in excel that was fine.

But the enchancement needed now , I was exporting the 3 gridview in same excel sheet under the workbook.

Now i need to export the 3 grid view in 3 different sheet in the same workbook.

so can please tell or share the updated code in my below code ...

public void writeovrsummary()


        Response.Write("Overall Summary");
        //Response.Write("Report as of " + DateTime.Now.ToString("hh:mm ss tt"));

        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);

        SqlCommand com = MyConnection.CreateCommand();
        com.CommandText = "useroverall_Report";
        SqlParameter[] par = new SqlParameter[1];
        par[0] = new

Insert data to Excel using OLEDB



I am trying to insert data to Excel Document using OLEDB as follows:-

 sql = "Insert into [MyFirstSheet$] (" + Title1 + "," +Title2 + "," + Title3 + ") 
values('" + Value1 + "','" + Value2 + "','" + Value2 + "')";
myCommand.CommandText = sql;

 sql = "Insert into [MyFirstSheet$] (" + Title1 + "," +Title2 + "," + Title3 + ") 

values('" + Value1 + "','" + Value2 + "','" + Value2 + "')";

myCommand.CommandText = sql;


But, the Title1 ,Title2, Title3 are in the 3rd row of the Excel Document. So, the data is not inserted.

Merge SQL to Excel--Need .Net button to open on fly and have current SQL data!


           Hello all,
I do not know if this is the proper place to ask this, but my question is...
I have a SQL DB and I want to convert it to Excel Sheet (I did this using the wizard)!
In my aspx page I have a button.  So when I press this button I want the Excel sheet to open!  But I need the Excel sheet to show me the up to date information that is in the SQL DB.  

Passing Data to a Cell in Excel



I have a webpage that is created in Visual Studio 2008 and programmed in C#.

I have a web form that prompts the user to enter a value into a text box.  For now, I do not have any validation on that field (I will be adding that later).

I need to pass the value that the user has entered to cell C1 of the excel spreadsheet (user_info.xls).

I understand that using excel is not ideal, but unforutnately need to try to make this work with the excel spreadsheet.

I do not have any code for this yet, because I honestly do not know how to begin.

The action of poulating the excel spreadsheet will be intialized from the On Click event of a Submit button.

Any suggestions would be much appreciated.

Thank you in advance for your assistance,


Crystal Report Asking for Database Authentication each time when I view Page.


Whenever I open my Crystal Report page, I am taken to the Database Authentication page where I am asked for

User name
Data Name

each time.

Is there a way to avoid this and I save these authentication in my page once.

I am using VS2005, C#.

Any Help will be appriciated.


display data in excel 2007


 I am using .net  version 1.1  and  excel 2003 to display data.I need to display data in 2007 .Can  anyone suggest the reference to be added ,connection string change and what should be imported.  

Test Run: Using Excel For Test Data


This month see how to use Excel for test automation storage, whether you're just starting out with NET, or you're an advanced programmer.

Dr. James McCaffrey

MSDN Magazine November 2006

SQL Server 2005: Jazz Up Your Data Using Custom Report Items In SQL Server Reporting Services


Custom report items in SQL Server 2005 Reporting Services address your needs for custom reports without the pain of doing it from scratch.

Teo Lachev

MSDN Magazine October 2006

Data Points: Report Controls in SQL Server 2005 Reporting Services


Reporting has always been one of the dark arts of development. The tools typically seem to do just enough to get you to a certain point, then leave you to find workarounds to solve more complex issues.

John Papa

MSDN Magazine July 2006

Data Binding: Give Your Everyday Custom Collections a Design-Time Makeover


In this article, Paul Ballard shows how you can provide data binding support for your custom collections to enable sorting, searching, and editing in as simple a manner as possible. In addition, he shows how to make all of these features available in the Windows and Web Forms Designers, just like an ADO.NET DataSet.

Paul Ballard

MSDN Magazine August 2005

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