5 Replies Latest reply on Mar 1, 2013 7:20 AM by 979482

    All partitions are re-aggregated


      I have a cube that is partitioned on time and I load/aggregate data for the previous day using a view on the fact table and LOAD PRUNE, SOLVE PARALLEL. This works just fine most of the fine and takes about 2 hours to complete.

      Sometimes - though - all partitions are re-aggregated and it takes about 2 hours per partition (right now I have 80 partitions so it takes quite some time even if I can run some processes in parallel). I have checked the cube build log if there were any changes to the dimensions but I can't find any.

      Is it possible to find out why this re-aggregation occurs from some other log?

      Regards /Magnus

      Edited by: 976479 on Feb 26, 2013 11:57 PM
        • 1. Re: All partitions are re-aggregated
          The OUTPUT column of the CUBE_BUILD_LOG for rows where command = 'SOLVE' contains limited information. My guess is that you will find something that says
          This would indicate that there were changes to one of the hierarchies used by the cube. As an example, you may have deleted a member or changed the parent of a member. See this post for more details: Re: Partition rebuild of cubes
          1 person found this helpful
          • 2. Re: All partitions are re-aggregated
            Hi David,

            Thank you for your answer and just like you say I have changed_relations=yes in the cube_build_log. But the funny thing is that I can see in the build log for the previous night that it said the same (changed_relations=yes) but then the solve completed in 0,3 seconds (i.e. did not re-aggregate).

            I just need to know why it happens so that I can possibly prevent it from happening again.


            • 3. Re: All partitions are re-aggregated
              Do you know if is possible to find out if a dimension was changed (apart from deleted members) from any DML command?

              • 4. Re: All partitions are re-aggregated
                nasar.ali-khan at -Oracle
                I am not sure if there is a way to find the changes through olap dml.

                One quick way could be through SQL.

                Lets say your dimension name is DEPT and its hierarchy is DHIER.

                Use the default cube_table views to create pre and post load tables.

                (1). CREATE TABLE PRELOAD_DEPT_DHIER as select * from DEPT_DHIER_VIEW;

                (2). Refresh you DEPT dimension through cube build command.

                (3). CREATE TABLE POSTLOAD_DEPT_DHIER as select * from DEPT_DHIER_VIEW;

                (4). Through sql query find the differences between PRELOAD_DEPT_DHIER table and POSTLOAD_DEPT_DHIER table.

                Of course you can do similar type of logic through olap dml also by storing the DEPT hierarchy information in some olap variables before and after dimension refresh. Then write and olap dml program to find the differences.

                Its preferable to use SQL.

                Only use OLAP dml as a last resort.

                1 person found this helpful
                • 5. Re: All partitions are re-aggregated
                  Hi Nasar,

                  Thank you for your suggestion. I will try that approach and see where it brings me.

                  Regards /Magnus