Query execution failed for data set 'Company'
Recently we were having problems to get the Role Center up and running. Report Server is setup and working in a correct way. Most reports can be generated, but a few does not answer correct. For those report the following error is generated: “Query execution failed for data set 'Company'” Reports in Role Center can not be accessed. Even those that default company was set in Business Connector Configuration Utility and on the connection tab correct AOS was set up we were still not able to make report working.
The root cause of this problem was that cubes in OLAP database DynamicsAX were not processed.
To solve the problem we were following WhitePapers which let us configure cubes and Role Center:
- The “Configure the Default OLAP Cubes” white paper and the cross-reference document are now available here: http://www.microsoft.com/downloads/details.aspx?FamilyId=28CDB057-416A-497E-A7E0-8D52C4EB1BFB&displaylang=en
- Microsoft Dynamics AX 2009 White Paper: Configuring Kerberos Authentication with Role Centers: http://www.microsoft.com/downloads/details.aspx?FamilyID=be720eb3-649a-49ff-b019-a1e4e7af6b47&DisplayLang=en
- Microsoft Dynamics AX 2009 White Paper: Configuring Enterprise Portal and Role Centers with SQL Reporting http://www.microsoft.com/downloads/details.aspx?FamilyID=3D2463FC-EA13-40EF-A375-2DA2F379652C&displaylang=en
- Fixing Microsoft Dynamics AX Cube Processing Errors Caused by EDT Field Changes: https://mbs.microsoft.com/partnersource/documentation/whitepapers/AX2009_FixCubeProcessingErrors
The way how we troubleshoot the problem was following. We had problem with report ProjUtilizationEffeciencyEmpl.
Report ProjUtilizationEffeciencyEmpl is based on Project Accounting Cube.
If Company data set cannot be processed it seems there is something wrong with this cube. Company data set is called with following query:
WITH
MEMBER [Measures].[ParameterCaption] AS '[Company].[Company accounts].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue] AS '[Company].[Company accounts].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[Key] AS '[Company].[Company accounts].CURRENTMEMBER.Properties("Key")'
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[Key] } ON COLUMNS , Except([Company].[Company accounts].MEMBERS, {[Company].[Company accounts].[All], [Company].[Company accounts].[Unknown]})
ON ROWS FROM [Project Accounting Cube]
.