none
LinRegPoint

    Question

  • We're trying to create a measure using the LinRegPoint function within our measures table in Database Engine.   Here's the code I've written, but we're not getting any results.  I'm hoping someone with more knowledge on MDX than I can help me debug this.

    This is a measure within the SAP BPC application which uses SQL Server 2005.

    IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="INC" OR [%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="EXP",IIF([%TIMEDIM%].Currentmember.Level.Ordinal = [%TIMEDIM%].[Month].Ordinal,LinRegPoint(7,LASTPERIODS(6, [%TIMEDIM%].Currentmember),[Measures].[12MOAVG]), MEASURES.[12MOAVG]), MEASURES.[12MOAVG])
    Wednesday, November 04, 2009 4:53 PM

Answers

  • ".. This is a measure within the SAP BPC application which uses SQL Server 2005 .." - but is the app using Analysis Services 2005, or just SQL 2005  relational with a different OLAP/MDX  engine? In the case of AS 2005, this paper should help:

    Using Linear Regression MDX functions for forecasting


    Based on the guidance in the paper, you can add a 4th parameter for LinRegPoint(), and change it to:

    LinRegPoint(7, LASTPERIODS(6, [%TIMEDIM%].Currentmember), [Measures].[12MOAVG],
    Rank([%TIMEDIM%].Currentmember, LASTPERIODS(6, [%TIMEDIM%].Currentmember)))

    - Deepak
    Friday, November 06, 2009 10:23 PM

All replies

  • Consider posting this to some Analysis Server/BI forum, you are more likely to get help there. :-)
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Thursday, November 05, 2009 7:51 AM
  • ".. This is a measure within the SAP BPC application which uses SQL Server 2005 .." - but is the app using Analysis Services 2005, or just SQL 2005  relational with a different OLAP/MDX  engine? In the case of AS 2005, this paper should help:

    Using Linear Regression MDX functions for forecasting


    Based on the guidance in the paper, you can add a 4th parameter for LinRegPoint(), and change it to:

    LinRegPoint(7, LASTPERIODS(6, [%TIMEDIM%].Currentmember), [Measures].[12MOAVG],
    Rank([%TIMEDIM%].Currentmember, LASTPERIODS(6, [%TIMEDIM%].Currentmember)))

    - Deepak
    Friday, November 06, 2009 10:23 PM