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.

Maps in Dossier

A map is a spatial detail which is composed of one or many layers. A layer is the way in which ESRI Maps for MicroStrategy visually represents geographic datasets.  A layer is similar to a legend item on a paper map. For example, on a road map, roads, national parks, political boundaries, and rivers might be considered different layers. When you add MicroStrategy business data to a map, Esri Maps for MicroStrategy creates a layer and displays it on the Contents pane. Once the layer is created, you can configure how it is styled, set its transparency, enable clustering or heat maps, turn on pop-up windows, and so on. 
 
You can add a Map visualization to your dossier, to display your data as map markers on an interactive map. You can use the visualization to:

• Filter the map markers shown in the visualization by selecting map markers for which to display data.
• Display map markers as static images in the visualization, or display the map markers as dynamic bubbles. If you choose to display the map markers as dynamic bubbles, the size of each dynamic bubble is automatically determined based on the value of the metric used to display the map markers, with the largest bubbles being displayed for the largest metric values.

In the image below, you can view different layers used to compare different attributes and metrics on the map.


In the image below, you can view clusters on various data points on the map.


Requirements:

• Attributes: 1 or more
• Metrics: 1 or more
• Geo Attribute: Latitude & Longitude
• Angle: 1 or more metric
• Color By:  1 or more metric

 
Minimum MicroStrategy version: 10.9
Publisher: MicroStrategy 

Installation Instructions:

• To view this dossier with the visualization, download the .mstr file below and deploy it in MicroStrategy Desktop or MicroStrategy Web.
• You can open a working demo by uploading the .mstr file below and doing the following:
• To open the demo visualization in MicroStrategy Desktop, double click the .mstr file.
• To open the demo visualization in MicroStrategy Web, choose Create -> Upload MicroStrategy File -> View Dossier.

Reference: MSTR Community

Dynamic Custom Group in Dashboard/Dossier

You can dynamically cluster attribute elements by using a table with range classification. This table can be imported from Excel file or can be a table in relational database

Let's assume you have a table that clusters your Employees by the Cost brackets. Based on those brackets a Rating is defined (like A, B, C...). This table can be imported from Excel file or from a table in relational database.
Please notice there is a Flag column that contains value '1' for each row. If you don't have this column in your data don't worry! Just use Upper column instead in the formula (see formula of 'Rating Metric' below).


Currently, your Dashboard/Dossier displays Cost per Employee, but it is missing Rating information.


As a first step import your Rating table. Import Rating as an attribute.


Create a metric 'Rating Metric' with the following formula:
Max(IF(((Cost>Lower)And(Cost<Upper)),Flag,0)){~+}


Create a grid that contains Employee, Rating (attribute) and Cost. The data comes from two unrelated data sets so you will get a cross join of data.


Add "Rating Metric" to Filter Pane and set a filter 'Greater than or Equal to" 1. You will see a proper Rating assignment and you don't even need to display 'Rating Metric" on the grid.


This assignment is dynamic, meaning that if you change the Rating brackets in your data set the new Rating rules will be applied automatically (assuming, you are using a live connection to a database).
You may need to change Level of your metrics if there are more attributes in your initial data set.

Dynamic Custom Group in Dashboard/Dossier

You can dynamically cluster attribute elements by using a table with range classification. This table can be imported from Excel file or can be a table in relational database

Let's assume you have a table that clusters your Employees by the Cost brackets. Based on those brackets a Rating is defined (like A, B, C...). This table can be imported from Excel file or from a table in relational database.
Please notice there is a Flag column that contains value '1' for each row. If you don't have this column in your data don't worry! Just use Upper column instead in the formula (see formula of 'Rating Metric' below).


Currently, your Dashboard/Dossier displays Cost per Employee, but it is missing Rating information.


As a first step import your Rating table. Import Rating as an attribute.


Create a metric 'Rating Metric' with the following formula:
Max(IF(((Cost>Lower)And(Cost<Upper)),Flag,0)){~+}


Create a grid that contains Employee, Rating (attribute) and Cost. The data comes from two unrelated data sets so you will get a cross join of data.


Add "Rating Metric" to Filter Pane and set a filter 'Greater than or Equal to" 1. You will see a proper Rating assignment and you don't even need to display 'Rating Metric" on the grid.


This assignment is dynamic, meaning that if you change the Rating brackets in your data set the new Rating rules will be applied automatically (assuming, you are using a live connection to a database).
You may need to change Level of your metrics if there are more attributes in your initial data set.

Change the Tooltip displayed on a Graph

Tooltips are only visible in Web. However, both MicroStrategy Developer and Web allow you to modify the tooltips displayed in a graph by using macros. These macros are dynamic text that is replaced with graph information when the tooltip is displayed in Graph View.

While the default macro used is {&TOOLTIP}, you can use different macros to customize what information appears in a given graph. You can use multiple macros in the same tooltip or combine static text and macros.

Some tooltip macros may not be available for use in specific types of graphs. For example, {&XVALUE} can only be used in Bubble charts or Scatter plots. If a macro is unavailable for the given graph type, no tooltip is displayed in the graph when viewed in Web.

The following table describes the macros you can use to customize graph tooltips in both MicroStrategy Developer and MicroStrategy Web: