Dynamics performance very slow in AX 2009

Friday, 9 May 2014

Dynamics performance very slow in AX 2009

1. sql server activity monitor, kill all suspending transactions.

2. Go to the Administrations->Periodic->sql administration -index actions->press re-index for all tables

3. Administrations->Periodic->sql administration -table actions->Synchronize  your database 

4. restart all the servers like: application server, database server, etc,

hope it will increase performance .

http://blogs.msdn.com/b/axperf/archive/2008/03/13/ax-database-configuration-checklist-part-2.aspx

Developing a SSRS report using the Report Data Provider in Microsoft Dynamics AX 2012

Overview

There are multiple methods to develop SSRS reports in Microsoft Dynamics AX 2012. This tutorial will guide you in developing Report Data Provider (RDP) based SSRS reports.

RDP based SSRS Reports are used when complex business logic cannot be achieved using AOT query.

Pre-requisites

1. Microsoft Dynamics AX 2012
2. Visual studio 2012
3. SQL Server Reporting Services (SSRS) must be configured
4. Reporting services extensions must be installed in Dynamics AX

Important Concepts

1. Report Data Provider (RDP) Class

Report Data Provider Class is an X++ class that is used to access and process data for a SSRS report. The RDP class processes the business logic based on a specified parameter and/or query and returns a dataset to the reporting services. In order to create a RDP class in AX, you have to extend that class with SRSReportDataProviderBase. This tells AX that this class will be used by reporting services to process the data.

Two important attributes are used in RDP classes:

1. SRSReportQueryAttribute: specifies which AOT query will be used in this report. If the RDP class uses an AOT query to process data, define this attribute at the beginning of the class.
2. SRSReportParameterAttribute: defines the data contract class that will be used by this report to prompt for parameter values. If the RDP class contains any parameters this define this attribute at the beginning of the class.

Both the attributes are optional. If the report does not use any query or does not want any parameter to filter report data, these attributes do not need to be used.

• Data Contract Class

A data contract class is an X++ class which contains parm methods with the DataMemberAttribute defined at the beginning of the method. This class is used to define one or more parameters that will be used in a SSRS report.

• Table

An AX table is used as the dataset to store data for the report. The RDP class processes the data and stores it in the table which is then used by a SSRS report to render data.

A table can be a temporary table (InMemory or TempDB) or a regular table, but it is Microsoft best practice to use a temporary table.

The type of temporary table is based upon the performance considerations. InMemory temporary table is used when the data set is small, while TempDB is normally used for larger datasets to improve performance.

Scenario

As part of this tutorial, the report will print a list of customers and their invoiced sales order counts.

Steps

• First of all, create a temporary table. Open AOT à Date Dictionary à Tables.
• Right Click on Tables and create a new Table called CustReportRDPDemoTmp.

• Set the TableType property to InMemory. This will define the table as a temporary table.





• Expand the CustReportRDPDemoTmp table node and add the following fields in the table:



S. No.	Field name	Extended Data Type	Label
1	CustAccount	CustAccount
2	Name	Name
3	SalesOrderInvoiceCount	Integer	Sales order invoiced



• The final table should look like the following:





• Now create a RDP class. Go to Classes and create a new class called CustReportRDPDemoDP by right clicking on Classes and selecting New Class. It is a best practice to suffix the RDP class name with DP .





• Open the Class declaration by right clicking on it and selecting View code.





• Now write the following code:

class CustReportRDPDemoDP extends SRSReportDataProviderBase
{
//Temporary table buffer
CustReportRDPDemoTmp custReportRDPDemoTmp;
}

• Add a new method and name it getCustReportRDPDemoTmp. This method is mandatory because reporting services uses this method to get the table buffer containing the processed data. The SRSReportDataSetAttribute attribute is used to indicate the temporary table name and also tells the reporting services to use this method to retrieve the processed data.
• Write the following code in the method:

[SRSReportDataSetAttribute(tablestr('CustReportRDPDemoTmp'))]
public CustReportRDPDemoTmp getCustReportRDPDemoTmp()
{
//select data from table buffer
select * from custReportRDPDemoTmp;
//return the buffer
return custReportRDPDemoTmp;
}

• Add a new method and name it processReport. This method contains the business logic and is called by reporting services to generate data.
• This method will query customer details and fill the temporary table buffer. Write the following code in the method:

