6 Replies Latest reply: Sep 17, 2012 10:20 PM by saranya.a RSS

    Enabling materialized view for fast refresh method

    saranya.a
      In AWM when i select refresh method as FAST and enable the materialized view, am getting the below error:
      "Refresh method fast requires materialized view logs and a previously run complete refresh of the cube mv". i need this Fast refresh of a cube materialized view, so that it performs an incremental refresh and re-aggregation of only changed rows in the source table.
      can anyone help me on this??
        • 1. Re: Enabling materialized view for fast refresh method
          AdamMartin
          Consider choosing "Force" which performs a fast refresh if possible, otherwise it does a complete refresh. You can also specify Complete as the refresh method in the Maintenance Wizard dialog the first time you maintain the cube.

          You will need to create materialized view logs on all the base tables if you have not done so already, otherwise the cube will never be able to do a Fast (incremental) refresh.
          • 2. Re: Enabling materialized view for fast refresh method
            saranya.a
            Thanks for the reply.. can u pls tell me how do to create materialized view logs on all the base tables ??
            • 3. Re: Enabling materialized view for fast refresh method
              AdamMartin
              Create Materialized View Log
              http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_6003.htm#SQLRF01303
              • 4. Re: Enabling materialized view for fast refresh method
                saranya.a
                i created materialized view log for all the base tables and that error got resolved. But my requirement is that, when i delete the data in RDBMS, the corresponding data in cube also gets deleted when i give maintain the analytic workspace(with Fast refresh and cube script without clear command). i want the cube to hold the data which i have deleted from RDBMS. Can u pls help me on this???
                • 5. Re: Enabling materialized view for fast refresh method
                  DavidGreenfield
                  If you want the cube to hold data even after it has been deleted from the relational table, then you should disable the MV on the cube.

                  Synchronization with the source table is determined by the default "cube script".

                  <li>CLEAR, LOAD, SOLVE : This will synchronize your cube with the source table. It is a requirement for MVs.

                  <li>LOAD, SOLVE: This will allow your cube to contain data even after it has been removed from the source table. It sounds like you want this.

                  Cube builds can be "incremental" in one of two ways.

                  (1) You can have an "incremental LOAD" if the source table contains only the changed rows or if you use MV "FAST" or "PCT" refresh. Since you can't use MVs, you would need a source table with only the changed rows.

                  (2) You will have an "incremental SOLVE" (a.k.a. "incremental aggregation") if there is no "CLEAR VALUES" or "CLEAR AGGREGATES" step and various other conditions hold.

                  To force a "complete LOAD" with an "incremental SOLVE" you should have all rows in your source table and run the following build script.
                  LOAD, SOLVE
                  You could also run "CLEAR LEAVES, LOAD, SOLVE" to synchronize the cube with the table.

                  To force an "incremental LOAD" with a "complete SOLVE" you make the source table contains only the changed rows and the run the following:
                  CLEAR AGGREGATES, LOAD, SOLVE
                  or
                  LOAD, CLEAR AGGREGATES, SOLVE
                  Finally, if you want both LOAD and SOLVE to be incremental you make the source table contains only the changed rows and the run the following:
                  LOAD, SOLVE
                  • 6. Re: Enabling materialized view for fast refresh method
                    saranya.a
                    Thanks a lot david. i mapped the cube to views and ran the script with load and aggregate.It worked.