When i am writing query im MDX using calculating members it takes the query
some time to run as i am runing the query on ms sql server. why? shouldent
the olap be fast?
thanks
Ruby

Could you provide more details of the problem you are trying to solve,
and of your cube design, because you already received generic answers in
an earlier thread:

http://groups-beta.google.com/group/...rver.olap/msg/
d55c70e33391eb73
First of all, OLAP only improves performance when complex aggregations
are
involved. RDBMS can hold its own quite well when no or simple
aggregations
are used. So it is indeed possible both queries take the same time, not
necessarily with correlation to the use of leaf members.

Second, in some cases, using OLAP not only does not improve performance,
it
also hinders it. One example that comes to mind is counting (or more
specifically, distinct counting).


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

hi Deepak Puri
You are right I wrote befor this question because i still dont get it.
i have 5 dimensions that 2 of the are big (more the 5,000,000) members.
when i am writhing query on MDX using calculated member it takes the the
server time to process it. those calculates members i am creating on the fly
on the mdx not on the design mode of the server because i get the query from
the user: for example the user choose to see a specific product of two kind
in specific day

with
member [Date].DateRange AS
'Aggregate ([Date].[All Date].[2005].[7].[12]:[Date].[ALL
Date].[2005].[7].[15]'
member [product].ProductType as
'Aggregate ({[Product].[All Product].[Food],[Product].[All
Product].[Electronics]})'
select
non empty {[Measures].allMembers} on columns,
non Empty {[Customer].[CustomerName].Members} on rows
Where ([Date].[DateRange],[Product].ProductType)

what the user asked for is ol the information about the customer between
dates and only in specific products.
this one takes the server almost 50 sec. is there a better way to do it?
thanks for your help
Ruby
"Deepak Puri" wrote:

Based on this info, I'm still not sure what improvements can be made,
but here are some further questions:

- Which 2 dimensions have 5 million members, and how deep are their
hierarchies? Hopefully, they don't have many Member Properties, which
could consume server memory.

- If there are calculated measures, are they empty when certain base
measures are empty, and has their "Non Mepty Behavior" Property been set
accordingly?

- Will very few of the customers typically return data in the MDX query
(ie. sparse data set) - in which case, maybe that can be exploited to
improve performance?

- Is the date range typically small or large (in the latter case, it may
be possible to leverage aggregations)?



- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi again Deepak Puri,
the two big dimensions are not on this mdx bus still i have problems with
them on other mdx queries. they both dont have calculated measures or
properties and both have 2 hierarchies the first one is grouping because of
the big amount of members and the second is the name.
about the date range every day ther are hundreds if not more of sales.
"Deepak Puri" wrote:

Maybe my questions weren't clear enough, but I'm not sure about your
answers for the last 2:

- Will very few of the customers typically return data in the MDX query
(ie. sparse data set) - in which case, maybe that can be exploited to
improve performance?

(So, how many Customers are there, and of these how many are typically
Non Empty in your MDX query?)


- Is the date range typically small or large (in the latter case, it may
be possible to leverage aggregations)?

(Is the range a few days, or a few hundred days..?)


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Almost all the customers return data and i have about 1,000,000 customers so
every query that shows the customer name will bring me back result set.for
this specific query (filtering the date with only two days) i get rtesult of
1000 customers only. the date range can be alot bigger and can be between
monthes or even years so finaly i will get huge data set.

"Deepak Puri" wrote:

A couple of things to try: using NonEmptyCrossJoin()may improve
performance for small date ranges, assuming that all measures are empty
when the base measures are:

Set [SelectedDates] as
'{[Date].[All Date].[2005].[7].[12]:[Date].[ALL
Date].[2005].[7].[15]}'
Member [Date].[DateRange] as
'Aggregate([[SelectedDates])'
Set [SelectedProducts] as
'{[Product].[All Product].[Food],
[Product].[All Product].[Electronics]}'
Member [product].ProductType as
'Aggregate([SelectedProducts])'
Select
non empty {[Measures].allMembers} on columns,
NonEmptyCrossJoin([Customer].[CustomerName].Members,
[SelectedDates], [SelectedProducts], 1) on rows
Where ([Date].[DateRange],[Product].ProductType)

For large date ranges, you can try to optimize the selected set to use
aggregations - see Chris Webb's blog:

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 ***