Hi Sorry again Posting may be second time is lucky....


Ok Consider the following Fact Table
EventID EventDuration [Other foreign-Key fields that link to dimension
members.....]
1 6
2 31
3 32

Now i have two measures on the corresponding cube
Event Duration->[Aggregate Function Sum(EventDuration)]
EVent Count->[Aggregate Function Count(EventID)]

What i want to accomplish is to apply aggregations only on records that
satisfy some simple (albiet arbitrary) filtering criteria.
For instance only those record are aggregate that have the
eventduration greater then 30,
I want some means to this do through an mdx query/function/[change in
design] but the filtering criteria has to be arbitrary or if the
comparison function is fixed then atleast the compariing value has to
be arbitrary.(i.e if i can only apply the greater then function then
atleast the value to be compared might be able to change)

Now the solution to the above problem(only partial or may be no
solution) that i am currently using is that i have added another field
in my fact table isGreater with values 1 when Event Duration > 30(or
some other CONSTANT) and value 0 otherwise,the solution is working but
the comparing value is hardcoded

Now one of the users (Milind) suggested a solution by using the filter
mdx function

e.g
SELECT [Measures].[EventCount] on COLUMNS,
{Filter({<Axis Specification>},
([Measures].[EventDuration]>30))} on ROWS

however what this does is that it first applies the aggregate function
and then performs filtering (I want the other way round) for example
for the above table the result from the above query is
EventCount EventDuration
3 69

however the result that i require is
Event Duration EventCount
63 2


Since only EventID 2 and 3 are greater then 30 hence
only they should be aggregated


Thank You for your support

What is the size of fact table, and are you using AS 2005? If there is a
degenerate (fact) dimension, then you could filter its members first,
then aggregate them. But the performance could be problematic for a
large fact table (since pre-built aggregations may not be usable).

For example, this Adventure Works query selects only Sales Order line
items > $1000, then aggregates across them. But it took 90 seconds to
execute on my AS 2005 server:

Orders].[FilteredLines] as
Aggregate(Filter(NonEmpty(
[Internet Sales Order Details].[Internet Sales Orders].[Internet Sales
Order].Members),
[Measures].[Internet Sales Amount] > 1000))
select {[Measures].[Internet Sales Amount]} on 0,
Non Empty [Customer].[Customer].[Customer].Members on 1
from [Adventure Works]
where ([Customer].[Customer Geography].[City].[Calgary],
[Internet Sales Order Details].[Internet Sales Orders].[FilteredLines])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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