Creating a custom unknown member in BISM Tabular

One nice feature that UDM / Multidimensional SSAS cubes have is that of being able to specify a custom name for the ‘unknown’ member – that is, the member automatically generated by the presence of foreign keys in a fact table that refer to members not present in the dimension table.

In Tabular, the default behavior is to assign a blank member. We can easily see this with an example:

DimProduct:

FactResellerSales:

Notice there is a ProductKey that is present in the fact table, yet missing in the dimension (ProductKey 216). This could be the case due to late arriving dimensions.  In any case, when pivoting this data, the default behavior is the following:

In order to assign a custom name to the unknown blank member, we must first assign a new unknown member to the dimension table and then generate a new calculated column in the fact table that will be used to establish the relationship.

For relational data sources, select the dimension table and then click on ‘Source Data’ on the properties window.  Be sure the drop down on the top right says ‘Query Editor’.  If not, switch from ‘Table Preview’ to ‘Query Editor’ in order to manipulate the query.  Then UNION the query with a row with ‘unknown’ values, for example:

We must delete the existing relationship as we won’t be using the same column to recreate the relationship with the custom unknown member.  Then, add a calculated column on the fact table that generates a lookup on the dimension and when unable to find a value, output a  “-1”: As the relationship won’t be there, we cannot leverage the RELATED function anymore, it must be done via a CALCULATE statement:

FactResellerSales[ProductKeyWithUnknown]

=IF( CALCULATE(
        COUNTROWS(DimProduct) ,
        FILTER(DimProduct,
        DimProduct[ProductKey] = FactResellerSales[ProductKey]) ) = 0,
    -1,
      CALCULATE(
        MAX(DimProduct[ProductKey]) ,
        FILTER(DimProduct,
        DimProduct[ProductKey] = FactResellerSales[ProductKey]) )
    )

Note that using MAX(DimProduct[ProductKey]) instead of VALUES(DimProduct[ProductKey]) as part of the calculation is necessary to avoid circular dependency errors when establishing the relationship. Having said that, we can go ahead and create this relationship between this new calculated column and the dimension key column.

That’s all.   Pivoting the data, should now look like this:

Though the approach may avoid the need for ETL in this kind of operation, a downside is that the Product Key is being stored twice.  For really big tables, it may be worth exploring the ETL option in order to keep memory as tight as possible and avoid the need to duplicate columns.