Which schema to use for ELT process for on-premises data warehouse
When using SSIS packages to retrieve data from Oracle R12.2 to populate an on premises data warehouse, should you select data from objects in the APPS schema or the Base schemas?
For example, should you select data from the table AP.AP_INVOICES_ALL or the synonym APPS.AP_INVOICES_ALL?
Certain tables have edition sets (i.e. identified by a ZD_EDITION_SET column), and so using synonyms in the APPS schema means it will retrieve the correct set. Although this doesn't seem to apply to all the tables we are interested in, and so should we simply use objects in the APPS schema for these, and the Base Schemas for the others? Or just select from objects in the APPS schema across the board.