How to deal with imported Input Data's NULL values and consume it in SAP PaPM Cloud

[former_member639063]

Hello there! I will not bother you with some enticing introduction anymore and get straight to the point. If you are:

(a) Directed here because of my previous blog post SAP PaPM Cloud: Uploading Input Data Efficiently or;
(b) Redirected here because of a quick Google search result or what not...

Either way, you are curious on how a User could use a HANA Table with NULL values upon data import and consume this model in SAP Profitability and Performance Management Cloud (SAP PaPM Cloud). Then, I got you covered with this blog post. 

NOTE: Since I will not be explaining every detail pertaining to SAP PaPM Cloud and SAP HANA DB Explorer, I suggest to read through my previous blogpost before proceeding further. 

As a backstory: A Modeler was able to successfully consume an SAP HANA Table with the use of SAP PaPM Cloud's Connection Management and Model Table HANA function.

The SAP HANA Table as viewed in SAP HANA Database Explorer is shown below, notice that there are NULL values on each field:

Figure 1.

Connection and Model Table HANA function configured 

Figure 2.

However, upon trying to project or further enrich the data from the Model Table HANA using e.g., a View function. The execution of the view function returns an error: Processing messages due to NULL values which are present in the model and shown accordingly in the message logs:

Figure 3. 

Therefore, lines with NULL values will be disregarded in the Show screen results:

Figure 4.

Since SAP PaPM Cloud belongs to the group of SAP Finance applications and NULL values are not allowed in the application data, just like in S/4HANA. Mainly due to NULL values are not auditable and evaluable, in such: 

• If only one Key Figure (e.g. Cost Amount) in a million cost records has the value NULL, then no more sums can be formed, i.e. the costs can no longer be evaluated.


• If only one Characteristic (e.g. Profit Center) in a million balance records has the value NULL, then the balance sheet can no longer be audited and reported because the value NULL has no semantic meaning.

So, as the Modeler, how can I workaround this scenario?

I could think of two ways:

 

1. In Modeling: Model Join Auto-filling option
   Basically, the Modeler can introduce a Model Join function with the Auto-filling option enabled. Upon successful activation and execution, NULL data records will be initialized as confirmed in the Show results.
   My colleagues have published informative blog posts about Model Join  and Auto filling feature just in case you would want to learn more about these topics.


2. In SAP HANA Database Explorer
   You are still in SAP HANA Database Explorer after importing csv data and prefer a more technical approach on the scenario by interposing a HANA process or HANA view. 
   
   As a brief example:
   
   a) By setting an initial value:
   
   

   IFNULL(COST_AMOUNT,0) AS COST_AMOUNT

   
   b) By derivation:
   
   

   CASE WHEN IFNULL(PROFIT_CENTER) AND COMPANY_CODE = '0001' THEN 'PC1000' 
   
   WHEN IFNULL(PROFIT_CENTER) AND COMPANY_CODE = '0002' THEN 'PC2000' END AS PROFIT_CENTER 

   
    

Let's try the option A and use the HANA table with NULL values as an example in Figure 1 above.

1. From the SAP HANA DB Explorer, open the SQL console for the underlying SAP HANA Database of the SAP PaPM Cloud Tenant
   
   


2. SQL Console will appear. Since the SAP HANA Table fields' all have NULL values and our goal is to initialize all NULL values in the table.
   The following SQL statement should do the trick:
   
   

   CREATE VIEW "SAP_PAPM_ADMIN"."INITIALIZED_VIEW" AS SELECT  
   
   IFNULL(CH_CHAR,'') AS CH_CHAR,  
   
   IFNULL(CH_NUMC,'') AS CH_NUMC, 
   
   IFNULL(KF_DEC,0) AS KF_DEC, 
   
   IFNULL(KF_CURR,0) AS KF_CURR, 
   
   IFNULL(UN_CUKY,'') AS UN_CUKY, 
   
   IFNULL(KF_QUAN,0) AS KF_QUAN, 
   
   IFNULL(UN_UNIT,'') AS UN_UNIT 
   
   FROM "SAP_PAPM_ADMIN"."NULLTABLE"; 

   
   NOTE: This is just one of the ways on how to initialize NULL values, if you know a better way feel free to comment below 🙂


3. Choose Execute and Statement message logs should appear
   


4. Going back to the SAP PaPM Cloud application, choose the Menu > Administration > Connections


5. In the Connection Management, create a new connection using the Add button. Maintain the connection properties according to the SAP HANA View created and choose Confirm.
   
   


6. 1. Go the Modeling Environment you wish the SAP HANA View to be used.
   Create a Model View HANA View function by dragging its icon from the Palette to the Modeling diagram
   6.2. Maintain the Model View HANA View Function ID and Description as desired
   6.3. Specify the Connection Name and the configure the Field Mapping
   6.4. Create a View function and maintain the Model View HANA View as its input function.
   6.5. Save changes in the modeling
   6.6. With the View function chosen, click on Activate and Execute


7. Expand the Message logs, notice that the Processing messages due to NULL values as shown in Figure 3 above did not persist anymore after the function execution
   


8. Choose Show – as confirmed all data records are intact
   

After reading through my blog posts focusing on Uploading input data and consumption in SAP PaPM Cloud, I trust that these technical articles have helped you on your modeling journey. A like or share will be much appreciated! 😄

If you have questions, feedback or suggestions that will benefit the SAP community, don't hesitate to put them up in the comments section.  

Thank you for your time.

Cheers!