I have a large cube with 55 mill rows. and a large customer-dim with
4.5 mill rows
I have a sum measure named [sum DDD].
I also have a distinct count customer measure, but don't know how to
use this with the filter "sum DDD">200, so I'm using the dimension
customer instead. BUT I have to loop thought the 4.5 mill customer-dim
, and it is VERY slow.

I want to use MDX for this query

My example psaudo-SQL is:
select count(distinct customer),
from large_table
where time = jan2004
group by age
having sum(DDD) > 200 -- this is pr. customer, and the number vary from
query to query.

My sample MDX is working, but is very slow. Do you have any suggestion
to rewrite it?


with
set customer_more_than_200_total as
filter(customer.members,
(
measures.[sum ddd]
) > 200)

select {customer_more_than_200_total}
on 0,
age.members
on 1
from kube1

where ({time.january2004})


I'm running this on MSSQL server 2005

Tommy Strandvold

Just one correction: remove "with set.." and add "with member
count(..." in the MDX.

The correct MDX is

with
member measures.customer_more_than_200_total as count(
filter(customer.members,
(
measures.[sum ddd]
) > 200) )


select {measures.customer_more_than_200_total}
on 0,
age.members
on 1
from kube1


where ({time.january2004})

try something like this:
aggregate only the lower level of the customer dimension.
then try something like this:

count(
filter(
nonemptycrossjoin(customer.members, {measures.[sum ddd]},1)
,
(
measures.[sum ddd]
) > 200) )

the nonemptycrossjoin will create a set of only your january customers, then
apply the filter on it.
and also, verify that your connection string has these parameters:
cache ratio=0.01;cache ratio2=0.01;cache policy=7

I have not tested with a 4 millions dimension members, but I have 10 cubes
with distinct counts measures and combining the nonemptycrossjoin + cache
policy reduce the execution time from minutes to seconds.

I hope this will help you.

Jerome.


"Tommy" <tommstra (AT) online (DOT) no> wrote

I have tried all of your suggestion, and it did not improve the
response time at all. Have tried the connection-string properties and
crossjoin. The query uses 1 min for a simple "one-month". and several
min if you use multiple month. This is to slow for my users. The cube
is partition by month, but the time-consuming part here is looping
through the customer-dim.



I know of two ways to get the results.

1. Count the customer-dim with the filter I apply.
2. Use the distinct count measure I allready have defined.

I want to use my distinct count measures in the cube named:
measures.[distinct customer count], but I don't know how to use "having
measures.[sum ddd] > 200" on it.

I have rewritten it to:

with
set customer_more_than_x_total as
filter(nonemptycrossjoin(customer.members,
nonemptycrossjoin([time].[january2004],{measures.[sum ddd]},1),1)
,( measures.[sum ddd] ) > 200 )

member measures.more_than_x as
count(
filter(customer_more_than_x_total,measures.[sum ddd] > 200 )

)

This way, it decrease the customer dim first, then it loops through the
filtered dim for each dim in the select-list. If you don't use the with
set, it loops through the customer(4,5 mill) for each cell in the
resultset

Do any of you have any suggestion to improve this?
/Tommy

Have you tried using just the leaf level of the customers dimension?
Using "descendants(customer.CurrentMember,,LEAVES)" instead of
"customer.members". Unless customers is just a single level dimension,
this might help a little.

eg

with
set customer_more_than_x_total as
filter(nonemptycrossjoin(descendants(customer.Curr entMember,,LEAVES)
nonemptycrossjoin([time].[january2004],{measures.[sum ddd]},1),1)
,( measures.[sum ddd] ) > 200 )

member measures.more_than_x as
count(
filter(customer_more_than_x_total,measures.[sum ddd] > 200 )
)

Using usage based optimization will not help in this situation as UBO
does not look into calculated members. You might find that using the
aggregation design tool from the SQL Server resource kit will help with
performance. You might find that there are not many aggregations that
include the Customer and Month levels.

I had a large cube with one client where we got a few particular queries
down from a few minutes to under 10 seconds, but manually inserting
aggregations.

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

The customer-dim is a single-level-dim. With all-level and leaves-level
only. I have tried the descendants-function, but the result was the
same. I only use this dim for my distinct count calc-measure.

This is on AS2005. Is there a "aggregation design tool from the SQL
Server resource kit" here? And how can aggregation help me with a
distinct count measure. I thought that was imposible??

/Tommy

Yes it will be the same, idea behind using descendants was to exclude
redundant higher levels, but if you don't have any higher levels it will
produce the same results.

The aggregations might help with the filter on [Sum ddd] > 200, but you
are right in that they will help with the distinct count bit.

I don't know if there is anything that can design aggregations for
AS2k5, I vaguely remember hearing something, it may have been in the
beta news groups. I will see if I can dig it up.

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

Found it! I think the only way to do this in AS2k5 at present is to use
XMLA scripting. So it means dealing with XML, rather than a nice GUI.

If you go into SQL Management Studio (not the BI Developer Studio).
Establish a connection to AS2k5, you can right click on a database and
choose to create an "Alter Script". You will find a section in the XML
called <AggregationDesign>, you should be able to copy and paste an
existing aggregation and alter it to suit your needs.

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