AX 2012 Technical
1.
What’s new
in Dynamics AX 2012 SSAS - Analysis service project wizard.
AOS - Validfrom and validto columns, Unit of work class,
Inheritance among the tables
Client -Form styles, Search and parts
EP - Sharepoint 2010, listpge famework, windows live authentication
Morphe x - Models and model store, dev. workspace, some layers
renamed and powershell
SSRS – Labels in reports,
auto reports, cross reference can access, unlimited dimensions
X++ -Eventing,
attributes, .Net proxies to X++ classes, faster compilition
2.
What are the
architecture changes done in AX 2012 R2?
The model store and
the transaction data are stored in separate OLTP databases. In other versions
of Microsoft Dynamics AX 2012 prior to Microsoft Dynamics AX 2012 R2, the model
store and transaction data are stored in a single OLTP.
3.
What are
Partitions? What is the purpose of partitions in Microsoft Dynamics AX 2012 R2?
This topic provides comprehensive information about most aspects of the partition feature for the developer audience.
Section
|
Description
|
|---|---|
Explains the definition and purpose of partitions.
| |
Identifies the infrastructure that supports partitions.
| |
Lists the sequence of major actions you can perform when you work with partitions.
| |
Describes the special processing that the Application Object Server (AOS) applies to queries that involve partitions.
| |
Describes the type of data that is shared across partitions and companies at different levels of data isolation.
|
1. Overview of Partitions and Legal Entities
1.1 Definition of Partition
In the glossary for Microsoft Dynamics AX, the formal definition of a partition is:
A division of an application’s processing into logical or functional parts.
Partitions divide and isolate the business data of an installation by using special processing that the AOS applies to data queries. This special processing occurs immediately before the queries are sent to the underlying Microsoft SQL Server database when a system field named Partition is present in a queried table.
1.2 Purpose of Partitions
The purpose of a partition is to logically separate the data within its boundaries from the data in other partitions. A partition enables the AOS to isolate the data in the partition from users who are not authorized to access the data.
For example, a holding corporation might have several subsidiaries or other legal entities. An installation of Microsoft Dynamics AX for the corporation can have several partitions, perhaps one for each subsidiary.
1.3 Relation between Legal Entities and Partitions
Each partition contains at least one company or legal entity. A legal entity occurs in only one partition. When you create a legal entity, the system assigns it to the current partition. The legal entity can never be moved to another partition. However, its data can be exported from the partition and then imported to another company in another partition.
 Note |
|---|
Partitions were added starting in Microsoft Dynamics AX 2012 R2. When you upgrade from a version that did not support partitions, you must assign the existing companies or legal entities to one or more partitions during upgrade.
|
For more information including a diagram of the partition architecture, see Data partitioning architecture.
2. The Partitions Table
The system stores information about partitions in the Partitions system table. The Partitions table is located in the AOT under System Documentation > Tables.
2.1 Fields of the Partitions Table
The Partitions table has the following fields:
Field name
|
Data type
|
Description
|
|---|---|---|
PartitionKey
|
String, 8 characters is the maximum length.
|
This is the short identifier that you use and see most often when interacting with the partition. For example, you can specify this value in the message header when you use AIF services.
The values in this field are unique. A few tables have a foreign key field that refers to this field. The AOS has no special process for foreign keys to this field.
|
Name
|
String, 40 characters is the maximum length.
|
This is long name of the partition. This value is displayed or used in only a few places in the system.
|
RecId
|
This is a standard RecId system field that most tables have, of type int64.
|
The AOS has special processing for foreign key fields that reference this field. The processing adds filters to the Where clause.
|
Under AOT > System Documentation > Types, the following two types match fields on the Partitions table:
- Partition – corresponds to the RecId field of the Partitions table.
- PartitionKey – corresponds to the PartitionKey field of the Partitions table.
2.2 Tables that are Affected by Partitions
The properties of tables in the AOT can indicate whether a given table is affected by data partitions. The following table describes database tables and their partition-related fields.
AOT path
|
Property value
|
Description
|
|---|---|---|
AOT > Data Dictionary >Tables > SystemTable
|
Systemtable= Yes
or
TableGroup=Framework
|
These tables are used by the Microsoft Dynamics AX system. They do not contain business data. An example is theAifAction framework table.
The AifAction table is one of the few tables for which you can modify the SaveDataPerPartition property by using theProperties window. Do not modify the value of this property. The system might change this value.
|
AOT > Data Dictionary >Tables > BusinessTable
|
Systemtable= No
and
TableGroup!= Framework
|
Most tables that contain business-related data are in this category. These tables have a system field named Partition.
Examples include the BankAccountTable and CustTable tables.
When you create a new table, the system adds the Partition field.
The Partition field never appears in the Fields list for the table. However, the Partition field is visible in the underlying SQL Server database.
|
AOT > Data Dictionary >Tables >InformalPartitionedTable
|
Not applicable.
|
A small number of tables do not have the system Partition field, but do have a PartitionKey field as a foreign key. Examples include the AifGatewayQueue and AifInboundPort tables.
|
AOT > System Documentation> Tables
|
Not applicable.
|
Some tables in this area of the AOT do have a Partition field. An example is the DataArea table.
Other tables in this area do not have a Partition field. An example is the TimeZonesList table.
|
3. Steps for Working with Partitions
The following subsections show the high-level steps that you perform when you work with partitions.
3.1 Add a Partition by using the Partitions Form
You add a new partition by using the Partitions form. You can access the form from the following locations:
- System administration > Area page > Setup > Partitions
- AOT > Forms > PartitionAdministration > Open
The following columns are displayed in the Partitions form:
- Partition Key – The short 8 character name or identifier of the partition. This name must be unique across the installation.
The values in this column correspond to the PartitionKey fields that are in some tables in the system. - Name – A short description of the partition, with a maximum length of 40 characters.
The Partitions form does not display the RecId value. The values in a RecId column correspond to values in the Partition fields on tables throughout the system.
A new installation of Microsoft Dynamics AX creates a default partition named initial. The following image shows the Partitions form after some partitions have been added.
You can never delete a partition.
3.1.1 To add a partition
- Click the Add button. This creates a new row in the grid.
- Enter string values into the cells of the new row.
- To commit the add, give focus to another row, or click the Close button to exit the form.
3.2 Switch to another Partition
When you start the AX32.exe client, your session is assigned to one partition context for that whole session. You can switch to another partition only by starting a new client session.
When you start a new client session, the system reads a variety of items to determine which partition is specified for your session. The process reads items in the sequence that they are listed in the following table. The process stops when it finds that a partition is specified.
Item
|
Description
|
|---|---|
Command-line option
|
You can start the client at a command prompt in a cmd.exe window. You can add the –partition parameter with a Partition Key value, by using the following format:
ax32.exe -partition=partition_key
|
The Partitionsetting in the client configuration tool.
|
This text box control is located in the General tab. By default, the text box is empty.
The client configuration for each user is stored as an entry in the following subkey in the registry:
HKEY_CURRENT_USER\Software\Microsoft\Dynamics\6.0\Configuration\. |
The default partition for the user.
|
In the Users form, the system administrator can select one user record in the grid, and then click the Edit menu. The form then shows a check box that is labeled Current partition is default partition. If the user is authorized for more than one partition, the administrator can select the check box to make the current partition be the default partition for the user.
The Users form and its check box control are discussed further elsewhere in this topic.
|
The initial partition.
|
The initial partition is used if no other partition is specified. The initial partition is part of every system.
|
If you specify a partition for which you do not have authorization, an error message is displayed, and then the empty client window closes.
3.3 Perform the Partition Initialization Checklist
The Partition initialization checklist is displayed the first time that you start the client in the context of a newly created partition. All tasks in the checklist are optional. Even if you mark all checklist items as complete, you can return to the checklist at any time. To return to the checklist, navigate as follows:
Click System administration > Setup > Checklists > Partition initialization checklist.
Click System administration > Setup > Checklists > Partition initialization checklist.
3.4 Assign a User to One or More Partitions
A system might have several partitions. A typical user has authorization in only one partition. However, some users might be authorized to see data in more than one partition. System administrators are authorized to see data in all partitions.
Use the Users form to add a user to the current partition. You can open the Users form as follows:
Click System administration > Common > Users > Users.
Click System administration > Common > Users > Users.
The Users form contains a check box that is labeled Current partition is default partition. This setting is considered in the startup sequence that determines the partition context for a client session.
3.5 Administer the Legal Entities in a New Partition
When you create a new partition, the system adds one legal entity or company in the new partition. The short Company code value for the new company is always DAT. However, because each DAT company is in a different partition, each is a separate company from the others. You cannot change the value of the Company code. You can change the longer company Name value by using the Legal entities form. Or, you can add another company to the new partition.
To access the Legal entities form, navigate as follows:
Click Organization administration > Setup > Organization > Legal entities.
Click Organization administration > Setup > Organization > Legal entities.
The Legal entities form displays only the companies that are in the current partition.
4. Data Access within Partitions and Companies
Queries of business data are restricted to the partition context of the current client session. Typically queries are also restricted to data for the current company, although you can sometimes query across multiple companies. These restrictions apply to AOT > Queries and to X++ SQL.
4.1 X++ SQL is Restricted to Current the Partition
The SQL statements in your X++ code are sent to the AOS for additional processing. The AOS generates Transact-SQL statements for SQL Server. The generated T-SQL often includes additional filters that are applied under the Where clause. The AOS adds filters for the current partition and the current company of your session. The filters target the fields named Partition and DataAreaId.
Do not add filters for partition and company to your X++ SQL source code.
4.2 Caching, Flushing, and Partitions
Suppose a table has its SaveDataPerPartition property set to Yes, meaning the table has the Partition system field. The system can maintain data from the table in a data cache. Cached data for one partition can never be access from a second partition. However, a cache flush command that is issued in one partition can sometimes flush data for all partitions.
The value of the table’s CacheLookup property affects whether a cache flush that is issued in one partition affects all partitions. The details are shown in the following table.
CacheLookup value
|
Effect of flush
|
|---|---|
|
Data for only the current partition is flushed from the data cache.
But data for other partitions is not flushed and remains unaffected. |
|
Data for the current partition is flushed from the data cache.
And data for other partitions is also flushed. |
|
Not applicable.
|
For more information about caching, see Record Caching.
4.3 Effect of the X++ crossCompany keyword
The crossCompany keyword can be added to your X++ SQL statement. The keyword suppresses the restriction that limits data access to the current company. It causes your X++ SQL statement to retrieve data regardless of what company the data is for.
The crossCompany keyword applies only to the companies that are in the current partition. The X++ crossCompany keyword is shown in the following X++ SQL code example:
while select crossCompany AccountNum from xrecCustTable {...}
For more information, see Cross-Company Data Access.
A query under AOT > Queries that has its AllowCrossCompany property set to Yes is also restricted to the companies in the current partition.
| Note |
|---|
There is no crossPartition keyword in X++. If you must access data from multiple partitions in one statement, consider issuing direct T-SQL by using the Statement class. This is explained in the next section.
|
4.4 Data Accessed by using Direct T-SQL can Span Partitions
If you have sufficient authorization, you can use the Statement system class to issue Transact-SQL commands in a manner that bypasses the AOS and goes directly to SQL Server. When you bypass the AOS, you must decide whether your code will access data outside the current partition. In many cases, you must enforce the same restrictions that the AOS enforces. We recommend that you use Transact-SQL sparingly, and only when it is necessary.
You can use the following Transact-SQL Select statement to obtain a report of all the partitions on the system, and of all the companies that are in each partition.
-- T-SQL to run in the Dynamics AX database directly in SQL Server: SELECT da1.Partition, pt2.PartitionKey, pt2.Name, da1.ID as [Company-ID] FROM DataArea as da1, Partitions as pt2 WHERE da1.Partition = pt2.RecId ORDER BY pt2.PartitionKey, da1.ID;
The following output was generated by the previous T-SQL Select statement. The output report shows that our test system had two partitions. It also shows that several companies existed in the initial partition:
Partition PartitionKey Name Company-ID
5637144827 g2part g 2 partition DAT
5637144576 initial Initial Partition CEBD
5637144576 initial Initial Partition CEU
5637144576 initial Initial Partition DAT
5637144576 initial Initial Partition SDKA
5637144576 initial Initial Partition SUSA
5637144827 g2part g 2 partition DAT
5637144576 initial Initial Partition CEBD
5637144576 initial Initial Partition CEU
5637144576 initial Initial Partition DAT
5637144576 initial Initial Partition SDKA
5637144576 initial Initial Partition SUSA
| Note |
|---|
For code examples of enforcing the partition restriction in direct T-SQL commands issued through the Statement class, see How to: Include a Filter for Partition in Direct Transact-SQL.
|
5. Data at Different Isolation Levels
The following table describes the type of data that is shared across partitions and companies at different levels of data isolation.
Scope of sharing
|
Description
|
Example tables
|
|---|---|---|
Shared across:
|
Data that is required by the Microsoft Dynamics AX system are shared across the system.
The Application Object Tree (AOT) displays metadata. All elements in the AOT are shared across the system.
|
About 4% of tables belong in this sharing category. The following tables are representatives:
|
Shared across:
|
Companies that are managed together on one installation of Microsoft Dynamics AX can benefit from sharing relatively static business data when appropriate. For example, the data in code tables does not have to be provided separately for each company.
Tables in this sharing category have the following two system fields:
|
About 30% of tables belong in this sharing category. The following tables are representatives:
|
Not shared.
|
Transactional business data is not shared and is specific to one company or legal entity.
Tables in this sharing category have the following two system fields:
|
About 66% of tables belong in this sharing category. The following tables are representative:
|
4.
What are Models
and Model store? Managing Models?
Models were introduced in Microsoft Dynamics AX 2012 to help partners and customers more easily install and maintain multiple solutions side by side in the same layer. This topic introduces the concept of models, and describes how models relate to layers and label files. This topic also describes the model store, which is a database in which all application elements for Microsoft Dynamics AX are stored.
Models
A model is a set of elements in a given layer. Each layer consists of one or more models. Each layer contains one system-generated model that is specific to that layer. Every element in a layer belongs to only one model. In other words, no element can belong to two models in the same layer, and every element must belong to a model.
A default model owned by Microsoft exists in each layer. Default models cannot be modified.
A model is permanently associated with the layer that the model was created in. If you need to move one of your models from one layer to another, you must create a project from the model in the Application Object Tree (AOT), export the project as an xpo file, create a target model in the desired layer, delete the original model to avoid having to resolve layer conflicts, and import the xpo file to the target model. If you are moving elements between models in the same layer, you can use the Move to model command in the AOT.
Models are stored in the model store. The model store is a database in which all application elements for Microsoft Dynamics AX are stored. Customizations are also stored in the model store. The model store replaces the Application Object Data (AOD) files that were used in earlier versions of Microsoft Dynamics AX. Models that have been installed in the model store are used at run time.
 Important |
