Data dictionary and Tables (in Dynamics AX)

Data Dictionary can be defined as the data model for the Axapta application.
The data in an Axapta installation is stored in a relational database. In short, this means that data is separated in tables to prevent redundant data occurring and relations are defined between the tables making it possible to gain access to the related data.

• An Axapta installation uses either a Microsoft SQL Server or an Oracle database for storing data.
• Tables, views, fields and indexes are synchronized to the database when created, changed or deleted from Axapta. This definition of the data, is the only information about the data dictionary stored in the database, the actual data is physically stored within the Microsoft SQL Server or Oracle database

Tip:

Whenever we face any synchronization problems, the primary effective solution could be Running a Check/Synchronize in the SQL tool located in the main menu under Administration | Periodic | SQL Administration > Table actions -> Check/Synchronize button will be useful.

 

There are three main categories of Tables:

1.       Application Tables

2.      System Tables

3.      Temporary Tables

 

Application Tables:

• These are user tables and are used to define and build the application modules.
• We can add a new field either to an Existing Table or to a New Table.
• If we add a new field to an existing table, then the new field is adding in the existing layer even though the modified table belongs to some other layer. (goes for all objects on a Table, except the delete actions) This is really an advantage while upgrading an application to a new release, because, we will only have to manually merge nodes modified in more than one layer.

 

System Tables:

§  As the name suggests, these tables are created in the Kernel of the application. They contain information specific to operation of Axapta infrastructure.

• System tables are non-changeable. You cannot modify the data model of a system table.
• System tables can be found in the AOT under the node SystemDocumentation/Table.
• Their purpose is to handle tasks such as keeping the database in sync with the application,  handling licensing and user information     

Note: The system table SysLastValue stores usage data. This table is frequently used in the application as it stores the last user settings for an object. But we might not see SysLastValue declared from code, as the table is wrapped in the runbase framework or used by the class xSysLastValue.

 

 

Temporary Tables:

• The table property Temporary determines whether a table is temporary or not. And any application table can be set to temporary by setting the table property Temporary to Yes.
  Caution: Setting a table containing data to temporary will cause existing data to be deleted.
• A temporary table contains no data and is not synchronized to the database. A temporary table may be used as any normal table in joins and selects.

• To easily locate temporary tables in the AOT, all temporary tables are prefixed with Tmp*.
• The major reason for using temporary tables is for the sorting of data. You might have to present data using a specific sort in a form/report which cannot be accomplished using a select statement or a query. Instead you can create a temporary table, by fetching data from the tables and insert according to the sort requirement. As long the temporary table is in scope, the temporary table will contain data.

The content of a temporary table is stored in the file system as a temporary file.

• It will initially take a longer time to load the form data-sourced with temporary tables, but the application will perform much faster once the data is loaded and thereby provide a more user-friendly system.

 

Table Browser:

• Table browser is quite handy to get an overview of the data in the table.
• Table browser can be accessed by right-clicking on any table in the data dictionary (Add-Ins > Table browser)
• The table browser is created using a standard form which is called SysTableBrowser.
• This can be used for creating/updating records in a table for Testing purposes.
• Whereas, this cannot be used as an application user tool, because, data entered through table browser will be only validated upon the business logic mentioned in the Table scripts and not the business logic that could possibly be mentioned in the Form methods. So Only the form which has been created within Axapta must be used to key in live data for tables.