INF: How to Overcome the 32 KB Limit for SQL Expressions (301909)

MORE INFORMATION

To work around the 32 KB limit for SQL expressions:

1. Create views on the dimension table in the relational database.
2. Build the dimension from a view instead of from the physical table.

Building a dimension from a view instead of from the physical table is generally a good practice that offers the following advantages:

• Provides a layer of insulation between the relational database management system (RDBMS) and the OLAP Cubes, maximizing the likelihood that you can change one without rebuilding the other.
• Moves the definition of complex SQL queries from OLAP into the RDBMS, where it can be more easily seen, understood, and optimized.
• Changes column names once in the view definition rather than every time the cube is rebuilt in Analysis Manager.

To see the SQL expression that is generated when you build a dimension, highlight the SQL statement and click View Details in the Processing dialog box.

For the purpose of illustration, assume that a dimension table for products exists on the computer that is running SQL Server, which has the following definition:

CREATE TABLE PROD_INFO (PROD_KEY INT IDENTITY(1,1) NOT NULL, PROD_NAME  VARCHAR(25) NOT NULL, PART_NAME VARCHAR(3) NULL)
				

While the column names are meaningful to the database administrator (DBA), their significance and meaning may not be readily apparent to an end user.

To create a view of the data for use as the source for a products dimension, open Query Analyzer and run the following SQL script:

CREATE VIEW PRODUCT_DIMENSION AS 
   SELECT PROD_KEY AS PRODUCT_ID,
      PROD_NAME AS PRODUCT,
      PART_NAME AS PART
   FROM DBO.PROD_INFO