SYMPTOM
In MicroStrategy Secure Enterprise 10.x, document/dashboard (VI) execution performance can be slow or memory consumption be higher when running a document/dashboard containing multiple datasets. Also, it is verified that the project and document are configured to enable the join of data from multiple datasets.
CAUSE
This issue can be seen in certain documents, where the datasets have multiple common related attributes (direct or indirect relations) and the engine tries to join all these datasets to get the complete result set.
ACTION
Starting with MicroStrategy 10.2 Secure Enterprise Platform, a new VLDB setting “MCE Join To Get Data Combination” has been introduced, that can help improve the document performance.
To access this VLDB, navigate to Project configuration>Project definition>Advanced>Project-Level VLDB settings>Configure the analytical engine settings, and click “Configure” as shown below:
The setting is located in the Metrics category as shown below:
There are two options: (By default, the first option is used)
- Use maximum relations to join attributes and get complete data combinations (higher memory usage).
- Use minimum relations to join attributes using mostly binary relations (faster, less memory usage).
The default setting is trying to get COMPLETE data for all possible datasets by checking all possible combinations from all dataset. This setting will possibly introduce higher memory usage and slow response time.
To help with the performance issue, users should switch to the second option (referred below as MinRelation). This can help achieve better performance by not building relationships between all the attributes in the same hierarchy.
NOTE: Changing this VLDB setting at the project level requires an Intelligence Server restart.
Starting with MicroStrategy version 10.3, this setting is also available at the document level and can be accessed from MicroStrategy Web, through the document editor as shown below.
In MicroStrategy versions 10.4 and later, this setting is also available for dashboards (VI) and can be accessed from the VI editor through the file > dashboard properties menu, as shown below:
The example below demonstrates the differences between the application of the two options of this VLDB setting.
Consider a document with three datasets as shown below:
The document template results with MinRelation disabled and enabled are give below:
- Use maximum relations to join attributes and get complete data combinations (higher memory usage).
- Use minimum relations to join attributes using mostly binary relations (faster, less memory usage).
When MinRelation is enabled, some relations for Year, Quarter, Month are lost. This is because MinRelation is under the assumption that all the datasets contain complete combinations of data. In the example, if the data for <Year, Month> in DS2 and <Quarter, Month> in DS3 is complete, joining these two relations on Month can generate complete combinations for <Year, Quarter, Month>. There is no need to join DS1.
With MinRelation, MCE just join the two binary relations <Year, Month> and <Quarter, Month> on common attribute Month. High level MCE trace as shown below:
select [Month]@[MONTH_ID],
[Month]@[MONTH_DESC],
[Year]@[YEAR_ID],
[Cost]
from DS2
to Ex0_tempcube0
select [Month]@[MONTH_ID],
[Month]@[MONTH_DESC],
[Quarter]@[QUARTER_ID],
[Quarter]@[QUARTER_DESC],
[Profit]
from DS3
to Ex0_tempcube1
select *
from Full Outer Join(
Ex0_tempcube0,
Ex0_tempcube1
) on Month
to Ex0_tempcube2
select [Month]@[MONTH_ID],
[Month]@[MONTH_DESC],
[Quarter]@[QUARTER_ID],
[Quarter]@[QUARTER_DESC],
[Year]@[YEAR_ID],
[Profit],
[Cost]
from Ex0_tempcube2
Without MinRelation, MCE will join all the three datasets to generate big relation < Year, Quarter, Month >. It will first use <Year, Month> in DS2 and <Quarter, Month> in DS3 to generate relationship of <Year, Quarter>.
Since the relations in the above example datasets are incomplete, MinRelation will result in some data loss. If the data in the datasets are complete, enabling MinRelation will not change the final result.