Dear all

I designed a cube with about 10 dimensions. Everything works fast as long as
I use hierarchies.

In one case I have to use all members of a dimension (about 1500). Then
querry performance gets worse.

Am I missing somthing here or is SSIS just a lot slower on the leaf level?

Thanks for your help!

Regards,

Marc

how the cube is aggregated?
do you have formula?
verify if the processing time is at the srever or client level.
(displaying 1500 rows is long)


"Marc" <Roger_Rombooth (AT) community (DOT) nospam> wrote

Hello Marc,

Nice to see you again in the community.:-)

As Jeje mentioned, you'd isolate this issue to server or client, and you
could use profiler to trace the query on server side including duration/cpu
time events. You may want to see if this take long time as you noticed.

Also, it is reasonable that server/client shall take more time to process
and render all members of a dimension (1500) and its aggregation with other
dimensions/measures.

Performance issues can be difficult to troubleshoot and resolve in a
newsgroup setting due to the number of variables and the amount of time
required to narrow down possible causes and observe the effects. We will
assist as best as we can, but you may wish to consider contacting CSS for a
more timely resolution for these type issues.

To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default...S;PHONENUMBERS

Please let's know if you have any update, and we look forward to your
reply.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Dear Peter and Jeje

thanks for answering. I had other priorities so I could not respond the last
week. Sorry. Fortunately this performance issue was not critical.

The processing for the 1500 members of one dimension and its aggregation
with oder dimensions/measures happens at server side and takes about 15 sec.
which is probabely still a good time.

I just was courious if there is a point where it is better/faster to query a
relational database rather then the OLAP DB when the request returns a lot of
rows.

Hopefully this question is not too silly... ;-)

Regards,

Marc

I would love to claim that olap is always faster; but that's not the
case.

Olap rocks though.. you shouldn't have a problem with 10 dims and 1500
members

are all your dimensions flat? it really comes down to-- how are you
querying.

its the query that is slow, not the cube


-Aaron


Marc wrote:

Hello Marc,

I agree with you 15 sec is still a good time for 1500 members of a
dimension. I think it shall be client side rendering takes too much time.

I understand that you'd like to know if a query request a lot of rows, if
it is more efficient to query the relational database directly.

I think There are some things to consider here. The first is that you shall
manage data source/data source view yourself when accessing rational
database directly. The datasource might be from different source such as
differnt OLTP servers/database/Web services, you do not have a consistent
view from user side. Also, when you do the same query again, it has to be
re-run and peformance is not improved by using the result of last running.

By using UDM (Unified Dimensional Modal) you shall have unified view from
user view and it could manage the data soruce/view properly. In the case of
proactive caching, queries initially sent to the UDM are answered by
querying the latest data in the data source. Meanwhile, a cache is built of
the data and aggregated data, and upon completion subsequent queries are
then answered much more rapidly from the cache. Upon a change to the data,
then upon the acceptable ‘latency’ time being passed, the cache is dropped,
queries are again directed to the data source, and a new cache is created.

Therefore, even if no aggregation information of OLAP DB is used when
performing a query from AS, it has similar performance as that querying
directly via OLTP database. If you do the same query again, the performance
shall be much better if you query from AS since it has cache.

You may want to refer to the following links for more related information:

http://sqljunkies.com/WebLog/tpagel/.../02/16311.aspx

http://www.microsoft.com/technet/pro.../solvngbp.mspx

http://msevents.microsoft.com/cui/We...ntID=103227310
1&EventCategory=5&culture=en-us&CountryCode=US

If you have further questions on the issue, please feel free to let's know.
Thank you! :-)

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


================================================== ===


This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Thanks, Peter!

Best regards,

Marc