Materialized Views in Oracle Warehouse Builder OWB Don Burleson (provided courtesy of Michael Armstring-Smith)

You could either get Oracle Warehouse Builder (OWB) to build the materialized view(s) or you could build the MV yourself. Either way you would need to get workflow to place the scheduling of these jobs at the end of the run after all the normal facts and dimensions had been loaded.

Other alternatives include creating a fixed roll-up tables (with partitioning) and using a materialized view "on prebuilt table",  with rebuilding the current rollup one each night, would probably lend itself better to being a manually written job. We could still insert the job (aka procedure / package) into the schedule using workflow.

To create an MV using OWB follow this workflow:

1. Launch OWB and open your project

2. Expand the Database node

3. Expand the target module

4. Right click on Materialized Views and select Create

5. A wizard pops up containing 6 steps

a. Give it a name
b. Define the name, data type, and whether mandatory for each column
c. Type or paste the MV SQL - just the select portion
d. Define constraints - optional
e. Define attribute sets - optional
f. Finish

6. After the MV has been created you can get back to the above wizard
using right click on the MV and from the pop-up selecting Properties

7. To set Build and Refresh characteristics, right click on the MV and
from the pop-up select Configure