Optimize Foodmart query

Milind

unread,

Jun 14, 2006, 6:20:07 AM6/14/06

to

Hi,

I have a query similar to the below Foodmart query. The below query
takes around 2 minutes to run. Can anyone make the execution time down
to less than 10 seconds?

With Set [TestExtract] as
'Generate(NonEmptyCrossJoin(
[Customers].[City].Members),
Generate(NonEmptyCrossJoin(
[Customers].Children),
Extract(Tail(NonEmptyCrossJoin(
{[Customers].CurrentMember},
[Time].[Month].Members,
[Promotions].[Promotion Name].Members)),
[Customers], [Promotions])))'

Select {[Measures].[Unit Sales]} on columns,
[TestExtract] on rows
from Sales
where [Product].[All Products].[Food]

Please help!

Thanks

Milind

Chris Webb

unread,

Jun 15, 2006, 9:43:02 AM6/15/06

to

Hi Milind,

Although your original query runs in around 30 seconds on my machine on
AS2K, the following version (which I'm fairly certain returns the same
results) runs in just a few seconds, so hopefully it'll do the trick for you:

With Set [TestExtract] as
'Extract(
Generate(NonEmptyCrossJoin([Customers].[City].MEMBERS),
Filter(
NonEmptyCrossJoin(
{[Customers].CurrentMember.CHILDREN},

[Time].[Month].Members,
[Promotions].[Promotion Name].Members

) as myset
, not(myset.current.item(0) is myset.item(rank(myset.current,
myset)).item(0)))
)
, [Customers], [Promotions])
'

Select {[Measures].[Unit Sales]} on columns,
[TestExtract] on rows
from Sales
where [Product].[All Products].[Food]

Rather than use the TAIL function to get the last Promotion for each
Customer, I've created a set of tuples of each Customer by Month and by
Promotion, then filtered it to only contain the tuples where the Customer is
different from the Customer in the next tuple in the set. I've used this
technique in the past and once again it seems to be significantly faster
here.

HTH,

Chris

--
I blog about Microsoft BI here:
http://spaces.msn.com/cwebbbi/
Do you need help with Analysis Services or MDX? I''m available for hire:
http://www.crossjoin.co.uk/

Milind

unread,

Jun 15, 2006, 11:33:16 AM6/15/06

to

My query which is similar to the Foodmart query below used to take 1
min 17 sec. With your mdx, it just got down to 4 sec!!!

Hats off to you, Chris!!!! Thanks alot

Milind

Chris Webb

unread,

Jun 15, 2006, 1:23:13 PM6/15/06

to

Interesting problem, and this advice is still relevant for AS2005 too. I've
just blogged about it here:
http://cwebbbi.spaces.msn.com/blog/cns!7B84B0F2C239489A!755.entry

Reply all

Reply to author

Forward