We have our production ETL run for 2.5 days every month. OBIEE reports will not be available during the run. We have a requirement to make the reports available all 24 hrs 365 days. So, what we are trying is to copy all the fact/Dim tables to the new schema running in the same or different DB server and amend the connection pool to point this schema during the run. Here are my questions. Is this a workabale solution (or) is there better approach for meeting our requirement
Sarvan, as per my understanding your trying to take a snapshot of all the tables including data into another schema... such that it doesnt impact your downtime. (During ur ETL Run)
The above solution is workable.
But, i still think solution provided by Srini.. is far better which removes the necessity of another schema/tables etc... So try to develop the same concept.
And, let us know your approach and solution you found.
If you are on Oracle Database,you can try implementing Exchange Partition feature provided by Oracle at ETL end for Data Movement / Data Load. Below link gives an idea about what Exchange Partition does:
Thanks for your response. It's 2.5 days for full load. We are thinking to recreate only the Fact/Dimensional MVs and aggregated tables in the new schema and swith the connection pool to point this schema via. a DB table as suggested. I started developing the POC in lower environment.