Rolling transition of forecast values (Prognose) into actual values (Ist-Daten)

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

  • Rolling transition of forecast values (Prognose) into actual values (Ist-Daten)

    Hi Jedox buddies,


    I want to exchange with you on possibilities to cover the rolling transition of forecast values (Prognose) into actual values (Ist-Daten)
    .. as well as the related aggregation to a full year forecast total in the cube, consisting of:
    - actual data for the first months
    - and forecast data for the remaining months.

    I have a version/ scenario dimension with elements:
    - actual (AC)
    - forecast (FC)

    Of course there is a time dimension across year and month:
    - 2015
    2015-01
    2015-02
    2015-03
    ...
    - 2016
    ...

    The FC slice is naturally filled by manual user entry before the current year starts and is updated continuously
    The AC slice is filled via ETL step by step each month - so everything fine.

    However when accessing the figures I need to get a "merged picture" of both slices for the current year.
    (see screenshot).

    This is quite easy in a Jedox Web spreadsheet by dynamically switching the PALO.DATA formula references between
    AC and FC for each month based on current date ..and calculating the year total across on actuals FC months – no problem.

    But now I need to have the "merged picture" directly in the cube for adhoc reporting and lookup in rules of other cubes.

    I want to discuss with you what's the best / most elegant option. I see the following options:
    1.
    Change the ETL process to directly overwrite the data in the FC slice with actuals, so that we get the FC updated and have a correct total.
    =>Intuitive for adhoc reporting users who query the current FC, but I loose the possibility to compare AC and FC.
    2.
    Create a 3rd slice "AC_FC_merged" where I create and continuously update the merged picture
    2.1
    Update the "AC_FC_merged" slice via ETL
    However the FC values will continuously be changed, so also the "AC_FC_merged" slice would need to be updated continuously, which would mean to trigger ETL after each change.
    2.2
    Update the "AC_FC_merged" slice via Macro SetData / Copy splashing in the planning spreadsheet

    2.3
    Create "hardcoded" rules for populating the values in the "AC_FC_merged" slice
    ['AC_FC_merge',{'2015-01','2015-02'}] = ['AC']
    ['AC_FC_merge',{'2015-03','2015-04', ..}] = ['FC']
    => this could be generated/updated via ETL each month

    2.4
    Create a generic rule for populating the values in the "AC_FC_merged" slice
    Using If() and compare the time dimension slice with the current date

    3.
    Use approach 1, but backup the previous AC/FC-state in a separate slice before each load (for comparison)
    FC 1 (no actuals)
    FC 2 (actuals till Jan)
    FC 3 (actuals till Feb)
    FC (current)

    Did I forget something (SVS? etc. ?)

    What do you think?
    Thanks for all statements and perspectives :)
    JCO
  • Also you can
    ['FC']=IF(['Status']==1,['AC'],STET())
    In this way users can see actual-forecast timeline in natural way and future is editable. This is regardless of interface - all transparent down to the olap cube.
    The downside is that you can not compare past period AC vs FC. But before updating status you can copy past to another archived region. Alternatively you can temporarily clear status to analyze original data if needed.
    Cheers,Madis