The standard ‘out-of-the-box’ system will perform reasonably for many companies. There is always a need to test the performance and tune the system to your requirements.
If your implementation uses inventory, and if you are using batch and or serial numbers, then you might find your inventory performance to be sluggish. You might want to look at your indexes on the tables InventDim and InventSum. These tables are used in almost every query when involved with inventory.
Using the Database Engine Advisor to analyse a SQL query can give you an insight into where to look for problems with performance. Recently I have a problem opening the advisor from a query window recieving an error: “Failed to connect to an IPC Port: The system cannot find the file specified”.
The problem was caused by a previous use of the system that had not been shutdown cleanly. The simplest way to resolve this problem is to open the windows task manager and end the process for DTAShell.exe.
Display fields can be a powerful tool, when displaying custom information on a form or a report. But there are times when a form has information from multiple tables being returned as part of the form query that is needed within a display field calculation.
During data migration it is often necessary to pull data from another SQL server database. It can also be necessary for integration purposes. Below is some sample code that can be used to get data from another system.
Using ODBC to connect to another database can be an important requirement, whether the connection is part of the initial data import or an ongoing integration.
There are a couple of error messages that you might experience when using the ODBC connection within DAX, with the main two, often cryptic messages being returned being:
error 1: Microsoft][SQL Server Native Client 10.0][SQL Server]Changed language setting to us_english
error 2: [Microsoft][SQL Server Native Client 10.0]Invalid Descriptor Index
One of the most common overlooked areas of the DAX system is the compile. A clean compile will go a long way to making you system stable, which is critical part of the requirement for an ERP system.
There are two compile stages: X++ and CIL. The X++ compile consists of four element: Errors, Warning, Best Practices and TODOs.
The DAX instance structure and code release methodology is more important that ever with the introduction of DAX 2012. More important it is the key, as it has been in reality in older version, if you want a stable environment.
With the introduction of DAX 2012 the need to enforce the process has become critical to ensure you have a stable production environment and a reliable code release process, and the ability to move your production data to other environments for testing and evaluation.