Hi,

I have an MDX query which does everything I want it to do except ... I
need to be able to run it between to dates (but not split by each date
within the range, so this would equate to a WHERE in SQL). I have
looked thorough various sources, especially this group to find the best
way and the only way I have been able to find is along the lines of

WITH
MEMBER [AnnouncementDate].[PeriodAgg] as 'Aggregate({
[AnnouncementDate].[1999].[March].[18]:[AnnouncementDate].[2003].[March].[18]})'
....
WHERE ([AnnouncementDate].[PeriodAgg])

This does not seem to work (I get a row of cells with 'Error' as the
value of each Cell) but also it slows the query down from about 10
seconds to 15 minutes.

I played with a similar structure

WITH SET [PeriodAgg] as ({
[AnnouncementDate].[1999].[March].[18]:[AnnouncementDate].[2003].[March].[18]}
and although I never managed to get this to produce the results I
wanted (as I had to place PeriodAgg in the select statement) it also
massively increased the response time. I also tried using a TimeByDay
table (turning dates into a range of integer ID's) but this doesn't
seem to have increased the efficiency.

So my question is ...

A) What is the best way to convert SQL "WHERE [NonSelectedDate] BETWEEN
'01 Jan 2005' AND '26 Mar 2005'" to MDX?
B) If it is the above is there a way of improving efficiency?

Thanks in advance for any help offered!

Hi Ben,

There is not any good way to solve this problem. Only solution I would
recommend your is to build alternative hierarchies for some use cases. I
don't know any good solution for arbitrary time interval. The Power of the
MSAS is building of aggregates and it's using in queries.

The only way is to use aggregated values.

Thanks,
Vladimir Chtepa



"Ben" <gringogordo (AT) hotmail (DOT) com> schrieb im Newsbeitrag
news:1136821816.394616.124170 (AT) g47g2000cwa (DOT) googlegroups.com...

(A) In AS 2005, you can directly specify a set in an MDX where clause,
like:
WHERE {[NonSelectedDate].[2005].[Jan].[01]:
[NonSelectedDate].[2005].[Mar].[26]}


(B) As Vladimir noted, leveraging MS OLAP aggregations is important in
improving efficiency - one approach is discussed in Chris Webb's blog
below:

http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!
107.entry

http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!
111.entry


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi,

AS 2005 makes possible using sets in the WHERE clause, but under hood AS
2005 engine makes aggregation on the fly. It has better performance
comparing with AS 2000, that should have additoonal CM, that always
calculated on PTS side, but it is always slower as precalculated aggregates.

Vladimir Chtepa.

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> schrieb im Newsbeitrag
news:eM7QuNVFGHA.3348 (AT) TK2MSFTNGP15 (DOT) phx.gbl...

Thank you both very much for your answers. I'll probably put that
aspect on the back burner for a couple of days so that I can get the
rest working first but I just whanted to ask the rather stupid question
of what levereging means in this context. Does this mean making as few
'aggregations' as possible (4 quarters being quicker to compute than 12
months) or has it gone over my head completely?

Thanks again.

Hi, Ben

You are right.

[NonSelectedDate].[2004].[Apr].[01]:[NonSelectedDate].[2005].[Mar].[31] is
slowest
[NonSelectedDate].[2004].[Apr]:[NonSelectedDate].[2005].[Mar] is quite quick
[NonSelectedDate].[2004].[Q2]:[NonSelectedDate].[2005].[Q1] is quicker

Vladimir Chtepa

"Ben" <gringogordo (AT) hotmail (DOT) com> schrieb im Newsbeitrag
news:1136885282.533296.54420 (AT) z14g2000cwz (DOT) googlegroups.com...