Microsoft KB Archive/301909

= INF: How to Overcome the 32 KB Limit for SQL Expressions =

Article ID: 301909

Article Last Modified on 2/21/2007

-

APPLIES TO


 * Microsoft SQL Server OLAP Services
 * Microsoft SQL Server 2000 Analysis Services

-



This article was previously published under Q301909



SUMMARY
Analysis Services limits the length of SQL expressions to 32 KB. Some applications require complex SQL Case expressions to define member names and keys, which would exceed the 32 KB limit. This article explains how you can work around the 32 KB limit.



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

Additional query words: VIEW DIMENSION OLAP BIHowto

Keywords: kbinfo KB301909

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.