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
- Create a model from a Live Data Connection
- Identify a dimension that has null values, which we will call here [DIM01]
- 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) - 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
- 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.
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 , Story Design & Visualizations , Problem