datawarehousing tips

hi,

If you're implementing Ax and need to assist the guys in charge of the datawarehouse/BI, here are some tips:

- do insist on setting up a separate datawarehouse/BI database. "But the data needed for the DW is not that much, the queries are simple and not blocking, they will not obstruct the live Ax DB in any way." Uhu, and then on an unpleasant day it is blocking and obstructing. Don't give in to this temptation. Keep it clean and simple: the Ax and any other DB need to be separated DB's.

- since Ax 5.0 and the UTCDateTime datatype, the date/time you see in Ax is not necessarily the date/time stored in the DB. The Ax kernel handles the timezone offset on forms and reports (not in code!). If the datawarehouse is to produce reports, do take the timezones into consideration (for instance if totals are calculated per day: end of day may very well be 10PM in the DB, depending on the timezone)

- if tables are shared in virtual companies, the Ax kernel handles this automatically. In the DB on the other hand it's a hell of a job to link the correct dataAreaId's. This job gives an overview of which table is included in which table collection as a result the translation dataAreaId to virtual dataAreaId.

- security: Ax handles a lot of security (RLS, AOS aothorisation, user group permission, ...). This is (if you don't anticipate) not the case if the data from the Ax DB is transferred (on DB level) to a reporting/DB/BI database. Make sure sensitive/confidential information is not made public in that way.

creating recId indexes on SQL 2005

hi,

The Dynamics Ax Performance Team Blog gives a pretty good description why you want to add a recID-index for tables having created/modifiedDateTime property activated (on a SQL 2005 DB backend).

But you don't want to do this manually, do you?

Thought so. You can find the script I used to add these indexes here.

In short, here's what the script does:

- on each table in the AOT it checks whether the created/modifiedDateTime fields are active and the createRecIdIndex property is set not set

- for tables that meet these conditions: it creates a brand new index on recId

When a new index is created I'm also adding the modified/createdDateTime field. The Performance team blog states

When you Insert into a table with CreatedDateTime field on SQL 2005, the following SQL statement will be issued immediately following the Insert:

SELECT CreatedDateTime From %table% WHERE RecID=%recId% AND DataAreaId=%dataAreaId%

The same goes for the modifiedDateTime field with an update statement.

So my reasoning is to include the created/modifiedDateTime field right away in the newly created index.

This should enable SQL to fetch all required data using just this single index.

More and wider indexes slow down insert/update/delete operations: true ... but if this can speed up each and every insert/update, this may be a well-considered decision.