Tag Archives: VLDB

Interview Question #9: Governing VLDB Properties

Question

Which of the following VLDB properties govern the length of a SQL string as well as the time a SQL pass takes to execute? (Select all that apply).

A.  SQL Time Out (Per Pass)

B.  Preserve All Lookup Table Elements

C.  Result Set Row Limit

D.  Maximum SQL/MDX Size

E.  Allow Index on Metric

Answer

Both

A.  SQL Time Out (Per Pass)

and

D.  Maximum SQL/MDX Size

 

Maximum SQL/MDX Size and SQL Time Out (Per Pass)

The Maximum SQL/MDX Size and SQL Time Out (Per Pass) VLDB Properties govern the length of a SQL string as well as the time a SQL pass can take to execute.

Maximum SQL/MDX Size sets the maximum size (in bytes), on a pass-by-pass basis, of the SQL that the ODBC driver sends to the warehouse. Or, in the case of MDX, it sets the maximum size of the MDX that is sent to multidimensional cube sources such as SAP BW, Hyperion Essbase, or Microsoft Analysis Services.

If a pass exceeds the limit, the report execution terminates and an error message displays:

I9-1

The possible value for this VLDB property is any valid integer. The default value is 0 (No limit).

SQL Time Out (Per Pass) sets the maximum duration allowed (in seconds) for each SQL pass (even intermediate passes). If any pass of SQL runs longer then its allocated time, the report fails and an error message displays:

I9-2

The value you enter to define this VLDB property must be an integer.

MicroStrategy Course Where You Will Learn About This Topic

MicroStrategy Engine Essentials Course

Tips & Tricks #8: MicroStrategy VLDB Properties Hierarchy

Very Large DataBase

VLDB is an acronym for Very Large DataBase. VLDB properties enable MicroStrategy products to take advantage of the unique optimizations that different databases offer. There are over 100 settings that affect how the MicroStrategy Engine handles functions such as:

  • Join options (star join, full outer join, and so forth)
  • Metric calculation options (null and zero check during division operations)
  • Query optimizations (subqueries and driving tables)
  • Table types (temporary or derived tables)

These settings, along with other settings, directly modify how the SQL Engine writes SQL against the warehouse.VLDB Hierarchy

VLDB Properties

The diagram above illustrates how VLDB properties that are set for one level will take precedence over those set for another level.

DBMS Level

The default value for every VLDB property is based on the database type of the warehouse and is written into the metadata at the time that you configure the project. MicroStrategy supplies the default values for the DBMS level, which are optimized for the database type and version.

The VLDB properties values are inherited from the DBMS object when you have not changed any VLDB properties (and all the Use default inherited value check boxes are selected for every VLDB property in the VLDB Properties Editor).

Database Instance Level/Project Level

In the MicroStrategy platform, the Database Instance level and Project level settings are merged together (or considered to be one overall level), because only one database instance can be applied for each project. Also, the Project level settings contain the Analytical Engine-related VLDB properties and the MDX VLDB properties.

Yield Red SignWhen you change a VLDB property setting at the database instance or project level, you must restart the Intelligence Server for the MicroStrategy Engine to read the latest schema information from the metadata.

Report Level

Properties you set at the report level override properties at every other level. For example, if a VLDB property is set to one particular value for a report and the same VLDB property is set to a different value for a metric on that report, the report-level setting takes precedence over the metric-level setting for that VLDB property.

Template Level

Values set at the template level override those at the metric, database instance, and DBMS levels.

Attribute/Transformation Level

There are only a select number of VLDB properties available within the Attribute Editor and the Transformation Editor. If you alter these properties, they automatically override the values for the same properties at the database instance and DBMS levels.

Use default inherited value Check Box 

By default, all objects in a project, including the project itself, have the Use default inherited value check box enabled for all of the VLDB properties (see screenshot below). As mentioned earlier, when you have not changed any VLDB properties and all the Use default inherited value check boxes are selected for every VLDB property in the VLDB Properties Editor, the values are inherited from the DBMS level. If you customize the settings for a VLDB property at  particular object’s level (whether it is a metric, template, project, etc.), any objects above it (in the VLDB Hierarchy) automatically accept the custom setting as the inherited value.

Tip 8-2

Example: Sub Query Type VLDB Property

As an example, let’s review the Sub Query Type VLDB property.

Suppose you define the Sub Query Type VLDB property at the template level to use temporary tables instead of sub queries. You create a new report using the template. When you view the Sub Query Type VLDB property at the report level, you notice that the same value (use Temporary Table) is automatically inherited at the report level. The report inherits the template’s VLDB value because the Use default inherited value check box is enabled (and the check box even lists “Template level” as the level from which the report inherited the custom value). This behavior of inheriting the custom value (as long as long as the Use default inherited value check box is enabled) occurs in a recursive fashion all the way to the DBMS object.

Tip8-3