Most of the qualities of an ideal ERP system such as, Total Cost of Ownership, Flexibility, Usability, Integration and many more are implemented by conformance to the technical best practices discussed in this two part article by Anil Kumar Gupta. The Dynamics AX best practice can be grouped into three categories i.e. application design standards, shared standards, and AOT object standards.

The first part of this article will discuss Application Design Standards.

Application Design Standards

The Dynamics AX design standards consist of the following considerations:

• Code placement
• Performance optimization
• Using field groups in tables
• Auto property settings

Code Placement

Code placement is important as it affects the following:

• Performance
• Customization
• Reusability
• Maintainability (upgrade, extensibility, etc.)

The general guidelines are that the code should be placed in such a way that various calls to other layers are minimized, the operation is performed at the layer where it is least expensive, and the same code need not be written at several places (e.g. if business logic is written on a form then it needs to be written in the enterprise portal for the web client also). So we should not only think about the tier of placement in the three-tier architecture, but also about the best AOT (Application Object Tree) element type for a piece of code. Once these have been designed we need to think about the type of method or objects in the classes, etc.

Three-Tier Architecture Considerations

The three tiers in this architecture are dedicated for the following three types of jobs:

• Client—The Presentation layer—This is where the forms are stored. Place client‑specific classes and methods here.
• Object server—The Business application logic layer—Transaction-oriented database update jobs should be run here, close to the database.
• Database server—The Database layer—Utilize the power of the database server by using aggregate functions, joins, and other calculating features of the database management system.

Dynamics AX has a property, RunOn, for every AOT element, which indicates the layer where it should be executed i.e. Client, AOS, or Database server. This RunOn property may have one of three values i.e. Client, Called from, and Server.

Client: The object will live on the client.

Called from: The object will live at the tier where the object is created using the 'new' constructor.

Server: The object will live on the server.

Now we will discuss how to decide the RunOn property value.

Classes

The value of the RunOn property for a class will decide the location of the object created from that class.

• The value of RunOn property for a class will be same as the parent class if the parent class has a RunOn property other than Called from i.e. the RunOn property cannot be changed for a class if the parent class has as its value either Client or Server.
• If the parent class has Called from as its RunOn property value, it can be changed to Client or Server and if it is not changed it will retain the inherited value i.e. Called from.
• The Called from value of the RunOn property means the object will live at the tier where the code creating it (by calling the new constructor) is running.

Methods

Now we will discuss the execution place for various types of methods.

• Class static methods and table methods (except for the database methods) can have their default behavior.
• The execution place for a method can be changed to Server or Client by adding the Client or Server modifier keywords in the method declaration as shown below:
   
  • server static boolean mymethod(): to make server the execution place.
   
  • client static boolean mymethod(): to make client the execution place.
   
  • client server static boolean mymethod(): to make called from the execution place.

The following table summarizes the execution place of various types of methods:

GUI Objects and Reports

GUI objects always live on Client. GUI objects include the FormRun, FormDataSource, all FormControls, DialogBox, and OperationProgress objects.

Reports always live on Called from, which means the object will live at the tier where the code creating it (by calling the new constructor) is running.

Temporary Tables

Temporary tables instantiate and live at the tier where data is first inserted and it does not matter where they are declared. Since the placement of temporary tables is very critical for performance, temporary tables should live at the tier where they are used. If a table is utilized in more than one tier then it should live on the tier where the greatest number of inserts and updates are performed.

Queries

QueryRun has Called from as the default value of the RunOn property. The QueryRun should always be supplied from the same tier from where it was originally run.

If you want to create a new QueryRun in place of an old one, it should be created on the same tier where the old QueryRun was executed.

AOT Element Type Consideration

The following guidelines must be followed to decide the type of code container:

Verifying Dynamics AX customization to the Microsoft IBI Standards Customization Best Practices backed by theory Learn rapidly how to test Dynamics AX applications Verify Industry Builder Initiative-compliance of ERP software Get ready-made testing templates Code, design, and test a quality Dynamics AX-based ERP solution For more information, please visit: www.PacktPub.com/quality-assurance-for-dynamics-ax-based-erp-solutions/book

Performance Optimization

The performance optimization guidelines can be categorized into the following three categories:

• Database design
• AOS performance optimization
• General programming

Database Design

The database design principles are based on the following considerations:

