LeaderBoard

Temporary Tables In Dynamics AX

In Microsoft Dynamics AX, a table typically maps to a corresponding table in the database. Temporary tables enable to you define table objects that are not persisted to the database. Define a table as a temporary table by doing one of the following:

  • Set the table's Temporary property to Yes at design time.

    –or–

  • Call the setTmp method in X++ code.

A temporary table is held in memory until its size reaches 128 KB. The dataset is then written to a disk file. The disk file for a temporary table has the naming convention $tmp<nnnnnnnn>.$$$.

A temporary table is located in the Application Object Tree (AOT) under the Data Dictionary\Tables node, just like a persisted table. If a table is defined as temporary, you can use it just as you would a static table. All X++ DML statements can be run against a temporary table.

Note: It is a best practice to infix temporary table names with Tmp. This improves readability in code.

Scope      A temporary table exists only while a record buffer variable that references the table exists. No memory is allocated to the temporary table until the first record is inserted. At that point, memory is allocated. Disk space is allocated, if it is needed. As soon as the record buffer goes out of scope, the memory is de-allocated and the disk file is deleted.

A temporary table resides on the tier where the first record is inserted. If a record is inserted on the server tier, memory for the temporary table is allocated on the server tier. If the temporary table exceeds 128 KB, a disk file is created on the server.

Adding data to temp table

To add data to a temporary table, you must define the record buffer and call the insert method. The following is a code example that uses the TmpCustLedger table.

static void TableTmpInsertRecord(Args _args)
{
TmpCustLedger custTmpLedger;
;
custTmpLedger.AccountNum = '1000';
custTmpLedger.Name = 'NameValue';
custTmpLedger.Balance01 = 2345000;
custTmpLedger.insert();
}


To free the memory and delete the file for the temporary table, set the record buffer variable to null, as follows.

custTmpLedger = null;

To populate a temporary table with data from a persisted table, use the setTmp method. The following code example copies all customers in Toronto to the temporary table.

static void CopyPersistedTableToTemp(Args _args)
{
CustTable custTable;
CustTable custTmpLedger;
;
custTmpLedger.setTmp();
custTable.recordLevelSecurity(true);

while select custTable where custTable.City == 'Toronto'
{
custTmpLedger.data(custTable.data());
custTmpLedger.doInsert();
}
}

Indexes


Indexes can be defined on a temporary table just as you would a persisted table. If a temporary table is created by copying a persisted table, the indexes are also copied to the temporary table. Indexes are very useful for retrieving data in temporary tables especially if the temporary table data is in a disk file.


Temporary table vs. Container 

Microsoft Dynamics AX supports a special data type called a container. This data type can be used just as you would use a temporary table.



  • Data in containers are stored and retrieved sequentially, but a temporary table enables you to define indexes to speed up data retrieval.
  • Containers provide slower data access if you are working with many records. However, if you are working with only a few records, use a container.
  • Another important difference between temporary tables and containers is how they are used in method calls. When you pass a temporary table into a method call, it is passed by reference. Containers are passed by value. When a variable is passed by reference, only a pointer to the object is passed into the method. When a variable is passed by value, a new copy of the variable is passed into the method. If the computer has a limited amount of memory, it might start swapping memory to disk, slowing down application execution. When you pass a variable into a method, a temporary table may provide better performance than a container.

Integrate Microsoft Dynamics AX analytic components with external data sources

Data warehouses are a popular solution for providing analytic capabilities to users. Until recently, data warehouses were the only reasonable solution for building robust analytic capability. However, as applications become easily interoperable and as technologies such as in-memory databases and OLAP become cost-effective and simpler to use, building a data warehouse is not the only solution to meet analytic requirements.
Table below presents several architecture options for integrating external data with the prebuilt
analytic solution; a data warehouse is just one of the options. The columns represent architecture options, whereas the rows represent the benefits and cost implications of each option.

image

When most data is in Microsoft Dynamics AX (assuming that Microsoft Dynamics AX is the
predominant  source of data in the organization), you have two options.

The data mash-up option is best suited to an environment where capable users author and publish analyses for the use of others. This option relies on client tools such as Excel PowerPivot. Microsoft Dynamics AX 2012 enables Microsoft Dynamics AX queries to be published to data mash-up tools through OData feeds, or as data exports to Excel.

You can bring external data into Microsoft Dynamics AX either through services (data services consumed by means of inbound ports) or as batch jobs that are executed periodically to import data into tables. With this approach, external data is represented as read-only data within Microsoft Dynamics AX. The benefit to this approach is that external data appears as native Microsoft Dynamics AX data to Microsoft Dynamics AX tools. You can create analytics, reports, and inquiry forms that use the combined data.

