Derived elements in MSTR

A derived element is a grouping of attribute elements on a report. Derived element groups are defined by a list, filter, or calculation. These groups provide a new view of report data for analysis and formatting purposes.

For example, the reports below contain Region, Category, and Profit. The report on the left does not display any derived elements. The report on the right displays derived elements defined using groups of Region attribute elements:

• East Coast: This derived element combines the data for the Mid-Atlantic, Northeast, and Southeast regions.
• West Coast: This derived element combines the data for the Northwest and Southwest regions.
• Central and South: This derived element combines the data for the Central and South regions.
• Web: This derived element displays the data for the Web attribute element, which is not included in any of the derived elements listed above.

You can do more than just simple combinations of attribute elements with derived elements. For example, after you have defined the East Coast derived element, you can determine the East Coast region’s percent contribution to profit, as shown in the last row of the report below.

This demonstrates only a fraction of the analysis and formatting capabilities of derived elements. With derived elements you can also create custom sort orders for attribute elements, use aggregation functions such as Average to combine attribute elements, and perform other analysis and formatting tasks.

Derived elements are evaluated on the report dataset without regenerating or re-executing SQL.

Cube performance best practices

You have to keep following things in mind while designing a cube

• Enable cube partitioning, the partition attribute should be the attribute with more cardinality in the cube (more cardinality means the attribute with more different elements, for example in the Tutorial project the attribute Item or Customer).
  Additionally, the number of partitions is based on the number of CPUs in the I-Server. It should always be the amount of CPUs divided by 2, for instance if there are 16 CPUs, the partitions should be 8.  More information about cube partitioning can be found here: https://www2.microstrategy.com/producthelp/10.8/InMemoryAnalytics/WebHelp/Lang_1033/Content/InMemoryAnalysis/Partitioning_cubes.htm  

• Under the VLDB settings for that cube, make sure that to increase the setting "Query Optimizations - Maximum Parallel Queries Per Report" to the same value that is determined in the partitions. For instance, if it is determined there are 8 partitions in the cube, then the value of this VLDB setting should be 8.

• Under VLDB settings > Query Optimizations > Data Population for Intelligent Cubes admins need to test all the following options:
  - Normalize Intelligent Cube data in the database.
  - Normalize Intelligent Cube data in the database using relationship tables.
  - Direct loading of dimensional data and filtered fact data.
  For this point admins need to try the three options and see which one has better performance.

Waterfall Chart

Visualize the cumulative effect of sequentially introduced positive and negative values. The waterfall chart uses columns and colors to show how an initial value is affected by a series of intermediate positive and negative values.

• The initial and final values are represented by whole columns, while the intermediate values are displayed as floating columns. If you enable the totals feature on the Properties panel, a third column is added representing the sum of the positive and negative values.
• The colors of the columns tell you whether the values are positive, negative, or totals. By default, green is used for positive values, red for negative values, and blue for totals. You can change colors in several ways - by applying thresholds to metrics, by using the Properties panel to choose a custom color for positive values, negative values, or totals, and by using the Properties panel to specify the color for a specific metric. If you use multiple methods to control colors, the order in which the colors are applied is first the default colors, then custom colors for positive and negative values, then a color for a specific metric, and finally threshold colors.
• You can customize the look of the visualization by removing the Y axis entirely and adding the labels inside the columns. The labels are placed at the inner top of columns that represent positive values and at the inner bottom of columns that represent negative values. For cases where you want to highlight differences in a specific portion of the data, you can use the Properties panel to start at a number other than zero. For example, this is a good option if one column is so large that the incremental difference makes other columns appear as lines.

Waterfall charts are frequently used to visualize financial statements or data about population, births and deaths. 

The waterfall chart shown below is based only on metrics. If you add an attribute, a succession of waterfalls is shown, with each waterfall representing the metric values for one element of the attribute. This is very useful for quick visual comparisons.

Additional features

• Supports showing the continuity of accumulated values over time
  To enable this feature, drag a time attribute and drop it in the Continuous drop zone on the Editor panel. When this feature is enabled, the starting point for each set of bars is the ending point for the previous set of bars.
• Supports showing totals
  To enable this feature, select the Show Totals checkbox in the Properties panel. The specified time dimension will be used to aggregate the totals. When this feature is enabled, a third column is added representing the sum of the positive and negative values. The color of this bar is determined by the value selected in the color picker for the Totals Bar Color in the Properties panel.
• Supports displaying horizontal lines
  To enable this feature, select the Show Horizontal Lines checkbox in the Properties panel. When this feature is enabled, horizontal lines are displayed at the level of each metric label on the Y-axis of the visualization.