///<summary>
/// Processes the SQL Server Reporting Services report business logic
/// </summary>
/// <remarks>
/// This method provides the ability to write the report business logic. This method will be called by
/// SSRS at runtime. The method should compute data and populate the data tables that will be returned
/// to SSRS.
/// </remarks>
public void processReport()
{
CustTable custTable;
SalesTable salesTable;
//select all customers
while select * from custTable
{
//clear the temporary table
custReportRDPDemoTmp.clear();
//assign customer account and name
custReportRDPDemoTmp.CustAccount = custTable.AccountNum;
custReportRDPDemoTmp.Name = custTable.name();
//select count of invoiced sales order of customer
select count(RecId) from salesTable
where salesTable.CustAccount == custTable.AccountNum
&amp;&amp; salesTable.SalesStatus == SalesStatus::Invoiced;
custReportRDPDemoTmp.SalesOrderInvoiceCount = int642int(salesTable.RecId);
//insert in temporary table buffer
custReportRDPDemoTmp.insert();
}
}

• Now create a new report. Since the development of a SSRS report is done in Visual studio, we first need to create a new project in Visual studio.
• Open Visual studio. Go to File à New à Project
• In the Installed templates section select Microsoft Dynamics AX and then select Report Model in the right pane. Name the project RDPBasedDemo and press Ok.





• A new project will be created as shown below.





• Now add a new report in the project by right clicking on the project RDPBasedDemo à Add à Report.



• A report will be added to the project with the name Report1. Rename the report RDPBasedDemo.
• Now double click the report to open it.



• The description of the individual node is given below:
1. Datasets: Datasets retrieve data from RDP class. It acts as a bridge between AX and the SSRS report. Only the fields added in the datasets can be used in a report.
2. Designs: It defines the layout of the report.
3. Images: It contains the images that you want to display in the SSRS report.
4. Data Methods: It contains the business logic which can then be used in the report.
5. Parameters: It is used to apply filtering to the data in a report. All the parameters defined in the data contract class are automatically added here when the RDP class is defined in the datasets.
• Now you will want to create a new Dataset by right clicking Datasets àAdd Dataset. Name it CustDetail.





• Select the CustDetail dataset and open the properties window. Set the Data Source Type to Report Data Provider. Then select the Query field. An ellipse button appears. Click it to open a dialog box.





• This dialog box lists all the RDP classes present in the AOT. Select CustReportRDPDemoDP and press Next.





• Select the fields to be displayed in the report and press OK. Only the fields selected in this dialog box can be shown in the report.





• There are two types of designs that can be created in a SSRS report:
1. Auto design: Visual studio automatically creates a design based on the dataset provided. Auto design is the preferred method because it is easy and usually fulfills the majority scenarios.
2. Precision Design: This is used when you need custom placement of fields or the layout of the report is too complex.
• In this demo we will use Auto Design. Now right click the Designs nodeàAdd àAuto Design. A new design is added. Rename it Design. It is recommended that you set the name of the Design to either ‘Design‘ or ‘Report‘.





• Now drag the CustDetail form to the Datasets node and drop it on the Design node. A table will be created which contain all the fields present in the data set. These fields will appear in the same order as in the report. So if you want to arrange the fields, right click the field and select either ‘move up’ or ‘move down’.
• The final design should look like the following:





• Now we have to define the layout of the report. Visual studio provides built in templates. Select the Design and open the properties window. Select ReportLayoutStyleTemplate in the LayoutTemplate field. Give a suitable title to the report.





• Select CustDetailTable under the Design node and open the properties window. Select TableStyleAlternatingRowsTemplate in the Style Template field.





• The report is now completed and can be viewed. To preview the report, select the Design node, right click it and select preview.





• Select the Report tab. The report will appear as shown below:



• To view this report from AX, Add the report to AOT and create an Output menu item and set the appropriate Properties. For further details on creating SSRS reporting, refer to our previous article ‘Developing SSRS report using Query‘.

Exploring SysQuery class

SysQuery is a helper class provided by Microsoft for working with queries dynamically. It has all its methods set as ‘static.’ It provides numerous functionalities that allow one to add data source in a query, add range, apply sort, etc. in a user-friendly system. This article will provide demonstrations for the most commonly used methods from SysQuery:

findOrCreateDataSource()

FindOrCreate is mainly used to check whether or not the query already contains the data source. If it does not, a data source will be created. FindOrCreate is most efficient when an event developer must write a code in which the event must be fired multiple times. This function allows developers to prevent adding additional elements to data sources. This is the most common situation in which findOrCreateDatasource() is used to avoid multiple addition of data sources in a query.

