6 Replies Latest reply on Jan 29, 2013 9:23 PM by sivausin

    How to assign values from a Variable to an FORMULA

    sivausin
      Hi All,

      I am trying to create an forecast cube. while runing the fcexec command, it is not allowing to directly populating the output values to a formula. Instead, i had created an variable and populated the output to it. It is now woking and values are populated to the variable. The variables doesnot show up in the GUI to query the results in data viewer.

      Now I want populate the values from the variable to the formula, so that I can view the data in GUI. How can I populate the values to formula?

      Note : Both the variable and formula as the same dimensionality..

      DEFINE FCSTCUBE_FORECAST VARIABLE NUMBER <TIME_DIM ORG_DIM CLASS_DIM ITEM_ALONE_DIM>


      Thanks in Advance.
      S
        • 1. Re: How to assign values from a Variable to an FORMULA
          DavidGreenfield
          What is the definition of your formula?
          • 2. Re: How to assign values from a Variable to an FORMULA
            sivausin
            HI David,

            Thanks for you response. I have create the Measure(in AWM) , but anyhow here is the definition of the formula

            ->listnames formulas
            2 FORMULAs
            -----------------
            FCSTCUBE_FCST
            FCSTCUBE_QUANTITY

            ->describe fcstcube_fcst

            DEFINE FCSTCUBE_FCST FORMULA LOCKDFN NUMBER WITH NULLTRACKING <ITEM_ALONE_DIM TIME_DIM ORG_DIM CLASS_DIM>
            EQ this_aw!FCSTCUBE_STORED(this_aw!FCSTCUBE_MEASURE_DIM 'FCST')


            Thanks in Advance
            S
            • 3. Re: How to assign values from a Variable to an FORMULA
              DavidGreenfield
              Then it is simple. You don't need to create your own variable, FCSTCUBE_FORECAST. Instead you should populate the variable named FCSTCUBE_STORED. This was created by the system when you defined the cube. The only thing you need to do first is to limit the measure dimension to name of the target measure.
              LIMIT FCSTCUBE_MEASURE_DIM TO 'FCST'
              ... Now run your forecast to populate FCSTCUBE_STORED
              ALLSTAT
              After this you should see the data in the cube.
              • 4. Re: How to assign values from a Variable to an FORMULA
                sivausin
                HI David,

                just a small clarification. Here is my piece of dml code.. for forecast
                ================================
                _handle = FCOPEN('MyForecast')

                "Set forecast parameters for 'best fit'
                fcset _handle APPROACH 'APPAUTO' periodicity 12 histperiods 24

                "Execute the forecast - save seasonal and seasonal smoothed into the variables just defined
                FCEXEC handle time timedim INTO fcstcube_forecast -
                seasonal fcstcube_seasonal -
                smseasonal fcstcube_smseasonal backcast -
                fcstcube_quantity

                "Close the forecast
                FCCLOSE _handle


                ================================


                You mean change to the code to ..


                ================================
                _handle = FCOPEN('MyForecast')

                "Set forecast parameters for 'best fit'
                fcset _handle APPROACH 'APPAUTO' periodicity 12 histperiods 24

                LIMIT FCSTCUBE_MEASURE_DIM TO 'FCST'

                "Execute the forecast - save seasonal and seasonal smoothed into the variables just defined
                FCEXEC _handle time time_dim INTO FCSTCUBE_STORED -
                seasonal fcstcube_seasonal -
                smseasonal fcstcube_smseasonal backcast -
                fcstcube_quantity

                ALLSTAT

                "Close the forecast
                FCCLOSE _handle


                ================================


                please let me know , If i understood correctly.


                Thanks
                S
                • 5. Re: How to assign values from a Variable to an FORMULA
                  DavidGreenfield
                  Yes, this was what I was thinking.

                  On an unrelated point, you should think about the level at which you will run your forecast since the FCEXEC call will rely on the [url http://docs.oracle.com/cd/E11882_01/olap.112/e17122/dml_basics.htm#OLADM129]status of the dimensions. As an example, if you want to run the forecast at the QUARTER level of your time_dim, then you will first need to limit it to quarters. You should then further limit time_dim to the union of the historical and forecast periods. E.g.
                   
                  LIMIT time_dim TO time_dim_levelrel 'QUARTER'
                  SORT time_dim A time_dim_end_date
                  LIMIT time_dim KEEP FIRST 36
                  (Object and level names are approximate.)

                  You should also consider the other dimensions, like ORG_DIM. If you want to forecast at the 'COMPANY' level, for example, then you need to write something like this.
                   
                  LIMIT org_dim TO org_dim_levelrel 'COMPANY'
                  ... other dimensions
                  Here is a program I wrote as an experiment a few years ago. It was for a very specific schema and I have edited it slightly to obscure details. But examples of this are scarse, so I thought you would appreciate seeing another one. It forecasts from the measure named 'UNITS' in 'MY_CUBE_TC' to the measure named 'UNITS' in 'MY_CUBE_FCST'.
                  define sales_forecast program
                  program
                   argument num_prod int            " Number of ITEMs to forecast
                   argument num_loc int             " Number of STOREs to forecast
                   argument num_hist int            " Number of historic periods
                   argument num_fcst int            " Number of forecast periods
                  
                   variable hndl int                " The forecast handle
                  
                   " variable initialization
                   had_error = false
                  
                   " Limit dimensions based on the input
                   limit this_aw!prod to this_aw!prod_levelrel 'ITEM'
                   limit this_aw!prod keep first num_prod
                  
                   limit this_aw!loc to this_aw!loc_levelrel 'STR'
                   limit this_aw!loc keep first num_loc
                  
                   limit this_aw!clnd to this_aw!clnd_levelrel 'DAY'
                   limit this_aw!clnd keep first num_hist + num_fcst
                  
                   " Create the forecast handle and set up parameters
                   hndl = FCOPEN('MyForecast')
                   fcset hndl method 'CROSTON' -
                              approach 'APPMANUAL' -
                              histperiods num_hist -
                              periodicity 360 
                  
                   " Error handling to clean up variables
                   trap on HADERROR 
                  
                   " Limit measure dimension so that we access the variables directly
                   " instead of the formulas.  This saves time in a tight loop.
                   limit this_aw!my_cube_tc_measure_dim to 'units'
                   limit this_aw!my_cube_fcst_measure_dim to 'units'
                  
                   " Run forecast
                   FCEXEC hndl time this_aw!clnd -
                    INTO this_aw!my_cube_fcst_stored -
                    this_aw!my_cube_tc_stored
                  
                  " Skip to the clean up code
                  goto CLEANUP
                  
                  HADERROR:
                   " Remember that we had an error
                   had_error = true
                  
                  CLEANUP:
                   trap off
                  
                   FCCLOSE hndl
                  
                   if had_error
                   then signal errorname errortext  " Throw error
                  
                  END
                  
                  {code}
                  
                  This is a fairly naive program and was based on the kind of code generated by AWM 10g.  If you deal with very large amounts of data (millions of cells to forecast), then you may need to use more sophisticated techniques like explicit looping over composites.  But I hope you don't need to go there.