1 Reply Latest reply: Dec 2, 2012 4:49 AM by MKKrishna RSS

    predictive use of measures

    Andy Nierhaus

      I do have a source system table with these columns
      Item, date, test_no, Factor_Value

      Item, date and test_no identify 1 record. Factor_Value is used for predictive analysis.
      The date indicates from which date on a factor can be used for the predictive analysis.
      There is no end date - as long as the record exists the factor is valid.
      If a new factor should be used the existing record will be replaced with a new date and factor.

      There is an Item-Dimension, a calendar Dimension and a Test-Dimension.

      If I do have the record below in the source system

      Item     Date          Test_no          Factor
      A     01-Oct-2012      1          1.5     

      Item and Test dimension would be joined to my fact table using an inner join.

      If I want to use the factor_value 1.5 in a predictive analysis for december 2012

      How should I populate and join the fact table to the calendar dimension?
      My first thought was to populate the fact table with the copies of the original record, changing the

      Item     date          Test_no          Factor
      A     01-Oct-2012     1          1.5
      A     02-Oct-2012     1          1.5
      A     03-Oct-2012     1          1.5

      My second thought:

      Is there a way to avoid duplicating the records and use a join like this?
      fact.dato <= calendar_dim.dato     

      would this let me use the factor_value 1.5 registered for 01-Oct-2012 in an analysis where date=15-dec-2012?

      Thanks for your help
        • 1. Re: predictive use of measures
          For future Predictive analysis you need to take the factor which is latest in the table.

          1. You can use Max Date functionality with Dynamic Repository variable with Initialization block with select factor_Value from table where date in select max(date) from the table.
          2. Level based measure based on dimension for Fact_no using aggregation rule for all tables and Last() function on time dimension.