Because this method is static, it uses Query as the first parameter. The next parameter will be the table id of the table that is being used as the data source in the query. Below is the method signature:

public static QueryBuildDataSource findOrCreateDataSource(Query _query, tableId _tableId, tableId _parentTableId = 0)

An alternative option, findOrCreateDataSourceByName() works in a similar fashion, but uses the table name for table identification in parameters.

findOrCreateRange()

This method is used to check whether or not a range already exists. If the range exists, the object will either be returned or a range according to the provided parameter will be created and returned. Using the findOrCreateRange() method, allows developers to avoid the possibility of adding a single range multiple times. The below is an example of the method:

range = SysQuery::findOrCreateRange(query.dataSourceName("CustTable"), fieldNum(CustTable, AccountNum));

range.value(SysQuery::value("123"));

There are other methods for specifying value for conditions in a range below:

SysQuery::range()

Range() method is used to specify value ranges of any type. This is most commonly used for specifying date ranges.

Queries in Dimension data sources

AX 2012 is more efficient when working with financial dimensions in comparison to previous versions. In AX 2012, the schema of dimensions’ framework has been normalized. However, this change has made querying in dimension data model more complex and harder to understand. Microsoft has provided helper classes for developers working with dimension data sources. They have also provided guidance in SysQuery class to help developers write queries in a more simplistic way—they do not need to have in-depth knowledge of the data model behind financial dimensions. There is generally only one reference field (recld) that references whole dimension value combination in the table DimensionAttributeValueCombination. It then becomes difficult to apply range on a specific dimension using one reference field (usually LedgerDimension in tables). This type of action requires complex joins in the dimension value tables hierarchy, but SysQuery class makes the process much simpler.

Below are a few methods to use when working with dimension data source:

1. addDimensionDataSource() – To add dimension data source
2. addDimensionAttributeRange() – To add range on dimension data source
3. addOrderByDimensionAttribute() – To add an order by clause on specific dimension

Understanding the Settlement Mechanism in Microsoft Dynamics AX

Settlement is an accounting transaction that occurs on accounts payable, accounts receivables, and general ledger. This transaction is used mainly for settling vendor invoices against vendor payments or  advanced payment, and customer invoices against customer collections or advanced collection. Settlement could occur on general ledger transactions as well.

It is necessary to classify posting profiles of the vendor and customer in the opening balance since it will affect  the settlement process.

The settlement transaction affects vendor and customer balances, and it is reported in the vendor or customer statement 

Few of the table name changes in ax 2009 to ax 2012

Few of the table name changes in ax 2009 to ax 2012

InventSize       : EcoResSize, EcoResProductMasterSize

InventColor    :  EcoResColor, EcoResProductMasterColor

ConfigTable    : EcoResConfiguration,
                         EcoResProductMasterConfiguration,

InventDim (InventColorId, InventSizeId, ConfigIdfields)        : EcoResProductDimensionAttribute


InventDimCombination :  EcoResProductVariantSize ,
                                       EcoResProductVariantColor,
                                       EcoResProductVariantConfiguratio

LedgerTable and LedgerTrans table name changes in ax 2012

LedgerTable To MainAccounts in ax 2012

LedgerTrans to General Journal Tables in ax 2012

GeneralJOurnalAccountEntry
GeneralJournalEntry
SubledgerVoucherGeneralJournalEntry
LedgerEntry(Optional)
LedgerEntryJournal(Optional)
LedgerEntryJournalizing(Optional)

Complete select statement
This pattern and example demonstrate how to select the general journal records that replace a single
LedgerTrans record.

Example
select RecId from generalJournalAccountEntry
join RecId from generalJournalEntry
where generalJournalEntry.RecId == generalJournalAccountEntry.GeneralJournalEntry
join RecId from subledgerVoucherGeneralJournalEntry
where subledgerVoucherGeneralJournalEntry.GeneralJournalEntry ==
generalJournalEntry.RecId
outer join RecId from ledgerEntry
where ledgerEntry.GeneralJournalAccountEntry == generalJournalAccountEntry.RecId
outer join RecId from ledgerEntryJournal
where ledgerEntryJournal.RecId == generalJournalEntry.LedgerEntryJournal
outer join RecId from ledgerEntryJournalizing
where ledgerEntryJournalizing.GeneralJournalAccountEntry ==
generalJournalAccountEntry.RecId

