I am looking for best practice to create a staging environment for COGNOS.
our situation is like this:
The main database is located on as AS400 DB2 (developed with synon). We do not want Cognos to read directly from this DB, so we want to create an intermediate db on SQL server for cognos to read.
The question is what is the best practice to get all necessary tables from DB2 on sql server (IDS, program, stored procedures, cognos framework, etc) considering that during that process we need to apply several rules & formulas creating new "calculated fields". Also some tables are quite big more than 20mil rows, whats is the best way to keed this tables synced on SQL server (changes might occur on existing data). Finaly the field names on DB2 are something like FDSERTD and they need to be renamed to something that makes sense (keep in mind that the description (in DB2) of the fields works fine)
million thanx in advance