|---|
In Microsoft Dynamics AX 2012 R2, the model store was moved into a database that is separate from the business database.
|
Models can be exported to files that have the .axmodel extension. These files are called model files. Model files are deployment artifacts. Model files can be signed with strong name signing and Microsoft Authenticode signing.
Models and label files
In Microsoft Dynamics AX 2012, label files, or ALD files, are part of models. A label file must be added to a model before the model can be installed. After a model has been installed, ALD files are pulled from the model store to the local of Application Object Server (AOS) instance when the AOS is started. When the AOS is shut down, the ALD files are pushed back to the model store.
ALD files from earlier versions of Microsoft Dynamics AX are not part of a model file. However, you can import these files into the model store from the Label Files section of the AOT. Use the Create from file shortcut command.
| Important |
|---|
The ALD file from an earlier version of Microsoft Dynamics AX must not be located in the application folder of AOS. Otherwise, you cannot import the file.
|
How models improve the installation and maintenance of side-by-side customizations
In earlier versions of Microsoft Dynamics AX, multiple partner solutions could not exist side by side in the same layer. However, models now enable side-by-side customizations. Additionally, the following improvements help you work with side-by-side customizations:
- The development environment for Microsoft Dynamics AX lets you create a project for each model that is installed. Therefore, you can quickly see all the installed customizations in a layer for a given model.
- When you import a model, elements in the model that you are importing may conflict with another model in the same layer. You can now create a conflict model in the patch layer that is associated with the layer that you are working in. You can then resolve the conflicts in the conflict model. In earlier versions, no warnings about conflicts were displayed. Instead, elements were just replaced.
- You can now leave the rest of the layer intact when you uninstall a model. In earlier versions, if you wanted to uninstall customizations, you had to either remove the customizations manually from the AOT or remove the layer.
| Important |
|---|
Element IDs are now specific to each installation. In other words, the same method or class has a different element ID in different installations. To maintain installation-specific element IDs when you import and export models and model stores, you must strictly follow specific procedures. If you do not follow the correct procedure when you import or update models, IDs can become randomized, and data integrity can be affected. For more information, see Maintaining Installation-Specific Element IDs and Element Handles.
|
Working with label files across solutions
We recommend that you use one label file per solution to simplify installation.
If you find that you require multiple label files, we recommend that you create a single shared, cross-solution label file and package it as a model file. Then, when you install solutions, you must install two models: the solution itself and the label model.
If you want to ship additional languages, you can add the languages to the solution model, increment the model's version number, and then reimport the model.
For more information, see Import label files into the new model store.
Installing metadata
Metadata can be installed in various ways. The following table describes each installation method that is available.
XPO files
|
Model files
|
Model store files
| |
|---|---|---|---|
Installation tool
|
MorphX
|
AXUtil.exe or Windows PowerShell cmdlets
|
AXUtil.exe or Windows PowerShell cmdlets
|
The files can be uninstalled.
|
No
|
Yes
|
No
|
The files can be signed.
|
No
|
Yes
|
No
|
Microsoft Dynamics AX element IDs are preserved.
|
Yes, if the elements already exist in the target model store
|
Yes, if the elements already exist in the target model store
|
Yes, all element IDs of the source model store are preserved.
|
Compilation is required after installation.
|
Yes
|
Yes
|
No
|
CIL generation is required after installation.
|
Yes
|
Yes
|
No
|
| Important |
|---|
To avoid ID conflicts, we recommend that you do not use xpo export/import to move customizations in environments in which you are primarily relying on importing and exporting models and model stores.
|
The following table describes the scenarios in which we recommend you use each installation method.
Scenario
|
Recommended installation method
|
|---|---|
Distributing a solution to customers
|
Model files
|
Deploying a solution in a development or test environment
|
Model files or XPO files
|
Deploying a solution to a production environment
|
Model store files
|
5.Table
Keys: Surrogate, Alternate, Replacement, Primary, and Foreign [AX 2012]
This topic describes several terms and concepts of keys on data tables, as they apply to Microsoft Dynamics AX.
All keys are unique keys, meaning they disallow duplicate values and null values.
Terminology for Major Concepts of Keys
This section describes the terminology for keys that appear in property names in the AOT Properties window.
Primary Key
A primary key is one type of key. The other type of key is an alternate key. There is a maximum of one primary key per table, whereas a table can have several alternate keys. The primary key is usually the type of key that other tables, called child tables, refer to when a foreign key field in those other tables need a relational identifier.
Starting in Microsoft Dynamics AX 2012 the primary key for every new table is always enforced by an index that has exactly one field. The one field is usually an incremented number or a completely meaningless number that is generated by the system. For new tables the default is a primary key based on the RecId field. This is represented as thesurrogate key in the user interface.
The following table describes the PrimaryIndex property and other major properties that are related to keys.
Property
|
Description
|
|---|---|
PrimaryIndex
|
The drop-down list contains the surrogate key plus every index on the table that has its AlternateKey property set to Yes.
|
CreateRecIdIndex
|
This property controls whether the system creates a unique index on the RecId field. The default value is Yes. This is the basis of the surrogate key.
No other field is added to this index, not even DataAreaId.
|
ReplacementKey
|
The drop-down list contains every index that has its AlternateKey property set to Yes.
You might change the default blank value to an index whose field values within each record provide a name or other moniker that is meaningful to people. If a ReplacementKey is chosen, its fields can appear on forms to helpfully identify each record.
The ReplacementKey should be a set of fields that represent the natural key.
|
ClusterIndex
|
The ClusterIndex value is given to the underlying Microsoft SQL Server database system as a performance tuning choice. This choice generally controls the physical sequence in which the records are stored in the underlying database.
|
The following AOT image highlights the table properties that are related to keys.
Properties of the AtomicElement demonstration table
Alternate Key
A table can have several alternate keys. Any one alternate key can switch to being the primary key, if the alternate key is comprised of only one field.
A table can reference the alternate key of another table. However, it is more common for a table to reference the primary key of another table. As an option, an alternate key can be chosen as the ReplacementKey of a table.
In practice each alternate key relies on a unique index for its implementation and enforcement. However, a unique index alone does not make an alternate key. TheAlternateKey property must be set to Yes to make a unique index be an alternate key.
The following table describes properties on the AOT node for an index.
Property
|
Description
|
|---|---|
AllowDuplicates
|
No means that the combined fields of the index must together make a value in each record which no other record has.
|
AlternateKey
|
Yes means that other tables can create foreign key relations that reference this key, as an alternative to referencing the primary key.
Indexes with two or more fields cannot have their AlternateKey property value set to Yes.
|
ValidTimeStateKey
|
A key that is marked as a valid time state key is not a candidate key for child tables to reference in their foreign key relations. Instead, this key is meant for managing date effective data in its own table.
The default is No. This field can be Yes only if the ValidTimeStateFieldType property is Yes on the table. Yes means this key contains the ValidFromand ValidTo fields.
The ValidTimeStateKey property cannot be set to Yes when the AlternateKey property is set to No.
|
The following image shows that the SymIdx index is an alternate key. Its AlternateKey property is set to Yes.
The properties of the SymIdx index
Relation
In Microsoft Dynamics AX a relation represents a foreign key. The following image shows that the AtomStIdx alternate key of the AtomicState parent table is referenced by this foreign key of the AtomicElement child table. The foreign key is comprised of the AtomicStateName field.
The properties for the AtomStFkyRel relation
The following image displays the AtomStIdx alternate key on the AtomicState table. The previous AtomStFkyRel relation references this alternate key.
The properties of the AtomStIdx alternate key and index
For more information about the properties of table relations, see Table Relation Properties.
ReplacementKey
A replacement key is an alternate key that the system can display on forms instead of a meaningless numeric primary key value. Each table can have a maximum of one replacement key.
The replacement key is chosen by setting the ReplacementKey property on the table. The drop-down list offers every alternate key as an available value. In the previous image of the AtomicElement table properties, the ReplacementKey property is SymIdx.
Other Terminology for Keys
In Microsoft Dynamics AX, there are other terms that are used to describe table keys. These terms do not appear as property names in Microsoft Dynamics AX. These terms are described in the following table.
Term
|
Description
|
|---|---|
foreign key
|
In Microsoft Dynamics AX, an AOT node under MyTable > Relations represents a foreign key. For more information, see the previous Relations section in this topic.
|
natural key
|
A key whose value has meaning to people. Most replacement keys are natural keys.
|
surrogate key
|
A key whose value has no meaning to people. A large number generated by the system, such as RecId, could be a surrogate key.
|
unique key
|
A broad term that applies to primary keys and to alternate keys. It does not apply to foreign keys. This term emphasizes that all values for a given key must be unique within one table. All fields in a unique key must be not-nullable.
|
6. Table
properties ?
This topic describes the properties that are in the Properties window for table elements in the Application Object Tree (AOT). Table elements are located under Data Dictionary >Tables.
For information about system properties available for tables, see System and Common Properties. For guidelines about setting property values, see Best Practices for Table Properties.
Next is a list of topics about the properties of AOT elements that are sub-elements under a table element.
Table Properties
The following table describes the properties of table elements in the AOT.
Property
|
Description
|
New in this version of
Microsoft Dynamics AX | ||||
|---|---|---|---|---|---|---|
Abstract
|
Specifies whether or not the table supports inheritance.
The default value is No. If the value is Yes, the table cannot be a direct target of X++ SQL statements such asupdate_recordset and select.
This property is unavailable when the SupportInheritance property is set to No. For more information, see Table Inheritance Overview.
|
AX 2012
| ||||
AnalysisDimensionType
|
Determines the type of dimension created based on the IsLookup property setting. You can specify one of the following values.
IsLookup property set to Yes
IsLookup property set to No
| |||||
AnalysisIdentifier
|
Specifies the field to be used as the identifier for the dimension in a SQL Server Analysis Services (SSAS) cube.
| |||||
AOSAuthorization
|
Specifies the type of operation that a user can perform on a table, depending on the user's permissions.
When the property is set to None, an authorization check is not performed.
| |||||
CacheLookup
|
Determines how to cache the records retrieved during a lookup operation.
This CacheLookup property exists only on tables that do not inherit from another table.
On an inheritance root table, this property cannot be set to EntireTable by using the AOT Properties window. You must not use other techniques to assign this value to inheritance root tables. For example, do not use the AOTsetProperty method of the TreeNode class to assign this value.
| |||||
ClusterIndex
|
Specifies the cluster index.
This property is used only for SQL optimization purposes.
| |||||
ConfigurationKey
|
Specifies the configuration key for the table.
Configuration keys allow a system administrator to enable and disable certain parts of an application.
| |||||
CountryRegionCodes
|
Specifies the country region codes where the table is applicable or valid. The client framework and application may make use of this property to enable or disable country or region specific features. This is implemented as a comma-separated list of ISO country codes in a single string. The values must match data contained in the global address book.
|
AX 2012
| ||||
CountryRegionContextField
|
Specifies the field that will be used to identify the country context. This relates to the CountryRegionCodes property.
|
AX 2012
| ||||
CreatedBy
|
Indicates whether the system maintains the CreatedBy field for the records in a table. This field contains information about who created a particular record.
| |||||
CreatedDateTime
|
Indicates whether the system maintains the CreationDate and CreationTime fields for the records in a table. This field contains the date when a record was created.
|
AX 2012
| ||||
CreatedTransactionId
|
Indicates whether the system maintains the CreatedTransactionId field for the records in a table. This field contains information about which transaction created the record.
| |||||
CreateRecIdIndex
|
Indicates whether an index on the Record ID field is created.
| |||||
DeveloperDocumentation
|
Describes the purpose of a table and explains how it is used in the application. A description is typically no more than five sentences long and is written as a single paragraph.
| |||||
EntityRelationshipType
|
Classifies a table according to common entity relationship (ER) data model notation. A table is classified as an entity or a relationship. An entity represents an object. A relationship represents an association between two objects.
| |||||
Extends
|
Derives the table from another table that is chosen as the property value.
The value is null when the SupportInheritance property is set to Yes. For more information, see Table Inheritance Overview.
|
AX 2012
| ||||
FormRef
|
Specifies the display menu item that is activated when a table is referenced. A display menu item is associated with a form.
When you use a primary index field on a report, this form is available as a link in the report. A primary index is specified by using the PrimaryIndex property.
If you leave this field blank, the system attempts to display a form that has the same name as the table.
| |||||
ID
|
Specifies the table ID generated by the system. For more information, see Application Object IDs.
| |||||
IsLookup
|
For report models, it specifies whether the table information is incorporated into other tables that reference it when a report model is generated.
For OLAP cubes, it determines whether to generate a consolidated dimension or a distinct dimension. You can specify one of the following values.
For more information about dimensions and star and snowflake schemas, see Introduction to Dimensions (SQL Server 2005 Books Online).
| |||||
Label
|
Specifies the label for a table.
| |||||
ListPageRef
|
Specifies a display menu item that points to a form that can show a list of this record type.
|
AX 2012
| ||||
Model
|
Specifies which model the table is in.
A model is a logical grouping of elements in a layer. An element can exist in exactly one model in a layer. Examples of elements are a table or class. The same element can exist in a customized version in a model in a higher layer.
|
AX 2012
| ||||
ModifiedBy
|
Indicates whether the system maintains the ModifiedBy field for the records in a table. This field records the person who performed the last modification to a record.
| |||||
ModifiedDateTime
|
Indicates whether the system maintains the ModifiedDate field for the records in a table. This field records the date of the last modification of a record.
| |||||
ModifiedTime
|
Indicates whether the system maintains the ModifiedDateTime field for the records in a table. This field records the date and time when a record was last modified.
| |||||
Name
|
Specifies the table name.
| |||||
OccEnabled
|
Specifies whether the optimistic concurrency mode is enabled for a table.
When this mode is enabled, data is not locked from future modification when it is fetched from the database. Data is locked only when the actual update is performed.
| |||||
PreviewPartRef
|
Specifies the Info Part or Form Part to be used in the enhanced preview.
An info part shows a collection of data fields from a specified query. An info part uses metadata to describe how the data appears. A form part represents a pointer to a form.
|
AX 2012
| ||||
PrimaryIndex
|
Specifies the primary index.
Only a unique index can be selected.
The property is used for database optimization purposes and to indicate which unique index to use as the caching key. If a primary index is not specified, the unique index with the lowest ID is used as the caching key.
| |||||
ReplacementKey
|
Specifies the fields to display as the identifier for data in some form controls.
|
AX 2012
| ||||
ReportRef
|
Specifies the output menu item that is activated when a table is referenced. An output menu item is associated with a report.
When you use a primary index field on a report, this report is available as a link in the report. A primary index is specified using the PrimaryIndex property.
| |||||
SaveDataPerCompany
|
Indicates whether the data for the current company is saved.
If you set the property to No, data is saved without a company identifier (DataAreaId).
| |||||
SaveDataPerPartition
|
Shows whether the table has a system field named Partition. This is meant to be a read-only system field.
If the table has a Partition field, each record is assigned to one partition. Each record is kept hidden from data access operations that are run under the context of other partitions.
|
AX 2012 R2
| ||||
SearchLinkRefName
|
Specifies the name of the menu item that links to information on a Web site about a table record listed in the Enterprise Portal search results.
If the SearchLinkRefType property is set to URL, select a menu item from the SearchLinkRefName property list that links to a Web part page that displays the table data.
Forms and reports on Web part pages can display data.
| |||||
SearchLinkRefType
|
Specifies the type of the menu item that links to information on a Web site about a table record listed in the Enterprise Portal search results.
| |||||
SingularLabel
|
Specifies the label that is used in a report model or a cube to display the singular name of items stored in the table.
| |||||
SupportInheritance
|
Setting this property to Yes enables you to set a value for other inheritance related properties such as Extends and Abstract.
For more information, see Table Inheritance Overview.
|
AX 2012
| ||||
SystemTable
|
Indicates if a table appears as a System table. It can then be filtered during export and import.
System tables are always synchronized when you log in. This may be useful for tables that you use as soon as you log in.
| |||||
TableContents
|
Specifies how setup/parameter data can be reused from one customer to another. The following values are possible:
| |||||
TableGroup
|
Determines which group the table belongs to.
Table Groups provide a method for categorizing tables according to the type of data they contain. Table groups can be used to define whether the system should prompt users when they update or delete from the table in forms by using the table as the data source. When exporting data, you can use table groups to filter records.
| |||||
TableType
|
Replaces the Temporary property found in Microsoft Dynamics AX 2009. For more information, see Temporary Tables and the TableType Property.
|
AX 2012
| ||||
TitleField1, TitleField2
|
Enables you to do the following:
| |||||
TypicalRowCount
|
Specifies the number of records that typically appear in a table.
If the AnalysisSelection property is not set, the TypicalRowCount property determines how records are selected by using Report Builder for Microsoft SQL Server Reporting Services.
The TypicalRowCount property setting affects whether a drop-down list, list box, or a filtered list box is used to select table records. For more information, see Best Practices for Table Properties.
| |||||
ValidTimeStateFieldType
|
Specifies the type of date-time field for the system to use when it tracks data within time spans.
|
AX 2012
| ||||
Visible
|
Specifies the access rights when the table is used as a data source in a form or a report.
If the table is used as a data source in a form, then the access rights in the form cannot exceed the access rights defined for the table.
|
7.AOT
Elements ?
In Microsoft Dynamics AX, the Application Object Tree (AOT) contains all of the definitions of elements that are used to build Microsoft Dynamics AX, such as classes, tables, forms, and so on. This topic provides an overview of the AOT and defines the top-level nodes.
To create a new element in the AOT, right-click the relevant node, and then click New. In addition, drag-and-drop operations are available for many elements.
All elements under the top-level nodes have:
- A shortcut menu. To open the shortcut menu, right-click an element. For more information, see Shortcut Menu Commands: AOT.
- Properties. To see the properties and property values of an element, right-click the element and then click Properties. The Properties sheet is displayed. For more information, see Application Object Properties.
The AOT contains the top-level nodes described in the following table.
Node
|
Description
|
|---|---|
Data Dictionary
|
Contains the data types and tables that make up the database. Also contains objects to control access to the data. It contains the following subnodes:
Tables: Tables that contain the Microsoft Dynamics AX data.
Maps: Enables you to create associations between closely related (but non-identical) table fields and methods.
Views: Enables you to join data from different tables, and then to select which fields you want to display.
Extended Data Types: Data types that extend one of the primitive data types or another extended data type.
Base Enums: Enumerable types that contain a list of literals.
License Codes: Determines which components of Microsoft Dynamics AX functionality are available to a company.
Configuration Keys: Allows administrators to enable or disable features in the application for all users.
Security Keys: Security keys are obsolete in Microsoft Dynamics AX 2012 and only exist to use for reference during a code upgrade. There is a new security framework, which is called role-based security. For more information on the new security framework, see Role-based Security in the AOT for Developers.
Table Collections: Collections of tables that contain data that is often shared between companies.
Perspectives: Collections of tables that were used to organize information for report models.
|
Macros
|
Contains the source code for the macros used by the standard application. In addition to viewing the existing code, you can add your own macros.
|
Classes
|
Contains the source code for the application (X++) classes.
You can also use system classes (also known as kernel classes). They are listed in the System Documentation\Classes node.
|
Forms
|
Dialog boxes in the user interface that are used to access the database.
|
Parts
|
Contains controls you can use to retrieve and show a collection of data. For more information, see Parts.
|
Data Sets
|
Provides a generic data access layer that allows for external presentation layers to bind to Microsoft Dynamics AX tables and data types. For more information, see Data Sets for Enterprise Portal.
|
SSRS Reports
|
Contains SQL Server Reporting Services reports that are included with Microsoft Dynamics AX.
|
Reports
|
Enables users to print or display summary information from the database.
|
Visual Studio Projects
|
Contains projects created in Visual Studio and added to Microsoft Dynamics AX by using Application Explorer. Project types that can be added to this node include Dynamics AX Model Projects, C Sharp Projects, Visual Basic Projects, Web Application Projects, and Analysis Services Projects. For more information, see Visual Studio Integration and How to: Add a Visual Studio Project to the AOT.
|
Report Libraries
|
Used to store Microsoft Dynamics AX 2009 SQL Server Reporting Services report libraries that are being upgraded for the Microsoft Dynamics AX 2012 AOT environment.
|
Queries
|
Used as the source of records for forms and reports.
|
Jobs
|
Typically holds small X++ programs that are used to test new code.
|
Menus
|
Contains the menus you want the end user to see.
|
Menu Items
|
Contains a complete list of the items that can be presented in a menu. Menu items act as a higher layer of abstraction for forms, reports, and so on.
|
Web
|
Contains objects related to Web development.
|
Services
|
Contains services that are exposed by Microsoft Dynamics AX.
|
Service Groups
|
Contains collections of services that are frequently consumed and managed together. All the services in a service group are published in a single WSDL file.
|
Workflow
|
Contains the workflow model elements used to create a workflow configuration. This node contains Categories, Tasks, Approvals, and Templates. For more information, see Implementing Workflow for Microsoft Dynamics AX.
|
Security
|
Contains the objects you use to implement application security, such as roles and permissions.
|
Resources
|
Contains references to image and animation files.
|
Label Files
|
Contains label files that store labels for all user interface elements. For more information, see Label Editor.
|
References
|
Contains references to Microsoft .NET assemblies and to external Web services. Both types of references can be used in X++ statements.
|
Help Documentation Sets
|
Specifies the documentation sets on the Help Server.
|
System Documentation
|
Contains items that represent system (kernel) classes, functions, tables, and so on.
|
8.
Layers &
their usage.
In Microsoft Dynamics AX, a layer system is used to manage elements. The USR layer is the top layer and the SYS layer is the bottom layer, and each layer has a corresponding patch layer above it.
Layers
The following table describes the application object layers in Microsoft Dynamics AX:
Layer
|
Description
| ||
|---|---|---|---|
USR
|
The user layer is for user modifications, such as reports.
| ||
CUS
|
The customer layer is for modifications that are specific to a company.
| ||
VAR
|
Value Added Resellers (VAR) can make modifications or new developments to the VAR layer as specified by the customers or as a strategy of creating an industry specific solution.
| ||
ISV
|
When an Independent Software Vendor (ISV) creates their own solution, their modifications are saved in the ISV layer.
| ||
SLN
|
The solution layer is used by distributors to implement vertical partner solutions.
| ||
FPK
|
The FPK layer is an application object patch layer reserved by Microsoft for future patching or other updates. For more information, see Patch Layers.
| ||
GLS
|
When the application is modified to match country or region specific legal demands, these modifications are saved in the GLS layer.
| ||
SYS
|
The standard application is implemented at the lowest level, the SYS layer. The application objects in the standard application can never be deleted.
|
Each layer has a corresponding patch layer that can be used to incorporate updates to your application or to store conflicts when you import models into a layer. For more information about patch layers, see Patch Layers.
9. OOPs
concepts?
Class : Class is the 1st OOPs concept .Class defines the
characteristics of objects which includes its attributes , fields properties and behavior . Let us say we have
a class called car , then the color , model number , top speed can be its
attributes and properties . Accelerating , breaking , turning will be its
behavior .
Objects: Objects can be considered as a thing that performs a set of related functions
.Programming objects are used to
model real worlds objects. An object is also an instant of a class . For our
class Car , Ferrari will be our object
Instance : One can have an instance of a class; the instance is the
actual object created at runtime. The
set of values of the attributes of a particular object is called its state. The
object consists of state and the behaviour that’s defined in the object’s
class.
Method
:Also called as functions in some
programming languages , methods defines the
behavior of particular objects . For our Car class , turning() ,
breaking () will be our methods .
Inheritance : a parent class can inherit its behavior and state to
children classes. This concept was developed to manage generalization and
specialization in OOP .Lets say we have a class called Car and Racing Car .
Then the attributes like engine no. , color of the Class car can be inherited
by the class Racing Car . The class Car will be Parent class , and the class
Racing Car will be the derived class or child class.
Abstraction : representing only the important details without
including all the details . For example the car Ferrari can be treated as
simple car only .
Encapsulation:The wrapping up of data and functions into a single
unit is called as encapsulation . For example the class car has a method turn
() .The code for the turn() defines how
the turn will occur . So we don’t need
to define how Mercedes will turn and how the Ferrari will turn
separately . turn() can be encapsulated with both.
Polymorphism: Its an important OOPs concept , Polymorphism means
taking more than one forms .Polymorphism allows the programmer to treat derived
class members just like their parent class’s members. More precisely,
Polymorphism in object-oriented programming is the ability of objects belonging
to different data types to respond to calls of methods of the same name .If a
Dog is commanded to speak(), this may elicit a bark(). However, if a Pig is
commanded to speak(), this may elicit an oink(). Each subclass overrides the
speak() method inherited from the parent class Animal.
10. Differences:
a.
MorphX & Intellimorph
MorphX is the Microsoft Dynamics
AX IDE( Integrated Development Environment) which includes:
- Data
Dictionary
- Tools
for creating menus, forms and reports for Windows- and Web clients
- Compiler
and debugger for the object oriented programming language X++
- Version
control system
- Label
(multi language text) systems
IntelliMorph
is the Runtime Environment embedded in Microsoft Dynamics AX, that draws menus,
forms, and reports for Windows- and Web-clients with the correct contents,
size, and layout according to:
- The
language your texts are displayed in.
- What
features you can access.
- How
wide you want the fields on your installation.
- The
formats you are using for dates and numbers.
b.
RunBase & RunBaseBatch
RunBase
class: The RunBase class is a framework for classes that need a dialog for user
interaction and that need the dialog values to be saved per user. The RunBase
application framework runs or batches an operation. An operation is a unit of
work, such as the posting of a sales order or calculation of a master schedule.The
RunBase framework uses
the Dialog framework to prompt a
user for data input. It uses the SysLastValue framework to persist usage data
and the Operation Progress framework to show operation progress.
class RunBase extends Object
implements SysSaveable, SysRunable
RunBaseBatch
class: All jobs that must be able to run in a batch must inherit from this
class. The RunBaseBatch framework extends the RunBase framework, and X++
classes that extend this framework can have their operations enlisted in the batch
queue.
class RunBaseBatch extends
RunBase implements Batchable
RunBaseReport class: The
RunBaseReport class makes all reports batchable and creates a standard dialog
box.
class RunBaseReport extends
RunBaseBatch
This class is instantiated
through the SysReportRun Class. It should be used by all reports. The purpose
of the class is to:
- Make
all reports batchable
- Create
a standard dialog
If you are creating more complex
reports, it might be necessary to inherit from this class. If this is the case,
you must create the following methods:
lastValueElementName(). Returns
the report name.
description(). Static. main(). Static.
c.
Element & this
"this" can be used in
any objects to reference the current object and member methods.
MorphX forms and reports are
composite objects.
In forms the collection of
objects is contained within a FormRun object. You can reference members in the
outer FormRun object by using the "element" reference.
If your code is placed at the top
level there are no functional difference between "this" and
"element".
If your code is placed in a
FormDataSource "this" will reference the datasource but
"element" will reference the "FormRun".
d.
COM & .NET Business Connector
.NET Business Connector provides
interoperability with the .NET Framework. This is enabled by providing Windows
Server SDK managed classes.
COM Business Connector provides
Microsoft COM interoperability. This is enabled by providing a Microsoft
COM-based interface. COM Business Connector is no longer going to be supported
in Ax 2012
c.
Concurrent user & Named user
Named User: specific individuals
are licensed
Concurrent user: you may have ‘N’
number but you can access only few use the Ax simultaneously.
d.
Primary key & Foreign key
Primary key is the unique key of
a table, for example ID.
Say, you have a student table,
the primary key is usually student id, as there won't be two students with the
same id.
As for foreign key, it's a key to
define the relationship between two tables.
Say, there is another table
called StudentEvent, which it contains a PersonInCharge field. In this
StudentEvent table, the primary key is EventID, where the PersonInCharge is
actually storing the StudentID, which it is the foreign key to the Student
table. This actually defines the relationship between Student table and
StudentEvent table.
e.
Construct & New methods
Method new() actually constructs
a class and finalize() destructs.
Method construct() is a best
practice to use when creating an instance. In this method you code how to
construct a specific class.
f.
Normal, field fixed & related field fixed relations
Lets say you have ClothesTable
and ClothesOrders.
ClothesTable has the following
fields: ClotheId, Name and CollectionTypeId
MenClothesOrder has the following
fields: OrderId, ClotheId, Qty OrderId
could be a number sequence and Qty entered manually bby the user.
CollectionTypeId has the
following elements:
0 - Men
1 - Women
2 -
Children
Example 1: Related Fixed Field
On MenClothesOrder we create a
new relation to ClothesTable and specify the follwing two:
1. Normal
= ClotheId to ClotheId (Best practice to specify this on the EDT) and
2. Related
Fixed Field 0 = ClothesTable.CollecTionTypeId.
This shows that the lookup to the
clothes table should show only clothes with the same ClotheId (point 1) AND
clothes that are of type Men (point 2) because the our table deals with order
for mens' clothes. We use 0 because Menis element 0 in the Enum.
Example 2: Fixed Field
This kinda works the other way
round:
Imagine you have a ClothesOrders
table (generic) and you have seperate tables for MenClothesTable,
WomenClothesTable and ChildrenClothesTable. Fixed field says that the specified
normal relation (on ClotheId) to MenClothesTable only works if the CollectionTypeId
of the current record is set to 0 (Men) else the relation is disabled.
c.
Table & View in AOT
Table : Relational Database is
composed of tables that contain related data.
View :
1. Views
are created from one or more than one table by joins, with selected
columns.
2. Views
are created to hide some columns from the user
3. Views
reduces the effort for writing queries to access specific columns every time.
4. View
doesn't contain any data.
d.
Auto design & Generated design in reports
Auto designs take full advantage
of MorphX, they allow for dynamic templates, auto headers and auto sums based
on criteria established in the query.
Generated designs are static, and
will not automatically adjust to changes made in the query or report template.
It is recommended using auto designs. You should only consider using generated
designs in special cases where a fixed layout is needed. Generated designs are
generally only required where the layout is fixed by contract or statute, or
when you need to use pre-printed forms such as checks and purchase orders.
Generated designs have some extra sections for adding headers and footers to
body sections. Beside that auto designs and generated designs use the same type
of sections.
e.
Business connector & External Connector
f.
VSS & TFS
Visual source safe for versioning
– VSS last version is 2005
TFS- Tem foundation server - versioning
g.
Refresh(),reread(),research() & executeQuery()-
1. Refresh
This method basically refreshes
the data displayed in the form controls with whatever is stored in the form
cache for that particular datasource record. Calling refresh() method will NOT
reread the record from the database. So if changes happened to the record in
another process, these will not be shown after executing refresh().
2.refreshEx
This method should be used
sparingly, in cases where multiple rows from the grid are updated, resulting in
changes in their display Options, as an example. So you should avoid using it
as a replacement for refresh(), since they actually have completely different
implementations in the kernel.
3. Reread
Calling reread() will query the
database and re-read the current record contents into the datasource form
cache. This will not display the changes on the form until a redraw of the grid
contents happens
4. Research
Calling research() will rerun the
existing form query against the database, therefore updating the list with
new/removed records as well as updating all existing rows. This will honor any
existing filters and sorting on the form, that were set by the user.
5. ExecuteQuery
Calling executeQuery() will also
rerun the query and update/add/delete the rows in the grid. The difference in
behavior from research is described below.
ExecuteQuery should be used if
you have modified the query in your code and need to refresh the form to
display the data based on the updated query.
c.
formDataSource.queryRun().query() &
formDataSource.query()-
An important thing to mention
here is that the form has 2 instances of the query object - one is the original
datasource query (stored in formDataSource.query()), and the other is the
currently used query with any user filters applied (stored in formDataSource.queryRun().query()).
When the research method is
called, a new instance of the queryRun is created, using the
formDataSource.queryRun().query() as the basis. Therefore, if the user has set
up some filters on the displayed data, those will be preserved.
This is useful, for example, when
multiple users work with a certain form, each user has his own filters set up
for displaying only relevant data, and rows get inserted into the underlying
table externally (for example, through AIF).
Calling executeQuery, on the
other hand, will use the original query as the basis, therefore removing any
user filters.
This is a distinction that
everyone should understand when using research/executeQuery methods in order to
prevent possible collisions with the user filters when updating the query.
11.Delete
actions & its types?
Types of delete action
a. Cascade
A cascading deletion action will
delete all records in the related table, where the foreign key is equivalent to
the primary key of the current table. That is, deleting the parent record will
also delete the child record(s) in the related table.
This cascading will take place whether the deletion is
performed in code or directly by a user through the user interface.
b.
Restricted
A restricting delete action will
raise an error message if the user tries to delete a record, where records
exist in the related table where the foreign key is equivalent to the primary
key of the current table.
This error will only appear if the deletion is performed
through the user interface. A deletion from X++ code will be allowed to proceed
and will not be cascaded to the related table. In this case the programmer
should call .validateDelete() themselves prior to the call to .delete()
c.
Cascade+Restricted
This delete action normally
works as a Restricted delete action. However if the deletion is performed
through X++ code, no error will be raised and the deletion will be cascaded to
the related table.
12.
Table groups
& its types?
Table groups provide a method for categorizing tables according to the type of data they contain. Determining group membership is not an exact science but more of a conceptual definition. When determining group membership for your own tables, follow the standards in the Microsoft Dynamics AX application.
When exporting data, you can use table groups to filter records. For example, if you wanted to specify that customers should be exported but customer transactions should not. The table group that a table belongs to is defined by the TableGroup property of the table.
The available table group values are listed in the following table.
Table group
|
Use this group for a table with these characteristics
|
Examples
|
|---|---|---|
Parameter
|
The table contains data primarily used as parameters or setup information for one of the main tables (a table that has aTableGroup property of Main).
The table typically contains only one record per company.
|
CustParameters,VendParameters
|
Group
|
The table contains data primarily used to categorize one of the main tables (a table that has a TableGroup property ofMain).
There is a one-to-many relationship between a Group table and a Main table.
|
CustGroup, VendGroup
|
Main
|
The table is one of the principal tables in the application and contains data for a central business object.
The table typically holds static, base information.
There is a one-to-many relationship between a Main table and a Transaction table.
|
CustTable, VendTable
|
Transaction
|
The table contains transaction data.
The table is typically not used for data entry directly.
|
CustTrans, VendTrans
|
WorksheetHeader
|
The table typically categorizes information in the WorkSheetLine tables.
There is a one-to-many relationship between a WorkSheetHeader table and a WorkSheetLine table.
|
SalesTable
|
WorksheetLine
|
The table contains information to be validated and made into transactions.
In comparison to the data contained in a Transaction table, the data in WorkSheetLine tables is temporary and may be deleted without affecting system stability.
|
SalesLine
|
Miscellaneous
|
The table does not fit in any of the other categories. This is the default value for a new table.
|
TableExpImpDef
|
Typically, the table groups Miscellaneous, Transaction, WorksheetHeader, and WorksheetLine are used for large tables. If you have checked the fields Use literals in complex joins from X++ or Use literals in join queries from forms and reports in the server configuration, then the kernel will add a forceliterals statement to the SQL query if two or more large tables have been joined.
The groups available are defined by the system enum TableGroup.
SalesTableType and
SalesLinetype. Insert() should be called for creating the sales order.
static void
createSalesTable(CustAccount _custAccount)
{
SalesTable salesTable;
NumberSeq NumberSeq;
;
NumberSeq =
NumberSeq::newGetNumFromCode(SalesParameters::numRefSalesId().numberSequence);
salesTable.SalesId = NumberSeq.num(); salesTable.initValue();
salesTable.CustAccount = _custAccount; salesTable.initFromCustTable();
salesTable.insert();
}
Example: Create a Sales Line
static void
createSalesLine(SalesId _salesId, ItemId _itemId)
{
SalesLine salesLine;
;
salesLine.clear();
salesLine.SalesId = _salesId; salesLine.ItemId = _itemId;
salesLine.createLine(NoYes::Yes,
// Validate
NoYes::Yes, //
initFromSalesTable
NoYes::Yes, //
initFromInventTable
NoYes::Yes, // calcInventQty
NoYes::Yes, // searchMarkup
NoYes::Yes); // searchPrice
}
//Code for posting Sales order Invoice static void
createSalesOrder(Args _args)
{
SalesFormLetter
formLetterObj; formLetterObj =
SalesFormLetter::construct(DocumentStatus::Invoice); formLetterObj.update(SalesTable::find(“SO-101248″));
}
Code to Create Purchase Order and Post the
Invoice:
Following Job creates the
Purchase order from code and post the invoice by making use of PurchFormLetter
class. If you don't have demo data ,
please test with your input values.
static void
Dev_CreatePO_and_Invoice(Args _args)
{
NumberSeq numberSeq;
Purchtable Purchtable;
PurchLine PurchLine;
PurchFormLetter purchFormLetter;
;
ttsbegin; numberSeq
=
NumberSeq::newGetNumFromCode(purchParameters::numRefPurchaseOrderId().NumberSequ
ence,true);
// Initialize Purchase order
values
Purchtable.initValue();
Purchtable.PurchId = numberSeq.num();
Purchtable.OrderAccount = '3000';
Purchtable.initFromVendTable();
if (!Purchtable.validateWrite())
{
throw Exception::Error;
}
Purchtable.insert();
// Initialize Purchase Line
items
PurchLine.PurchId = Purchtable.PurchId;
PurchLine.ItemId = 'B-R14';
PurchLine.createLine(true, true, true, true,
true, false); ttscommit;
purchFormLetter = purchFormLetter::construct(DocumentStatus::Invoice); purchFormLetter.update(purchtable, //
Purchase record Buffer
"Inv_"+purchTable.PurchId, //
Invoice Number systemdateget()); //
Transaction date
if
(PurchTable::find(purchTable.PurchId).DocumentStatus ==
DocumentStatus::Invoice)
{
info(strfmt("Posted invoiced journal for
purchase order %1",purchTable.PurchId));
}
}
Change the DocumentStatus to
packingSlip , if you want to post packing slip.
14.Flow
of SSRS report generation in Dynamics AX?
AX menu item from rich client->Report viewer opens->Issues request
to report server->
Report
server connects Dynamics AX(.NET BC)->Fetches the data->Return to client
15.
How
can we create primary key for a table?
This step below is to create primary key in a table in AX 2012. It can consist of single field.
1) Create the Table and add required fields to the table as you all knows.
2) Create an Index by dragging the required field to the Index.
3) Set the following Index properties:
a) AllowDuplicates to "NO".
b) Alternate key to "YES".
4) Set the PrimaryIndex property of the table to newly created index after creating the Index.
2) Create an Index by dragging the required field to the Index.
3) Set the following Index properties:
a) AllowDuplicates to "NO".
b) Alternate key to "YES".
4) Set the PrimaryIndex property of the table to newly created index after creating the Index.
This step below is to create primary key in a table in AX 2012. It can consist of single field.
1) Create the Table and add required fields to the table as you all knows.
2) Create an Index by dragging the required field to the Index.
3) Set the following Index properties:
a) AllowDuplicates to "NO".
b) Alternate key to "YES".
4) Set the PrimaryIndex property of the table to newly created index after creating the Index.
2) Create an Index by dragging the required field to the Index.
3) Set the following Index properties:
a) AllowDuplicates to "NO".
b) Alternate key to "YES".
4) Set the PrimaryIndex property of the table to newly created index after creating the Index.
16.
What
precautions you need for overriding fetch() method for a report?
| Note |
|---|
Do not call super() when you override the fetch method in a report.
|
By default, each record that is returned by the query appears in the report. To reduce the number of records returned, add range restrictions to the query. Report ranges are more efficient than overriding the fetch method; however, report ranges are less expressive. For information about adding ranges to queries, see Query Elements in the AOT.
Example
The following code example loops through each record that is returned by the query. The code tests a field in each record and branches to a send method call for records that belong in the report.
In this example, the BankAccountTable table is the only data source for the report. The fetch method in the report is overridden with the following code.
public boolean fetch()
{
boolean retCode = false;
BankAccountTable bankAccountTableRec;
QueryRun qrun;
;
// Use the queryRun object that is associated with the
// report; element refers to the report.
qrun = new QueryRun(element);
// Verify that the report dialog works.
if (! qrun.prompt())
{
return retCode;
}
// Loop through each record from the data source query of the report.
while (qrun.next())
{
// Get the BankAccountTable fields from the query record.
bankAccountTableRec = qrun.get(TableNum(BankAccountTable));
// Exclude ODDBANK from the visible report.
if (bankAccountTableRec.AccountID != "ODDBANK")
{
// Include the current record in the report.
element.send(bankAccountTableRec);
}
}
retCode = true;
// retCode = super(); // Do not call super() when you override the fetch method.
return retCode;
}
17.
Difference between OCC (Optimistic
concurrency control) and PCC (Pessimistic concurrency control)?
The locking of records is necessary to ensure transactions are processed accurately and with a high level of concurrency. Unfortunately the more records are locked the higher is the chance other transactoins are getting blocked resulting in peformance reductions on the one hand and end user frustration on the other hand.
Dynamics AX and Microsoft SQL Server have built in functionalities that help identfiying and reducing locking and their effect the blocking. However not all of the features are self-explaining so I thought it is a good idea to give a little overview on some of them.
Concurrency models: OCC vs PCC in Queries
The concurrency model in Dynamics AX controls how records are locked when a select forupdate is executed.
| Pesimistic Concurrency Control (PCC) | Optimistic Concurrency Control (OCC) |
| Dynamics AX 3.0 supports only the pessimistic concurreny model in that a select forupdate results in a SQL statement which aquires an update lock. This will cause all selected rows to be readable but you can't update them or aquire any new update lock anymore. The update lock is held until the transaction is commit. | Dynamics AX 4.0 supports and primarily uses theoptimistic concurrency model in that a select forupdateresults in a SQL statement with no lock. Here all selected rows can be read and updateded besides the rows that were updated already. In order to detect an update conflicts the RecVersion field is checked by the Dynamics AX Kernel. |
while select forupdate custTable
// SELECT ... WITH (UPDLOCK) |
while select forupdate custTable
// SELECT ... WITH (NOLOCK) |
In both concurrency models the exclusive locking time is the same. The below table visualizes this.
The combination of the olive bars and teal bars below reprsent the selected dataset, every record that is retunred by your select forupate call. The teal bars are the updated, exclusively locked rows, the olive bars are the so far not updated rows. Over the time the amount of updated rows increases until at the end all rows are updated and the transaction is committed.
| All rows processed | ||||||
| 5/6 rows processed | ||||||
| 2/3 rows processed | ||||||
| 1/2 rows processed | ||||||
| 1/3 rows processed | ||||||
| 1/6 rows processed | ||||||
When using OCC the positive effect is obvious: There are much more rows available for updating (the olive bars). When we would have PCC the olive bars would not be available for updating, or in the words another process / tranaction would be blocked until all rows are processed. So when using OCC the chances for blocking is much smaller.
For more information see Optimistic Concurrency Control.
Read Committed Snapshot Isolation (RCSI)
When a record is exclusively locked also the reading is not possible (blocked) which can have a negative impact on the end user experience. Therefore it is recommended to enable the Read Committed Snapshot Isolation (RCSI)for the Dynamics AX database. RCSI is available on Microsoft SQL Server 2005 and higher. RCSI can help reducing locking and blocking also with Dynamics AX 3.0.
If you are not sure if RCSI is already enabled you can execute the following SQL query command to check (1 means on, 0 means off):
select name, is_read_committed_snapshot_on from sys.databases
For more information about and including how to enable RCSI see Enabling Row Versioning-Based Isolation Levels
RCSI is creating a version store in the TempDB to allow the reader to read from the version store instead of the exclusively locked row. As soon as you activate RCSI for performance reasons you should be sure to have the TempDB stored physically on your own dedicated disk. Please also make sure you have as many TempDB files as physical CPU cores exists on the SQL Server, to prevent contention within the TempDB.
For more information about the TempDB see Working with tempdb in SQL Server 2005.
Preventing locking and blocking and their implications on the transaction log
When looking at the following examples please remember one thing: For every ttscommit the Microsoft SQL Server transaction log is written to!
Not every write is an immediate physical write but definitely a logical write. The transaction log writer of Micosoft SQL Server is writing sequentially, this means it cannot be parallelized in any way. So the less transactions are opened the faster the changes are processed in general, but the longer the exclusive locking time will be.
Example 1: Longest exclusive locking / blocking time but fasted execution
The fast execution is because you are only writing one time to the transaction log of Microsoft SQL Server. At the same time you work in a very save way as you fully align with the concurrency model you are using.
static void UpdateCreditMax(Args _args)
{
CustTable custTable;
;
ttsbegin;
while select forupdate custTable where custTable.creditMax == 0
{
if (custTable.balanceMST() < 10000)
{
custTable.creditMax = 50000; custTable.update();
}
}
ttscommit;
}
{
CustTable custTable;
;
ttsbegin;
while select forupdate custTable where custTable.creditMax == 0
{
if (custTable.balanceMST() < 10000)
{
custTable.creditMax = 50000; custTable.update();
}
}
ttscommit;
}
Again please be aware that only this example has a low transactional workload as there is only one write to the transaction log file happening!
Example 2: Most roundtrips, short locking time
This example is (was) mainly usefull on Dynamcis AX 3.0 and causes a transaction overhead. This example makes not very much sense on Dynamics AX 4.0 or higher due to the changes in the concurrency model.
static void UpdateCreditMax(Args _args)
{
CustTable custTable;
CustTable updateableCustTable;
;
while select custTable where custTable .creditMax == 0
{
if (custTable.balanceMST() < 10000)
{
ttsbegin;
select forupdate updateableCustTable where updateableCustTable.AccountNum == custTable.AccountNum;
updateableCustTable.creditMax = 50000;
updateableCustTable.update();
ttscommit;
}
}
}
{
CustTable custTable;
CustTable updateableCustTable;
;
while select custTable where custTable .creditMax == 0
{
if (custTable.balanceMST() < 10000)
{
ttsbegin;
select forupdate updateableCustTable where updateableCustTable.AccountNum == custTable.AccountNum;
updateableCustTable.creditMax = 50000;
updateableCustTable.update();
ttscommit;
}
}
}
Example 3: Mix between Example 1 and Example 2
This example is mainly useful on Dynamics AX 4.0 and higher but causes an transaction overhead. In the select statemet below you could replace optimisticlock also with forupdate, but in this case you would not enforceoptimistic concurrency.
static void UpdateCreditMax(Args _args)
{
CustTable custTable;
;
while select optimisticlock custTable where custTable.creditMax == 0
{
if (custTable.balanceMST() < 10000)
{
ttsbegin;
custTable.creditMax = 50000;
custTable.update();
ttscommit;
}
}
}
{
CustTable custTable;
;
while select optimisticlock custTable where custTable.creditMax == 0
{
if (custTable.balanceMST() < 10000)
{
ttsbegin;
custTable.creditMax = 50000;
custTable.update();
ttscommit;
}
}
}
Some comments on Example 2 and Example 3
Both examples are very effective in regards to locking and blocking. Overall these examples will perform more slowly than the Example 1 and there is also the chance of running into a last writer wins scenario. Also you cannot use them if you need all of the updates to be done or cancelled as a single transaction.
You should evaluate these examples only if you think that locking / blocking is an issue for you and if you are sure that you can accept the risks of partly bypassing your concurrency model.
When you are using this approach and run into performance issues on your Microsoft SQL Server you should have a look at the DMV SYS.DM_OS_WAIT_STATS. More precise look for the value WRITELOG.
select * from sys.dm_os_wait_stats order by wait_time_ms desc
If WRITELOG is very high compared to the other wait stats you should reduce code following Example 2 and 3 as much as possilbe and replace it with the code from Example 1.
In general the mentioned DMV can also give you a good overview if you are suffering from a transactional overhead or if you have an issue on the drive where the log file resides.
For more information see sys.dm_os_wait_stats.
Update_recordset is a very effective way to update multiple rows at once
The examples in the last section were about based update operations which have been very common in Dynamics AX 3.0. With Dynamics AX 4.0 you should use however set based update operations where ever possible. This is especially effective as Microsft SQL Server is basically working set based and not line based.
There are a lot less roundtrips between the Dynamics AX Kernel and the database if you are using theupdate_recordset command for updating multiple rows instead of the while select forupdate X++ equivalent.
If you want to know more about set based update operations see update_recordset. And for a general overview how to speed up certain DML operations within X++ code please have a look at Speeding up SQL Operations.
18. How many types of MAP there in Dynamics
AX?
There are two types of Maps available in dynamics AX
1. X++ Maps: it can be used as a temp data store for the given scope of a process. This takes us less over head, and is much quicker than a TempTable. For Further reading
2. AOT Maps: A map can unify the access to similar columns and methods that are present in multiple tables. You associate a map field with a field in one or more tables. This enables you to use the same field name to access fields with different names in different tables. Methods on maps enable you to create or modify methods that act on the table fields that the map references.
19.What is cache lookup what is it used for?
Cache Location
Caches are used on both the client and the server. The Microsoft Dynamics AX runtime manages the cache by removing old records when new records are added to the cache.
Client Cache
A client-side cache can be used only by the client. The client cache is used when a select is executed from the client tier. If no record is found in the client cache, the client then searches the server cache for the record. If the record isn't located in the server cache, it's retrieved from the database. The maximum number of records maintained in a client cache is 100 records per table for a given company.
Server Cache
A server-side cache can be used by any connection to the server. The server cache is used when a selectis executed on the server tier. If no record is found in the cache, it's retrieved from the database. The maximum number of records maintained in a server cache is 2,000 records per table for a given company.
Record Caching
Microsoft Dynamics AX database record caching is a performance-enhancing feature that helps avoid database access when it's not strictly necessary. Retrieving database records from memory instead of the database significantly speeds up data access. Caching can reduce the performance penalty for repetitively accessing the same database records.
Types of Caching
Caching is transparent to the application; however, it's important to know how caching works to optimize its performance in Microsoft Dynamics AX. Following are the types of caching:
- Single-record
- Set-based
Single-record caching has the following characteristics:
- Defined at design time
- Moves records to the cache based on the table's CacheLookup property and the type ofSELECT statement that is used to retrieve the record
Set-based caching has the following characteristics:
- Defined either at design time or in X++ code
- Moves sets of records to the cache
- Implemented either through the table's CacheLookup property or in code by using theRecordViewCache class
Single-Record Caching
Record caching is enabled for a table when all the following statements are true:
- The CacheLookup property on the table is enabled by setting it to one of the following values:
· notInTTS
· Found
· FoundAndEmpty
- The table's PrimaryIndex property is set to a unique index that exists on the table. The RecId index does not qualify as a caching index unless you set the table'sPrimaryIndex property to this index.
- The record buffer disableCache method has not been called with a parameter of true.
The fields in the table's unique index make up the caching key. A record is placed in the cache when the following criteria are met:
- The table is cached by setting the CacheLookup property to notInTTS, Found, or FoundAndEmpty.
- The SELECT statement that selects the records uses an equal operator (==) on the caching key. The fields in the WHERE clause of the SELECT statement match the fields in the index referenced by the table's PrimaryIndex property.
The table's CacheLookup property defines how and when records are cached as shown in the following table.
CacheLookup Property Value
|
Result
|
None
|
No data is cached or retrieved from the cache for this table.
This property value should be used for tables that are heavily updated or where it's unacceptable to read outdated data.
|
NotInTTS
|
All successful caching key selects are cached.
When in a transaction (after ttsBegin), no caches made outside the transaction are used. When inside a transaction, the record is read once from database and subsequently from cache. The record is select-locked when read in a transaction, which ensures that the record cached is not updated while the transaction is active.
A typical example of the NotInTTS property is the CustTable in the Microsoft Dynamics AX standard application. It's acceptable to read outdated data from the cache outside a transaction, but when data is used for validation or creating references, it is ensured that the data is real-time.
|
Found
|
All successful caching key selects are cached. All caching key selects are returned from the cache if the record exists there. A selectforUpdate in a transaction forces reading from the database and replaces the record in the cache.
This is typically used for static (lookup) tables, such as Unit, where the record usually exists.
|
FoundAndEmpty
|
All selects on caching keys are cached, including selects that are not returning data.
All caching key selects are returned from caching if the record exists there, or the record is marked as nonexistent in the cache. A selectforUpdate in a transaction forces reading from the database and replaces the record in the cache.
An example of FoundAndEmpty record caching is in the Discount table in the Microsoft Dynamics AX standard application. By default, the Discount table has no records. By using a FoundAndEmpty cache on this table, the keys that are queried for but not found are stored in the cache. Subsequent queries for these same non-existent records can be answered from the cache without a round trip to the database.
|
EntireTable
|
Creates a set-based cache on the server. The entire table is cached as soon as at least one record is selected from the table.
|
The Found and FoundAndEmpty caches cross transaction boundaries. The NotInTTS cache is newly created inside a transaction. This example, modified for the purposes of this topic, demonstrates how records are retrieved from the cache when the table's CacheLookup property is set to NotInTTS, and thePrimaryIndex property is set to a unique index on the AccountNum field.
static void NotInTTSCache(Args _args)
{
CustTable custTable;
;
// The query looks for records in the cache.
// If records don't exist, the query accesses the database.
select custTable
where custTable.AccountNum == '4000';
// The transaction starts.
ttsbegin;
// The cache is not used. The query accesses the database
// and records are placed in the cache.
select custTable
where custTable.AccountNum == '4000';
// The query uses the database because
// the forupdate keyword is used.
select forupdate custTable
where custTable.AccountNum == '4000';
// The query uses the cache and not the database.
select custTable
where custTable.AccountNum == '4000';
// The query uses the cache because
// the forupdate keyword was used previously.
select forupdate custTable
where custTable.AccountNum == '4000';
// The transaction is committed.
ttscommit;
// The query will use the cache.
select custTable
where custTable.AccountNum == '4000';
}
If the table CacheLookup property was set to Found or FoundAndEmpty, the first select statement inside the transaction (after the TTSBegin statement) would retrieve the record from the cache.
Set-Based Caching
In Microsoft Dynamics AX, groups of records can be cached all at once with set-based caching. Set-based caching can be implemented in two ways:
- At design time, by setting the table's CacheLookup property to EntireTable.
- In code, by using the RecordViewCache class.
EntireTable Cache
When you set a table's CacheLookup property to EntireTable, all the records in the table are placed in the cache after the first select. This type of caching follows the rules of single record caching in which theSELECT statement WHERE clause fields must match those of the unique index defined in the table'sPrimaryIndex property.
The EntireTable cache is located on the server and is shared by all connections to the Application Object Server (AOS). If a select is made on the client tier to a table that is EntireTable cached, it first looks in its own cache and then searches the server-side EntireTable cache. An EntireTable cache is created for each table for a given company. If you have two selects on the same table for different companies the entire table is cached twice.
Joins that include an EntireTable cached table are only performed against the cached copy when all tables participating in the join are EntireTable cached. Otherwise a database join is performed.
Important Note:
Avoid using EntireTable caches for large tables because once the cache size reaches 128 KB the cache is moved from memory to disk. A disk search is much slower than an in-memory search.
Flushing the Cache
An EntireTable cache is flushed whenever an insert, update, or delete is made to the table. At the same time, the AOS notifies other AOSs that their caches of the same table must be flushed. After the cache is flushed, a subsequent select on the table causes the entire table to be cached again. Therefore, avoid caching any table that's frequently updated. Regardless of when updates are made, EntireTable caches are flushed every 24 hours by the AOS.
RecordViewCache Cache
Set-based caching is implemented in code by using the RecordViewCache class. You must first create a record buffer using the nofetch statement and then pass the record buffer to the RecordViewCacheclass when it's instantiated.
The cache is created on the server and is only accessible by the process that creates the cache object. Once the cache is instantiated, all select statements are issued against the cache, as shown in the following
static void RecordViewCache(Args _args)
{
CustTrans custTrans;
RecordViewCache recordViewCache;
;
// Define records to cache.
select nofetch custTrans
where custTrans.AccountNum == '4000';
// Cache the records.
recordViewCache = new RecordViewCache(custTrans);
// Use cache.
select firstonly custTrans
where custTrans.AccountNum == '4000' &&
custTrans.CurrencyCode == 'USD';
}
Due to concurrency issues, the forUpdate keyword on the instantiating X++ SELECT statement should only be used when all of the records in the result set will be updated. Otherwise it's a better strategy to use select forUpdate only for the records that are to be updated.
The RecordViewCache class is used in a select when the select is from a table that's cached, the select statement doesn't participate in a join and the select WHERE clause matches the WHERE clause with which the RecordViewCache was instantiated.
The cache created by the RecordViewCache class stores records in a linked list. Therefore Microsoft Dynamics AX searches the cache sequentially for records that match the search criteria. If the SELECTstatement contains an ORDER BY clause, a temporary index is placed on the cache and the runtime uses the index when searching records.
20.
Difference between table and views?
Difference between a view and a base relation: -Views:1. This is one type of relation which is not a part of the physical database.2. It has no direct or physical relation with the database.3. Views can be used to provide security mechanism.4. Modification through a view (e.g. insert, update, delete) generally not permittedBase Relation:1. A base relation is a relation that is not a derived relation.2. While it can manipulate the conceptual or physical relations stored in the data.3. It does not provide security.4. Modification may be done with a base relation.We can assign the view, a name & relate it the query expression as Create View as Let EMPLOYEE be the relation. We create the table EMPLOYEE as follows:-Create table EMPLOYEE (Emp_No integer of null, Name char (20), Skill chars (20), Sal_Rate decimal (10, 2), DOB date, Address char (100),)For a very personal or confidential matter, every user is not permitted to see the Sal_Rate of an EMPLOYEE. For such users, DBA can create a view, for example, EMP_VIEW defined as:-Create view EMP_VIEW as (Select Emp_No, Name, Skill, DOB, Address From EMPLOYEE)
21.
Why we use dialog? And how to
accomplished it?
A dialog in Microsoft Dynamics AX is a simple form with a standardized layout, created by using the Dialog system class.
Dialogs should allow users to enter some simple values. Do not create complex dialogs—create forms instead.
Dialogs are non-modal. The dialog should not open a secondary window that requires user interaction. All user interaction should be carried out within the dialog.
Dialog Methods
Following are some of the most commonly used methods in the Dialog class:
- Use the addField method to add fields to the dialog. The addField method returns objects of the DialogField type.
- Use the addGroup method to group related fields.
- Use the addTabPage method to add tabbed pages.
- Use the value method to set and get values in the dialog.
- Use the run method to launch the dialog. When the user clicks OK or Cancel, the run method returns true or false, respectively.
Dialog Classes
Dialog is the main class used to construct dialogs. DialogRunBase is an extension of the Dialog class that is used by the RunBase framework. The DialogControl class defines a single control in the dialog. DialogControl is extended by classes for the following control types:
- DialogField
- DialogGroup
- DialogTabPage
- DialogText
- DialogWindow
Example
The following code sample implements the dialog shown in the previous figure.
boolean myDialog(str FromChequeNum="1000", str NumOfCheque="300")
{
Dialog dialog = new Dialog("@SYS23133");
DialogField dialogAccountId = dialog.addField(typeid(BankAccount));
DialogField dialogFromChequeNum = dialog.addField(
typeid(BankChequeStartNum),
"@SYS4083");
DialogField dialogNumOfCheque = dialog.addField(
typeid(BankChequeQty),
"@SYS14578");
;
dialogAccountId.Value("456");
dialogAccountId.Active(false);
dialogFromChequeNum.Value(FromChequeNum);
dialogNumOfCheque.Value(NumOfCheque);
if (dialog.run())
{
FromChequeNum = dialogFromChequeNum.Value();
NumOfCheque = dialogNumOfCheque.Value();
return true;
}
return false;
}
22. What are the different type of index?
An index is a table-specific database structure that speeds the retrieval of rows from a table. Indexes are used to improve the performance of data retrieval and occasionally to ensure the existence of unique records. It's up to the database-specific query optimizer to use available indexes to facilitate efficient data retrieval.
Indexes are associated with a single table and located in the Application Object Tree (AOT) under the Data Dictionary\Tables node underneath the specific table.
Like most database objects in Microsoft Dynamics AX, indexes are synchronized with the database. Once created, indexes are managed automatically by the DBMS every time a record is inserted, updated, or deleted. Within Microsoft Dynamics AX, an index can be enabled or disabled. When an index is disabled, it's deleted from the database and rebuilt if it's enabled later. For more information about creating an index, see How to: Create an Index.
An index is defined by one or more fields. The system attempts to order the index according to the first field, and if there is more than one record with the same value in this field, the sorting conflict is resolved by looking at the next field and so on. When selecting table fields for an index consider the following:
- Fields that are often searched by a range.
- Fields that frequently participate in joins.
- Fields that are frequently used to order or group a result set.
In theory, a table can have an unlimited number of indexes. However, it's common to have at most a few indexes enabled because every insert, update, and delete causes each index to be updated and can affect performance.
Unique and Non-Unique Indexes
There are two types of indexes: unique and non-unique. Whether an index is unique is defined by the index's AllowDuplicates property. When this property is set to No, a unique index is created. The database uses the unique index to ensure that no duplicate key values occur. The database prevents you from inserting records with duplicate key values by rejecting the insert
Setting the index's AllowDuplicates property to Yes creates a non-unique index. These indexes allow you to enter duplicate values for the indexed fields and are used for performance reasons.
| Note |
|---|
A field of data type memo or container cannot be used in an index.
|
System Index
Microsoft Dynamics AX requires a unique index on each table so if there are no indexes on a table or all the indexes are disabled, a system index is automatically created. The system index is created on the RecId and DataAreaId fields if the DataAreaId field exists. Otherwise the system index is created on the RecId field. You can see system indexes in the database but they aren't visible in the AOT.
If there are indexes on a table but none of them are unique, the runtime estimates the average key length of the existing indexes, chooses the index with the smallest key length and appends the RecId column to create a unique index.
23.Difference b/w cascade + restricted
and restricted delete actions?
The DeleteAction element helps maintain database consistency when a record is deleted. Define delete actions to specify what should occur when data being deleted in the current table is related to data in another table.
For example, use a cascading delete action to specify that the system is to delete a customer's address when that customer is deleted from the CustTable table. Another example is to use a restricted delete action to prevent a customer from being deleted from the CustTable if one or more transactions exist for the customer in the CustTrans table.
Use the following best practices.
- Have a delete action on every relation between two tables.
- Use table delete actions instead of writing code to specify whether deletes are restricted or cascaded.Delete actions for groups usually don’t exist except when two groups are related. If there are delete actions on a table and DELETE_FROM is in use, performance will be slow.
Add a Delete Action
- In the Application Object Tree (AOT), expand the Data Dictionary.
- Expand Tables, and then locate the table that you want to add a delete action to.
- Click the table, right-click DeleteActions, and then click New DeleteAction.
- Right-click the new delete action, and then click Properties.
- Select a related table from the Table property list.
- Set the DeleteAction property. The following table describes the available values.
Delete Action
|
Description
|
Comments
|
|---|---|---|
None
|
Delete action disabled
| |
Cascade
|
Deletes related records.
|
Setting the DeleteAction property to Cascade extends the functionality of the table's delete method. As a result,super(), in delete, initiates a cascaded deletion, propagating the delete from table to table.
A cascaded delete is implicitly protected by tts. Database changes aren't committed until the entire transaction is complete.
Example
On the CustTable table, a cascading delete action has been defined for the CustBankAccount table. When a customer is deleted from the CustTable table, the delete method also ensures that the corresponding bank account information is automatically deleted.
|
Restricted
|
Restricts deletion in the current table if data is present in related tables.
|
Setting the DeleteAction property to Restricted extends the functionality of the table's validateDelete method.
As a result, super(), in validateDelete, checks whether records exist on related tables. If records do exist,validateDelete returns false. The forms system ensures that the deletion is not performed. In your own X++ code, check the return value of validateDelete. Don't delete the primary or related records if the method returns false.
Example
On the CustTable table, a restricted delete action has been defined for the CustTrans table. When a customer is deleted in the CustTable table, the validateDelete method ascertains whether transactions exist for the customer in the CustTrans table. If so, validateDelete returns false.
|
Cascade+Restricted
|
Cascade the delete, even though records exist on related tables.
|
Setting the DeleteAction property to Cascade+Restricted extends the functionality of the table's validateDelete anddelete methods.
As a result, super(), in validateDelete, ascertains whether records exist on related tables. Whether deleting records from forms or X++, if validateDelete returns false, the primary record isn't deleted and the cascading delete isn't performed. You should first delete the records in the related table before deleting the primary record.
If the primary record is being deleted as part of a cascading delete, the primary record and the records in the related table will be deleted.
Example
The Cascade+Restricted delete action is used in the standard application for LedgerJournalTrans on LedgerJournalTable.
This type of delete action is useful when you prefer a total clean-up—when you delete a customer, you also delete all the transactions associated with that customer.
|
24.
In which case delete_from and delete()
have same result?
Delete() will
delete one record at a time.
Delete_from
can delete multiple records at a time.
25.Explain sales/purchase order processes in AX.?
http://www.dynamicsaxtraining.com/dynamics-ax-trade-and-logistics-training/create-purchase-order
http://www.dynamicsaxtraining.com/dynamics-ax-trade-and-logistics-training/create-sales-order
26.Table properties?
Tables are the foundation objects in Microsoft Dynamics AX and store data used by the system. A table is made up of records (or rows) that contain information about a single entry in the table. For example, a specific customer or product. A record consists of one or more fields (or columns) that contain a discrete piece of data of a specific data type.
In Microsoft Dynamics AX, tables are located in the Application Object Tree (AOT) under the Data Dictionary\Tables node. Each table contains the following primary elements:
- Fields
- Field Groups
- Indexes
- Relations
- DeleteActions
- Methods
A table name can contain letters and numbers but must begin with a letter. Spaces and special characters are not allowed. For more information about adding tables, see How to: Create Tables.
Fields
The Fields node contains all the fields in the table. By specifying a field's data type, you define the type of data that can be stored in it. Microsoft Dynamics AX performs data validation to ensure that only valid data is entered into each field in the table. Constraints are also added to the database to set default field values if a field is left blank.
Each field in a table has a number of properties that describe the behavior of the field. The Type property contains the native data type of the field. The ExtendedDataTypeproperty contains the extended data type value (if the field is based on an extended data type). For more information about field properties, see Table Field Properties.
System Fields
When a new table is created, system fields are automatically added to each table. These fields are in the database table but aren't visible in the AOT Fields node. The system fields that are appended to a table depend on the value of particular table properties as shown in the following table.
System field
|
Table property
|
|---|---|
RecId
|
Always
|
RecVersion
|
Always
|
DataAreaId
|
SaveDataPerCompany = Yes
|
CreatedBy
|
CreatedBy = Yes
|
CreatedDate
|
CreatedDate = Yes
|
CreatedTime
|
CreatedTime = Yes
|
CreateTransactionId
|
CreateTransactionId = Yes
|
ModifiedBy
|
ModifiedBy = Yes
|
ModifiedDate
|
ModifiedDate = Yes
|
ModifiedTime
|
ModifiedTime = Yes
|
ModifiedTransactionId
|
ModifiedTransactionId = Yes
|
Field Groups
Field groups are objects that group together fields that logically belong together. For more information about field groups, see Best Practices for Field Groups, Defining Field Groups,and How to: Create a Field Group.
Indexes
An index is a table-specific database structure that speeds the retrieval of rows from the table. Indexes are used to improve the performance of data retrieval and sometimes to ensure the existence of unique records. It's up to the database-specific query optimizer to use available indexes to facilitate efficient data retrieval.
The AllowDuplicates property on the index has the biggest impact on how the index is used. When set to No, the system creates a unique index on the specified fields in the database. Otherwise, a non-unique index is created. For more information about indexes and keys, see Indexes Overview.
System Index
Microsoft Dynamics AX requires a unique index on each table. If there are no indexes on a table or all the indexes are disabled, a system index is automatically created. The system index is created on the RecId and DataAreaId fields if the DataAreaId field exists. Otherwise, the system index is created on the RecId field. You can see system indexes in the database, but they aren't visible in the AOT.
If there are indexes on a table but none of them are unique, the runtime estimates the average key length of the existing indexes, selects the index with the smallest key length, and then appends the RecId column to create a unique index.
Relations
Relations define the relationship between two tables that contain related data. Table relations are used to enforce referential integrity among other functions.
Table relations are most commonly used in form fields to enable the look-up of information in another table. If a table relation exists, the Lookup button can be used to display a lookup list of values for a particular field. For more information about table relations, see Defining Table Relations.
DeleteActions
The DeleteAction element is used to maintain database consistency when a record is deleted. Define delete actions to specify what should occur when data being deleted in the current table is related to data in another table. The delete action values are None, Cascade, Restricted, and Cascade + Restricted. For more information about delete actions, see Maintaining Data Integrity and How to: Create Delete Actions.
Methods
The Methods node displays all the methods available from a table. Use this node to add a new method, or override methods on the Table kernel class and add your own code.
Add a New Method
- Browse to the Data Dictionary, Tables node in the AOT.
- Expand the table, right-click the Methods node, and then select New Method.
- Enter your code in the Editor window and save your changes.
Override a Method
- Browse to the Data Dictionary, Tables node in the AOT.
- Expand the table, right-click the Methods node, and then select Override Method.
- Select the method that you want to override.
- Enter your code in the Editor window, and then save your changes.
Methods that have been overridden display an icon with an arrow.
For more information about using methods in your tables, see Table Methods.
27.
Different types of relation? Explain it detail?
http://msdn.microsoft.com/en-us/library/aa556809.aspx
28.
Explain Queries? What’s it used for?
http://msdn.microsoft.com/en-us/library/bb314753.aspx
http://msdn.microsoft.com/en-us/library/aa638454.aspx
29.
Explain different types of reports? There are two
types of reports in AX?
http://msdn.microsoft.com/en-us/library/cc553120.aspx
http://msdn.microsoft.com/en-us/library/cc553120.aspx
30.Differentiate auto design spec & Generated design?
Which one is a preferable choice and Why?
http://msdn.microsoft.com/en-us/library/cc967418.aspx
