x

SSAS Storage Mode

If I have 4 GB of analysis services database and my storage mode is MOLAP. Does is mean that my cube will have a copy of 4GB data from my source plus aggregations???

It is like having 8GB plus data on the server in case of MOLAP storage if my source DB also resides on the same server??

If yes, how about the performanance??
more ▼

asked Dec 28, 2011 at 01:06 AM in Default

sameer 1 gravatar image

sameer 1
23 1 1 2

Just got a hint on it while browsing for the answer... MOLAP Stores the detail and aggregate data in the OLAP server in a "compressed multidimensional format" as a result the cube browsing is fastest in this mode.

I'll be glad if anyone could explain it further with example as to compression limit and other setting available in MOLAP.

Dec 28, 2011 at 01:25 AM sameer 1
(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

As @usman Butt mentioned, MOLAP provides the best query performance on SSAS Server. On the other side it consumes most space as all the data are cached in the SSAS Database.

Anyway not all data must be necessary compressed. It depends on the kind of data being stored. SSAS first analyses the data and evaluates a compression ratio and based on this it compress or not the data. This evaluation is done for each attribute of a dimension and each partition in the SSAS database. So in final some data can be compressed and some note.

Other thing related to the pre-generated calculations. It is not true, that all calculation are pre-generated. Aggregation are pre-calculated only if aggregations are designed and only those designed are pre-calculated. It is even not possible to calculate all aggregations as in case of all aggregations should be calculated, then it will be a cross product of all leaf hierarchy levels of all attribute hierarchies + cross products of all higher hierarchy levels. In case of counting only leaf levels and let's say you have 10 dimensions and each dimension has only 10 leaf level members, then it will be 10^10 - 1 = 10 * 10 * 10 * 10 * 10 * 10 * 10 * 10 * 10 * 10 - 1 = 9999999999 aggregations.

The power of the performance of SSAS is in the the way the data are stored (physical model) and especially on the good attributes relationship and hierarchies design with combination of right aggregation design (logical model). In this way SSAS can use pre-aggregated values of lower levels of hierarchies to aggregate values of higher levels of hierarchies etc.

If you are really interested in the details and internals of SSAS you should definitely read the [Microsoft SQL Server 2008 Analysis Services Unleashed][1] book.

[1]: http://www.amazon.com/Microsoft-Server-Analysis-Services-Unleashed/dp/0672330016
more ▼

answered Dec 28, 2011 at 12:11 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

+1 from here.
Dec 28, 2011 at 10:26 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left
MOLAP provides most efficient query performance as compared to other storage modes, because all the required data, which includes the detail data and calculated aggregate data, is created as a completely offline data set and there is no need to refer to the underlying relational database. This is where you could have almost 8 GB data (but MOLAP data copy would be in compressed multidimensional format and should occupy less storage then Relational database data). Moreover, all the calculations are pre-generated when the cube is processed and stored locally on the OLAP server. Hence, even the complex calculations, as a part the query result, will be performed quickly.
more ▼

answered Dec 28, 2011 at 01:42 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x172
x30
x4

asked: Dec 28, 2011 at 01:06 AM

Seen: 1291 times

Last Updated: Dec 28, 2011 at 01:06 AM