VLDB Properties for Metrics

VLDB Properties:

VLDB Properties facilitate to customize the SQL generated by Microstrategy engine. It also changes the way of Join characteristics and Query Optimizations which are managed by I-Server (Intelligence Server). VLDB Properties can be applied in many levels such as reports, template, project and metrics. In this post, let us see brief information on VLDB Properties in metrics level.

Steps to configure:

• Edit a metric which requires Query optimization.
• In Metric Editor, select the Tool Menu.
• In Tool menu, configure the VLDB Properties under Advanced settings.

1. Integer Constant

Integer Constant determines whether to add decimal for integer values. Based on the source database, we can accordingly toggle on or off the decimal values. Following are the options available,
– Add decimal
– Do not add decimal
– Use default inherited value

2. Join Type

Join Type provides what type of join condition to be used when the metric invoked.
Choices available are,
– Inner join (Rows which contains data for all the metrics are shown)
– Outer join (Rows are displayed when current metrics contains data)

3. Null Check

Null check handles the NULL values in any arithmetic operations. Generally, it has been handled by converting NULL to zero. Available options are,
– Do nothing (Null handling taken care by Analytical Engine)
– Check in all queries (Null has been handled after verifying all the query conditions)
– Check in temporary table join only (Handling Null in temporary table and not in passes involving database tables)
– Use default inherited value

4. Zero Check

Zero check handles the division by zero in division arithmetic operations. It converts the zero to null in denominator. Options available for zero check are,
– Do nothing (Divided by zero taken care by Analytical Engine)
– Check in all queries (Zero are converted to Null after considering all the queries)
– Check in temporary table join only (Divided by zero handled only in temporary tables created and not in passes involving database tables)
– Use default inherited value

5. Null checking for Analytical Engine

The NULL values are converted to zero and the calculations would be performed by Analytical Engine.
Options available are,
– False (null values are not converted to zero)
– True (the Analytical Engine converts null values to zeros)
– Use default inherited value

6. Subtotal Dimensionality Aware

SBA determines how the subtotal of columns should be. The level of the subtotal are considered by this property. Usually, subtotals depend on the metrics’ level. Available choices are,
– True (subtotal are calculated by considering the lowest hierarchy column)
– False (subtotal are calculated by not considering any specific hierarchy)