Refreshing the warehouse

7.6 Refreshing the warehouse

An extremely important management task is refreshing the warehouse with the latest data. This is a management task that is usually performed overnight, with the data presented in batches. As was described earlier, frequently the data has to be cleansed and transformed before it can be loaded. Chapter 5 described various techniques that can be used for loading the new data into the warehouse.

Throughout this book, we have seen extensive use of Oracle Enterprise Manager for managing and controlling many of our management tasks. Depending on how complex your tasks are and on the data dependencies, you may prefer to use your own techniques. Otherwise, you could place the jobs on the console job queue and come in the next day to see that everything has run smoothly. For example, it is not uncommon to receive data loads from various sources at different times. If there are dependencies between the data, then you may have to control that within your own management suites.

Once the data has been loaded into the warehouse, the materialized views must be refreshed. Depending on the number of materialized views, this could also take a significant amount of time.

An OEM job could be created to refresh the materialized views. Executing the dbms_mview.refresh_all_mviews procedure could refresh all of the materialized views. Alternatively, you could use the dbms_mview.refresh_dependent option to request that it refresh only materialized views dependent on certain tables. Or you can call the refresh procedure and specify which materialized views are to be refreshed. Specific details on how to refresh materialized views were described in Chapter 4.