x

ENGINE MSOLAP SET TIMEOUT {nn|0}

where:

MSOLAP

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

nn

Is the number of seconds before a time-out occurs. 30 is the default value.

0

Represents an infinite period to wait for a response.

Note: If you do not specify a COMMANDTIMEOUT value, the current Microsoft SQL Server 2000 Analysis Services (MS OLAP) default timeout setting is used.

x

ENGINE MSOLAP SET NONBLOCK n [TIMEOUT m]

where:

n

Is the polling period in seconds.

m

Is the total timeout after which the query is terminated if it does not get completed.

Tip: You can change this setting from the Web Console by clicking Data Adapters in the navigation pane, clicking the name of a configured adapter, and choosing Change Settings from the menu. The Change Settings pane opens.

x

ENGINE MSOLAP SET EMPTY {ON|OFF}

where:

ON

Does not suppress rows, or report lines, without values.

OFF

Suppresses rows, or report lines, without values. This is the default.

Tip: You can change this setting from the Web Console by clicking Data Adapters in the navigation pane, clicking the name of a configured adapter, and choosing Change Settings from the menu. The Change Settings pane opens.

Top of page

Example: Activating EMPTY Mode

In the following request, EMPTY is set to OFF. Therefore, rows (report lines) with no data are suppressed.

TABLE FILE SALES
PRINT PROFIT
BY CITY
END

The output is:

City          Profit
----          ------
Albany        8,516.532900000
Altadena      3,345.876900000
Anacortes     956.535100000

With EMPTY set to ON, the rows (report lines) with no data are displayed.

City          Profit
----          ------
Acapulco                    .
Albany        8,516.532900000
Altadena      3,345.876900000
Anacortes       956.535100000

x

Used in conjunction with the SET EMPTY OFF command, the CROSSJOINOPTIMIZATION command usually significantly accelerates queries that involve two or more large dimensions. The syntax is

ENGINE SSAS SET CROSSJOINOPTIMIZATION {ON|OFF|NONEMPTY}

where:

ON

Optimizes queries by generating a MDX statement with CROSSJOINs of the sets of members of referenced dimensions on the ROWS axis instead of generating an MDX statement with each dimension on a separate axis. ON is the default value.

When SET EMPTY is OFF (the default), this technique reduces the amount of data passed from the SQL Analysis Services client to the adapter by delegating the screening of empty tuples of data to the Analysis Services engine. The reduction is especially dramatic when low dimension levels on many dimensions are involved. (If EMPTY is ON, this option will not reduce the amount of data passed from the client to the adapter.)

OFF

Suppresses CROSSJOIN optimization. This is provided for performance comparison and tuning purposes.

NONEMPTY

Optimizes queries by generating an MDX statement with NONEMPTYCROSSJOINs of the sets of referenced dimensions on the ROWS axis.

Note: The SET EMPTY command must be set to OFF (the default), to use this option.

Under certain conditions, the NONEMPTY option delivers the best performance for queries that reference low levels of multiple dimensions because these queries usually yield very high numbers of cells. However, NONEMPTYCROSSJOINs cannot be used if the dimensions contain calculated members. In such cases the resulting reports will be incorrect. (If this occurs, try the CROSSJOINOPTIMIZATION ON.)

Before using this option, refer to the description of the NONEMPTYCROSSJOIN function in the Microsoft Analysis Services documentation.

Tip: You can change this setting from the Web Console by clicking Data Adapters in the navigation pane, clicking the name of a configured adapter, and choosing Change Settings from the menu. The Change Settings pane opens.

Top of page

Example: Accelerating Query Performance Using CROSSJOINOPTIMIZATION

Suppose that you want to create an intermediate file containing the data aggregated on the lowest levels for the dimensions TIME, PRODUCT, CUSTOMER and STORE:

TABLE FILE SALES
  SUM Sales_Count Store_Sales Store_Cost Unit_Sales
     BY Month
     BY Name
     BY Store_Name
     BY Product_Name
     ON TABLE HOLD
END

Given the cardinalities of the involved levels: (24, 10281, 25 and 1560, correspondingly) this request would never be completed without CROSSJOIN optimization because Microsoft Analysis Services software would have to instantiate an astronomical number of cells: 9623016000. However, if you set CROSSJOINOPTIMIZATION to NONEMPTY the request finishes within 30 seconds, resulting in a HOLD file that contains 250983 records.

Top of page

Example: Combining EMPTY OFF With CROSSJOINOPTIMIZATION NONEMPTY

The SET EMPTY OFF command suppresses rows, or report lines, where all the selected measure columns have empty values. For example, in the following request,

TABLE FILE SALES 
  SUM Sales_Count Store_Sales Store_Cost Unit_Sales
     BY Month
     BY Name
     BY Store_Name
END

most users would prefer to see on the report only those combinations of stores and customers where Unit_Sales contains a number reflecting actual sales (EMPTY OFF), rather than seeing all possible combinations of stores and customers where the majority of report lines probably contain a 0 or a dot in the Unit_Sales column (EMPTY ON).

In EMPTY OFF mode, CROSSJOIN optimization delegates the selection of the non-empty rows to the database engine.

x

The SLICEROPTIMIZATION command usually significantly accelerates queries where some dimensions are referenced for tests only. The syntax is

ENGINE MSOLAP SET SLICEROPTIMIZATION {ON|OFF}

where:

ON

Optimizes queries by putting some member sets on the slicer axis and using the AGGREGATE function rather than retrieving them. As a result, much less data has to be read by the adapter.

OFF

Suppresses the optimization. This is provided for compatibility and performance comparison purposes. OFF is the default value.

Tip: You can change this setting from the Web Console by clicking Data Adapters in the navigation pane, clicking the name of a configured adapter, and choosing Change Settings from the menu. The Change Settings pane opens.

Top of page

Example: Accelerating Query Performance Using SLICEROPTIMIZATION

In the following request, the PRODUCT dimension is referenced for testing only, therefore the AGGREGATE of all the products containing 'Best' can be put on the slicer. As a result, much less data will have to be read by the adapter.

TABLE FILE SALES
WRITE SALES
BY YEAR 
WHERE PRODUCT_NAME LIKE 'Best%';
END

x

ENGINE MSOLAP SET CHECKAGGREGATION {ON|OFF}

where:

ON

Only allows verbs to be used that match the measure aggregator. This is the default.

OFF

Allows any verb to be used with any measure regardless of the measure aggregator.

Tip: You can change this setting from the Web Console by clicking Data Adapters in the navigation pane, clicking the name of a configured adapter, and choosing Change Settings from the menu. The Change Settings pane opens.