I'm pretty new to MDX and I'm trying to create a calculated member to
add to my cube based on the following dimension structure:

Year
Qtr
Month
Day (2005-10-03)
DateHour (2005-10-03 05:00)

What I need to do is create an 30 day moving average by hour of day.
So if the member I'm on is 2005-10-03 05:00, get the 05:00 hour of
every day for the last 30 days and average them, all other hours being
excluded.

Right now I have the following MDX:

Avg
(
[Session Dt].[Year - Qtr - DatePartMonth - Day -
DateHour].CurrentMember.Lag(30) :
[Session Dt].[Year - Qtr - DatePartMonth - Day -
DateHour].CurrentMember,
[Measures].[Not Registered]
)


The problem here is that it just gives me the last 30 hours, I need a
way to have the set being averaged be just the members with the same
hour of day as the member I'm on.

The answer to this depends a bit on how we can identify the hours.

basically the MDX would looks something like the following

Avg
(
UNION({[Session Dt].[Year - Qtr - DatePartMonth - Day -
DateHour].CurrentMember} as CURRENT_MEMBER,
GENERATE({({[Session Dt].[Year - Qtr - DatePartMonth - Day -
DateHour].CurrentMember.Parent.Lag(30) :
[Session Dt].[Year - Qtr - DatePartMonth - Day -
DateHour].CurrentMember.Parent}
, FILTER(DESCENDANTS([Session Dt].CurrentMember, [Session Dt].[Year -
Qtr - DatePartMonth - Day -
DateHour]), VBA!Right([Session Dt].CurrentMember.Name,5) = VBA!Right
(CURRENT_MEMBER.item(0).Name,5) )
,
[Measures].[Not Registered]
)
)

A couple of notes:

1) I have a redundant UNION statement at the beginning so that I can
create a named set called CURRENT_MEMBER, this is because the
..CurrentMember function is context sensitive and the value of
..CurrentMember changes as we go down through the Generate() and Filter()
functions.

2) I am not sure of the format of your member names, but I am assuming
that they are in the hh:mm format so getting the last 5 characters of
the hour dimension should allow us to match these.

I have not been able to test this MDX so I hope there are no syntax
errors or missing brackets in there.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell


In article <1128380241.025598.148070 (AT) g47g2000cwa (DOT) googlegroups.com>,
knight.randy (AT) gmail (DOT) com says...

Might be simpler to use Cousin(), instead of Strcmp's?

[Year - Qtr - DatePartMonth - Day - DateHour].
CurrentMember} as CURRENT_MEMBER,
Avg(GENERATE({[Session Dt].
[Year - Qtr - DatePartMonth - Day - DateHour].
CurrentMember.Parent.Lag(30) : [Session Dt].
[Year - Qtr - DatePartMonth - Day - DateHour].
CurrentMember.Parent},
{Cousin(CURRENT_MEMBER, [Session Dt].
[Year - Qtr - DatePartMonth - Day - DateHour].
CurrentMember}),
[Measures].[Not Registered]))

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Oops - CURRENT_MEMBER is actually a set, so:

[Year - Qtr - DatePartMonth - Day - DateHour].
CurrentMember} as CURRENT_MEMBER,
Avg(GENERATE({[Session Dt].
[Year - Qtr - DatePartMonth - Day - DateHour].
CurrentMember.Parent.Lag(30) : [Session Dt].
[Year - Qtr - DatePartMonth - Day - DateHour].
CurrentMember.Parent},
{Cousin(CURRENT_MEMBER.Item(0).Item(0), [Session Dt].
[Year - Qtr - DatePartMonth - Day - DateHour].
CurrentMember}),
[Measures].[Not Registered]))

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Much simpler thanks Deepak.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Thanks Deepak. That works great.

BTW, I think we met at SQLPass last week. At the MSBIC reception.

Randy

Thanks for your example too. I never was able to get the FILTER to
work right and ended up going with Deepak's method ... but learned a
lot of MDX while I was at it.

Randy

Darren and Randy,

Glad it worked out - only seldom have I used Cousin().

Wow - where virtual and real (SQL PASS/MSBIC) worlds meet!


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Ok so I've used the above to get the Avg and StDev working, then
calculated a zScore based on the two + the actual measure. The last
thing I need to do is add a "measure" (not sure if that's what it
really is as it is not numeric) which will have a text representation
of the status based on the zScore (i.e. Good, Bad, Excellent,Expected,
etc.). I supposed I could do this in the report instead of the cube
but it would be nice to include in the cube structure if at all
possible.

Thanks for all your help.

Randy

Hi Randy,

A text calc. measure could be defined, like:

'iif([Measures].[zScore] < 0.5, "Bad",
iif([Measures].[zScore] < 0.7, "Expected",
"Good"))
If you're using AS 2005, you may also define a Key Performance Indicator
(KPI) that "wraps" the zScore:

http://msdn2.microsoft.com/en-us/lib...S,SQL.90).aspx

Key Performance Indicators (SSAS)

In business terminology, a key performance indicator (KPI) is a
quantifiable measurement for gauging business success. A KPI is
frequently evaluated over time. For example, the sales department of an
organization may use monthly gross profit as a key performance
indicator, but the human resources department of the same organization
may use quarterly employee turnover. Each is an example of a KPI.
Business executives frequently consume KPIs that are grouped together in
a business scorecard to obtain a quick and accurate historical summary
of business success.

In Microsoft SQL Server 2005 Analysis Services (SSAS), a key performance
indicator is a collection of calculations that are associated with a
measure group in a cube that are used to evaluate business success.
Typically, these calculations are a combination of Multidimensional
Expressions (MDX) expressions or calculated members. KPIs also have
additional metadata that provides information about how client
applications should display the results of the KPI's calculations.

One key advantage of KPIs in Analysis Services is that they are
server-based KPIs that are consumable by different client applications.
Server-based KPIs support a single version of the truth version separate
versions in separate client applications and take advantage to the
performance benefits of performing the sometimes complex calculations on
the server rather than on each client computer.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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