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

The following behavioroccurs in SAP Analytics Cloud (SAC):

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

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

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)

• 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

• 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

• 2569847 - Where can you find SAC user assistance (help) to use, configure, and operate it more effectively?
• Have a question? Ask it here and let our amazing SAP community help! Or reply and share your knowledge!
• 2487011 - What information do I need to provide when opening a case for SAP Analytics Cloud?
• Search for SAP Analytics Cloud content using Google or Bing:
• https://www.google.ca/search?q=site%3Ahttps%3A%2F%2Fapps.support.sap.com+SAP+Analytics+Cloud
• https://www.bing.com/search?q=site%3Ahttps%3A%2F%2Fapps.support.sap.com+SAP+Analytics+Cloud
• Note: Add relevant text or warning/error messages to the text search field to filter results.
• SAP Analytics Cloud > Learning > Data Connections
• SAP Analytics Cloud > Learning > Guided Playlists
• SAP Analytics Cloud > Learning > Guided Playlists > Getting Support
• Need More Help? Contact Support or visit the solution finder today!

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

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 , Story Design & Visualizations , Problem