• Minimizing the database calls by caching
• Minimizing database transactions
• Wise index designing
• Using the select statement in an optimum way
• Performing transactions in the shortest time possible

Caching

Database access should be avoided whenever it is not absolutely necessary as retrieving database records from memory is far cheaper and faster. Recording database records in memory is known as caching. The following are the possible type of caching on the server:

• Record caching
• Entire table caching
• Result-set caching

Record Caching

Record caching is a type of performance enhancement technique in which one or a group of records is retrieved from the memory rather than the database. Retrieving a record from memory rather than database significantly improves the data access. Record caching can be enabled only when the following conditions are satisfied:

• The CacheLookup property in the table should be enabled by selecting the values notITTS, Found, or FoundAndEmpty.
• The table has a unique index; either use the primary index or the first unique index. The index based on RecId (known as RecId index) does not qualify as a caching index.

The retrieved records can be placed in cache if the following conditions are met:

• The table is cached i.e. the above conditions are met.
• The select statement used to read the record uses an equal operator (= =) on the caching key.
• All the fields in the record are retrieved.

A record is looked for when the following conditions are met:

• The table is cached.
• The select statement used to read the record uses an equal operator (= =) on caching key.
• The select statement is either inside or outside TTS, but the value of the caching property for the table is not NotITTS and the select is not forupdate.

The following table summarizes the different types of caching mechanism:

Result-set Caching

The RecordViewCache is useful for caching tables that are not of static nature, or contain so many records that the other caching methods would be impractical.

This type of caching can be available through the RecordViewCache class. The RecordViewCache is instantiated using X++ select with a where clause that defines the result set. Technically the RecordViewCache can be instantiated using X++ select but it will create a copy of table in memory, which may be an inefficient use of memory.

The following rules apply to the instantiating X++ select:

• It may not be a join.
• It must be noFetch.
• The table may not be temporary.
• When running a Dynamics AX Object Server thin client, instantiation must be on the server.

The limitations of the result-set caching are as follows:

• The RecordViewCache is not shared between Dynamics AX clients.
• The RecordViewCache is deactivated as soon as the RecordViewCache object goes out of scope or is destroyed.

In some cases result-set caching may be dangerous and hence only careful use is recommended. The following facts about result-set caching will be helpful in deciding the use of result-set caching.

• The database is simultaneously updated with the RecordViewCache.
• Updating the key of a row that did not qualify the result set at the time of instantiation will not result in the row being included in the cache.
• Inserts are always included in the RecordViewCaches for that table. It is definitely an advantage; however, care should be taken when inserting a large number of rows in a table and at the same time having a RecordViewCache on the same table as it will prolong the cache update time.
• A delete will remove the row from RecordViewCaches on that table but not the table.
• A Delete_from will invalidate RecordViewCaches on that table.

As mentioned above the RecordViewCache can go out of synchronization and hence we may need to re-synchronize it again. X++ has a method reread, which retrieves the data from database and updates the cached copy.

Verifying Dynamics AX customization to the Microsoft IBI Standards Customization Best Practices backed by theory Learn rapidly how to test Dynamics AX applications Verify Industry Builder Initiative-compliance of ERP software Get ready-made testing templates Code, design, and test a quality Dynamics AX-based ERP solution For more information, please visit: www.PacktPub.com/quality-assurance-for-dynamics-ax-based-erp-solutions/book

Indexing

Index design is very critical as far as database performance optimization is concerned. In general indexes are stored in a separate file or table to that of the data table. Thus searches are performed through a small set of columns (indexed columns only) and then the data is ultimately retrieved from the table itself by the database using the index. The objectives are as follows.

Primary keys or indexes are generally used to uniquely identify a record. The original intention of primary and foreign keys was that of the representation and enforcement of referential integrity between related tables. Primary keys are now used as the most important index for a table and do not necessarily have referring foreign keys in other tables. Primary keys are usually attached to the data space of the table itself but not always. This is database product specific.

Non-primary indexes are indexes constructed from one or more table columns. Non‑primary indexes may or may not include the primary index column or columns. The purpose of non-primary indexes is to improve database access performance. These indexes will be created as a separate file within the database. Thus when searching these indexes a small number of columns is loaded into memory for searching. These indexes have virtual memory addresses into the data space of the table, thus allowing rapid access between index space and table space.

