June 24, 2003 at 8:47 am
Does SQL Server or Analysis Services have the ability to do Linear Regression?
If yes, how do I do this? If no, what tools are there that can be utilized.
Thanks in advance.
June 24, 2003 at 8:56 am
There's a bunch of MDX functions to do Lineair regression calculations. Check BOL for LinRegPoint, LinRegIntercept and so on.
June 24, 2003 at 10:10 am
Yes I found them. Thanks. However, how do I get these out into a line chart showing the actual movement? This part of it is very confusing to me.
Thanks for any help you can give me.
June 1, 2010 at 7:58 am
This is an old topic, but I recently had to produce a Regression Line in SSAS 2005 for several measures for the previous 28 days. After many days of work, I solved the problem. See if this helps you:
--Template
YesterdaysDateID =
VBA!DateDiff("d", '<Day 1 in the Date Dimension>', VBA![Date]())
--NOTE: this assumes the key is the day number, which is BTW the R Kimball recommended method
LINREGPOINT(RANK([<Date Dimension Name>].[<Desired Level Name>].CurrentMember,
LastPeriods(<No of Pds to go back>,
StrToMember('[<Date Dimension Name>].[<Desired Level Name>].&[' + VBA!Cstr([Measures].[YesterdaysDateID]) + ']'))),
LastPeriods(<No of Pds to go back>,
StrToMember('[<Date Dimension Name>].[<Desired Level Name>].&['+ VBA!Cstr(VBA!Cstr([Measures].[YesterdaysDateID])) + ']')),
[Measures].[<Desired Measure>],
RANK([<Date Dimension Name>].[<Desired Level Name>].CurrentMember,
LastPeriods(<No of Pds to go back>,
StrToMember('[<Date Dimension Name>].[<Desired Level Name>].&[' + VBA!Cstr(VBA!Cstr([Measures].[YesterdaysDateID])) + ']'))))
--Formula in SSAS for a LSRL that is for the last 28 complete days
YesterdaysDateID =
VBA!DateDiff("d", '1/1/06', VBA![Date]())
LINREGPOINT(RANK([Creation Date].[Creation Date].CurrentMember,
LastPeriods(28, StrToMember('[Creation Date].[Creation Date].&[' + VBA!Cstr([Measures].[YesterdaysDateID]) + ']'))),
LastPeriods(28, StrToMember('[Creation Date].[Creation Date].&['+ VBA!Cstr(VBA!Cstr([Measures].[YesterdaysDateID])) + ']')),
[Measures].[Work Orders],
RANK([Creation Date].[Creation Date].CurrentMember,
LastPeriods(28, StrToMember('[Creation Date].[Creation Date].&[' + VBA!Cstr(VBA!Cstr([Measures].[YesterdaysDateID])) + ']'))))
--Hardcoded equivalent for 6/1/10 with a dw for which Day 1 is 1/1/06:
LINREGPOINT(RANK([Creation Date].[Creation Date].CurrentMember,
LastPeriods(28, [Creation Date].[Creation Date].&[1612])),
LastPeriods(28, [Creation Date].[Creation Date].&[1612]),
[Measures].[Work Orders],
RANK([Creation Date].[Creation Date].CurrentMember,
LastPeriods(28, [Creation Date].[Creation Date].&[1612])))
--Test in SQL Server Management Studio as MDX Statement
WITH MEMBER [Measures].[YesterdaysDateID] AS
VBA!DateDiff("d", '1/1/06', VBA![Date]())
MEMBER [Measures].[OrderCountVariance] AS
LINREGPOINT(RANK([Creation Date].[Creation Date].CurrentMember,
LastPeriods(28, StrToMember('[Creation Date].[Creation Date].&[' + VBA!Cstr([Measures].[YesterdaysDateID]) + ']'))),
LastPeriods(28, StrToMember('[Creation Date].[Creation Date].&['+ VBA!Cstr(VBA!Cstr([Measures].[YesterdaysDateID])) + ']')),
[Measures].[Work Orders],
RANK([Creation Date].[Creation Date].CurrentMember,
LastPeriods(28, StrToMember('[Creation Date].[Creation Date].&[' + VBA!Cstr(VBA!Cstr([Measures].[YesterdaysDateID])) + ']'))))
SELECT [Measures].[OrderCountVariance] ON COLUMNS,
[Creation Date].[Creation Date].Members on rows
FROM [Work Orders]
--WHERE LastPeriods(-4, [Creation Date].[Creation Weeks Passed].&[1])
--Note the where clause should be something, although the above may not work for you;
otherwise if you have a lot of days and/or items to measure, the query may take awhile.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply