.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

SQL Server 2005 Analysis Services - Performance of Excel Pivot Tables

Posted By:      Posted Date: September 15, 2010    Points: 0   Category :Sql Server
Hi, I've several cubes available for several users within my organization. I've the SQL Server 2005 and the Analysis Services running on the same machine but it has 30Gb of RAM, 8 processors and a quick enouth disk. The problems is that i've some automatic processes that "build" some excel reports and the users use pivot tables with olap connections to the cubes regularly. I've noticed that the excel "locks" when the user is drilling down the data and the server has only one processor (there are 8...) at 100%... Is there a way to make the analysis services use the maximum processing capacity that is available? The users are complaining about the slowness of the cubes... Thanks in advance Hugo

View Complete Post

More Related Resource Links

SQL Server 2005: Unearth the New Data Mining Features of Analysis Services 2005


In SQL Server 2005 Analysis Services you'll find new algorithms, enhancements to existing algorithms, and more than a dozen added visualizations to help you get a handle on your data relationships. Plus, enhancements to the Data Mining Extensions to SQL along with OLAP, DTS, and Reporting Services integration make it possible to create a new breed of intelligent apps with embedded data mining technology. Here the author explains it all.

Jamie MacLennan

MSDN Magazine September 2004

XML for Analysis Parser; The restriction value - sql server 2005 with Excel 2010 error


Trying to refresh an existing pivot table that is connected to Analysis Services Sql Server 2005 database.  My excel 2010  gets the eroor:\

XML for Analysis parser:  The restriction value provided by the consumer either does not match other restrictions or refers to an unknown object.

I already tried the pivot table patch and I am still experiencing the error.  I also tried starting a new excel spreadshsst and connecting to two other sql servers of newer verions and was succesful.

SQL Server 2000 Analysis Services & Excel 2003 Add-in

I've been working with SQL Server 2000 AS cubes and presenting them in pivot-tables in Excel 2003 with no problems.

I was recently looking at other BI tools I could use and stumbled across the Excel Add-In for Analysis Services, which adds a Cube Analysis menu to Excel and seems to have lots of functionality I could use.

But! It's no longer supported by Microsoft.

I managed to get a download of it from www.download.com and searched for help and I have some good documents to help me understand how to use it, but my problem is that it just doesn't work! I've uninstalled and reinstalled and rebooted, but to no avail.

I can connect to my data source and cube and I can view the dimensions and measures, but when I click apply on them and select the area of the report to put them into, they just don't appear. I can briefly see an EMBED command appear in the formula bar and then disappear, but nothing happens!

I've been through all the trouble-shooting and checked the settings of Excel as requested, but nothing works. 

Has anyone else had this problem or have any suggestions as to how to fix it?

Analysis Services 2008 e Excel pivot table: different filters for each column


I have to create a report in excel 2010 based on a  analysis services 2008 cube.

I created a pivot table that contains four columns containing the elements of a hierarchy. My problem is that each column should be filtered with different values of the same field.

For example: given a hierarchy that describes me a set of products, I have to filter the product for customer  1 and 3, Product B for  customer 2 and the product C for the customer 3.

I think this thing is not possible in Excel (right?)

So I decided to use a mdx script to filter each product 

I tried so (for example, product A):
scope (descendants([Products].[products].[liv0].&[A], 1, leaves));
[Measures].Quantity = sum({[Customer].[Code].&[01], [Customer].[Code].&[03]}, [Measures].quantity);
end scope;

I still get an error of infinite recursion. How can I solve my problem? Is there a better system than what I am following?

SQL Server 2005 Analysis Services Step by Step Companion CD


Hi everyone,

I'm also reading this book, and do not have the companion cd. If someone could e-mail it to me at: sibasishdasgupta@gmail.com i would really appreciate it. Thanks in advance.

Where to find code samples for "SQL Server 2005 Analysis Services Step by Step" book?


I am reading SQL Server Analysis Services from "SQL Server 2005 Analysis Services Step by Step" book and I want to download the code samples for the same. Please tell me how I can do that?

Thanks in advance,

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 Points: Designing Reports with SQL Server Reporting Services 2005


Many applications require some degree of integration with a reporting tool. A good solution, SQL ServerT Reporting Services 2005, provides Web-based reports and can be integrated into both Windows® Forms and Web-based applications.

John Papa

MSDN Magazine June 2006

OLAP: Build an OLAP Reporting App in ASP.NET Using SQL Server 2000 Analysis Services and Office XP


