Provide ability to report on consistent set of data while it is changing
Posted on 2011-04-21
We are in the process of building a new data warehouse using Oracle 11g.
Most of the new data will be loaded into the data warehouse overnight, however, our users have decided they want to be able to report on the DW 24 x 7!
Our dilemma is how to make the DW available for reporting (via a Business Objects Universe), while loading new data into it, and ensuring the reports produce results that are correct.
Our current thinking is that we will build a Data Mart from the DW and refresh this Data Mart each night after all data has been loaded into the DW. The data in the data mart will only show the "current" view of data i.e. it will not contain all historical data. The BO universe will then be based on the Data Mart, rather than the DW.
However, if a report is running against tables in the Data Mart when the Data Mart is refreshed, this may affect the reports.
We are considering using materialized views (instead of standard tables in the data mart), which will provide for a simpler refresh mechanism. If a materialized view group is created, refreshing the group will refresh all data in the data mart. Simple!!!
Is it possible to refresh a materialized view while queries are running against it? (I know I could try this, but just don't have the time). If this is possible, is Oracle clever enough to provide a read-consistent view of a materialized view to running queries, and to then allow newly started queries to see the new data in the view? If this is possible, then hopefully refreshing a materialized view group will provide a read consistent view for ALL data in ALL materialized views in the group?
I suspect we are expecting a lot from Oracle, but it must be possible to provide 24 x 7 reporting, while data is being changed.
I would appreciate any comments or suggestions on how to achieve our aims based on your experiences of doing this.