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!

[Chris_Kornely]

Nice blog Jose!

What options do you have when working with NULL’s when using a PaPM file adapter FID ( on premise) ?

The file adapter appears to be similar to option 2 In SAP HANA Database Explorer but is all handled by the system( generated).

In the last couple of weeks,  we upgraded PaPM ( on premise ) from SP11 to SP16 and now get a new error on running FIDs of type file adapter when there is a record in the file with a field = null.

Turns out starting in SP15 you can no longer import rows of data with the PaPM file adapter when there is a null unless you specifically handle the NULLs in a formula( ISNULL).

We were inconsistently handling NULLs using a formula , sometimes in the file adapter mapping and other times in a join FID. The latter started to fail with the error messages in your blog upon running any FID that executed a file adapter FID in the FID network flow.

My task now is to add the ISNULL formula to all fields in all file adapter type FIDs.

Please advise.

Many thanks!

Chris

[Rainier_Orduna]

Hi Chris,

On behalf of Noel, thanks a lot for your interest on the topic and for sending us your concern!

As one of the highlights in this blog post when it comes to the rationale of PaPM in treating NULL values, like what you have observed in your comparison of the present behavior from the previous in On Premise, it is because NULL values are not allowed in the application data which is intrinstric to SAP Finance applications - since they are not auditable.

As an option in the Mapping tab of your File Adapter function, you can maintain IFNULL(FIELDNAME,' ') for Characteristic fields or (IFNULL,0) for Key Figure fields like what I have done below:

IFNULL formula in File Adapter Mapping tab

This way, the fields containing such values will be initialized and will be displayed in your result.

Hope this helps. Have a good day!

Best regards,
Rainier

[Chris_Kornely]

Thanks Rainer,

this does help! IFNULL is shorter and cleaner than a CASE statement with IS NULL condition.

 

Chris