SAP Knowledge Base Article - Public

2979868 - Dimensions with Null values are not being processed in formulae of Calculated Dimensions in SAP Analytics Cloud (SAC) & SAP Digital Boardroom KBA Template

Symptom

The following behavioroccurs in SAP Analytics Cloud (SAC):

  • Dimensions with Null values are not being processed in formulae of Calculated Dimensions

Environment

  • SAP Analytics Cloud (Enterprise) 2020.21.1
  • Live Data Connection - HANA 2.0

Reproducing the Issue

  1. Create a model from a Live Data Connection
  2. Identify  a dimension that has null values, which we will call here [DIM01] 
  3. Create a calculated dimension with formula IF (ISNULL([DIM01]),  "***", [DIM01])
    this should display null values as "***", all other values as they are.
    However, the null values still come as (Null)
  4. Create anoter calculated dimension with formula   "***" + [DIM01]
    this should display null values as "***", all other values preceded by "***".
    However, the null values still come as (Null)

Cause

  • The behavior is caused by background limitation
  • Calculated dimensions like CD1=IF(ISNULL(D1), "A", "B") produce a pre-query result table that is then joined to the original data source using the dimensions/columns that are present in both the pre-query result and the original data source
  • When trying to execute a join on a column row that has a NULL value,  even though the pre-query result may have the expected value, the final data source will not match NULL during a join and those rows appear as (Null) in your table.

Example:

Pre-query Result

D1      CD1
null      "A"
"foo"    "B"
"bar"    "B"

Original Data Source

D1       M1
null      123
"foo"    234
"bar"    345

Final Data Source

D1       CD1      M1
null      null       123
"foo"     "B"       234
"bar"    "B"        345

Resolution

  • Create a calculated column in HANA, that replaces the Null dimensions with blank or other values that can be handled with a condition different than ISNULL()

    Example:

Create a Calculated Column CD1=IF(ModifiedD1="", "A", "B") and get the following result:

Pre-query Result

ModifiedD1       CD1
 ""                        "A"
"foo"                    "B"
"bar"                    "B"

Original Data Source

D1 Modified       D1         M1
null                     ""          123
"foo"                  "foo"      234
"bar"                  "bar"      345

Final Data Source

D1 Modified      D1     CD1      M1
null                    ""         "A"     123
"foo"                  "foo"    "B"     234
"bar"                  "bar"    "B"     345

See Also

Your feedback is important to help us improve our knowledge base.

Keywords

SAP Cloud for Planning, sc4p, c4p, cforp, cloudforplanning, Cloud for Analytics, Cloud4Analytics, CloudforAnalytics, Cloud 4 Planning, BOC, SAPBusinessObjectsCloud, BusinessObjectsCloud, BOBJcloud, BOCloud., SAC, SAP AC, Cloud-Analytics, CloudAnalytics, SAPCloudAnalytics,Error, Issue, System, Data, User, Unable, Access, Connection, Sac, Connector, Live, Acquisition, Up, Set, setup, Model, BW, Connect, Story, Tenant, Import, Failed, Using, Working, SAML, SSO, sapanalyticscloud, sap analytical cloud, sap analytical cloud, SAC, sap analyst cloud, connected, failure, stopped, null , KBA , LOD-ANA-DES , Model, Story Design & Visualizations , Problem

Product

SAP Analytics Cloud 1.0