Database DataWarehousing (MOSC)

MOSC Banner

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.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center