NonEmpty (PQL)

* Click on the function's arguments above for more details on the input values.

Comments

• The first list should be a list or set of elements from an existing hierarchy in the model.
• The second argument can be composed of 3 different types of items:
  • Another list of elements from the same model, These do NOT need to be from the same hierarchy - and usually are not.
  • A data point from the same model.
  • A 'list of lists' or "cross-joined" lists built from 2 or more hierarchies from the same model.
• If no measure is used in the second argument, the default measure for the model is used.
• For details on how to employ and use this function see the semantic calculation overview.

This function is like the MDX nonempty function; it retrieves the members of the specified list that have intersecting values with the items represented in the secondary list or tuple.

How it works

NonEmpty looks at the first list and removes any items that have no values based on the items in the second set of items (or list). In effect, it looks at each element in the first list, cross joined with elements in the second, and keeps only those that have values (i.e. are not empty). Because so many data models are sparsely populated with data, eliminating the 'empties' or the 'blanks' allows the engine to ignore irrelevant noise. This greatly reduces the number of relevant elements used in subsequent calculations, which improves server memory, speed, and overall calculation performance.

Using the grid below, Canadian Promotion Sales in 2008 contains a number of empty cells (yellow highlight). Although this is a small data set, if we wanted to find the top 5 promotions for 2008 Canadian sales, we would ordinarily consider 16 items in the list of promotions. By eliminating the 'non-empties' that list would drop to 10 items instead because 6 data points are empty and wouldn't even be factored into such a calculation. That is roughly a 33% reduction in the effort needed to complete that calculation. Given that sparsity in most data models can account for millions of empty data points, the impact is usually significant.

Examples

This example retrieves all the members in the email hierarchy that have a bike or clothing sales (sales is the default measure in this case, so it is used even though it is not specified):

NonEmpty( AllMembers([Customer].[emails]), { [products].[product category].[bikes], [products].[product category].[clothing] } )

This example retrieves all the members in the email hierarchy that have bike expenses:

NonEmpty( AllMembers([Customer].[emails]), ( [products].[product category].[bikes], [measures].[expenses]) )

This example retrieves all the members in the email hierarchy that have values across any discounted bike costs OR discounted clothing costs:

NonEmpty( AllMembers([Customer].[emails]), { [products].[product category].[bikes], [products].[product category].[clothing] } * { [Promotions].[promotion].[discounts]} * {[measures.cost]} )