Microsoft Dynamics AX 2012 Reporting - tips how to improve performance

There are some tips we can use to improve performance of reports for Dynamics AX 2012.

1. Use static reports. The deployment of static reports is described here: http://technet.microsoft.com/en-us/library/hh496446.aspx In this way we will safe several calls to Metadata service to get labels values and correct formatting of dates and numbers.
2. Install application hotfix KB 2879661 which fix the situation when first generation of a report after Dynamics AX client restart takes long. This happens for each user.
3. Newest kernel. Hotfixes for performance improvement are included in new kernels that are released; sometimes there is a dramatic impact on performance.
4. In the morning a first report generation takes a lot of time

SSRS uses session pooling. The first request that comes in from a user – a session will be created for them within the AOS services. Each user gets a session cached on the AOS services, so subsequent calls just reuse the session which matches the user who is making the request. This means that the first time this is created for the user the report will perform slower.
SSRS also uses a metadata cache the first request coming in which needs a certain piece of metadata, the metadata is read from the application code, and then cached in SSRS so it doesn’t have to read it subsequent times. There is one metadata cache for all users – once an object is cached in metadata, the services don’t go back to the code and retrieve it again, and they just read from cache, until the cache is cleared. This is also why a report may become slower the first time it is run.
The RecycleTime is used to restart the application domain which causes a refresh of the metadata.You could edit the RecycleTime (value is in Minutes) to therefore stop the cache from clearing and which then stop the extra hop to the AOS to gather the metadata.
Edit the <RecycleTime> element within:
RSReportServer.config (http://technet.microsoft.com/en-us/library/ms157273.aspx )
Open the RsReportServer.config file.
•              If you are using SQL Server 2008, the default location of this file is: \Program Files\Microsoft SQL Server\MSRS10.[SSRSInstanceName]\Reporting Services\ReportServer.
•              If you are using SQL Server 2008 R2, the default location of this file is: \Program Files\Microsoft SQL Server\MSRS10_50.[SSRSInstanceName]\Reporting Services\ReportServer.
•              If you are using SQL Server 2012, the default location of this file is: \Program Files\Microsoft SQL Server\MSRS11.[SSRSInstanceName]\Reporting Services\ReportServer
The default value is 720 (the value is in min so it is 12 hours.
NOTE:
Problems you may see when deploying reports because of metadata caching.
A new field is added to a table in AX, the field is used on a report in VS. The report deploys to SSRS.
However when the report is called, it fails on the new field.
This is because SSRS caches metadata. The new object is not recognized as the old metadata is still cached
The other solution is when every morning the first user has to wait for report very long is to pre-warm up the specific report. We would have to choose those reports which are critical for customer and have to run fast regards the time. To pre-warm up the report we can:

1. Run report in batch job ie. SalesInvoice report:

public void run()
    {
       SrsReportRunController controller = new SrsReportRunController();
    SrsReportDataContract contract;
    SalesInvoiceContract rdpContractClass;
    LogisticsAddressCountryRegionISOCode code = "US";
    // establish the report name
    controller.parmReportName(ssrsReportStr(SalesInvoice, Report));
    // suppress the parameter dialog
    controller.parmShowDialog(false);
    controller.parmReportContract().parmPrintSettings().printMediumType(SRSPrintMediumType::File);
    controller.parmReportContract().parmPrintSettings().fileFormat(SRSReportFileFormat::PDF);
    controller.parmReportContract().parmPrintSettings().overwriteFile(true);
    controller.parmReportContract().parmPrintSettings().fileName("c:\\TEMP\\DummyReport.pdf");
    // set the required parameter.
    contract = controller.parmReportContract(); 
    rdpContractClass = contract.parmRdpContract() as SalesInvoiceContract;
    rdpContractClass.parmCountryRegionISOCode(code);
    // start the operation
    controller.runReport();
    }
2. Run Power-Shell script. . This solution is not so easy as we need to specify correct parameters which is not so obvious in Dynamics AX case. So to get parameters values we can run following SQL on Reporting Services database (this is SalesInvoice example):

SELECT e.timestart,DATEDIFF(ms,e.[timestart],e.[timeend]) AS [TotalTime]
,e.[timedataretrieval] AS [DataRetrievalTime]
,e.[timeprocessing] AS [ProcessingTime]
,e.[timerendering] AS [RenderingTime]
,COALESCE([AdditionalInfo].value( '(/AdditionalInfo/RdceSnapshotGenerationTime//node())[1]', 'int'),0) AS [RDCESnapshotGenTime]
,e.*
FROM [executionlog2] e

There is column Parameters. Which looks in my case following:
AX_PartitionKey=initial&AX_UserContext:isnull=true&AX_RenderingCulture=en-us&AX_CompanyName=CEU&FormLetterRecordId=0&PrintCopyOriginal=Original&RecordId=22565421785&LedgerJournalTransRecordId=0&AX_ReportContext=?RunOnClient%3D1%26PrintMediumType%3DScreen&AX_RdpPreProcessedId=5637144577&SalesInvoiceDS_isTaxInvoice=False&SalesInvoiceDS_TaxInvoiceItemType=Item&SalesInvoiceDS_CountryRegionISOCode=US&DocumentTitle=Invoice&DocumentTitleSequel:isnull=true&IdentificationText:isnull=true&InstanceType=Original&InvoiceInstanceText:isnull=true&CustInvoiceDuplicateRecId=0&UserName:isnull=true&SalesInvoiceDS_AgentAddress_TH:isnull=true&SalesInvoiceDS_AgentId_TH:isnull=true&SalesInvoiceDS_AgentName_TH:isnull=true&IsUnrealizedVATEnabled_TH=False&SalesInvoiceDS_Location=None&SalesInvoiceDS_MiscText:isnull=true&SalesInvoiceDS_PrintHeader:isnull=true&SalesInvoiceDS_ProdType=All&SalesInvoiceDS_TaxBranchCode_TH:isnull=true&SalesInvoiceDS_TaxBranchName_TH:isnull=true

What we do is:
Restart Reporting services – this step can be removed
Open internet explorer
Set IE window to be not visible
Navigate in SalesInvoice report with dummy parameters (here I pause the script for 3 minutes to make sure the report will finish rendering)
Close IE.

Stop-Service "SQL Server Reporting Services (MSSQLSERVER)"
Start-Service "SQL Server Reporting Services (MSSQLSERVER)"

# based on http://msdn.microsoft.com/en-us/library/ms155391.aspx)

[string] $url = "http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fDynamicsAX%2fStaticReports%2fen-US%2fSalesInvoice.Report&AX_PartitionKey=initial&AX_UserContext:isnull=true&AX_RenderingCulture=en-us&AX_CompanyName=CEU&FormLetterRecordId=0&PrintCopyOriginal=Original&RecordId=22565421785&LedgerJournalTransRecordId=0&AX_ReportContext=?RunOnClient%3D1%26PrintMediumType%3DScreen&AX_RdpPreProcessedId=5637144577&SalesInvoiceDS_isTaxInvoice=False&SalesInvoiceDS_TaxInvoiceItemType=Item&SalesInvoiceDS_CountryRegionISOCode=US&DocumentTitle=Invoice&DocumentTitleSequel:isnull=true&IdentificationText:isnull=true&InstanceType=Original&InvoiceInstanceText:isnull=true&CustInvoiceDuplicateRecId=0&UserName:isnull=true&SalesInvoiceDS_AgentAddress_TH:isnull=true&SalesInvoiceDS_AgentId_TH:isnull=true&SalesInvoiceDS_AgentName_TH:isnull=true&IsUnrealizedVATEnabled_TH=False&SalesInvoiceDS_Location=None&SalesInvoiceDS_MiscText:isnull=true&SalesInvoiceDS_PrintHeader:isnull=true&SalesInvoiceDS_ProdType=All&SalesInvoiceDS_TaxBranchCode_TH:isnull=true&SalesInvoiceDS_TaxBranchName_TH:isnull=true&rs:Command=Render";

#documentation of InternetExplorer.Application: http://msdn.microsoft.com/en-us/library/aa752084(v=vs.85).aspx

$ie = New-Object -comObject InternetExplorer.Application;
$ie.navigate2($url);
$ie.visible = $false;
# sleep for 3 minutes to make sure the call is properly completed
Start-Sleep -s 180;

$ie.Quit();

Thing to remember is in case of using static reports:http://technet.microsoft.com/en-us/library/hh496446.aspx
In this case we create directory StaticReports under DynamicsAx directory and there we have for each language separated report.
That makes reports better performed as we safe couple of trips to Metadata service as we do not run in this case AX rendering extension, but we have RDL ready to be run (see image below) but then if we want to warm up the report we have to make sure we warm up each language version we want to use. (ie http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fDynamicsAX%2fStaticReports%2fde-de%2fSalesInvoice.Report for German)