A more complex approach involves integrating external data directly into the prebuilt BI solution.
With this option, a BI developer adds another data source to the prebuilt BI solution by using Business Intelligence Development Studio. Additional data tables are brought into the DSV by using the new data connection. It is possible to create dimensions and measures by using the new tables in the DSV.

The traditional ETL-based data warehouse option is suited to scenarios that require complex
transformations or large volumes of data. Although this option is more flexible in terms of capabilities, it is also the most expensive to implement and manage.

You might want to build a data warehouse to implement the following scenarios:

■ Integrate external data sources with Microsoft Dynamics AX data In this approach, the Microsoft Dynamics AX implementation serves as one of many corporate applications.
Although Microsoft Dynamics AX contains some of the corporate data, other systems contain
a considerable portion of the data. To make decisions, data must be combined across systems, and the data warehouse serves that need.
■ Incorporate legacy data into Microsoft Dynamics AX analytics Most organizations
migrate recent data when implementing Microsoft Dynamics AX. Legacy data is still
maintained in read-only instances of legacy applications. Although legacy data is no longer
used for operational purposes, it is required for historical trend analysis. A data warehouse
serves as the repository where legacy data is combined with current data.

Although Microsoft Dynamics AX 2012 does not directly support the creation of a data warehouse schema, the following artifacts generated in Microsoft Dynamics AX 2012 can be used to build a data warehouse:

■ The DSV generated as part of the prebuilt analytic solution can be used within SQL Server
Integration Services when an ETL package is developed to extract data from Microsoft
Dynamics AX.
■ Microsoft Dynamics AX document services can be consumed as data sources based on Simple Object Access Protocol (SOAP).
■ Microsoft Dynamics AX queries can be exposed as OData feeds.

Extend cubes [AX 2012]

you can customize the prebuilt analysis project relatively easily by using the SQL Server Analysis Services Project Wizard. But in some cases, you may want to make
deeper customizations. For example, you might want to:
■ Create a rich hierarchy, such as a parent/child hierarchy to model organizational units.
■ Add new KPIs.
■ Bring external data into the analysis project and create a custom dimension.
You can use Business Intelligence Development Studio to make these types of changes.

Because the prebuilt BI components are included in the AOT as an SSAS project, you can modify the project. To modify the prebuilt Analysis Services project, do the following:
1. In the AOT, expand the Visual Studio\Analysis Services Projects node.
2. Right-click the project that you want to modify, and then click Edit.
An Infolog message appears, stating that a copy of the SSAS project has been created and saved, as shown in Figure below

 image

If SQL Server Business Intelligence Studio is installed, it will start and open the copy of the project.
Changes that you make to the project are not automatically saved to the AOT. You need to save the project and import it back into the AOT.

DSV
The DSV contains the table and view definitions that are used by analytic artifacts. Notice that the OLAP framework has implemented several query definition patterns in the DSV:
■ Financial dimensions that the wizard has added appear as custom query definitions in the DSV.
■ The OLAP framework has created query definitions corresponding to Microsoft Dynamics AX views.
■ The OLAP framework has added a reference relationship to resolve virtual companies, if your Microsoft Dynamics AX installation has virtual company definitions.
■ The OLAP framework has created views that make Microsoft Dynamics AX enumerations accessible

in all of the languages that have been added to the project.
Avoid modifying any of the framework-generated objects in the DSV. Any changes that you make to these objects are overwritten without warning the next time you update the project.
You may add your own objects to the DSV (for example, new query definitions. The Project Update  option will preserve these objects.
In Microsoft Dynamics AX 2012 R2, do not implement any partition-specific logic in any of the query definitions. Otherwise, when the project is deployed to multiple partitions, the system may
generate processing errors. (Because the framework adds partition-specific logic to the DSV at deployment time, it may not apply the changes accurately to your query definitions.)

Data source
A data source has been created that points to the Microsoft Dynamics AX OLTP database.


Dimensions, measures, and measure groups

In Figure below , notice the dimensions that are included with the Microsoft Dynamics AX 2012 prebuilt BI solution, as well as the measures and measure groups.
For a list of measures and dimensions, see “Cube and KPI reference for Microsoft Dynamics AX 2012” at http://msdn.microsoft.com/en-us/library/hh781074.aspx.
image

KPIs and calculations
The SSAS project contains prebuilt KPIs and calculations. Microsoft Dynamics AX 2012 does not provide the capability to model KPIs and calculations in the AOT. You can modify these definitions or
add new ones directly in Business Intelligence Development Studio.