DAX Questions

1. Relations

1) Normal to specify relation fields without conditions 

2) Field fixed to specify relation fields to restrict the records in the primary table. 

3) Related field fixed to specify relation fields that restrict the records in the related table. 

4) New ForeignKey to specify a correspondence between a foreign key field in the present table to the primary key field in another parent table.

Ex:-SalesTable->salesLine.

Normal Relation:

1. In the Field property, select the field in the primary table that relates to a field in the present table.

2. In the RelatedField property, select the field in the related table.

Ex:-Salesline.salesid==SalesTable.salesid

Field fixed Relation:

1.  In the Field property, select the field in the primary table to use to restrict the records.

2.  In the Value property, enter the value of the selected field as the filter. This relates only records in the primary table that match that field value. Only numeric values can be entered in the Value property. Field fixed relations can be created only on numeric fields. Each of the related fields are AND ed in the table relation.

Ex:-Salesline.Salesid==1234

Related field fixed Relation:

1.  In the Value property, enter the filter value of the selected field. This causes only records in the related table that match that field value to be related. Only numeric values can be entered in the Value property. Related field fixed relations can be created only on numeric fields.

2.  In the Field property, select the field in the related table to restrict the records. Each of the related fields are ANDed in the table relation.

Ex:- 1234==SalesTable.SalesId.

New ForeignKey Relation-2012

We often use the term child to refer to a table that has a foreign key column. And we use the term parent to refer to the other table that supplies the value for the foreign key column. But we never use the terms parent and child to describe the base and derived tables in an inheritance relationship.

1.  Set the Table property to the name of the parent table, the table that contains the primary key field.

2.  Set the RelatedTableRole property to a word or phrase that describes the purpose of the parent in the relationship.

3.  Set the Name property. A helpful value is a combination of the Table property and RelatedTableRole property values.

4.  Right-click the node for your relation, click New, and then click New ForeignKey. Next click either PrimaryKey based or Single field AlternateKey based. A new field is instantly added to the child table. This field stores the foreign key values.

5.  Under the Fields node, click the new field, and then change its Name property value.

6.  For performance benefits, you might decide to add an index on the new foreign key field.

 

2. Caching Mechanism

  1. single record cache,
  2. entire table cache, and
  3. record view cache.

Single Record Caching
The record cache is utilized for full key lookups only and contains the 100 most recently used records for a particular table. In a 3-tier environment, there is a record cache on the client and a record cache on the server. The server record cache is shared between all clients.
Record Cache are of the following types:

(a) NotInTTS
(b) Found
(c) FoundAndEmpty

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:  

i)   notInTTS

ii)  Found

iii)  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's PrimaryIndex 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:

  a) The table is cached by setting the CacheLookup property to notInTTS, Found, or FoundAndEmpty.

 b)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 the PrimaryIndex 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.

Entire Table Caching
The entire table cache is a different type of caching compared to record caching,which is a complete local copy of a table. The entire table cache exists on the AOS, a 3-tier rich client, the COM connector, and on a 2-tier client. This
means that there is no entire table cache on a 3-tier thin client, as it utilizes the entire table cache located on the AOS.
The cache is populated at the time of the first access to that particular table for a given company.

Record View Caching
The RecordViewCache is instantiated using a X++ select with a where clause that defines the result set. The RecordViewCache is deactivated as soon as the RecordViewCache object goes out of scope or is destroyed.
It is private and offers a result-set cache with a limited lifetime.

Clearing the Cache

ax_{GUID}.auc  The GUID is unique for each installation and is stored in the SysSQMSettings table. 

example: ax_{76F5BA3B-AD98-4E5F-A1EE-D58C24370BDC}.auc

C:\Documents and Settings\%USERNAME%\Local Settings\Application Data\

Estimation Techniques

First place the old  version layers to the  old directory in the current application folder and run the following

AX-DevelopmentTools->code upgrade->Detect code upgrade conflicts.

After identifying the conflicts run the estimation report.

Before running the estimation report define the parameters for estimation for all the objects of the current client.

Virtual Company

Dynamics Ax stores data as per company in tables. But there might be occasions when you want to share data across companies, like country, state, zip codes data. This sharing of data is achieved by creating a virtual company and storing data in this virtual company. Normal companies are then configured to read/write data from this virtual company. The only purpose of virtual company is to share data across companies, you cannot log into this virtual company.

