Hi,

I have created 2 queries which provide the same result.
the first one use the where clause and the second a subcube clause.

using the where clause result in 23 seconds of process (cold cache) and 3
seconds (warm cache)
the subcube version always takes 23 seconds!

subcube ersion:
select {[Measures].[Count], [Measures].[% Time lost]} on 0,

non empty ([dimension1].[attribute1].members

* [dimension2].[attribute1].members

) having([Measures].[% Time Lost] > 0.9) on 1

from (select ({[dimension3].[hierarchy 1].[level 2].&[0]

:[dimension3].[hierarchy 1].[level 2].&[40]}

* -{[Time].[Year].&[2004]}

) on 0 from absences)



Where clause version

select {[Measures].[Count], [Measures].[% Time lost]} on 0,

non empty ([dimension1].[attribute1].members

* [dimension2].[attribute1].members

) having([Measures].[% Time Lost] > 0.9) on 1

from absences

where ({[dimension3].[hierarchy 1].[level 2].&[0]

:[dimension3].[hierarchy 1].[level 2].&[40]}

, -{[Time].[Year].&[2004]})



I'm doing this for test purpose, but I expect better performance.

Why the cache works better (or only) using the where clause?

where can I find more information about subcube queries?

(when to use andwhen to not use this feature)



thanks.



Jerome.

Please let us know if you figure anything out. I'm also seeing this same
issue. SSRS builds exclusively subcube queries from the graphical query
designer
By moving these into simple WHERE clauses sometimes I get drastic
improvements.
My theory was that the autoexists functionality between hierarchies of the
same dimension wasn't working correctly on subcube queries but I haven't been
able to prove it.

Thanks,
David

"Jeje" wrote:

Hi JeJe,

It is very interesting. I guess the subcube clasue doesn't cache very
well. My thinking is subcube clause could be very useful when generating a
generic query in the BI application (reporting services seem to always
generate this kind of query in the report wizard when you specify a parameter
or constraint since it is a simply appending action instead of modifing the
exsiting sets on axis which is very convoluted ).

And where clause should always outperformance subcube clause since it is
already resolve the subcube result and query should be easier to understand
and optimized by the OLAP engine.

"Jeje" wrote: