I am planning on changing the storage model for a particular cube that
has no aggregation from MOLAP to ROLAP. I hear there is a querying
performance hit if I switch over to ROLAP. Can someone give me an
idea (in percentage) of what kind of performance hit I'm going to get
when the cube will be queried using the ROLAP storage model?

you'll see a drop of around 50-60%. Maybe more. It depends on the size of
your underlying database and the speed of your relational database server
(RAID etc).

The best thing is to set one up and mark the speed differential.




"Rico Cali" <ricocali (AT) hotmail (DOT) com> wrote

Wow, thats a considerable hit. Are saying that if it take 3 seconds
to normally query the cube for MOLAP that it will take 6 seconds or
more for a ROLAP configuration?

"Avi Perez" <avi.perez (AT) irisbi (DOT) com> wrote

a lot of elements can impact the performance.
Because ROLAP mode store aggregations in the database, so all your queries
are impacted by the speed of your database engine...
but...
thanks to the AS caching!

Because AS cache results in memory, so the other accesses to the same query,
or subset of already loaded data come from your memory!
so the response time, in this case, is the same as a MOLAP mode.

your database access can be very slow with complex queries which required a
lot of joins and filter in the database, so insure that your tables has a
lot of indexes.

ROLAP mode is recommended for huge cubes (more 300gb, see the T3 project on
the microsoft web site) , for real-time cubes or for historical data which
are never or less accessed by the users. I never use ROLAP mode except if
I've no choice. For example, I prefer to provide a MOLAP cube for the
current day in a (supply chain) proccessed every hour which takes 30sec
instead-of using a realtime cube. Why? because my queries takes 10sec. in
ROLAP mode and 1sec. in MOLAP mode...

So test some standard queries executed against your cube to evaluate the
performance and then take your final decision.

"Rico Cali" <ricocali (AT) hotmail (DOT) com> a écrit dans le message de news:
675e1895.0409061441.540ef15d (AT) po...OT) google.com...

"Jéjé" <willgart (AT) BBBhotmailAAA (DOT) com> wrote

....
T3 used pure MOLAP (though it had much more than 300GB of data) as do
the vast majority of real-world Analysis Services deployments. The main
reasons to use ROLAP are if there are very large dimensions, or you need
(near) real-time access.

Nigel,

When you say "very large dimensions", did you mean the number of
dimensions or the number of members in the dimensions?

Rico

"Nigel Pendse" <nigelp.nospam (AT) olapreport (DOT) com> wrote

This is discussed *EXTENSIVELY* in the Analysis Services Performance Guide.
http://www.microsoft.com/technet/pro.../ansvcspg.mspx
If you haven't read it, I strongly recommend that it is your next step.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Rico Cali" <ricocali (AT) hotmail (DOT) com> wrote