New week, new article: Welcome back to #BestInMicro! A place where I will be writing about different topics related to MicroStrategy, its use, best practices, and functionalities to get the most out of the tool.
Today I will go into a topic that is a little more familiar, but at the same time very complex: VLDBs, a set of options as diverse as powerful, which can have a decisive impact when defining them in a project or a particular connection. But let’s go slowly…
VLDB is an acronym for “Very Large DataBase”. They allow you to modify the SQL statement generated by MicroStrategy’s SQL and analytical engine. That is, we can define the order in which operations are performed, optimization level, joins intermediate steps, Hints, and many more things that even go into advanced complexity.
Precise control of these options can have drastic effects on the tool’s performance (for better and for worse if not used correctly!), so today I’ll talk a bit about how they work, where and how you can configure them, and some of the most common settings to modify.
Well, this is an excellent question. This is an example that happened to me a few days ago: By architecture definition, a project has set the default cross between objects to be an inner join type, but for a specific requirement it needed to be an outer join type. While this can be changed at the indicator level, for example, it may have an impact on other reports, so it needs to be defined for this particular report.
Here we encounter a conflict: at the project level we have one definition, but at the report level we have another. Which one rules over which one? Is it possible to define it at different levels?
Yes, it is. There is a priority order for the VLDBs, which is described in the KB14830:
Going from the top-down, we start with the report level. This would be the most specific level. All preferences defined at this level (i.e. in each report), will overwrite those defined at lower levels (according to the graphic), but redundantly, they will affect only the report in question. The same applies to Templates. Except for defining a different VLDB property for a particular report, a template can be created to define certain properties that we want to define as a basis when creating a report, without these properties being applied to all new reports (or existing ones, of course!).
Continuing with the hierarchy, we come to the object-level properties. And I know this is where you will tell me: but Joaquin, in the technical note it clearly says “metric”! why do you say “object“? And this is where I explain that at this level not only indicators have VLDB properties, but there are also other objects such as attributes or transformations. So it is important to understand that sometimes adding an attribute to a report can change its behavior about how other attributes are calculated.
Then, we could say that almost simultaneously come Project and DBI. Digo esto porque solo se puede aplicar una instancia de base de datos principal para cada proyecto… pero dependiendo distintas configuraciones se puede aplicar una u otra DBI.
In addition, the project-level configuration contains VLDB properties related to the MDX reporting and analytical engine. And I add, as a colorful fact, that when modifying a VLDB at this level it is necessary to restart the IS for the changes to take effect.
The most basic DBMS (database) level where properties are defined. It sets the general standards. By default, they are defined by MicroStrategy, optimized for the database and its version.
By default, all properties are governed by the one at the top level. This is reflected in the checkbox “Use default inherited value for all VLDB properties”.
If the configuration for a VLDB property is edited at the level of a particular object (be it a metric, template, project, etc.), any object above it in the VLDB hierarchy automatically takes the custom configuration as the inherited value.
The truth is that it is not. There are hundreds of different possibilities within the VLDB properties. Many of them are even hidden by default to try to simplify the possibilities, but also to avoid that modifying without understanding what is being done can generate errors of greater impact.
On the other hand, we also do not have the same options if we define VLDBs at the report level as at the indicator level, for example.
For this reason, I recommend that you analyze well when editing VLDBs to define the bases of a project, or if they are more “exceptional” cases, and above all understand the impact that modifying them can have.
In the following, I will leave only some of the cases that I have encountered more frequently or that I have also found useful, but remember that, depending on the database, its architecture, the model, etc., what may be optimal in some cases may be very bad for others.
Pre/Post Statements: This property allows us to inject different sentences or lines of code when attacking the database, and it is distributed by levels, which allows us to say when this code will be executed. This allows us to generate customizations to the executed SQL and if you know where and what to use, you can end up with great performance advantages. Or it can also be as logging, to keep track of what is executed, by whom and analyze the impact it has on the warehouse.
For SQL databases we can add the SQL HINT to this functionality. Below is an example, but the options and variations are endless! How do you use it? Any suggestions?
Metric Join Type: this is one of the most “popular”, and its simplicity does not overshadow the impact it generates. It is always good practice to be clear about the best configuration and to define it. By default, it is set to Inner, but I have rarely kept this setting.
Default to metric Name: I have found this one useful when analyzing queries. It does not generate any change at the performance level, but it helps a little at the time of having to read queries. If we enable this option, the query will use the indicator name as an alias instead of WJXBFS (Color fact: these are initials of the original SQL Engine developers, according to Bryan Brandow’s blog).
Before:
Afterwards:
Cartesian Join Warning: This property is important if, for example, you have users who create reports without having a good understanding of how a query works internally. The fact of having different reports with Cross join can have very big impacts on the performance of the tool and the database, this is why it is a good practice to disable them, and in any case, enable them at report level for particular situations.
Well, wrapping up this week’s article. I invite you to look for more information in some sites that I leave below that served as a support for me not to leave anything important out and do not forget to leave me any question or comment, a topic that interests you that I write or that is relevant to you. See you next time!
* Tu información será utilizada exclusivamente para contactarte en relación al Business Data Master. No hacemos spam ni compartimos datos con terceros.