Before seeing how to do virtual company setup, I would like you to show another trick that can be used to share data across Ax. There is a property on Ax tables called "SaveDataPerCompany", you can use this property to save data globally in Ax. To share data set this property to "No".

Virtual Company setup:

Step 1: Create Table Collection

Decide which tables you want to share and create a table collection for these functionally related tables. For example; if you want to share Global Address Book across companies then you can utilize the existing table collection "DirPartyCollection".

 To create a table collection, go to AOT\Data Dictionary\Table Collections and on right click select "New Table Collection", then just drag your required tables in this collection.

Step 2: Create Virtual Company, configure/attach normal companies and table collection

Create a virtual company that will hold the shared data for normal companies.
Note: Before doing the below steps, make sure you are the Ax administrator and the only user online.

1    1) Go to Administration -- Setup -- Virtual company accounts, and create a virtual company.

2    2) Decide which companies needs to share data and attach those normal companies with this virtual company.

3    3) Attach the table collection with this virtual company.

Your Ax client will re-start and you are done with setting up the virtual company account.

Now, when you have virtual company in place, all new data will be saved in this virtual company. Only companies attached to the virtual company can use this shared data. All other companies which are not attached will work normally, these companies will continue to read/write data as per company bases.

How to move existing data to virtual company

When you setup a new virtual company, Ax does not move data automatically from normal company to virtual company. This is done by system administrator manually.

There are many ways to do this data move.

Ax Import / Export:

 This is standard Ax approach.

1      1) Manually export existing normal company data from Ax.

2      2)  Remove duplicate records from this exported data set.

3      3) Delete exported data from normal companies.

4      4) Import the exported data back in Ax, while logged into one of the participating companies.

5      5) Create records deleted in point 2 again in Ax using your logic. 

Maps

Maps define X++ elements that wrap table objects at run time. With a map, 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. Map methods enable you to create or modify methods that act on the map fields.

A table can be accessed through more than one map. Typically, if more than one map accesses the same table, each map accesses different subsets of fields in the table. Maps don't define database objects and so they aren't synchronized with the database. For more information about creating maps,.

Benefits of Map:

The benefits of maps include:

a)  Simplicity - maps provide a single interface to fields in multiple tables. This means that any object referencing the map field can be used against multiple tables without changing any field names.

b) Consistency - table fields with varying names can be accessed in code in a consistent manner. For example by using a map, fields named Zip in one table, ZipCode in another, and PostalCode in yet another table can all be accessed by the name ZipCode.

c) Code reuse - a map method enables you to add code that runs against the map fields. A single map method prevents the duplication of methods and code on each table.

An example of a map in Microsoft Dynamics AX is the Address map, which can be used to access fields in two tables (among others) called Address and CustVendTransportPointLine. This enables developers to use one Address map object to access common address fields and methods. The following table shows how the map fields are associated to table fields.

Field in Address map	Field in Address table	Field in CustVendTransportPointLine table
Address	Address	ToAddress
City	City	ToCity
State	State	ToState
ZipCode	ZipCode	ToZipCode

Map Elements

In Microsoft Dynamics AX, maps are located in the Application Object Tree (AOT) under the Data Dictionary\Maps node. Each map has four primary elements:

1) Fields

2) Field Groups

3) Mappings

4) Methods

Fields

The Fields node contains the map field elements. Each field must be the same data type as the field to which it's associated. Use the ExtendedDataType property to specify the map field's data type if the map field is associated with a table field that's based on an extended data type.

Field Groups

The Field Groups node contains field groups that group together fields that logically belong together. Field groups in maps work the same way they do in tables. For more information about field groups

Mappings

The Mappings node is where the map fields are associated with table fields. Directly under the Mappings node are the MappingTable objects. Each MappingTable object specifies a table that the map is associated with. Under the MappingTable object are the field mappings that associate a map field with a table field. If a field exists in the map with no associated field in a particular table just leave the MapFieldTo property blank.

Methods

This node displays all the methods available from a map. In this node you can add a new method or you can override methods on the xRecord kernel class and add your own code.

Map methods are useful because code that acts on the map fields can be encapsulated in a map method instead of being in multiple table methods. For example, the AddressMap has a formatAddress method that formats the address consistently whether the map references the Address table or the CustTable table.

     Table properties that you can remember      

1.        Label
2.       Titlefield1
3.       Titlefield2
4.       Primary index
5.      Cluster index
6.      Temporary

Default index for a table 

Recid index