After searching for detail explanation on the SRSReportDataProvide, found some good explanation on these topics, sharing with you the same data and their link in the end.
Why does converting a report to pre-processing help to resolve timeout issues?
Sometimes in AX we need to create reports that exceed the default run-time limits defined in AX (default is 10minutes) because of the amount of data we are either calculating or returning. As discussed in one of my other posts (AX SSRS Report times out after 10 minutes) one of the ways to overcome this is to change the config files of the client/ssrs server but another way to overcome this limit and improve the speed in which a report can be ran is changing your data provider to extend the class 'SrsReportDataProviderPreProcess' instead of the regular type of 'SRSReportDataProviderBase'In order to switch from the regular process we need to make the following changes:
- On the table that is returned as a dataset to the report make the following changes
- Created by - Yes
- CreatedTransactionId - Yes
- TableType - Regular
- Change the DP class so it extends SrsReportDataProviderPreProcess
- Within the DP class.processReport() method add the following code reportData.setConnection(this.parmUserConnection()); where reportData is the table that is returned as a dataset within your report
- Execute a incremental cil
- Open the SSRS report and hit refresh on the dataset. At this point you should see the field 'createdTransactionId' added to the available field.
- Deploy report
- Security Note: For your security object it is good to note that when using this preprocessing method you need to add the DPClass.processReport() method to the Server methods node in the privilege associated with the report
Without pre-processing the report is executed in these steps:
With pre-processing the steps are executed in a different order:
The report server execution time is the time between the two steps:
This explains why in case of reports with pre-processing it takes less time for the report server to render the report and why some timeouts will not be hit. (In many reports, it is actually the preparation of the data by AX that is most time consuming.)
This also explains why converting a report to pre-processing does not necessarily mean that the report will execute faster in overall. Particularly it will not, if the underlying tables are SQL tables. In these cases, the steps are only swapped – nothing else. The overall execution time of the report as perceived by the user is the sum of all three steps and this sum is the same in either approaches. On the other hand, reports that use InMemory temporary tables may execute faster after the conversion because pre-processing is implemented with SQL tables. InMemory temporary tables with large amounts of data may need to be paged to the file system and that may influence the data retrieval time. Moving these data to SQL tables may improve the report’s overall execution time.
Keeping in mind that only reports that use the Report Data Provider class can be converted to pre-processing (and this in fact does not necessarily lead to reports executing faster in overall), it is often required to increase the timeouts to enable a long running report to complete successfully. In order to reduce the trial and error phase to minimum, it makes sense to configure very generous timeouts for the start just to go sure that a report can be executed successfully. There is basically nothing that speaks against setting this timeout initially to 24 hours, for example. If the report was executed successfully we can take the exact time from the corresponding RS log file or RS Execution Log (they are collected by default) and reduce the timeouts to a value that ensures a successful execution of the affected report.
Configuring the timeout to a value higher than 12 hours, it is also recommended to increase the RecycleTime in the rsreportserver.config file accordingly. This value is 720 minutes per default (12 hours). We saw that when the application domain got recycled while a long running report was executed that it led to an error in most cases. Taking into account that all thresholds/timeouts will be reset at the Reporting Services service startup, it is recommended for testing to restart the service before starting a long running report so all counters get initiated at the same time.
Furthermore, in AX 2012 R2, it is required that the kernel build version is 6.2.1000.6578 or higher, in AX 2012 R3, 6.3.164.2421 or higher (KB 2936794). If it is not, the latest kernel hotfix available to date should be installed. Installing the latest kernel hotfix is recommended in general. (See FAQ: Microsoft Dynamics AX Kernel Hotfixes if you have any questions regarding installing a kernel hotfix.)
- If the report continues to fail, make sure that it does not fail due to a different reason (check RS log file/RS Execution Log).
- If the report fails due to a timeout again, make sure if it fails at the value that has been just configured (check RS log file/RS Execution Log). If so, increase the timeouts.
- If the report fails at a time before the chosen timeout, make sure that all settings were configured properly, none is missing or has an incorrect value by mistake.
- If the report fails at a time before the chosen timeout and this time is always the same, check in the log how long it took exactly. 10 minutes? 30 minutes? Knowing it may help in identifying the setting that was missed when increasing. 10 minutes – could be a call to the AOS service, for example. 30 minutes – could be the default timeout on the report in the report server. Double check if the timeouts were configured properly following the post thoroughly.
- If the report fails at a time before the chosen timeout, this time is always the same and none of the settings is missed, upload the attached report WaitFor2012.rdl to the report server (using Report Manager) and run it directly on the report server. This report is a pure SSRS report so there is no AX component involved. Enter the timeout currently configured and verify if the report fails again before this time and if this time is the same. If the report does fail, the reason could be an external (non-SSRS and non-AX) setting, in the network or ASP.NET, for example. If the report does not fail, this would indicate an issue in AX that may require investigation by AX support.
How To: Long Running Reports
Overview
One segment of reports worthy of its own classification are those operational reports that incur heavy processing time due to the number of objects involved in generating the report data set. The vast majority of our out-of-box reports take on average <1 minute to render to screen depending upon the size of the data set. However, there are many core reports that include business logic that iterates over transactions and inventory information which could easily exceed 10M table records in a production database. As a result, these reports can take hours to complete the data set generation for the report. A safe rule of thumb is to treat any report that takes >5 minutes in a non-production environment as a Long Running Report thus deserving special consideration when planning out the solution. A few examples of long running reports include the Trial Balance Report, Customer Aging Report, and the Inventory Dimensions Statement Report.
What's important to understand as Report Developers is the fact that the SSRS Server utilizes service calls to communicate with the AOS when constructing the report data set. As a result, the process of generating any report data set is subject to these service timeout limits. By default, service timeouts are fixed at 10 minutes. Further, any data set generation process exceeding this service timeout minute will fail to complete. This article describes the extensions introduced as part of the Microsoft Dynamics AX 2012 R2 Release to facilitate the Long Running Reports to ensure that they are able to complete even if they exceed the 10 minute service timeout limits.
How do I migrate to Pre-process RDP?
- Modify RDP base class: SRSReportDataProviderBase -> SRSReportDataProviderPreProcessTempDB
- Update table type: InMemory -> TempDB
- Perform a full compile of IL
- Restart the AOS
- Introduce Controller class to run the report
- Update Output Menu Item: Report -> Controller class
Here's a quick YouTube video demonstrating the process of creating a pre-process RDP class for a Long Running Report:
[View:https://www.youtube.com/watch?v=6tdQtHV2WAc&list=UUfGIUxWmd1ZA11NB8FgVWpw]
Using SrsReportDataProviderPreProcessTempDB class
In Ax2012, we introduced class SrsReportDataProviderPreProcess class as the solution for long running SSRS reports that cause SSRS time out. The approach is to process data in Ax session before calling SSRS. The preprocessed data stored in a regular SQL table shared by all user sessions, only to be striped by session id. That approach creates a bottleneck in case of many concurrent user sessions. In R2, we introduced this new class to allow use of tempDB to carry report data across sessions, from Ax data processing session to SSRS data retrieval session. AOS kernel will not purge tempDB table when session end leaving it to application code to clean up the tempDB table. In case of report, this base class will take care of the clean up.
You should stop use the old SrsReportDataProviderPreProcess class but use the new base class instead for long running reports. You will see performance gain in case of multiple concurrent sessions on the same report.
Currently there are many OOB Ax2012 R3 reports already take advantage of this feature. You can check out how they are leverage the feature there. One typical example is TaxListDP class.
This R3 feature is implemented in the kernel. The usage is not limited to reporting. Attached is a sample project showing how this kernel feature is used in case of multi-threading in Ax: each thread creates its own instance of a temp table, inserts rows into the table, and passes the table name back to the main thread (crossing session boundary), where contents from each temp table is then merged into one DB table using set-based insert. At the end, the code must despose the temp tables. The code is for demonstraction only. A more practical application to leverage this feature is in batch processing.
Using Dynamic Set-based insert operation
In AX2012, set based insert operation is part of X++ syntax and is precompiled. That limits its use when filters are dynamic, i.e. taking from end user at run time.
In AX2012, R3 we implemented a DCR that allows constructing a set based insert where clause based on Query range on the fly. This feature allows much wider adoption of set based operation and greatly improve performance when applied.
A good coding example can be seen in TaxListDP.insertTaxListTaxTmpData. It is done using a new static method on Query object. Here is how it is used.
Query::insert_recordset(taxListTaxTransTmp, insertRecordsetMap, query);
the first parameter is the tempDB table and the second one is the mapping between the tempDB table fields and query fields. See detail in the AOT class source code. Here is the highlight of the row-based old code:
Without the DCR, we can’t change this code to set-based because query range is set by end users. Now, the highlight of the new high performance code is as follow:
Summary :
As the name suggests, SrsReportDataProviderPreProcess class is a special kind of Report Data Provider class that pre-process data.
in short, pre-processing is used when preparing the data would take so long that the printing would time out, therefore it first prepares the data and only then it calls the report.
SrsReportDataProviderPreProcess uses regular tables and therefore it needs to distinguish data for different runs of the same report, so it uses the transaction ID.
Since SrsReportDataProviderPreProcessTempDB has been introduced, you should use it instead of SrsReportDataProviderPreProcess. TempDB don't need such the distinction over transaction ID, because there is a separate instance of the table for each run.
No comments:
Post a Comment