Select for a specific transaction date
This pattern and example demonstrate how to select the general journal records for a specific
transaction date.

Example
select RecId from generalJournalAccountEntry
join RecId from generalJournalEntry
where generalJournalEntry.RecId == generalJournalAccountEntry.GeneralJournalEntry
&& generalJournalEntry.AccountingDate == <transaction date input>

Select for a specific voucher and transaction date 
This pattern and example demonstrate how to select the general journal records for a specific voucher
and transaction date

Example
select RecId from generalJournalAccountEntry
join RecId from generalJournalEntry
where generalJournalEntry.RecId == generalJournalAccountEntry.GeneralJournalEntry
join RecId from subledgerVoucherGeneralJournalEntry
where subledgerVoucherGeneralJournalEntry.GeneralJournalEntry ==
generalJournalEntry.RecId
&& subledgerVoucherGeneralJournalEntry.Voucher == <voucher input>
&& subledgerVoucherGeneralJournalEntry.VoucherDataAreaId == <voucher data area ID
input>
&& subledgerVoucherGeneralJournalEntry.AccountingDate == <transaction date input>

How to import Contoso Demo Data to Dynamics Ax 2012 R3.

The setup contoso demo data for Dynamics Ax 2012 R3 is different from previous version. I remember, I used .dat file to load demo data into Dynamics Ax 2012 R2. Now DynamicsAX2012R3DemoData.exe available partner Resource.

DynamicsAX2012R3DemoData.exe extracted files round 15 GB. Extracted folder contains three types of files. According to MSDN

