IsNotApplicable (N/A) attribute and count measures in AX (2012) SSAS cubes

With Dynamics AX 2012, a new attribute shows in the AX SSAS cubes’ dimensions: IsNotApplicable (N/A) . The attribute has been added as part of the solution to prevent errors during the cubes processing that were due to missing relationship-keys, e.g., attribute keys, between the fact and dimension tables. Such errors and their logging had a significant impact on the performance of the cube processing.

The solution consists of adding an additional N/A row to the dimension tables that can be used for those records in the fact table that otherwise would miss (or have null) values for the field used in the relationship between the fact and the dimension table. This solution, however, has a side-effect on the cube’s count measures if the fact table for the measure is also a dimension table: the additional row added to the table will be counted and, hence, the count value will be off by 1 unit (per company).

To get the count right for the measure, the values need to be filtered by the IsNotApplicable attribute for the dimension whose source table is also the fact table of the count (or aggregated) measure.

The next example shows the Invoice amount (Customer invoice line amount – accounting currency) measure sliced by the Product category (Row Labels) dimension. The invoice lines that do not have associated a product category show under the “N/A” label.

A similar example, but for a count measure with the N/A filter applied is shown below: