Indexed Views for ROLAP Partitions
If the storage mode of a partition is relational OLAP (ROLAP) and its source data is stored in Microsoft® SQL Server™ 2000, SQL Server 2000 Analysis Services attempts to create indexed views to contain aggregations of the partition. If Analysis Services cannot create indexed views, it automatically generates and uses aggregation tables instead of indexed views. While Analysis Services handles the session requirements for creating indexed views on SQL Server 2000, the creation and use of indexed views for aggregations requires the following conditions to be met by the ROLAP partition and the tables in its schema:
- The partition cannot contain measures that use the aggregate functions Min or Max.
- Each table in the schema of the ROLAP partition must be used only once. 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 FromClause like : "tk"."customer", "john"."store", or "dave"."sales_fact_1999".
- 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
- ANSI_NULLS
- The total size of the index key, in SQL Server 2000, cannot exceed 900 bytes. SQL Server 2000 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 2000 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 on: ARITHABORT, CONCAT_NULL_YEILDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNING. This setting can be made in SQL Server Enterprise Manager.
- The session creating the indexed view must have the following option off: NUMERIC_ROUNDABORT. This setting can be made in SQL Server Enterprise Manager.
For more information about the requirements of indexed views on SQL Server 2000, see Creating Indexed Views.