XML for Analysis parser: The XML for Analysis request timed out before it was completed -- Error in SSAS Cube Synchronization

Komal Sahu 311 Reputation points
2020-08-24T09:35:04.557+00:00

Hi All,

Sorry for little descriptive question..
I am using SSIS job to process the ssas DB 'ESS MOLAP ISSJMT' in source server and XMLA script in the same SSIS package to synchronize the SSAS DB from source server to destination server. This SSIS job is deployed and runs daily on source server.
This was running fine since years and gets completed in approx 20-30 minutes, but recently SSAS DB Synchronization is getting hang and not stopping/completing even after 3-4 hours. then I need to kill the job and manually sync the SSAS DB using synchronization wizard at destination server. SSAS DB Synch using synchronization Wizards works fine.
This is intermittent issue and since last 3 weeks it occurs 1-2 times in a week. recently I got chance to set profiler and capture traces. In approx 8 entries in profiler traces, I got below error:

  • <XML for Analysis parser: The XML for Analysis request timed out before it was completed.>

But in one entry I got MDX query as well along with same error(as below)

XML for Analysis parser: The XML for Analysis request timed out before it was completed.

Query Text:
WITH
MEMBER [Measures].[ParameterCaption] AS [Reporting Period].[Reporting Year].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Reporting Period].[Reporting Year].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [Reporting Period].[Reporting Year].CURRENTMEMBER.LEVEL.ORDINAL
Select {
[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel] }
on COLUMNS,
NONEmpty ([Reporting Period].[Reporting Year].[Reporting Year].Members,[Measures].[RM-Reported Debit])
On ROWS
FROM [EAL IA]

When ran this query on SSMS, it working fine without any issue.

Can anyone please suggest what could be the problem here? any tip/hint will be useful and much appreciated..
I am also attaching the trace file 19837-synch-job-hanged-profilertraces-trc.pdf (please replace .pdf with .trc to open trace file) --if anyone can please quickly check and advice as SQL Profiler is something new for me.
I noticed one more thing in below image(taken from profiler trace) which may or may not be reason for this issue:
as highlighted in the mentioned trace, TextData column has 'ESS MOLAP ISSJMT' in script but under the DatabaseName it is coming as different SSAS DB as 'ORION MOLAP ISSJMT'. This is something weird ( may be weird for me) that 2 different DBs are there.
19911-errorincubesynch.jpg

Many Thanks,
Komal

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,004 questions
No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Lukas Yu -MSFT 5,796 Reputation points
    2020-08-25T06:35:26.787+00:00

    Hi,
    I noticed that in your last post, it has also messed with the database name in this thread.
    These database name should not be changed as metadata during the process.

    Would it possible to generally check the whole process make sure it is logically right, then it would be easy to work with rest errors.