24 Hrs BI availability using Oracle Partitioning/Swap methodology

This blog deals with  Global Organizational Data needs where there is insufficient window of non-Business hours to run heavy batch loads and at the same time a provision to access the data 24 hours with  negligible or no interference.

Why 24 hours availability is needed?

Organizations have a high need for 24 hour availability of their Data warehouse without much or any interference of batch loads which might be happening monthly, daily or even multiple times in a day. When Organizations are globally operating in multiple Time Zones then it becomes tricky to find a good non-business window for the loads to do take place without impacting the Business time. Especially when data is time critical and data volume is too large,  it can result in lot of delays with unexpected data load errors resulting in unavailability of data to the users meant for analysis and impeccable functioning.

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

How to implement 24 hours availability on a Data warehouse?

Oracle Partitioning is one the best options which successfully counters this issue. The idea here is to load all the data into temporary tables where the actual tables which are being used for analysis or reporting purpose are not impacted during the loads executing behind the scenes. Once load on temporary tables is done then exchange Partition can be used to swap the tables in split seconds without the business user having to deal with any wrong or incompletely loaded table data. Even renaming the table is an option where partitioning is not possible.

Below flow diagram gives an idea of how this works:

Note: Any Source, Any ETL tool can be used for implementing this methodology.  SQL Script will include Exchange Partition or Renaming of the Fact tables.

Advantages:

1. Batch loads will be faster since there is no overhead due to constant usage by business users
2. Performance gain during batch loads is higher if partitioning is used for Facts
3. More Flexibility w.r.t timing the batch load is achieved especially for organizations with Global presence
4. Uninterrupted 24 hrs usage of Data warehouse can be achieved even if Batch loads have failures
5. Minor Savings on time critical supports required to bring the Data warehouse backup and running due to failures during the load.