asked
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??
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.
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
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.
