AX 2012 - Table Inheritance Exposed
I'm been convering, as part of my post on this blog some of the great new features that come as part of Microsoft Dynamics AX 2012. One of those new topics, that really needs some deep understanding is around derived tables, also known as table inheritance.
This concept is very new to AX 2012, and some key master data elements, like the EcoResProduct data set, that represents the Product-Item Data in AX 2012 make use of this. Take away the complexity that can be read into this concept, in it's simpliest form derived tables are meant to represent master data, not transactional data. As well, this new concept is meant to address the need to shared and common fields among a dataset. The concept of when to use table inheritance, or derived table concepts, is throughly covered in the following MSDN article: When to Use Table Inheritance [AX 2012].
There are some very key concepts that are clearly laid out, for what table inheritance is meant to be used for, and what is' not designed to do. From the article: "When you consider the use of inheritance between two tables, one table is the proposed base table, and the other is the proposed derived table."
Another nice resource, which goes hand in hand with understanding derived tables, is how to work with data from X++ that is contained within those datasets that make use of Table Inheritance. That is the following walk through: Selecting Data from a Table Inheritance Hierarchy [AX 2012] "This topic explains how you can maximize the efficiency of your operations that read data from tables that are related through inheritance."
With these two resources, you can start to understand the concepts behind table inheritance in AX 2012. The final resource, which reference these two actually, is Walkthrough: Creating Base and Derived Tables [AX 2012] "In Microsoft Dynamics AX, a table can inherit from another table. The AOT node for each table has the Extends property which you can use to derive your table from a table that you specify. There are other table properties related to inheritance which must be assigned values in a specific sequence." With understanding the thought process of table inheritance, as well as how to work with such tables via X++, the walkthrough that was just referenced is key in one understanding of how to create base and derived tables that use the concepts of table inheritance.
Now, with all of this in hand, it's important to understand what this does to the actual query, that the AX kernel generates and executes against the SQL Server database. This places Joins that could impact performance, not help it. This is why it's very critical in understanding the purpose of based and derived tables, and that it's only meant for certain types of scenario's, which one of them is not transactional tables.
Hopefully with this better understanding of Table inheritance, concepts like I described in the following post, "Microsoft Dynamics AX 2012 - MorphX Drag and Drop, but no fields", makes a lot more sense. You have to build up to concepts sometimes, in order to gain a true understanding of a given topic. Table Inheritance and it's use for creating Base and Derived tables is one of those topics in AX 2012.
Table Inheritance, when properly understood can be a new design tool that helps reduce the amount of duplicate fields, and data that span like datasets. This can be a great tool, however this one has a sharp double-edge to it, that can cause havoc is used in an improper manner. Well that's all for this post, check back soon as a whole lot more to come!
Till next time!