Note that the memory or cache space required for an index is much smaller than that of a data table since the row length of an index table is smaller. The reason behind this is that indexes will have a fewer columns in comparison to the data table. Thus in the case of page swapping in and out of memory or cache and disk, indexes load many more pages into memory at once since they are smaller than data tables and thus indexes can be traversed much more rapidly that data table spaces. Different databases handle this in different ways.

There are different types of indexes available.

Indexes can be unique or non-unique. Unique indexes are generally used as primary keys (not always) and non-unique indexes contain duplications that are generally used for database access.

Clustered indexes are generally data table space forms of hashing or btree algorithms. A clustered index clusters or groups the actual data table rows of the table where the actual data rows reside in the leaf pages of the index.

A non-clustered index sets the leaf pages of the index as pointers to the data pages containing the rows in the table.

Any type of indexes can be ordered as ascending or descending. Ascending indexes are an usual practice.

Now it is obvious that indexing does not make every database operation fast; operations other than read operations are even slowed because of indexing due to the increased efforts required to update index files in addition to the table.

What to Index

To unleash the maximum benefits from indexing while avoiding or minimizing the disadvantages of indexing, what to index or what not to index should be carefully decided. The following are rules of thumb:

Use indexes where frequent queries are performed:

• Columns are used in joins.
• Columns are used in where clauses or groupby.
• Columns are used in order-by clauses.
• The group-by clause can be enhanced by indexing when the range of values being grouped is small in relation to the number of rows in the table selected.

Indexes will degrade performance of inserts and updates, sometimes substantially. Following are a few such cases:

• Tables with a small number of rows
• Static tables
• Columns with a wide range of values
• Tables changed frequently and with a low amount of data access
• Columns not used in data access query select statements
• Tables having no fixed width or numeric data column to index or indexing not done on that column

Types of Indexes

There are several types of indexes and each has its own merits and demerits. Clustered indexes are indexes made in the table itself and not in a separate index file and hence with this type of indexes the entire table is sorted as per the indexed column. This is the primary difference between clustered and non-clustered indexes. In non-clustered indexes, the index is created on one or more column and then column value it referenced to record in table. So in non-clustered indexes are primarily make two operations i.e. locating key in index and then locating referenced record in table unlike directly searching record in clustered indexes. Due to this fact clustered indexes are a better choice than non-clustered indexes and hence it is recommended to have a clustered index if only one index is required or that the most frequently used column or primary key should be a clustered index.

Clustered indexes also have a few disadvantages. When an update action changes the value of the indexed column, it will essentially require two actions i.e. delete from previous position and then insert at next position as per the updated value.

A composite index is an index on two or more columns, which can be either clustered or non-clustered. Usually composite indexes are not advised, except in the following cases:

• To cover a query: To cover all columns in a query so that the database will not search for the reference in database and just scanning the column will serve the purpose.
• To meet the specific (complex) search criteria e.g. the primary key itself is a combination of two or more columns.

Some tips for indexing:

Select Statements

The following are the best practices regarding the use of select statements:

• Use joins instead of multiple nested while selects.
• Use field lists where applicable.
• Use select or aggregate functions where applicable.
• Use delete_from (instead of while, select, ... delete()).
• Select from the cache where possible.

Transactions

The key principles for the best practices related to database transactions are that the number of database transactions should be minimized and those that are needed should be conducted in minimal time and deadlock must be avoided. Hence the following best practices can be derived:

• Never wait for a user interaction inside a transaction.
• If several operations must be performed before data returns to a consistent state, perform all the operations in one transaction.
• Make transactions as short and small as possible to avoid deadlocks and large rollback logs.
• Avoid deadlocks or lengthy locks. Explicitly select records to be updated in a specific order within the table (preferably the order of the key), each time, throughout the application. Select records for update from different tables in the same order, each time, throughout the application.
• Do not write or update if the record has not been changed.
• To avoid lengthy locks on commonly used records the following best practices are further recommended:
  • Use Optimistic Concurrency Control (OCC).
  • Update locks on central, commonly used (updated) records that represent bottlenecks in the application.
  • Try to avoid updating commonly used records inside transactions.
  • Structure the design so that you can use inserts instead of updates. Inserts do not lock objects.
  • Place the select statement for updates as close to the ttsCommit statement as possible, to reduce the amount of time records are locked.

Use Joins in Forms

