MicroStrategy ONE
MDX Non Empty Optimization
The MDX Non Empty Optimization VLDB property determines how null values from an MDX cube source are ignored using the non-empty keyword when attributes from different hierarchies (dimensions) are included on the same MDX cube report.
You can choose from the following settings:
- No non-empty optimization (default): The non-empty keyword is not included during the cross join of data. By selecting this option, all null data is included on the MDX cube report. Including all null data can require more system resources to perform the necessary cross joins.
- Non-empty optimization, use default measure: The non-empty keyword is added to any required cross joins based on the default measure within the MDX cube source. Data is only displayed on an MDX cube report for rows in which the default measure within the MDX cube source has data.
- Non-empty optimization, use first measure on template: The non-empty keyword is added to any required cross joins based on the first metric used on an MDX cube report. Data is only displayed on an MDX cube report for rows in which the first metric used on an MDX cube report has data. For example, if Revenue and Profit metrics are on an MDX cube report and Revenue is in the first column (left-most column), the non-empty keyword is added based on the Revenue metric. In this scenario, null or empty data may still be returned for the Profit metric.
- Non-empty optimization, use all measures on template: The non-empty keyword is added to any required cross joins based on all metrics used on an MDX cube report. Data is only displayed on an MDX cube report for rows in which at least one of the metrics used on an MDX cube report has data. For example, Revenue and Profit metrics are on an MDX cube report, which includes the following data:
| Year | Category | Revenue | Profit |
|
2024 |
Books |
$1,000,000 |
$300,000 |
|
Electronics |
$2,500,000 |
|
|
|
Movies |
|
$500,000 |
|
|
Music |
|
|
By selecting this option, the following data would be returned on the MDX cube report:
| Year | Category | Revenue | Profit |
|
2024 |
Books |
$1,000,000 |
$300,000 |
|
Electronics |
$2,500,000 |
|
|
|
Movies |
|
$500,000 |
The Music row does not appear because all the metrics have null values.