Customizing the SQL Server Analysis Services Environment

In this section:

The Adapter for SQL Server Analysis Services provides several parameters for customizing the environment and optimizing performance. This topic provides an overview of customization options.

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
x
Specifying a Timeout Limit

How to:

TIMEOUT specifies the number of seconds the adapter will wait for a response after it issues a request to SQL Server Analysis Services.

x
Syntax: How to Specify a Timeout Limit
ENGINE SSAS SET COMMANDTIMEOUT {nn|0}

where:

nn

Is the number of seconds before a timeout occurs. 30 is the default value.

0

Represents an infinite period to wait for response.

Note: If you do not specify a COMMANDTIMEOUT value, the current SQL Server Analysis Services default timeout setting is used.
Top of page
x
Activating NONBLOCK Mode and Issuing a TIMEOUT Limit

How to:

You can use the NONBLOCK command to prevent runaway queries. The Adapter for SQL Server Analysis Services uses non-blocking protocol for query execution.

TIMEOUT specifies the number of seconds the adapter will wait for a response after you issue an SQL request to SQL Server Analysis Services.

x
Syntax: How to Activate NONBLOCK Mode and Issue a TIMEOUT Limit
ENGINE SSAS 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.
Top of page
x
Retrieving Rows (Report Lines) Without Values

How to:

You can use the SET EMPTY command to display or suppress rows, or report lines, where all the selected measure columns have empty values.
x
Syntax: How to Control EMPTY Mode
ENGINE SSAS 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.

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 partial 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
Top of page
x
Including System Hierarchies in Synonyms

How to:

Some hierarchies are flagged as user-defined. By default, synonyms include fields only for user-defined hierarchies. To create synonyms that include fields for all hierarchies in a cube, issue the ENGINE SSAS SET SYSTEMHIERACHIES command in any supported profile.

You can also select this option when creating a synonym by checking the Include System-Enabled Hierarchies check box.

Note: You can redirect UDA references to attribute hierarchies without including them in the synonym using the SET USE_ATTRIBUTE_HIERARCHIES command. For more information, see Redirecting UDA References to Attribute Hierarchies.
x
Syntax: How to Include System Hierarchies in Synonyms
ENGINE SSAS SET SYSTEMHIERARCHIES {ON|OFF}

where:

ON

Includes fields for all hierarchies when creating a synonym.

OFF

Includes fields only for those hierarchies flagged as user-defined when creating a synonym. OFF is the default value.
Top of page
x
Using the MDX VISUALTOTALS Function

How to:

When reporting against SQL Server Analysis Services (SSAS) cubes, by default, the report output displays the measure values stored in the cube cells for members that are displayed on the report output. The values displayed are determined by the report selection criteria and dimension levels referenced in the sort phrases (BY and ACROSS) for level hierarchies, or explicit member selection for Parent/Child hierarchies and dimensions not referenced by sort phrases.

These values are aggregates resulting from rolling up measures along the cube dimensions when the cube was processed, and are based on rolling up all the underlying members of the hierarchy even if the report performs some selection on the levels below the displayed ones.

To retrieve data from a SSAS cube, the adapter translates the WebFOCUS request to MDX, the language that is used by the SSAS engine. SSAS then returns the requested cells with rolled up data.

For more information, see SQL Server Analysis Services (SSAS) Reporting With WebFOCUS.

Rollup using the MDX VISUALTOTALS function is a new SSAS Adapter feature that enables you to select members using WHERE/IF clauses (for level hierarchies) or WHEN clauses (for parent/child hierarchies), and have the SSAS engine recalculate the values for the displayed members based on the report selection criteria.

x
Syntax: How to Enable Rollup by Visual Totals

Visual Totals mode is enabled by the following setting that can be issued in a FOCEXEC or a server or user profile.

ENGINE SSAS SET ROLLUP_BY_VISUALTOTALS {ON|OFF}
               [FILE File ][HIERARCHY Hierarchy]

where:

ON

Uses the MDX VISUALTOTALS function to recalculate measure values so that, on the report output, they reflect only those members selected in the report request.

OFF

Retrieves values stored in the cube without having them recalculated based on the member selections in the report request.

File

Is a synonym for a SSAS cube.

For example, FILE Adventure_Works references a synonym with the name Adventure_Works.

Hierarchy

