Saving Time Function Forecast in a cube

Options
Héctor Fernández
Héctor Fernández Active Partner
Photogenic Name Dropper First Comment
in Platform

Hi everyone.

I'm interested in using the Forecast function in a cube that contains information about the sales amount until the past month, in order to obtain a prediction for the following ones.

Currently I'm applying this Forecast function inside a dataview, the problem is that it takes too long to load the page. Is it possible to apply the Forecast to a cube and save the obtained values in another one?

Thanks in advace!

Tagged:

Answers

  • Willians Santana
    Willians Santana Employee
    Level 100: Foundations of Building in Board Level 200: Leveraging Board for Business Insights First Anniversary 5 Likes
    Options

    Hi, @Héctor Fernández,


    Thank you for your message and question! I do appreciate that.
    Yes.


    You can create a forecast cube separately by flowing data into that and automating the process using a Data model procedure.


    Please also send us a screenshot of the layout you are using. So we can better understand the forecast functions you are using.


    Best Regards,
    Willians

  • Héctor Fernández
    Héctor Fernández Active Partner
    Photogenic Name Dropper First Comment
    edited April 2023
    Options

    Hi @Willians Santana,

    Thanks for the answer.

    What I am currently doing is applying the Time Function: Forecast to a cube containing data, which I later use it to fill an empty cube with a simple Dataflow.

    But the only data copied is the "real" one, so cube A and B end having the same data (no Forecast included).

    Captura.PNG

    Thanks in advance,

    Héctor

  • Willians Santana
    Willians Santana Employee
    Level 100: Foundations of Building in Board Level 200: Leveraging Board for Business Insights First Anniversary 5 Likes
    Options

    You are very welcome @@Héctor Fernández!

    Thank you for all the information provided! I do appreciate that.

    I willl add my observations below, please, let me know if they work for you.

  • Willians Santana
    Willians Santana Employee
    Level 100: Foundations of Building in Board Level 200: Leveraging Board for Business Insights First Anniversary 5 Likes
    Options

    a) According to the Board Best Practices, the target cube should be last block at the Data Flow layout, for example:

    Data Flow:

    a: Factor Cube

    b: Target Cube → DP. Forecast Board (test)

    Then,

    Target b: DP.Forecast Board (test) = b

  • Willians Santana
    Willians Santana Employee
    Level 100: Foundations of Building in Board Level 200: Leveraging Board for Business Insights First Anniversary 5 Likes
    Options

    b) Please, also review the Data Flow Calculation Domains according to the application requirements:

    Limit calculation to tuples where values of at least one cube of the Layout are non-zero (default).

    Execute calculation on existing sparse combination of target cube for all the dense (back-compatible).

    Limit calculation to tuples of the Cube.

    Reference: https://www.boardmanual.com/2021/summer/data-modeling/2_Database_design_functions/8_Procedures/The_Procedure_Designer/procedure-actions/The_DataFlow_process.htm?rhhlterm=target%20cube%20cubes&rhsearch=target%20cube

    c) And, finally, please, also review the Forecast function defintion vis-à-vis the desired output:

    Forecast. Applies a statistical forecasting function to the Cube and calculates predictive values of future periods. This function estimates the trend component and the seasonal component based on the historical data of the Cube to return a forecast that includes historical trends and seasonality.
    Depending on the historical data series available, Board automatically selects the most appropriate statistical model from three options based on the exponential smoothing and moving average models, ARIMA models and the Winters models.

    The Forecast function can be combined with the "Ignore Current Period" option (checkbox) to exclude the last period of the current time selection from the data set used to calculate the statistical functions.Ignore Current Period. This option can be enabled when the Trend or Forecast statistical functions are used. It excludes from the data set used to calculate the statistical functions the last period of the current time selection.

    Reference:

    https://www.boardmanual.com/2021/summer/capsules/screens/2_Objects/The_Layout/Layout_Data_Blocks/About_Data_Block_settings.htm?rhsearch=forecast%20function&rhhlterm=forecast%20forecasting%20functions%20function

  • Willians Santana
    Willians Santana Employee
    Level 100: Foundations of Building in Board Level 200: Leveraging Board for Business Insights First Anniversary 5 Likes
    Options

    b) Please, also review the Data Flow Calculation Domains according to the application requirements:


    Limit calculation to tuples where values of at least one cube of the Layout are non-zero (default).


    Execute calculation on existing sparse combination of target cube for all the dense (back-compatible).


    Limit calculation to tuples of the Cube.


    c) And, finally, please, also review the Forecast function defintion vis-à-vis the desired output:

    Forecast. Applies a statistical forecasting function to the Cube and calculates predictive values of future periods. This function estimates the trend component and the seasonal component based on the historical data of the Cube to return a forecast that includes historical trends and seasonality.
    Depending on the historical data series available, Board automatically selects the most appropriate statistical model from three options based on the exponential smoothing and moving average models, ARIMA models and the Winters models.

    The Forecast function can be combined with the "Ignore Current Period" option (checkbox) to exclude the last period of the current time selection from the data set used to calculate the statistical functions.Ignore Current Period. This option can be enabled when the Trend or Forecast statistical functions are used. It excludes from the data set used to calculate the statistical functions the last period of the current time selection.

    We are all years for you.

    Thank you, @Héctor Fernández!

  • Willians Santana
    Willians Santana Employee
    Level 100: Foundations of Building in Board Level 200: Leveraging Board for Business Insights First Anniversary 5 Likes
    Options

    b) Please, also review the Data Flow Calculation Domains according to the application requirements:


    Limit calculation to tuples where values of at least one cube of the Layout are non-zero (default).


    Execute calculation on existing sparse combination of target cube for all the dense (back-compatible).


    Limit calculation to tuples of the Cube.

  • Willians Santana
    Willians Santana Employee
    Level 100: Foundations of Building in Board Level 200: Leveraging Board for Business Insights First Anniversary 5 Likes
    edited April 2023
    Options

    c) And, finally, please, also review the Forecast function defintion vis-à-vis the desired output:

    Forecast. Applies a statistical forecasting function to the Cube and calculates predictive values of future periods. This function estimates the trend component and the seasonal component based on the historical data of the Cube to return a forecast that includes historical trends and seasonality.
    Depending on the historical data series available, Board automatically selects the most appropriate statistical model from three options based on the exponential smoothing and moving average models, ARIMA models and the Winters models.

    The Forecast function can be combined with the "Ignore Current Period" option (checkbox) to exclude the last period of the current time selection from the data set used to calculate the statistical functions.Ignore Current Period. This option can be enabled when the Trend or Forecast statistical functions are used. It excludes from the data set used to calculate the statistical functions the last period of the current time selection.

  • Willians Santana
    Willians Santana Employee
    Level 100: Foundations of Building in Board Level 200: Leveraging Board for Business Insights First Anniversary 5 Likes
    edited April 2023
    Options

    References:

    https://www.boardmanual.com/2021/summer/data-modeling/2_Database_design_functions/8_Procedures/The_Procedure_Designer/procedure-actions/The_DataFlow_process.htm?rhhlterm=target%20cube%20cubes&rhsearch=target%20cube

    https://www.boardmanual.com/2021/summer/capsules/screens/2_Objects/The_Layout/Layout_Data_Blocks/About_Data_Block_settings.htm?rhsearch=forecast%20function&rhhlterm=forecast%20forecasting%20functions%20function

  • Willians Santana
    Willians Santana Employee
    Level 100: Foundations of Building in Board Level 200: Leveraging Board for Business Insights First Anniversary 5 Likes
    Options

    We are all years for you!

    Thank you!

  • Willians Santana
    Willians Santana Employee
    Level 100: Foundations of Building in Board Level 200: Leveraging Board for Business Insights First Anniversary 5 Likes
    Options

    You are very welcome, @Héctor Fernández! Thank you for all the details provided! I do appreciate that.

    I will add my observations below.

    Please, let me know if they work for you.

    a) According to the Board Best-Practices, the target cube DP: Forecast Board Test should be at the last block in the layout.

    For example,

    Data Flow:

    a: Factor cube

    b: Target Cube → DP: Forecast Board Test

    Then, Target b: DP: Forecast Board Test = a

    b) Please, also review the Calculation Domain settings and Forecast function definition and let us know if they work according to your expectation:

    The domain can be limited or expanded using the following options:

    Limit calculation to tuples where values of at least one cube of the Layout are non-zero (default). The domain is limited to the tuples that are non-zero in at least one of Block of the associated Layout ignoring the density/sparsity of the target Cube and considering only the Cubes that can be aggregated to the target Cube structure on the Entities that are not extended

    Execute calculation on existing sparse combination of target cube for all the dense (back-compatible). The Dataflow in Board 10.x wrote data only on existing combinations in sparse structures and for each dense combination of Entity members included in the target Cube structure and this option will automatically set the domain to what it was in version 10.x of Board: this is particularly useful in case of upgrades from version 10.x, since Dataflows will be using the same behavior of cell-based Dataflows in versions 10.x

    Limit calculation to tuples of the Cube. The domain is limited to the tuples that are non-zero in a specific Cube of the Data model, selected in the "Cube" dropdown menu. This option limits the domain so that the Dataflow writes data only on existing combinations of a given Cube

    Forecast. Applies a statistical forecasting function to the Cube and calculates predictive values of future periods. This function estimates the trend component and the seasonal component based on the historical data of the Cube to return a forecast that includes historical trends and seasonality.
    Depending on the historical data series available, Board automatically selects the most appropriate statistical model from three options based on the exponential smoothing and moving average models, ARIMA models and the Winters models.

    The Forecast function can be combined with the "Ignore Current Period" option (checkbox) to exclude the last period of the current time selection from the data set used to calculate the statistical functions.Ignore Current Period. This option can be enabled when the Trend or Forecast statistical functions are used. It excludes from the data set used to calculate the statistical functions the last period of the current time selection.

    References:

    https://www.boardmanual.com/2021/summer/data-modeling/2_Database_design_functions/8_Procedures/The_Procedure_Designer/procedure-actions/The_DataFlow_process.htm?rhhlterm=target%20cube%20cubes&rhsearch=target%20cube

    https://www.boardmanual.com/2021/summer/capsules/screens/2_Objects/The_Layout/Layout_Data_Blocks/About_Data_Block_settings.htm?rhsearch=forecast%20function&rhhlterm=forecast%20forecasting%20functions%20function

    We are all ears for you.

    Thank you!

  • Willians Santana
    Willians Santana Employee
    Level 100: Foundations of Building in Board Level 200: Leveraging Board for Business Insights First Anniversary 5 Likes
    Options

    b) Please, also review the Data Flow calculation domain settings according to the application requirements:

    The domain can be limited or expanded using the following options:

    Limit calculation to tuples where values of at least one cube of the Layout are non-zero (default). The domain is limited to the tuples that are non-zero in at least one of Block of the associated Layout ignoring the density/sparsity of the target Cube and considering only the Cubes that can be aggregated to the target Cube structure on the Entities that are not extended

    Execute calculation on existing sparse combination of target cube for all the dense (back-compatible). The Dataflow in Board 10.x wrote data only on existing combinations in sparse structures and for each dense combination of Entity members included in the target Cube structure and this option will automatically set the domain to what it was in version 10.x of Board: this is particularly useful in case of upgrades from version 10.x, since Dataflows will be using the same behavior of cell-based Dataflows in versions 10.x.

    Limit calculation to tuples of the Cube. The domain is limited to the tuples that are non-zero in a specific Cube of the Data model, selected in the "Cube" dropdown menu. This option limits the domain so that the Dataflow writes data only on existing combinations of a given Cube

    Reference:

    https://www.boardmanual.com/2021/summer/data-modeling/2_Database_design_functions/8_Procedures/The_Procedure_Designer/procedure-actions/The_DataFlow_process.htm?rhhlterm=target%20cube%20cubes&rhsearch=target%20cube

  • Héctor Fernández
    Héctor Fernández Active Partner
    Photogenic Name Dropper First Comment
    edited April 2023
    Options

    Hi @Willians Santana

    I've rearranged the procedure and tried the different setting given in your point b), even added a Select so the Forecast is applied only for the next 8 months.

    Despite that my target cube only gets the real data and not the forecasted one.

    image.png

    As can be seen in a data view the Forecast function is well calculated for the Factor Cube, but the target cube hasn't obtained any data after the procedure (except the real one that goes until Februrary 23).

    Once again, thanks in advance!

  • Willians Santana
    Willians Santana Employee
    Level 100: Foundations of Building in Board Level 200: Leveraging Board for Business Insights First Anniversary 5 Likes
    Options

    Hi @Héctor Fernández,

    Thank you for your message! I do appreciate that.

    I have asked permission to the Board Partner Management Team to have a remote session with you.

    Please, could you send me an e-mail to wsantana@board.com so we can through this analysis together?

    Best Regards,

    Willians

  • Johannes Blischke
    Johannes Blischke Employee
    First Anniversary DACH Level 100: Foundations of Building in Board Level 200: Leveraging Board for Business Insights
    Options

    Hello,

    just a quick and dirty workaround: you could also export the DataView via procedure as a text file (incl. the forecast function and all dimensions you need in row) and then load the data via ASCII data reader into your new cube.

    Best
    Johannes

  • Héctor Fernández
    Héctor Fernández Active Partner
    Photogenic Name Dropper First Comment
    Options

    @Johannes Blischke I will keep your solution as a last resorce in case I don't get a solution to that.

    @Willians Santana mail sended, thanks for the opportunity.

    Thanks both of you for the answers.

    Best Regards,

    Héctor

Categories