Many organizations analyze their business-critical data using Online Analytical Processing (OLAP) technology. OLAP-based data mining provides a way to query multidimensional data sets and drill down into the data to find patterns. ASP.NET and the Microsoft Office Web Components (OWC) enable Web-based OLAP reporting. The OWC controls include PivotTable and Chart components that can be embedded in a Web page and scripted by programmers. In this article, the authors build a Web-based OLAP reporting app using ASP.NET, OWC, and SQL Server 2000 Analysis Services to illustrate the process.

Jeffrey Hasan and Kenneth Tu

MSDN Magazine October 2003

Problem when installing Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint


I have configured the SharePoint Production Server(Standalone) with WS 2008 64 bit version, SQL Server 2005 with SP3 , Visual Studio 2008 and MOSS 2007(SP3). I have configured SSRS 2005 in SharePoint integration mode. Everything works fine till the last step. Now, i am trying to install Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies feature and failed. I am getting below error message.

Product: Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies -- You must first install SharePoint Server before installing SQL Server 2005 Reporting Services Add-in for SharePoint Technologies

Any help will be very appreciated!


Consuming External Data Using SharePoint Server 2010 Business Connectivity Services and an Excel 201

Learn how to use BCS in SharePoint Server 2010 to access and update external data by using Microsoft Excel 2010 as a client.

Interacting with the Excel Web Services API for SharePoint Server 2007

Get a quick start with the Excel Web Services API, which enables interaction with published Excel 2007 workbooks in SharePoint Server 2007 from a remote application. Learn considerations around session state, security, and performance.

Restore - Cube Analysis Services 2005

Hi, I'm from Brazil. Sorry for my English. I made a backup for my cube that has more than 2 GB, but de backup is created with only 50 mb. The backup don't showed erro message. I restored this backup without erro, but when I tryed query the cube, i receive this message: The query could not be processed: o File system error: The following file is corrupted: Physical file: \\?\C:\SQL Server\MSSQL.2\OLAP\Data\teste.59.db\"Nome_Servidor" - "Nome_Database".190.cub\Fato Inadimplencia Safra Contrato.190.det\Fato Inadimplencia Safra Contrato.167.prt\172.fact.data. Logical file . Someone can help? I realy need make this backup.Fabrício França Lima | MCP, MCTS, MCITP | Visite meu site: http://fabriciodba.spaces.live.com/ | Dicas de artigos SQL: Siga-me no twitter @fabriciodba.

SQL Server 2005 Performance Dashboard Reports user context

HI, We are planning on installing the Dashboard reports. But we'd like to know if the Dashboard is run under admin user or the login user who runs the Dashboard reports? Thanks! Kong

SQL Server 2005: Install reporting services - which version?

Hello - I installed SQL Server 2005 as part of an SBS2008 install. I need to install reporting services, but now I'm having trouble identifying which version (X64 or X86) I'm running. Is there an easy way of figuring out which installation disk I need to insert for SQL Server 2005? Thanks, Nick.

Kerberos issue with SQL Reporting Services 2005 on Server 2003 R2

Hi Guys,apologies if this is the incorrect forum, so moderators, feel free to move it to SQL/IIS/SharePoint as appropriate... [Windows Server Security moderator pushed me this direction]I have a test environment that I'm trying to get SQL Reporting Services 2005 SP3 working in integrated mode with SharePoint 2007 SP2.The environment is all in VMWare, running Server 2003 R2 x86 and is layed out like this:SERVER A:AD/DNS/DHCPSERVER B:SQL 2005 SP3 CU8SERVER C:SharePoint 2007 SP2 Dec 09 CU- Central admin on port 9000- SSP on port 9001- MySite on port 81- Main Content on port 80SQL Reporting Services 2005 SP3 CU8- Reporting Service website on port 82SERVER D:SharePoint 2007 SP2 Dec 09 CU- Central admin on port 9000- SSP on port 9001- MySite on port 81- Main Content on port 80SQL Reporting Services 2005 SP3 CU8- Reporting Service website on port 82Through the use of DNS and (SharePoint) Alternate Access Names, SERVER D is used to deliver the Main Content in SharePoint and the Reporting Service website.  SERVER C is used to deliver the Central Admin, SSP and MySite.I've set up SPN's for the SharePoint App Pools, using the following: [main content] setspn -S HTTP/SERVERA DOMAIN\AppPoolUserA setspn -S HTTP/SERVERA.FQDN DOMAIN\AppPoolUserA setspn -S HTTP/SERVERB DOMAIN\AppPoolUserA setspn -S HTTP/SERVERB.FQDN DOMAIN\AppPoolUserA [repor
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