Is a hierarchy unique name.

For example, HIERARCHY '[Customer].[Customer Geography]' references the Customer Geography hierarchy by its technical unique name consisting of the name of the shared dimension Customer and the name of the hierarchy Customer Geography on that dimension. According to MDX syntax rules, the dimension and hierarchy names must be enclosed in delimiters and separated by a dot.

Several settings can be issued one after another and their actions will be combined to give the user ultimate flexibility.
x
Syntax: How to Query SSAS Adapter Settings

The following command displays all ROLLUP_BY_VISUALTOTALS settings in effect at the time it is issued.

ENGINE SSAS ?

For example:

(FOC11351) EXCLUSION OF EMPTY CELLS FROM REPORT - ON
(FOC11350) CROSSJOIN OPTIMIZATION - ON
(FOC11352) NONBLOCK OPTION - OFF, POLLING INTERVAL - N/A, TIMEOUT - 
default
(FOC11366) USE ATTRIBUTE HIERARCHIES - OFF
(FOC11367) THE FOLLOWING ROLLUP_BY_VISUALTOTALS SETTINGS ARE IN EFFECT:
(FOC11368)   OFF
(FOC11368)   ON FILE ADVENTURE_WORKS
Example: Sample SET ROLLUP_BY_VISUALTOTALS Commands

The following command turns visual totals ON for all hierarchies in all files:

ENGINE SSAS SET ROLLUP_BY_VISUALTOTALS ON

The following command turns visual totals OFF for all hierarchies in all files:

ENGINE SSAS SET ROLLUP_BY_VISUALTOTALS OFF

The following command turns visual totals ON for all hierarchies in the synonym Adventure_Works.

ENGINE SSAS SET ROLLUP_BY_VISUALTOTALS ON FILE Adventure_Works

The following command turns visual totals OFF for all hierarchies in the synonym Adventure_Works.

ENGINE SSAS SET ROLLUP_BY_VISUALTOTALS OFF FILE Adventure_Works

The following command turns visual totals ON for the hierarchy Customer Geography in all synonyms:

ENGINE SSAS SET ROLLUP_BY_VISUALTOTALS ON 
            HIERARCHY '[Customer].[Customer Geography]'

The following command turns visual totals OFF for the hierarchy Customer Geography in all synonyms:

ENGINE SSAS SET ROLLUP_BY_VISUALTOTALS OFF 
            HIERARCHY '[Customer].[Customer Geography]'

The following command turns visual totals ON for the hierarchy Customer Geography in the synonym Adventure_Works:

ENGINE SSAS SET ROLLUP_BY_VISUALTOTALS ON FILE Adventure_Works
            HIERARCHY '[Customer].[Customer Geography]'

The following command turns visual totals OFF for the hierarchy Customer Geography in the synonym Adventure_Works:

ENGINE SSAS SET ROLLUP_BY_VISUALTOTALS OFF FILE Adventure_Works
            HIERARCHY '[Customer].[Customer Geography]'

Suggestion: Issue settings with more detailed specification after more global settings in order to fine-tune the global settings. For example, if you want to turn on rollup by VISUALTOTALS for all hierarchies in all files except for the hierarchy [Scenario].[Scenario] issue the following sequence of commands:

ENGINE SSAS SET ROLLUP_BY_VISUALTOTALS ON 
ENGINE SSAS SET ROLLUP_BY_VISUALTOTALS OFF 
                HIERARCHY '[Scenario].[Scenario]'

In general, each SET command specifies a new ROLLUP_BY_VISUALTOTALS value for a certain scope of objects determined by the keywords used. Thus a subsequent command may cancel an action of a previously entered setting.

For example, either of the following commands remove all previous settings:

ENGINE SSAS SET ROLLUP_BY_VISUALTOTALS ON

or 

ENGINE SSAS SET ROLLUP_BY_VISUALTOTALS OFF
Top of page
x
Redirecting UDA References to Attribute Hierarchies

How to:

UDAs (User-Defined Attributes) are Properties that are represented by Master File fields with the attribute PROPERTY=ATTRIBUTE. They correspond to SSAS metadata entities defined in the MDSCHEMA_PROPERTIES Rowset for hierarchies and their levels. In the Business Management Studio, these entities are created as Attribute Relationships that link attributes comprising hierarchy levels to other related attributes. Examples of such properties/attribute relationships could be Color and Net Weight for Product, or Address, Phone Number and Gender for Customer.

