Report PreProcess for Performance.

Report PreProcess

When a report processes a huge amount of data or is found to take considerable time during the execution, then you can decide to incorporate preprocessing. Follow these steps to enable preprocessing.

1. Modify RDP base class:  SRSReportDataProviderBase -> SRSReportDataProviderPreProcessTempDB

2. Update table type:  InMemory -> TempDB

3. Perform a full compile of IL

4. Restart the AOS

5. Introduce Controller class to run the report

6. Update Output Menu Item:  Report -> Controller class

These are pre-processing AX-SSRS reports. in these report user connection information is taken using method parmUserConnection(). Yes, this method sets the User Connection object on the temp table(s) that are used in the RDP class to return data. As per documentation , for pre-processing RDP reports, you must set the User Connection before the tables can be used. It is a best practice to set it in the beginning of processReport() method.

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:

Changes Required  In classDeclaration extend SrsReportDataProviderPreProcess instead of SrsReportDataProviderBase    2.  Temp table properties should be  (a) Table type : Regular instead of tempDB   (b)  Created by : Yes  (c) Created Transaction Id : Yes  3. In process report of the class add this line  in Temporarytablename.setConnection(this.parmUserConnection());

Now that we’ve explored timeout troubleshooting, let’s take a look at the settings that can affect report timeouts.

BE CAREFUL OF “JUST INCREASE THE TIMEOUTS!”

Before we start, when running into timeouts in SSRS a common answer is, “Just increase the timeouts!” This kind of thinking is commonly a band-aid for poor report design. If you must increase timeouts as a troubleshooting measure, remember to set them back to previous levels after finding out why your reports are timing out! Long timeouts can cause the [ReportServer]TempDb database on the SSRS database server to grow very large when many users have open sessions (among other challenges).

SSRS SETTINGS

Below are some common SSRS settings you can extend as your troubleshoot long running reports.

REPORT EXECUTION TIMEOUT: SITE LEVEL

To extend this timeout, open the SSRS Home Page and navigate to the Site Settings page (top right).

The default timeout is 30 minutes (1800 seconds).

REPORT EXECUTION TIMEOUT: REPORT LEVEL

By default, all reports use the Site level Report Timeout value. However, you can change the Report Timeout setting on a per-report basis.

SESSIONTIMEOUT & SESSIONACCESSTIMEOUT

The SQL Server instance has two settings that may cause timeouts if the user session used to render SSRS reports takes too long.

The SessionTimeout and SessionAccessTimeout settings are in seconds.

By default, the SessionAccessTimeout setting is not visible in the SSRS settings. You can view them in SQL Server Management Studio by connecting to the SSRS instance and viewing the Properties of the instance.

To change the SessionAccessTimeout setting for the first time, you must run a script against rs.exe. This executable is typically located in \Program Files\Microsoft SQL Server\110\Tools\Binn (SQL 2008) or\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn (SQL 2012).

Create a text file the following and save it with the .rss extension.

Public Sub Main()

Dim props() as [Property]

props = new [Property] () { new [Property](), new [Property]() }

props(0).Name = "SessionTimeout"

props(0).Value = timeout

props(1).Name = "SessionAccessTimeout"

props(1).Value = timeout

rs.SetSystemProperties(props)

End Sub

Now you can run the following command against rs.exe to update the timeout settings.

"C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\rs.exe" -i c:\temp\extendTimeouts.rss -s http://localhost/reportserver -v timeout="6000"

This example will set both user session timeouts to approximately an hour and a half.

For future changes to these timeouts, you can change them from the SQL Server Management Studio.

AX SETTINGS

Since SSRS uses the AX Query Service, we may run into timeout issues due to these settings.

AX SETTINGS: SERVER-SIDE

We can make changes to the WCF thresholds in the AX32Serv.config. This file is typically located at \Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin on the AOS server.

Since the Query Service sends data to SSRS when requested, we can increase the sendTimeout attribute of the binding element with the QueryServiceBinding name attribute to handle requests that take longer than the default 10 minutes.

AX SETTINGS: SSRS HOST

The AX Reporting Extensions use the Business Connector settings for the AX Client installed on the SSRS host computer. Alternatively, SSRS can use an AXC file placed in \ReportServer\bin directory of the SSRS instance. View Create a new Microsoft Dynamics AX configuration [AX 2012] if you have this sort of configuration as you will need to edit that AXC file instead of the Business Connector configuration. These instructions are for a Business Connector configuration that is saved in the registry.

On the SSRS host server, open the Dynamics AX 2012 Configuration Utility as an Administrator (otherwise your changes may not be saved due to User Account Control) and navigate to the Business Connector configuration.

 If there are no configurations other than the Original (installed configuration) create a new one.

Open Configure Services and acknowledge your settings will overwrite the default WCF settings.

If you see this message, you can download and install the Windows SDK for your version of Windows Server and the SDK for .NET Framework 4.0. But this is not required.

If you do not have the Windows SDKs installed, you will be presented with a text file. Search forQueryServiceEndpoint and increase the sendTimeout and maxReceivedMessageSize attributes of the binding element with the QueryServiceEndpoint name attribute.

When troubleshooting, we typically increase sendTimeout to 00:20:00 (20 minutes) andmaxReceivedMessageSize to 4294967294 (twice as big).