FDI :Aggregate tables vs MV's

Abhishek Bajpai - CEAL Team-Oracle

Lot of customers have asked us what should be factors that need to be factored in while deciding on creation of Aggregation tables vs Materialized views.The blog tries to discuss the considerations while deciding between the two

https://videohub.oracle.com/embed/secure/iframe/entryId/1_4v3gp3rg/uiConfId/35965902

Benjamin Arnulf-Oracle

Helpful!

kalikhan

Users should also look at the 'Analytic Views' (AVs) functionality of Oracle database. It simplifies analytical reporting significantly. There is no need to create Summary table or MVs. BI Data Model (including hierarchies, attributes and measures) is pushed inside database.

All data exists at all levels of all dimensions, and no need for SUM..GROUPBY queries.

The new sql syntax (of AVs) simplifies creation of calculations - like Time Series calcs.

All queries are simple SELECT..FROM…WHERE types of queries - with no need for JOINs and aggregation.

OAC DV has a separate data source for 'Analytic Views' in addition to Oracle Autonomous Database.

Even Essbase Federated Partitions (in essbase-cloud) use 'Analytic Views' features of autonomous database - behind the scenes.

https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/overview-analytic-views.html

There is a livelab at:

https://apexapps.oracle.com/pls/apex/r/dbpm/livelabs/view-workshop?wid=3594

Bud Endress has many videos on YouTube about 'Analytic Views'.

He has also written some blogs about it:

https://blogs.oracle.com/datawarehousing/authors/william-endress