When creating Cubes in OWB10g R2, are materialized views automatically created as well?
When creating a Cube in OWB10g R2 one has the possibility to define aggregations to be performed for the Dimensions that the cube references.
When defining the aggregations you are able to select the aggregation method (sum, max, min, first, last, average, hierarchical average etc.) and the data refresh method (On demand or On commit).
You are also able to select the dimension levels for which data should be pre computed. If you do not select levels for the dimensions, OWB will perform aggregations for alternative levels starting from the lowest level. According to the documentation, for ROLAP cubes Warehouse Builder implements these aggregations by creating materialized views. The documentation also states that these materialized views are NOT displayed under the Materialized Views node in the Project Explorer of OWB Design Center.
I had created a few Cubes using the aggregation setup but I couldnâ€™t find any materialized views in the database. I noticed that in the Cubes Configuration screen there is the possibility to name the tablespace that is to be used for the materialized views.
So I created a tablespace â€œMVIEWTâ€, added the tablespace name to the Cubes configuration, created a new mapping for the Cube, deployed and executed the mapping but unfortunately the tablespace remained empty. So I decide to contact Oracle Support to check with them where the materialized views are being placed. They were able to confirm that this functionality does not work yet and hopefully it will be activated in the next release.
- Introducing Materialized Views as mechanism for Business Rule implementation (complex declarative constraints)
- Smart denormalization and Enhanced PL/SQL Function Call performance – Comparing Function Based Index and Materialized Views
- Courtesy of Tom Kyte: Generating rows in SQL with the CUBE statement – no dummy table or table function required
- Aggregating in SQL across multiple levels and along several dimensions using ROLLUP and GROUPING SETS
- Creating Multi-Type Node Children and Child Node labels in ADF Faces Tree Component