DSXchange

Welcome aboard. :D

These are really questions for your DBA and possibly your Data Modeler or Architect. You have all kinds of ways to create and/or refresh materialized views nowadays and 'automatic query rewrite' adds more wrinkles to that.

Depending on how the MVs are created, you could incrementally load the 'base' tables and Oracle will automatically keep the MV in sync. Or you could force a full rebuild of the MV after each load, keeping in mind the fact that that may take hours to accomplish.

You don't really 'build Materialized Views' in DataStage, per se - you populate the tables the MV is built upon and Oracle 'builds' the view, either automatically or when you trigger it.

Have a serious chat with your DBA to understand how the one's you've setup work.

DataStage is not a tool for producing reports.

The question should be: do I use MV's to maintain aggregate (summary) tables and collapsed dimensions or write ETL?

The answer is: depends on your volume of data, processing window, hardware available, skill of your team, and time available to develop. MV's are easy to use, but problematic.

There are 4 ways to manage summary tables:

1. Truncate and reload the entire summary table
2. Delete a time range of data and reload that portion
3. Track groupings that have undergone change and reload those groups
4. Track each underyling row in the detail tables and incrementally reverse prior-to-update rows out of the summary and add in the new and changed rows.

MV's work well until Oracle gets confused or realizes it can't achieve an incremental update. In that case, it does option #1. If you manage the aggregate tables with ETL you can utilize all of the methods. In addition, Oracle doesn't "reverse" out a row well, it has limitations. There's also a limit as to how many MV's you can practically manage because once Oracle gets going on a refresh or update, the control is out of your hands.

Do you have any not-fully-additive facts? You will find that these are very difficult to maintain with DataStage, but that's better than the impossible with materialized views.