AnsweredAssumed Answered

Datamart Rollup - Time Facts and Time Summary runs in a continuous loop. (

Question asked by omplata on Jan 14, 2009
Latest reply on Jun 1, 2009 by Chris_Hackett
On of the stored procedures used in the job appears to  run  in a continuous loop and thus causing the Datamart Rollup to run until the SQL process is killed by our DBA. The loop basically inserts records into the NBI_ROLLUP_SQL table. Upon further investigation we found that the problem is related to missing recrords from the NBI_DIM_CALENDAR_TIME. Specifically, records for the dates 12/25/2012 through 12/31/2012 are missing from the table. The last record for the year 2012 is 12/24/2013. It appears that Clarity only allows 52 weeks per calendar fiscal year.    The problematic stored procedure appears to be NBI_RTF_SP. In the code snippet below, SET @V_CONTINUE_LOOP will never = 0 before of the data condition referenced above in the DIM_CALENDAR_TIME table.         IF @V_TIME_TYPE = 'WEEK'
                BEGIN
                      SET @V_TO_DATE = @V_FROM_DATE + 6
                END
                ELSE
                BEGIN
                    SELECT     @V_TO_DATE = MAX (NEXT.DAY)
                    FROM         NBI_DIM_CALENDAR_TIME NEXT, NBI_DIM_CALENDAR_TIME CURR
                    WHERE       NEXT.HIERARCHY_LEVEL = 'DAY'
                    AND           (NEXT.MONTH + (NEXT.YEAR * 12)) = (CURR.MONTH + (CURR.YEAR * 12) + 1)
                    AND           CURR.HIERARCHY_LEVEL = 'DAY'
                    AND           CURR.DAY = @V_FROM_DATE
                END
                  --   If its a partial period then exit
                IF NIKU.CMN_TRUNC_DATE_FCT (@V_TO_DATE) > NIKU.CMN_TRUNC_DATE_FCT (@V_LAST_CURVE_DATE)
                BEGIN
                      SET @V_CONTINUE_LOOP = 0
                END
          END
    END
 Does anyone have any auggestions? I know that the NBI_DIM_CALENDAR table gets populated by the Datamart Extract job but where can I fix this? Is this just a bug in Clarity?

Outcomes