The use of join in forms can offer distinct advantages in comparison to use of display methods that contains select queries. In one call all records can be fetched for a form showing approximately twenty grid lines. An additional advantage of using join is that it is very easy to sort, filter, and find on the joined fields.

AOS Performance Optimization

The key principle behind AOS optimization is to minimize the number of calls between the client and the server. The amount of data transported in each call is secondary to the number of calls. A few of best practices are:

• Achieve select discount.
• Use containers to reduce the client or server calls.

Achieve Select Discount

Whenever a select statement needs to be executed from a data source located at another tier, the returned record has certain minimum size, which means multiple records can be fetched in a single call and hence less round trip calls to AOS are needed for a given number of records. This is phenomenon known as 'Select Discount'.

If only one record needs to be fetched, the number of records can be minimized to the first record only.

Using Field Groups in Tables

The field groups are an important feature in Dynamics AX design, which is not only used to synchronise forms and reports with the concerned tables (while adding, deleting, or modifying any field of a table), but also for many other purposes such as caching and performance optimization. The following are a few advantages of using field groups in a table:

• IntelliMorph can automatically update the layout of all related forms and reports whenever any change is made to the field groups.
• It is easy to upgrade new forms and reports that have identical field groups to the standard table i.e. the custom objects in standard field groups will also be updated in the upgraded version.
• The sequence of fields can be used to determine the sequence of fields in forms and reports and hence the same order of fields can be assured in all concerned forms and reports related to a table.
• Identical field groups on tables and forms help in AOS optimization.

Maintaining Auto Property Settings

In Dynamics AX many properties should have their values set to Auto, which can enable the application object to accommodate any changes automatically as per the kernel's interpretation about various things such as locale, etc. Some advantages of the Auto property settings are as follows:

• Auto selection of date and time format as per the locale settings of the client
• Auto selection of currency code
• Form or report layout control as per the rights available to the logged-in user

Summary

In this article we discussed the the Application Design Standards best practices for Dynamics AX. The second part will discuss the Shared Standards and AOT Object Standards.

Verifying Dynamics AX customization to the Microsoft IBI Standards Customization Best Practices backed by theory Learn rapidly how to test Dynamics AX applications Verify Industry Builder Initiative-compliance of ERP software Get ready-made testing templates Code, design, and test a quality Dynamics AX-based ERP solution For more information, please visit: www.PacktPub.com/quality-assurance-for-dynamics-ax-based-erp-solutions/book

About the Author

Mr. Anil Kumar Gupta is a highly experienced Information Technology professional with proven Software Test Management and Software Process Improvement expertise. He has led various programs in diverse software projects such as ERP, eGovernance, eBanking, eLearning, system Software, Application Software, etc. in conjunction with Commercial off-the-shelf (COTS) products, Business Process Management (BPM), Customer Relationship Management (CRM), and Enterprise Resource Planning (ERP) technology solutions, J2EE, and .Net to meet and exceed challenging business needs in both the commercial and governmental sectors. His 10+ year professional career combines expertise and strong technical qualifications in many domains including but not restricted to Systems Engineering and Integration, Financial Services, and ERPs for Manufacturing and Process Industries fields using computer tools to solve complex engineering problems. His exceptional analytical and communications skills along with effective interaction with management, vendors, customers, and staff, have allowed for meeting aggressive deadlines under pressure. At the same time, Mr. Gupta identified, managed, and resolved multiple complex project tasks that included changing priorities in both team and sole-person environments. Mr. Gupta provides strong collaborative management which is task-oriented with attention given to required details and accuracy. Mr. Gupta is a self-motivated, energetic, dependable, flexible, assertive, attentive, and resourceful manager, who provides effective team support that combines interpersonal, coordination, mentoring, and verbal and written communication skills.

During his professional career he met many challenges, which included the establishment of QA and Testing practice at EuroInfo Systems Pvt. Ltd. (Currently known as Tectura India). Tectura India is the largest MBS solution provider and globally Tectura is the biggest MBS solution provider. EIS was the pioneer in establishing a separate QC department in 2004 and this challenge was given to Anil. Due to his successful quality initiatives EIS was awarded the Global Axapta Excellence Award for the year 2005, among 1000 MBS partners. The author is the most experienced resource for Dynamics AX Testing in India and has worked for multiple IBI-compliant Dynamics AX ISV projects.