Saving Time Function Forecast in a cube

Héctor Fernández · 2023-03-31T13:43:27+00:00

There was an error rendering this rich post.

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!

Find more posts tagged with

Cube

Dataflow

Optimization

Algorithm

Accepted answers

All comments

Willians Santana

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

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).

Thanks in advance,

Héctor

Willians Santana

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

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

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

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.

We are all years for you.

Thank you, @Héctor Fernández!

Willians Santana

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

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

Willians Santana

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

Willians Santana

We are all years for you!

Thank you!

Willians Santana

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

References:

We are all ears for you.

Thank you!

Willians Santana

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

Reference:

Héctor Fernández

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.

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

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

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

@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