• Xml (A bcp data file that contains table data. Columns are separated by #|EOC|#. Rows are separated by #|EOR|#\n.)
• Out (A bcp data file that contains the table metadata (column descriptions).
• OutModel (This metadata includes all names and IDs of the table and its fields. This file also includes the elementType attribute, which stores the names and IDs of any Microsoft Dynamics AX tables, classes, or extended data types that are referenced by the table)

Installation of Test Data Transfer Tool:

The MSDN Described The Import export with Test Data Transfer Tool (beta) as follow.

Test Data transfer Tool is available on partner resource.

Go on following link

https://informationsource.dynamics.com//RFPServicesOnline/Rfpservicesonline.aspx?ToolDocName=Microsoft+Dynamics+AX+2012+Test+Data+Transfer+Tool+(Beta)%7cQJ4JEM76642V-8-1465

On login I found download page

There will be zip file downloaded.  On running I found following installation wizard.

Setup run will be generated as following

Import Data Into Dynamics Ax 2012 R3 instance:

And import MetaDataXMLGenerator.xpo in dynamics Ax.

This xpo imports results a job inside job node under AOT.

This job will generate Metadata.xml. This Metadata.xml will be created in window temporary folder.

Path of file in metadata.xml can be get from infobox which will appear after successful run of job.

if you lost infobox,You can get temporary folder path with following command echo %temp%. where you can get Metadata.xml

Copied metadata.xml into [list] folder of test tool Data folder. If it already exists then overwrite it.

Go to services, and stop the Dynamics Ax service.

Now open command prompt. Go to directory where Test import tool is extracted.

Run the following command

DP.exe IMPORT “E:\Contoso_Demo_Data\DynamicsAXR3DemoData” MicrosoftDynamicsAx

If your SQL server configure with other than default instance then you must mentioned the server with instance name for example

DP.exe IMPORT “E:\Contoso_Demo_Data\DynamicsAXR3DemoData” MicrosoftDynamicsAx WIN-TLRNMFCLU9N\SQLSERVER

Please update path according to your installation.

After running the command, you will find following window which describe the process by remaining table to number data, and number of error occurs during import.

This process takes hours and hours with respect to your machine. After completion, I found demo data inside my dynamics Ax 2012 R3.

Microsoft-dynamics-ax-lookups

http://www.slideshare.net/MAnasKhan/microsoft-dynamics-ax-lookups?next_slideshow=1

Dynamics Ax 2012 R3 Cu8 demo data download link

Demo data file for Dynamics Ax 2012 R3 CU8 is available for download. You can download it from following link
https://mbs.microsoft.com/partnersource/northamerica/sales-marketing/demo-tools/virtual-machines/AX2012DemoToolsMaterials
To log on here you must have partner source or customer source credentials.

Test Data Transfer Tool (beta) (AX 2012)

The Microsoft Dynamics AX 2012 Test Data Transfer Tool (beta) (DP.exe) is a command-line tool that exports data from a Microsoft Dynamics AX 2012 business database in a production or non-production environment. The tool also imports data into a Microsoft Dynamics AX 2012 business database in a non-production environment. The non-production environment can be either a development or test environment.

Table of Contents

• Benefits
• Limitations
• Who should use this tool
• Personally identifiable information

The Test Data Transfer Tool (beta) has been moved. It is now available from the Downloadable tools section of Microsoft Dynamics Lifecycle Services.
Go to Lifecycle Services.
Note: Microsoft does not support using the Test Data Transfer Tool (beta) to import data into a production environment. The tool can be used only to export data from a production environment, and to import data into a test or development environment.
The Test Data Transfer Tool (beta) is especially useful in the following circumstances:

• You want to export or import a large, multicompany dataset.
• You have to move data between different Microsoft Dynamics AX environments that have slightly different customizations.
• You have to store data in a version control system.
• You have to export or import data without running Microsoft Dynamics AX Application Object Server (AOS).

Benefits

The Test Data Transfer Tool (beta) uses the Microsoft SQL Server bulk copy tool (bcp). The Test Data Transfer Tool (beta) provides the following key benefits that other methods for importing and exporting Microsoft Dynamics AX data do not provide:

• You can export or import data when an AOS instance is not running.
• You can export or import Microsoft Dynamics AX data more quickly compared to other methods.
• Only minimal changes are made to the data that you import. This feature helps guarantee that the data is stable over time. For example, the tool never renumbers RecIDs.
• The data file format is text-based. Therefore, the data can be compared with earlier versions and stored in a version control system.
• The data file format is a standard format that is produced by bcp.

The Test Data Transfer Tool (beta) adds the following features that work with bcp. These features make bcp an appropriate tool for managing data for Microsoft Dynamics AX.

• Exported data can be filtered. Therefore, specified tables, columns, or rows can be easily excluded from the export.
• Differences between builds of Microsoft Dynamics AX 2012 are found and corrected. These differences often occur during development. Because of this feature, data can often be imported without user intervention, even when the table definitions have changed. For example, tables or fields that have been renamed do not prevent import.
• Entity IDs, such as table IDs, class IDs, and extended data type IDs, are updated to match the IDs of the target system.
• The tool reads and correctly updates the SYSTEMSEQUENCES table.

Limitations

• The Test Data Transfer Tool (beta) does not make sure that data that you export is complete or coherent. However, the tool does export any data that you ask it to export.
• The tool does not make sure that data that you import produces a complete or coherent database. However, the tool does import any data that you ask it to import.
• The Test Data Transfer Tool (beta) can only be used to move data between environments that are running on the same version of AX 2012, for example, from an AX 2012 R2 production environment to a AX 2012 R2 test environment. You can move data between different builds of the same version. To move data between environments that are running on different versions, you must first upgrade the database through the standard upgrade process.

The Test Data Transfer Tool (beta) imports and exports the data as-is. In some cases, you might exclude tables or records from either import or export. Nevertheless, the tool does not make sure that the data is complete or coherent when other tables contain references to excluded tables or records. Therefore, after import and export, you must make sure that the data that you are importing or exporting is coherent.

Who should use this tool

Only advanced users should use the Test Data Transfer Tool (beta). You should be a database administrator or a developer who has experience using SQL Server. You should also have permission to read from or write directly to the Microsoft Dynamics AX database that you are working with, and to execute applications directly on the computer that is hosting the database.
Before you use the tool, you should read this topic and understand how the tool works. The Test Data Transfer Tool (beta) is a powerful tool for importing and exporting data, but every operation that works with data has risks. You are responsible for creating regular backups of your data before you perform operations by using the tool. You are also responsible for testing your particular uses of the tool to determine whether the tool meets your requirements.
You should also make sure that you understand which conditions are logged as errors during import. For example, you might try to import data for a table or column that does not exist in the target database. This attempt is not treated as an error by the tool, because the tool is designed to work seamlessly when the source and target databases have a different set of tables.
If you are not sure whether the Test Data Transfer Tool (beta) is the tool that you should use, see Plan for data.

Personally identifiable information

The Microsoft Dynamics AX 2012 Test Data Transfer Tool (beta) runs in the security context of the user who starts the tool. Therefore, the tool has the same permissions and restrictions that the user has when he or she accesses SQL Server directly.
You must run the tool directly from the computer that is hosting the database during import.