Friday, August 27, 2010

datawarehousing tips


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.

No comments:

Post a Comment