Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
INF: OLAP: How to Index Star/Snowflake Schema Data
Article ID: 199132 - View products that this article applies to.
This article was previously published under Q199132
When preparing data to be moved into OLAP cubes, the indexing of the dimensions and fact tables in the base data is critically important to the performance of cube processing. This article is intended to give users of SQL Server OLAP Services an overview of this issue and a simple and effective starting point.
In MOLAP, ROLAP, or HOLAP, OLAP Services connects to the central data warehouse (which can be on SQL Server) like any other client application (through OLE DB) and sends a GROUP BY query to it. SQL Server parses the query and either sends the data back to OLAP Services (with MOLAP and HOLAP) or simply aggregates the data into new tables (with ROLAP). OLAP Services is just a client.
When a client application connects to SQL Server and performance is not what is expected, it is common practice to look at SQL Server and check how to optimize the query/schema for better performance. The same is true with OLAP Services. If performance is slow in retrieving data from SQL Server, something is probably wrong with the star/snowflake schema in SQL Server (or other heterogeneous data source), including schema design, indexing, key relationships, referential integrity, and insufficient data scrubbing. You should treat and optimize your schema as you would for any other client application.
The solution given below is only one possibility. As with all data projects, individual schemas and uses will determine the best solution for that project. This solution is meant as a starting place. The "OLAP and Data Warehouses" topic in the product documentation provides additional information regarding schemas, design of dimension tables, normalization, fact summarization, referential integrity, and indexes.
Before processing an OLAP Services cube, do the following:
Article ID: 199132 - Last Review: June 28, 2004 - Revision: 5.2