In SharePoint 2010, using PerformancePoint Dashboard Designer tool, you are trying to create report and it brings the following message:Unable to create report. Query produced too many results.
Contact the administrator for more details.
Event Viewer Errors on SharePoint App Server:
Error 1:
Log Name: Application
Source: Microsoft-SharePoint Products-PerformancePoint Service
Date: 2/4/2014 11:37:55 AM
Event ID: 1
Task Category: PerformancePoint Services
Level: Error
Keywords:
User: DomainSPFarmAct
Computer: SrvApp02.domain.com
Description:
An exception occurred while running a report. The following details may help you to diagnose the problem:
Error Message: Error running data source query.
<br>
<br>
Contact the administrator for more details.
Dashboard Name:
Dashboard Item name:
Report Location: {6ed9a727-029b-1e22-d81a-f39274d4d9e2}
Request Duration: 300,564.30 ms
User: DomainBIDeveloper
Parameters:
Exception Message: Error running data source query.
Inner Exception Message:
Stack Trace: at Microsoft.PerformancePoint.Scorecards.Server.PmServer.ExecuteAnalyticReportWithParameters(RepositoryLocation
analyticReportViewLocation, BIDataContainer biDataContainer)
at Microsoft.PerformancePoint.Analytics.ServerRendering.OLAPBase.OlapViewBaseControl.ExtractReportViewData()
at Microsoft.PerformancePoint.Analytics.ServerRendering.OLAPBase.OlapViewBaseControl.CreateRenderedView(StringBuilder sd)
at Microsoft.PerformancePoint.Scorecards.ServerRendering.NavigableControl.RenderControl(HtmlTextWriter writer)
PerformancePoint Services error code 20604.
Event Xml:
<Event xmlns=”http://schemas.microsoft.com/win/2004/08/events/event”>
<System>
<Provider Name=”Microsoft-SharePoint Products-PerformancePoint Service” Guid=”{A7CD5295-CBBA-4DCA-8B67-D5BE061B6FAE}” />
<EventID>1</EventID>
<Version>14</Version>
<Level>2</Level>
<Task>1</Task>
<Opcode>0</Opcode>
<Keywords>0x4000000000000000</Keywords>
<TimeCreated SystemTime=”2014-04-18T16:37:55.171984000Z” />
<EventRecordID>603586</EventRecordID>
<Correlation ActivityID=”{43305F92-CD1F-4E75-B19B-2581FE7C04CD}” />
<Execution ProcessID=”6420″ ThreadID=”6468″ />
<Channel>Application</Channel>
<Computer>SrvApp02.domain.com</Computer>
<Security UserID=”S-1-5-41-1623458274-11437692956-399368559-1726″ />
</System>
<EventData>
<Data Name=”string1″>An exception occurred while running a report. The following details may help you to diagnose the problem:
Error Message: Error running data source query.
<br>
<br>
Contact the administrator for more details.
Dashboard Name:
Dashboard Item name:
Report Location: {6ed9a727-029b-1e22-d81a-f39274d4d9e2}
Request Duration: 300,564.30 ms
User: DomainBIDeveloper
Parameters:
Exception Message: Error running data source query.
Inner Exception Message:
Stack Trace: at Microsoft.PerformancePoint.Scorecards.Server.PmServer.ExecuteAnalyticReportWithParameters(RepositoryLocation
analyticReportViewLocation, BIDataContainer biDataContainer)
at Microsoft.PerformancePoint.Analytics.ServerRendering.OLAPBase.OlapViewBaseControl.ExtractReportViewData()
at Microsoft.PerformancePoint.Analytics.ServerRendering.OLAPBase.OlapViewBaseControl.CreateRenderedView(StringBuilder sd)
at Microsoft.PerformancePoint.Scorecards.ServerRendering.NavigableControl.RenderControl(HtmlTextWriter writer)
PerformancePoint Services error code 20604.</Data>
</EventData>
</Event>
Error 2:
Log Name: Application
Source: Microsoft-SharePoint Products-PerformancePoint Service
Date: 2/4/2014 11:37:55 AM
Event ID: 2
Task Category: PerformancePoint Services
Level: Warning
Keywords:
User: DomainSPFarmAct
Computer: SrvApp02.domain.com
Description:
Error running data source query.
XML for Analysis parser: The XML for Analysis request timed out before it was completed.
Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: XML for Analysis parser: The XML for Analysis request timed out before it was completed.
at
Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.IExecuteProvider.ExecuteMultidimensional(ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection parameters)
at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.ExecuteCellSet()
at Microsoft.PerformancePoint.Scorecards.DataSourceProviders.AdomdDataSourceProvider.RunQueryInternal(String query, Boolean
isCellSet, Int32 sessionTimeout)
PerformancePoint Services error code 10116.
Event Xml:
<Event xmlns=”http://schemas.microsoft.com/win/2004/08/events/event”>
<System>
<Provider Name=”Microsoft-SharePoint Products-PerformancePoint Service” Guid=”{A7CD5295-CBBA-4DCA-8B67-D5BE061B6FAE}” />
<EventID>2</EventID>
<Version>14</Version>
<Level>3</Level>
<Task>1</Task>
<Opcode>0</Opcode>
<Keywords>0x4000000000000000</Keywords>
<TimeCreated SystemTime=”2014-04-18T16:37:55.156358900Z” />
<EventRecordID>603585</EventRecordID>
<Correlation ActivityID=”{43305F92-CD1F-4E75-B19B-2581FE7C04CD}” />
<Execution ProcessID=”6420″ ThreadID=”6468″ />
<Channel>Application</Channel>
<Computer>SrvApp02.domain.com</Computer>
<Security UserID=”S-1-5-21-1627688274-1190192956-3999157559-1626″ />
</System>
<EventData>
<Data Name=”string1″>Error running data source query.
XML for Analysis parser: The XML for Analysis request timed out before it was completed.
Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: XML for Analysis parser: The XML for Analysis request timed out before it was completed.
at
Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.IExecuteProvider.Ex
ecuteMultidimensional(ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection
parameters)
at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.ExecuteCellSet()
at Microsoft.PerformancePoint.Scorecards.DataSourceProviders.AdomdDataSourceProvider.RunQueryInternal(String query, Boolean
isCellSet, Int32 sessionTimeout)
PerformancePoint Services error code 10116.</Data>
</EventData>
</Event>
Resolution:
I went to Central Admin > Manage Service Applications > PerformancePoint Service Application Settings and found these default settings.
There wasn’t any relevant setting. I then used Power shell commands.
Run this PowerShell Get command to see current values.
Get-SPPerformancePointServiceApplication -identity “PerformancePoint Service Application”
Then run following PowerShell to set a higher value for AnalyticQueryCellMax.
Set-SPPerformancePointServiceApplication -identity “PerformancePoint Service Application” -AnalyticQueryCellMax 10000000
References:
1. http://technet.microsoft.com/en-us/library/ee906547(v=office.14).aspx
2. http://social.technet.microsoft.com/Forums/en-US/3e676663-0d5f-4cbf-9694-14e82772c8e6/unable-to-create-report-query-produced-too-many-results?forum=ppsmonitoringandanalytics