Hi MDX people,

I have this MDX script in a cube:

CREATE MEMBER CURRENTCUBE.[Time].[Year - Week - Date].[Last7Days]
AS AGGREGATE(LASTPERIODS( 7 ,Tail(Filter([Time].[Year - Week -
Date].[Date].Members, Not IsEmpty([Measures].[Cost])),1) .Item(0))),
VISIBLE = 1 ;

As a way of always having details for the last 7 days. This works in
most situations, but in queries such as:

SELECT {[Measures].[ProfitOrLoss] } ON COLUMNS ,

{Filter(NONEMPTY([CJ Advertiser].[CJ Advertiser].&[779922]* [Google Key
Word].[Google Key Word].Children*[Last7Days]),[[Measures].[Max
CPC-Changes] >1)}ON ROWS
FROM [Adwords V2]

Things just to grind to a halt. I imagine that if I have a relative
time dimension then meausures could be aggregated for this time period
during processing, but designing a relative time dimension seems
decidedly non-trivial.

Any help on such a dimension or optimising this member definition or
query would be very gratefully received.

Cheers,
Donovan Hide.

One obvious optimization to try is the use of NonEmpty() in lieu of
Filter(), as explained by Richard Tkachuk here:

http://sqlserveranalysisservices.com...ormance%20Hint
sv1.htm
...
Use NonEmpty Function

The NonEmpty function (new in SQL Server Analysis Services 2005) is
optimized for removing empty tuples. So instead of doing this to get the
customers who bought an Xbox,

Filter(Customer.Name.members, not IsEmpty( ([Measures].[Unit Sales],
[Product].[Name].[Xbox])

Do this:

NonEmpty (Customer.Name.members, ([Measures].[Unit Sales],
[Product].[Name].[Xbox]))
...

There is an entry in Chris Webb's blog discussing relative time:

http://spaces.msn.com/cwebbbi/blog/cns!7B84B0F2C239489A!654.entry
...

A comment on this entry references another article:

http://www.databasejournal.com/featu...le.php/3518771
Relative Time Periods in an Analysis Services Cube, Part II

By William Pearson
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,

thank you very much for helping with this problem. I tried your
optimisation tip, but no speed up was given. Richard mentions that the
NONEMPTY optimisation should occur automatically. The other two
suggestions seems variations on calculated members, which would still
involve the aggregate command. I think the problem is that the
Aggregate command does not give good performance over dynamic time
periods, especially when distinct count measures are used. The basic
problem that I have is how to represent these changing sets:

Yesterday,
Last 7 Days,
Last 30 Days,
Last 90 Days,

The time dimension extends forwards into the future, so I have to use
the last non empty date trick to get today.

The best solution seems to be to create another dimension from a table
separate to the time dimension table. This table has a stored procedure
that runs daily just before the cube and dimensions are processed,
which defines the memberships of the above sets.

The problem I'm having now is that these sets are overlapping, ie. Last
30 Days includes Last 7 Days and also Yesterday. This makes a hierarchy
a difficult thing to achieve. It's very confusing! I've been
investigating
http://www.geekswithblogs.net/darren...les/57811.aspx which
solves a similar problem using Many To Many Dimensions. Could anyone
work out a good way of solving this relative time problem using this
method?

Thanks again.
Donovan.