By default, SSAS creates a separate attribute hierarchy for each dimension attribute. The members of these hierarchies contain aggregated values for distinct values of the attribute. Some of these aggregations might or might not have business sense. For example, aggregation of Sales Amount by customer gender might make sense, while aggregation by phone number does not.

Within the current implementation of the SSAS Adapter, a BY clause determines the level at which aggregated data is extracted from a cube. Therefore, if COLOR is a field referencing the Color UDA, the BY COLOR clause causes individual members of the Product hierarchy level to be extracted. It is equivalent to BY COLOR BY PRODUCT NOPRINT.

To see values aggregated by Color, you can reference the Master File field that was created for the corresponding level of the Color attribute hierarchy. However, this might be inconvenient for the following reasons:

If you issue the adapter SET USE_ATTRIBUTE_HIERARCHIES ON command, the adapter automatically interprets references to UDA fields as references to the corresponding attribute hierarchies. Therefore, although the attribute hierarchies are not included in the Master File, the adapter will use them to retrieve the aggregated measure values when a request references the UDA resulting from the attribute relationship created for this attribute.
x
Syntax: How to Redirect UDA References to Attribute Hierarchies
ENGINE SSAS SET USE_ATTRIBUTE_HIERARCHIES {ON|OFF}

where:

ON

Redirects UDA references to corresponding attribute hierarchies.

OFF

Does not redirect UDA references to attribute hierarchies. OFF is the default value.

Note: This setting applies only to Master Files created with level hierarchies.
Example: Effect of Redirecting UDA References to Attribute Hierarchies

In the Adventure_Works Master File, the Product dimension has a level named Product with a user-defined attribute (UDA) named Color. The following request sums Internet Sales Amount by Subcategory and Color. It does not mention Product: 

TABLE FILE ADVENTURE_WORKS 
WRITE INTERNET_SALES_AMOUNT 
BY SUBCATEGORY1 
BY COLOR 
WHERE SUBCATEGORY1 LIKE 'S%'
END

The setting USE_ATTRIBUTE_HIERARCHIES OFF (which is the default), results in the following report in which the values by Color are not aggregated along Product. A separate line is generated for each product (to see the product names, add a BY PRODUCT phrase after the BY COLOR phrase):

Subcategory        Color         Internet Sales Amount
-----------        -----         ---------------------
Shorts             Black                   $21,276.96
                                           $24,636.48
                                           $25,406.37
Socks              White                    $2,679.02
                                            $2,427.30

However, the setting USE_ATTRIBUTE_HIERARCHIES ON results in the following report in which the values for Color are automatically aggregated by Product:

Subcategory        Color         Internet Sales Amount
-----------        -----         ---------------------
Shorts             Black                   $71,319.81
Socks              White                    $5,106.32
Top of page
x
Accelerating Queries

How to:

The Adapter for SQL Server Analysis Services (SSAS) supports a number of modes of operation for accelerating the performance of queries that deal with large volumes of data, particularly those that require instantiation of large numbers of cells. Since the number of cells resulting from the query is equal to the product of cardinalities of member sets selected for each of the referenced dimensions, a very large number of cells can be involved.

These options may speed up or, under some circumstances, slow down particular queries. Since it is difficult to predict which approach will be most effective in a particular situation, it is useful to try various combinations of options for queries with slow response times to determine which produces the best performance results.

x
Syntax: How to Accelerate Query Performance Using CROSSJOINOPTIMIZATION

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.
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.
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.
Top of page
x
Adapter Functionality

The Adapter for SQL Server Analysis Services (SSAS) supports:
Example: Using SUM and PRINT Commands in a Report Request

In the following request, the PRINT verb is used, and SUM is the aggregator for STORE_COSTS. However, the value displayed is always the value stored in the cube or the specified measure at the intersection of the dimension values, regardless of the verb used in the request.

TABLE FILE SALES
PRINT Store_Costs
BY City
END

The correct request produces the same report output:

TABLE FILE SALES
SUM Store_Costs
BY City
END

The partial output is:

City          Store Cost
----          ----------
Albany        5,593.28
Altadena      2,239.71
Anacortes     641.93
Arcadia       2,045.73
Ballard       2,169.51
iWay Software