Need Help: MDX Query Fails/Hangs

[newtoolap]

We are having problem running the following MDX query:

With Member [Currency].[SelectedCurrency] as
'aggregate({[Currency].&[USD] })'
Member [CustomerType].[SelectedCustomerType] as
'aggregate({[CustomerType].&[99999],[CustomerType].&[04020],[CustomerType].&[05001],[CustomerType].&[09000],[CustomerType].&[04090],[CustomerType].&[06003],[CustomerType].&[04134],[CustomerType].&[05008],[CustomerType].&[05002],
[CustomerType].&[09001],[CustomerType].&[04010],[CustomerType].&[04081],[CustomerType].&[04133],
[CustomerType].&[06004],[CustomerType].&[05009],[CustomerType].&[08001],[CustomerType].&[05015],[CustomerType].&[04080],
[CustomerType].&[06001],[CustomerType].&[04142],[CustomerType].&[05000],[CustomerType].&[05006],[CustomerType].&[03000],
[CustomerType].&[08002],[CustomerType].&[04070],[CustomerType].&[05007],[CustomerType].&[05005],
[CustomerType].&[02000],[CustomerType].&[01003],[CustomerType].&[04060],[CustomerType].&[04130],[CustomerType].&[07002],
[CustomerType].&[04138],[CustomerType].&[05012],[CustomerType].&[04050],[CustomerType].&[05014],
[CustomerType].&[04120],[CustomerType].&[04137],[CustomerType].&[08000],[CustomerType].&[06000],[CustomerType].&[05003],
[CustomerType].&[09002],[CustomerType].&[01001],[CustomerType].&[04110],[CustomerType].&[07000],
[CustomerType].&[01000],[CustomerType].&[04136],[CustomerType].&[05010],[CustomerType].&[05004],[CustomerType].&[04030],
y[CustomerType].&[10000],[CustomerType].&[04100],[CustomerType].&[01002],[CustomerType].&[07001]})'
Member [BOTProductLine].[SelectedProd] as
'aggregate({[BOTProductLine].[Prod Line Id].[871],[BOTProductLine].[Prod Line Id].[872]})'
select {[BOTTenor].[Tenor Id].members} on columns,
non empty({[BOTRefNo].[Ref No Code].members})
DIMENSION PROPERTIES [BOTRefNo].[Ref No Code].[Contract ID],[BOTRefNo].[Ref No Code].[Name],
[BOTRefNo].[Ref No Code].[Ccy Id],[BOTRefNo].[Ref No Code].[Cur Principal],
[BOTRefNo].[Ref No Code].[Start Date],[BOTRefNo].[Ref No Code].[Mature Date]
on rows
From BOT
Where ([Measures].[Principal Re Payment] ,[Currency].[SelectedCurrency] ,
[CustomerType].[SelectedCustomerType], [BOTProductLine].[SelectedProd])

we execute the query using ado.net to read the result into datatable. It just hang and didn't give any feedback.
We are running analysis service (SP3) on pentium 4 2.0GHz + 1.5 GB RAM.
The query is supposed to return around 10,000+ rows
Basically the query will retrieve the contract's payment amount (and contract properties) for the selected customer types and products.
We are not sure what went wrong, is it too many properties or too many members . It just hang.
We don't have much time to rewrite the query or cube because of the tight deadline. the query work fine with small data.
Thank you in advance.

 

[AMJackson]

Have you tried executing the above query in the MDX Sample Application?
Does it work okay in there?

 

[MDXer]

If it works fine with a smaller data set but is having trouble with a larger dataset you could have issues with your aggregations, your system configuration, or the cube structure.

Some things to check

1) Perfmon the Analysis Services Query and look at
"Current Process Thread Queue Length" if the number in
this counter starts counting up quickly or maintains a
high que for any length of time you may need to config
more threads to Analysis Server.

2) Cube structure - Do you have a distinct count measure
in your cube? Is it in the same cube as other
measures? if so this could be a problem. Distinct count
measures interfere with the way your aggregations are
applied, resulting in many more aggregations than
needed and causing the service to read through many
more than needed. If so copy the cube and paste it back
into the same database name it something so you know
what it contains I usualy append Dist to the cube name
so I know it is a distinct count. Delete all measures
except the Distinct. Bring it and your other cube back
together into a virtual. This may sound like a lot of
work but it usualy takes a couple minutes to do and
then however long to process.

3) Aggregations - Have you applied the aggregations that
support this query if it worked with a small dataset
but takes forever with a large dataset it could be
becuase it lacks the needed aggs and has to then query
the full cube.



"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[newtoolap]

Thank you for the help. At the moment we decide to delete some dimension and will come back to this issue later and will try what you advise.

I think the following part might cause the problem:

non empty({[BOTRefNo].[Ref No Code].members})
DIMENSION PROPERTIES [BOTRefNo].[Ref No Code].[Contract ID],[BOTRefNo].[Ref No Code].[Name],
[BOTRefNo].[Ref No Code].[Ccy Id],[BOTRefNo].[Ref No Code].[Cur Principal],
[BOTRefNo].[Ref No Code].[Start Date],[BOTRefNo].[Ref No Code].[Mature Date]

See the [BOT RefNo] dimension has about 300,000 members and we use the RefNoCode which is a varchar(35) as a member key. We also store lots of properies (about 12) under this key.

Thanks again,
KJ

 

[MDXer]

yes this would be a problem. Try and use surogate keys that are of an int datatype. Also carrying this numner of dimension properties could be a bad thing both in query performance and usage of system resources. Remember every element of a all dimensions is read into and help in memory while Analysis Services is running.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck