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.
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.
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. This can only be done by architect level.
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.
The following are performance tweaks:
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 Constantinstead 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.
The next are not performance tweaks, but they can speed up development or bug fixing:
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.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
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.
No comments:
Post a Comment