Database model changes in AX 2012 Good/Bad?

(1) Share

Report

Posted on by Rudi Hansen

4,075

So one of the major changes from AX 2009 to AX 2012 are the huge changes in the database model, (Number of tables up from 2800+ to 7900+ for one thing)

And I would just like to start up a little discussion about this subject, since I do not quite understand why Microsoft did what they did, and from the little information I picked up about AX 7, (*), they do not seem to have planned to change the database model.

*(from this video General Session: Microsoft Dynamics AX (BSAX01) (54:15 first mention))

Now it seems to me like Microsoft's idea was, the AX database has some issues with concurrent users and speed, so perhaps we can solve this with database normalization, but then they just took it too far and ended up with a database that is a lot more complex and even slower than before.

However, what do you think?

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (3)

Verified answer

jasman 1,413 on at

Like (0)

Report

IMHO some of the changes made in AX 2012 is an attempt to increase user support of modelling and supporting changes in large organisations without having a developer making changes to the system.

If you look at the financial dimensions / accounting framework it now supports an (in theory) infinite number of dimensions, and it is quite easy to include more without having redundant data and changes to code.

In the previous versions if e.g. an organisation used the project module and wanted the project ID "stamped" in a financial dimension on the general ledger transactions, you would have to have a developer make changes to increase the number of dimensions (and you would also have to purchase a license for using extra dimensions) and make code a number of places in the system to support synchronizing the financial dimensions to the project id.

This is not so in AX2012. Here you can use actual tables as backing entities so that when new projects are created there are there for use in the dimensions.

BUT - again in IMHO - with increased flexibility comes increased complexity. So making BI-reporting (the usual economic KPI's) using general ledger transactions in AX 2012 has become much more challenging and I doubt that the result of the normalization done to the database is doing wonders for performance. You will have to query / join several tables to get a general ledger transaction in AX 2012 where in the previous versions one table was enough.

But a step towards transition towards the .net-platform has also been made, so that x++ code can be compiled to CIL which should be faster than interpreted x++ (byte-code).

There is always a trade-off when you make design decisions, so I think that a lot of trade-offs between performance and flexibility goals have been made.

But the learning curve from 2009 to 2012 is steep.

Was this reply helpful? Yes No
Rudi Hansen 4,075 on at

Like (0)

Report

Hi Jacob.

Sorry about the late answer, had a busy day :-)

I know that the change to the dimension tables had something to do with changed functionality, but I am not sure if that is the only reason why there are more tables now than before, does anyone have a comment about this or some knowledge?

And so far from everyone I have talked to, that had to do something with dimensions in AX 2012, the complaint is that Microsoft made it more complicated than it had to be.

So it seems like they might have forgotten about KISS!

As to the last part of your message, I am not sure I understand that correct, are you saying that its ok they made the system more complex and possible slower, since they then might or might not also make the overall performance better with the move to some better compilation tools?

Was this reply helpful? Yes No
Verified answer

André Arnaud de Cal... 301,037 Super User 2025 Season 2 on at

Like (0)

Report

Hi Rudi,

Compared to previous versions AX 2012 has a lot more features which also requires additional tables. Next to the financial dimension framework think of Retail, Process manufacturing, Warehouse management, Organization hierarchies, etc.

Many 7900 tables are temporary tables used by SSRS reports.

More tables makes it more complex for developers, but the aim is to have it easy to understand for end-users.

More tables does not mean that the system becomes slower. This also depends on hardware, SQL configuration and the way indexes are defined and how the AX business logic is built.

Was this reply helpful? Yes No
Verified answer

Sohaib Cheema 49,438 User Group Leader on at

Like (0)

Report
What I can see Microsoft has moved it towards a more normalized system. E.g.

In AX 2009, all names were being saved with respective tables, e.g. Customer Name was being saved in CustTable.Name ; whereas In ax 2012 system is keeping names of all parties in once place DirPartyTable.

In AX2009, normally there were only 3 dimensions (Financial). Now you can create numberless dimensions, as many as you want in AX2012

Items were being managed only in InventTable. Whereas now in AX2012, there is full Product Model

Functionality of AX was very limited in AX 2009, as compared to 2012. Modules such as following were not separate

Procurement and sourcing

Sales and Marketing

Product information

Compliance and internal control

So overall the reason for increasing Number of tables is because of following two reason

More functionality

More Normalized Database

Was this reply helpful? Yes No
jasman 1,413 on at

Like (0)

Report

I think it would be fair to say that the KISS-design principle has not been applied in this version.

Was this reply helpful? Yes No