Storage Model in SSAS

Question

Hi ,

i query is that a cube has three types of Storage Structure MOLAP, RLOAP, and HLOAP. 

ROLAP storage mode allows the detail data and aggregations to be stored in the relational database.

now my question is where these all data is going to be store on RDBMS

1> does AS required new table to be created for storing Aggregations

2> is it system DB (MASTER, MSDB, ETC..) or any USER DEFINE DATABASE where these data will store, and

3> if this Data stored in RDBMS then is it mean that we may need more space for my Relational Database for storing all that extra Data. 

4>  does SSAS Create new table Structure for Storing these cube data.

in short if i say, i am completely confused with this thing that how data is storing in Relational DB.

please let me know with details.

many many thanks in advance.

Regards,

Shanu

---

Regards, Shanu

Answer 1

 

Shanu,

I have found the below information from the below link

http://msdn.microsoft.com/en-us/library/ms174915(v=sql.105).aspx

Also please look at the Microsoft white paper Analysis Services ROLAP for SQL Server Data Warehouses

The ROLAP storage mode causes the aggregations of the partition to be stored in indexed views in the relational database that was specified in the partition's data source. Unlike the MOLAP storage mode, ROLAP does not cause a copy of the source data to be stored in the Analysis Services data folders. Instead, when results cannot be derived from the query cache, the indexed views in the data source is accessed to answer queries. Query response is generally slower with ROLAP storage than with the MOLAP or HOLAP storage modes. Processing time is also typically slower with ROLAP. However, ROLAP enables users to view data in real time and can save storage space when you are working with large datasets that are infrequently queried, such as purely historical data.

 If a partition uses the ROLAP storage mode and its source data is stored in SQL Server Database Engine, Analysis Services tries to create indexed views to contain aggregations of the partition. If Analysis Services cannot create indexed views, it does not create aggregation tables. Although Analysis Services handles the session requirements for creating indexed views on SQL Server Database Engine, the following conditions must be met by the ROLAP partition and the tables in its schema in order for Analysis Services to create indexed views for aggregations:

• The partition cannot contain measures that use the Min or Max aggregate functions.
• Each table in the schema of the ROLAP partition must be used only one time. For example, the schema cannot contain [dbo].[address] AS "Customer Address" and [dbo].[address] AS "SalesRep Address".
• Each table must be a table, not a view.
• All table names in the partition's schema must be qualified with the owner name, for example, [dbo].[customer].
• All tables in the partition's schema must have the same owner; for example, you cannot have a FROM clause that references the tables [tk].[customer], [john].[store], and [dave].[sales_fact_2004].
• The source columns of the partition's measures must not be nullable.
• All tables used in the view must have been created with the following options set to ON:
  • ANSI_NULLS
  • QUOTED_IDENTIFIER
• The total size of the index key, in SQL Server Database Engine, cannot exceed 900 bytes. SQL Server Database Engine will assert this condition based on the fixed length key columns when the CREATE INDEX statement is processed. However, if there are variable length columns in the index key, SQL Server Database Engine will also assert this condition for every update to the base tables. Because different aggregations have different view definitions, ROLAP processing using indexed views can succeed or fail depending on the aggregation design.
• The session creating the indexed view must have the following options set to ON: ARITHABORT, CONCAT_NULL_YEILDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNING. This setting can be made in SQL Server Management Studio.
• The session creating the indexed view must have the following option set to OFF: NUMERIC_ROUNDABORT. This setting can be made in SQL Server Management Studio.

Tinto

Answer 2

ROLAP allows to store detail and aggregations in the relational database. True., but that doesn't mean new database objects are created to store this. 

Fact and dimension tables in a warehouse contains data. This is called detail data. When cubes are stored in ROLAP, no additional copies of the detail is created. For aggregations, SSAS will create indexed views on your data warehouse database (when applicable). More space might be required for these indexed views.

On a side note, ROLAP will be slow. I personally think, ROLAP should never be used. Main reason ROLAP should be considered is in situations where you have constant updates, but there are other alternatives for this.

Below link talks more about this:

http://blogs.msdn.com/b/sqlperf/archive/2007/02/12/storage-modes-in-analysis-services.aspx

---

Regards, Samuel Vanga Twitter Blog

Answer 3

Hi  Samuel,

Thanks for your kind response. but still i am having some doubts 

So as per your guidance i got that SSAS will access the Relational DB for Detail data and for Aggregation it will resolve the query using Indexed view created on SSAS Engine. is it correct , if not please le me know where i am making mistake again.

ok now if my first understanding is correct , then where and how(based on what considerations) the indexed views are creating in SSAS (if i am right) or  Relational DB . and can i query this indexed view like as we do in Relational DB or if this is creating at SSAS Engine can i check this in Directories as we can see folders/ files created for Partitions , dimensions on our Disk.

i am really sorry for my poor English 

all your assistance is highly appreciable.

many many thanks again

---

Regards, Shanu

Answer 4

 For aggregations, SSAS will create indexed views on your data warehouse database (when applicable). More space might be required for these indexed views.

as per MSDN http://msdn.microsoft.com/en-us/library/ms174915(v=sql.105).aspx

ROLAP

.............

…………..

………………

…….............

Although Analysis Services handles the session requirements for creating indexed views on SQL Server Database Engine, the following conditions must be met by the ROLAP partition and the tables in its schema in order for Analysis Services to create indexed views for aggregations:

• The partition cannot contain measures that use the Min or Max aggregate functions.
• Each table in the schema of the ROLAP partition must be used only one time. For example, the schema cannot contain [dbo].[address] AS "Customer Address" and [dbo].[address] AS "SalesRep Address".
• Each table must be a table, not a view.

…………..

……………

………..…

……………

So Samuel, in your post as you mentioned that Indexed view is created on Dataware house is not according to MSDN, So please let me know how to indexed view is meaintained and how an i check this

---

Regards, Shanu

Answer 5

Hi Shanu,

Just like Tino mentioned, there are some conditions should be meet before indexed views been created, if certain pre-conditons are met, SQL Server indexed views are created in relation database rather than SSAS server for ROLAP aggregation.

Thanks,

---

Challen Fu

TechNet Community Support

Answer 6

Hi Challen,

thanks for your response.

ok suppose all pre request meet up and the indexes created , so how can i check this , i mean with what name that index will create or what naming convention sql follows to create that index.

---

Regards, Shanu

Answer 7

My assumption (I could be wrong): These indexed views are session specific, you can't see them. Only the session that created them, in this case SSAS, can use these indexed views, and are dropped after the session is closed.

Anyway, can i ask you why you want to check them? Are you planning on using them for your other queries?

 

---

Regards, Samuel Vanga Twitter Blog

Answer 8

Hi Shanu,

Analysis Services created the indexed view based on a ROLAP aggregation design, actually Analysis Services is not involved in materializing these views or storing their result sets. Analysis Services delegates these tasks to the SQL Server relational engine by submitting CREATE VIEW and CREATE UNIQUE CLUSTERED INDEX commands to the data warehouse during cube partition processing. The relational engine then creates and maintains the indexed views in the database in much the same way as tables with a clustered index. Then you will find the view in the View folder in the relational database, you can see below image from SQL CAT 

For more information about ROLAP, please see the whitepaper from SQL Cat at this link http://sqlcat.com/sqlcat/b/whitepapers/archive/2010/08/23/analysis-services-rolap-for-sql-server-data-warehouses.aspx 

---

Challen Fu

TechNet Community Support