VLDB Properties

Today’s post is at the request of @jayzzzz on Twitter.

VLDB (Very Large Database) Properties are settings that allow you to tweak some of the detailed behavior of MicroStrategy.  These settings control options on how the SQL Engine and Analytical Engine behave, and are necessary to address in every environment.  Precise control of these options can have dramatic effects on the performance of your reports, so today I’ll talk a little bit about how they work, where you can set them, and some of my favorite settings to tweak.

Recommended VLDB Properties for the Database

MicroStrategy publishes technical notes for nearly all of the certified Warehouse platforms that recommend the settings you should apply.  Some of the popular ones: Netezza, Oracle, SQL Server, Teradata.  For more, just search for “recommended vldb” on the MicroStrategy Knowledge Base.

In all of the places that you can modify VLDB Settings, you want to be sure that you’re seeing all of the available options.  From within any of the VLDB Property windows, enable Advanced Settings via the Tools -> Advanced Settings menu item.

If you want to compare environments, or make a note of your settings if you’re preparing an upgrade, there’s a handy little report tool from within the VLDB Property window.  Just select Tools -> VLDB Settings Report to have a simple text output of all of the settings.  You can choose to only show the settings that are different from system defaults to make it even easier to keep track of.

Places to Set VLDB Properties

Database Instance Level
This is the most common place to set VLDB Properties.  From the Project Configuration screen, choose Database Instances, select the Database Instance you want to configure, and click VLDB Properties.  Be careful though, because these settings are applied set as the defaults for every Report in every Project that uses this same Database Instance.

Attribute Level
There are a few settings that are available per Attribute that override the DBI level settings.  These settings are available from within an Attribute via the Tools -> VLDB Properties menu item.

Report Level
You can override the DBI level and attribute level settings for individual reports.  Not all of the same settings are available, but the majority are available at this level.  These settings are available from within a Report via the Data -> VLDB Properties menu item.

My Favorite VLDB Properties
These are just some settings that I always apply to my environment and are in addition to any recommended performance settings for my warehouse that I noted at the top.  As always, choose the settings that work best for your environment.  The best way to determine what those settings are is to test them!

Database Instance Level
Joins -> Cartesian Join Warning
The default option is to allow reports with Cross Joins to execute, but I find this to be a bad practice.  I prefer to change this setting at the DBI Level to option #3- Cancel Execution only when a warehouse table is involved in either side of the cartesian join.  The reason I use #3 over #2- Cancel Execution is because I do want to allow temp tables to cross join since that’s how the SQL Engine handles outer joining metrics in some cases.  However, there’s very rarely a legitimate reason to cross join warehouse tables.  Instead of allowing a report to run that will either waste system resources or give the user an incorrect result, I’d rather the report just fail immediately.  An example of a time where you would want to allow a warehouse table cross join is if you’re using the Report setting to Preserve Attribute Lookup Values.  In order to do this, the Report requires a cross join, so in those cases you can override the DBI Level setting by changing this property to option #1- Execute for that Report only.

Metrics -> Default to Metric Name
This setting is purely personal preference and has no impact on performance.  By default, this option is disabled which gives you metric aliases of WJXBFS (fun fact: these are the initials of some of the original SQL Engine developers).  This can make it very difficult to debug SQL, especially when using Multipass SQL.  Enabling this option will instead use the name of the metric as the alias.  Just consider the limitations of your database platform, as some have a limit on the number of characters that a column can contain.

Metrics -> Metric Join Type
By default, this is set to Inner Join, which means if you have 2 metrics from different passes, any attribute elements they don’t share in common will result in dropped rows.  Personally, I prefer Outer Join as the default here so that I can ensure that I am seeing all of the results.  There can be some cases where this has a negative performance impact, so on poorly performing reports, if I’m positive this won’t result in losing data, I’ll change this back to Inner Join at the Report Level.  I find that I very rarely have to do that though.

Query Optimization -> MD Partition Prequery Support
If you use Metadata Partition Mapping, I find that I get much better performance form the prequery using the option Use Constant instead of the default Use Count(*).

Query Optimization -> OLAP function support
This is a strange setting.  It doesn’t give much detail, but it says that it’s recommended to change in 9.0+ (even though it’s not on by default in 9.0+).  It says that the previous behavior could lead to incorrect subtotals, so I don’t see why you wouldn’t want to change this one.

Select/Insert -> Attribute Form Selection Option for Intermediate Passes
This option will allow for the Description forms of Attributes to be selected in individual passes instead of picked up at the end.  This can save the need for joins at the end and I’ve found that it increases performance in most situations.

Select/Insert -> Attribute Selection Option for Intermediate Pass
Same as above, this option will also grab any parent Attributes in the same pass as the data, instead of doing extra joins at the end to get those display values.

Select/Insert -> Custom Group Interaction With Report Filter
This option was new in 9.0 and controls whether the Report Filter is applied to the filters contained in a Custom Group.  I want this setting the majority of the time, and if there is a specific case when I don’t, then I can override this setting at the Report Level.

Attribute Level
There’s not much you can do at the Attribute Level, but you can generate Left Joins in some cases.  Refer to this post for more details on those VLDB Settings.

Report Level
Aside from tweaking settings for individual reports, there’s also some very valuable settings related to debugging Intelligent Cubes.  If Dynamic Sourcing isn’t working for you, you can enable 3 logging settings in the Report’s VLDB Properties under Dynamic Sourcing -> Enable xx Log in SQL View.  There is a hit to performance, so use this for debug purposes only.  When you run the report, a log will be shown at the bottom of the Report SQL that includes why the report did or didn’t choose a particular Intelligent Cube.