I have a disk space cube with dimensions
Date,
ServerDisk,
Time

Date is at the day level and Time is at the Hour level, ServerDisk is a
simple hierachy of servers and their disks(volumes) with measures
TotalMB,FreeMB,UsedMB.

I'm trying to come up with a query that will give me the last 6
Months,Disk,Closing Balance of UsedMB and also the next 3 months forecast of
UsedMB based on the average daily growth of the disk for a specific server.
What I have so far seems to work for the last 6 months but I'm struggling on
how to append the next 3 months and calulate their measure values (really
only need the UsedMB and it can be as simple as 30,60 or 90 * the average
daily growth). I think some of my test query is a bit redundant (and
hardcoded datewise) but the main thing I need is how to append the forecasted
values to the historical so that I can graph the results (in RS)
Any pointers/help much appreciated!

with member [Measures].[AvDailyUsed] as
'Avg(Descendants([Time],[Time].[Hour]),[Measures].[Usedspace MB])'

member [Measures].[DailyGrowth] as 'iif(
([Measures].[AvDailyUsed],[Date].CURRENTMEMBER.PREVMEMBER)=0
OR
ISEMPTY(([Measures].[AvDailyUsed],[Date].CURRENTMEMBER.PREVMEMBER))
,NULL,
(
([Measures].[AvDailyUsed]) -
([Date].CURRENTMEMBER.PREVMEMBER,[Measures].[AvDailyUsed])))'

member [Measures].[Closing Usedspace MB] as
'([Measures].[AvDailyUsed],ClosingPeriod([Date].[Date],[Date].CurrentMember))'

member [Measures].[AvDailyGrowth] as
'Avg(Descendants([Date],[Date].[Date]),[Measures].[DailyGrowth])'

set Last6months as '([Date].[All Date].[2006].[April].Lag(5):[Date].[All
Date].[2006].[April])'

member [Measures].[GrandAvGrowth] as
'Avg(Last6months,[Measures].[AvDailyGrowth])'

select {[Measures].[Closing Usedspace
MB],[Measures].[GrandAvGrowth],[Measures].[Closing Total MB]} on 0,
nonemptycrossjoin({Last6months
},{[Servers].[Disk].[Server].[SERVER1].Children}) on 1
from DiskSpace

How about projecting a Linear Regression trend for Usedspace, based on
data points for the last 6 months, like:

'Avg(Descendants([Time],[Time].[Hour]),
[Measures].[Usedspace MB])'

member [Measures].[Closing Usedspace MB] as
'([Measures].[AvDailyUsed],ClosingPeriod([Date].[Date],[Date].CurrentMem
ber))'

set Last6months as
'([Date].[All Date].[2006].[April].Lag(5)
:[Date].[All Date].[2006].[April])'

set Next3months as
'([Date].[All Date].[2006].[April].Lead(1)
:[Date].[All Date].[2006].[April].Lead(3))'

member [Measures].[Trend Usedspace MB] as
'LinRegPoint(Rank([Date].CurrentMember,
[Date].[Month].Members),
Last6months, [Measures].[Closing Usedspace MB],
Rank([Date].CurrentMember, [Date].[Month].Members))'

select {[Measures].[Closing Usedspace MB],
[Measures].[Trend Usedspace MB]} on 0,
nonemptycrossjoin({Last6months, Next3months},
[Servers].[Disk].[Server].[SERVER1].Children) on 1
from DiskSpace

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks, that's perfect. I can combine the 2 measures into one as a
calculated field in the report and it works great in a matrix. I did see
that function when I was googling around the issue but couldn't make much
sense of it :-) Another approach I'm trying is creating a session mining
model using Time Series in a SQLCLR stored procedure as described in Teo's
Applied Microsoft Analysis Services
book(http://www.microsoft.com/technet/pro...rtreports.mspx)
mainly because it's a very interesting technique but doing it in MDX is a
lot cleaner.

--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com


"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Hi Jasper,

By Mosha's own admission, the MDX LinRegxxx function syntax isn't the
most approachable, so I'm not surprised:

http://sqljunkies.com/WebLog/mosha/a...2/21/5689.aspx

MDX provides several functions for computing linear regression, however
those functions are anything but intuitive or easy to use. This is
definitely an area in MDX, that given a chance I would've done
differently now. The problem is that when we designed those functions,
we unnecessarily generalized them so it would be possible to use them on
any dimension with any expression for both "x" and "y" coordinates, but
in the process of generalization, we made the most natural use cases -
forecasting by Time too difficult and unnatural.
...
Anyway, the data mining Time Series approach might be more accurate than
a linear projection, particularly if there is significant periodicity in
your disk usage data:

http://msdn2.microsoft.com/en-us/library/ms174923.aspx
...
PERIODICITY_HINT

Provides a hint to the algorithm as to the periodicity of the data. For
example, if sales vary by year, and the unit of measurement in the
series is months, the periodicity is 12.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***