This discussion is archived
5 Replies Latest reply: Feb 28, 2013 11:20 PM by 979482 RSS

All partitions are re-aggregated

979482 Newbie
Currently Being Moderated
Hi,

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
    DavidGreenfield Expert
    Currently Being Moderated
    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
    CHANGED_RELATIONS="yes"
    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
  • 2. Re: All partitions are re-aggregated
    979482 Newbie
    Currently Being Moderated
    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.

    Thanks.

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

    /Magnus
  • 4. Re: All partitions are re-aggregated
    Nasar Journeyer
    Currently Being Moderated
    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.


    .
  • 5. Re: All partitions are re-aggregated
    979482 Newbie
    Currently Being Moderated
    Hi Nasar,

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

    Regards /Magnus

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points