The following article explains some query optimization methods that can be used for improving performance of your views. Complete article can be downloaded in PDF here:

 performance optimizations in nova view  256.57 Kb

Performance Optimizations in NovaView

 

The Optimize Huge Crossjoins Option

  This option is useful when you nest dimensions on the crosstab. It takes advantage of the NonEmptyCrossJoin (NECJ) or NonEmpty MDX function based on the global Settings you have applied.

 

NECJ Function.

The NECJ function can sometimes perform hundreds of times faster than the usual cross join. The idea behind this algorithm is to predict empty combinations and to exclude them from the cross join even before calculating. There is no performance gain at all if the crossjoin of two or more dimensions is completely full (even slight degradation can be noticed), however, this is not a common case. Usually a crossjoin of any two or more dimensions is very sparse.

 

As an example, let us imagine standard product and customer dimensions. The NECJ optimization does not contribute if all the customers have non-zero values for all the products (for the current slicing). It is clear that in most organizations customers usually buy only a subset of their products. Another example would be a crossjoin between customers and customer gender, a crossjoin that is half-empty.

 

In NovaView this optimization can be set per view. By default, it is disabled and can be turned on by checking the Optimize Huge Crossjoins option in the advanced properties of the grid. Remember to save the view after the change. See NovaView Desktop 5.0 Users Guide Access for more information.

 

The feature improves performance when the following conditions are large dimensions (and not measures) and are nested in the crosstab.

 

Calculated members are removed from the NECJ result by design (Microsoft SSAS). For example if you enable Subtotals in one of the dimensions, it will be removed from the result set if you enable optimize huge cross joins.

 

To overcome this limitation and if you are using SQL 2005, you can add a registry key called UseAlternateNECJ=1 (Can be only used with Nova view 5.0 April 2007 PF or higher, and with SQL 2005 only) which will use the MDX function NonEmpty instead of NECJ. This  key is a global setting and will  apply the changes to all the views. The June 2008 Planned Fix brings new option to still use the old NECJ for some views after adding UseAlternateNECJ.To activate this new ability:

 

In the view Annotations add the string  - /*UseOldNECJ=1*/

 

This will override the global registry setting of UseAlternateNECJ=1 for this view only, forcing ‘Optimize huge crossjoins’ to use NECJ instead of NonEmpty

 

This function returns the tuples in the first specified set that are non-empty when evaluated across the tuples in the second set. The NonEmpty function takes into account calculations and preserves duplicate tuples. If a second set is not provided, the expression is evaluated in the context of the current coordinates of the members of the attribute hierarchies and the measures in the cube.  You can find more information about this function in the following link

 

http://msdn.microsoft.com/en-us/library/ms145988.aspx

 

 

Optimize Sparse Dimensions

 

There are certain views that can be optimized with the Optimize Sparse Dimensions feature. The improvement is most noticeable when large sparse dimensions are in the grid (without nesting). This feature will be only useful when you only have that specific dimension in the cross tab (useful when you have a big flat Attribute/Hierarchy)

 

 

To enable this feature, move the dimension to the slicer bar, locate to its properties and enable the Optimize sparse dimension query in crosstab option. For more information, see Nova View Desktop 5.0 User’s Guide.

 

This feature can be applied per dimension in a view (applies to that specific dimension). Remember to save the view after the change.

 

The mechanism of this optimization is similar to the above-described NECJ optimization. It performs a NECJ between the selected dimension and the slicers. However, the Optimize Sparse Dimension feature does not require nesting. It “filters” the empty dimension members before running the actual view query. Thus, if only few dimension members appear in the resulting crosstab it is unnecessary to pre-calculate all the dimension coordinates, and the dimension can shrink down many times before the main query.

 

By default it uses the NECJ MDX function, so it has the same limitations as explained above (cannot be used with calculated members).  If you working with SQL 2005 and Have Nova view 5.0 with April 2007 PF, you have an option to use the NonEmpty function if required.

 

You will need to add a registry key called ReplaceNECJWithNE=1 and it forces all views to use the  NonEmpty MDX function when Optimize Sparse Dimensions is enabled.

 

 

Show Only Non Empty Members

 

This property is available in the Dimension properties (you will need to move it to the slicer to access the dimension properties). This option is very useful when you open a dimension in the slicer which has a huge number of members (flat dimension). It will remove the empty members from the set before it displays the dimension tree window.

 

It performs a NECJ between Dimension and the Axis dimensions.

 

If you are using  Novaview5  2007 April PF or higher, you can switch to the NonEmpty function instead of the NECJ. If you have the key ReplaceNECJWithNE=1, it forces the NonEmpty function to be used globally on all views.

 

So if you add the key ReplaceNECJWithNE=1 it applies to both Optimize Sparse Dimensions and Show Only Non Empty members.

 

 

 

Optimize Slicer Calculation

 

This option should be used only when selecting multiple members in a slicer, in order to optimize performance. Optimize Slicer Calculation does a NECJ for the slicers with the axes . By default we use the NECJ function. June 2008 Planned Fix gives us an option to use the NonEmpty function as described below for formulas

 

NOTE: The "Optimize Slicer Calculation" is an option only recommended for OLAP 2000, and not for 2005. For 2005 add a registry key called AllowSetInWhereClause=1, therefore whenever you select multiple members in the slicer, it adds the set to the Where Clause

Performance Improvements for Calculations (Cube or Panorama)

 

In SQL 2005 there is an ability to define calculated members with a Non Empty behavior for Non Empty and NECJ optimizations. Basically, it tells the OLAP engine to treat a calculated member as combination of other "real" fact table measures.

 

Currently there is no ability in SQL 2005 to define Non Empty Behavior for query scope calculations.

 

The UI for this is in Cube Definition/Calculation tab. You have a Non-Empty behavior field in which you can select relevant measures. So if your calculation is M1-M2/M3, it makes sense to select all three.

If you have Panorama Calculations you can use the NON_EMPTY_BEHAVIOR tag. For example if you have created a calculated member M1 +M2 in Nova view, please add the following Syntax.

M1+M2 /*NONEMPTY_BEHAVIOR={M1, M2}*/

 

M1, M2 are the measures used for the calculation, and the Non Empty behavior will be applied to them

• Panorama 5.0 and above
• SQL Analysis Services 2005 and above
• for UseAlternateNECJ and ReplaceNECJWithNE - April 2007 Planned Fix and newer
• for UseOldNECJ=1 and NONEMPTY_BEHAVIOR - June 2008 Planned Fix and newer