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: NetezzaOracleSQL ServerTeradata.  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.

You may also like...

25 Responses

  1. srk says:

    thanks a lot !!

  2. Vitthal Kale says:

    How to find out? How many reports have special VLDB properties and how many of these use pre and post SQL?

    Thank you!!!

    • Bryan says:

      I don’t believe there is a way, since there’s no indication in the UI (other than opening each report and checking) and it would most likely be stored in the DSSOBJDEF2 table which is encoded (ie, not readable).

    • Vitthal Kale says:

      Actually we are migrating to Teradata from Netezza and wants to find out how many reports with special VLDB settings will be impacted.

    • Bryan says:

      The best way to do that impact analysis would be to use Integrity Manager and see if any reports throw errors. It’ll be a more comprehensive test anyway.

    • Raluca says:

      There is actually a way to enable logs and enable VLDB properties to display in the SQL view of a report in order to track how dynamic sourcing is being used. You can also use Enterprise Manager reports to see statistics info on dynamic sourcing activity to see how often it is being used.

    • Raluca says:

      In the Project Configuration Editor->Project Definition->Advanced->Analytical EngineVLDB properties->Configure->Enable cube parse log in SQL

      Make sure that the Tools->Advanced Settings are enabled before you can do the above procedure. Otherwise you won’t see the Enable cube parse log option.

      This setting will enable you to show, in the SQL view of every report that is being run, whether a Cube was used or not.

      You can find the same setting in the Report Editor as well, to enable the parse logs at the report level. Or you can do it at the project level as described above.

  3. Thanks a lot this post clarified all my questions.!

  4. Unknown says:

    Can we add NOLOCK in MSTR formed queries ??

    • Bryan says:

      Nope. The only way to accomplish this is to use a Logical View or Database View.

    • Unknown says:

      Thanks for the reply Bryan. Could you please give me some more details around it or any reference? I am a beginner in MSTR .

    • Bryan says:

      You can check out this post on Logical Views. Basically, a Logical View will generate a subquery in your report. Alternatively, you can perform this using a database view. The general idea is that instead of being able to do select * from table with (nolock), you have to do select * from (select * from table with (nolock)) r That middle subquery is what will be made by the Logical View, or by using a database view it would be select * from view where the view has the same definition.

    • A. says:

      Hi Bryan, first congrats for this blog, it’s an amazing resource for me. I have a question about this topic, which I can’t find an answer: how can we work with Teradata database views, if those views haven’t the columns datatypes in it. Is there any workaround? It must be, as Microstrategy and Teradata are partners :) thanks a lot! ;) Adriano.

  5. Unknown says:

    Hi Bryan,

    Can we change the color of selected bar in a graph to highlight it ?? If yes, then ho w?

  6. Hello Bryan,

    thanks for continues posting on important issues. Need to ask one thing about indexes on attributes. I am using mysql db, having one attribute(column) who’s length is more than 4000 characters thus showing error on report and want to disable creating indexes on it. Is it possible to disable indexing on particular attribute or their is some workaround for this.

  7. MFoltz says:

    Bryan,

    I’m looking into this VLBD property:
    Select/Insert -> Attribute Form Selection Option for Intermediate Passes

    I want my intermediate temp table to pull all attribute forms so that it only has to join the much smaller temp table to my fact table. For example I want the intermediate query to pull these forms:
    ID
    DESC
    SORT

    Currently, using the setting above I have the intermediate query pulling the ID and DESC forms, but it does not grab the SORT form. On final join to fact table the query is still joining to the lookup table to pull the SORT form.

    Any work around?

    Thanks,
    Mark

  8. sonu says:

    i have windows 8.1 and try to install microstrategy 9.1 or 9.4.1 but getting many problem can any one help me

  9. payal says:

    what properties need to be changed to insert left outer join in a query ?

  10. payal says:

    what are all the possible ways to avoid cross join in MSTR? can we avoid it using Fact Extension?

  11. payal says:

    What are MSTR ways of handling Slowly changing dimension ?

Leave a Reply

Your email address will not be published. Required fields are marked *

CAPTCHA Image

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current day month ye@r *