SQL Server Performance

Pivot table using Cube object - data overflow error

Discussion in 'SQL Server 2005 Analysis Services' started by sqldev, Aug 7, 2007.

  1. sqldev New Member

    Hi,We are using SQL Server 2005. In SSAS, we created a dimension with 10 columns from a view and it returns 3000 rows in it. We created a cube using this dimension, it has no factual data and count of that view is the only measure in this.
    We make use of this cube object in Microsoft Excel 2003 Pivot Table. Now, in the hierarchical report, I am able to add very limited number of columns in the output. In this report, when I try to add the fourth column, it gives the following message,
    "Excel cannot display this PivotTable report. The row area has more than 65,536 items, or the column area has more than 16,384 items."
    It varies from report to report, it happens based on the volume of data. In another report, we get this message while adding the sixth column.
    In the similar scenario, I get another message and it stops the Analysis Service itself, we need to restart the services, clear the pivot table and recreate it again. The following is the error message,
    "Internal error:An unexpected exception occured in pivot tables"
    When we use SQL Select statement in pivot table instead of cube object, it works pretty well as intended.
    Please throw some light on this and help us to overcome this problem, thank you.
  2. satya Moderator

    Try to capture that MDX and look from SSMS to see how many rows are returned.

Share This Page