We're having some issues converting data from SAP/ECC to our data warehouse.

For example, we have an SAP table T001, with a date field T001_FMHRDATE/YYMD that displays as a blank when reported on from SAP.
The field is marked mandatory (not MISSING) in the master file, but it's not mandatory according to the SAP documentation on https://www.se80.co.uk/saptabf...01/t001-fmhrdate.htm

When this field gets migrated to our data warehouse (on PostgreSQL), we see dates 1900/12/31 for that field, which is clearly incorrect and undesirable.

The field at the SAP side apparently does not use MISSING values, as adding WHERE T001_FMHRDATE IS MISSING; to a TABLE request on the SAP table, we get no results.
Storing the result in an ALPHA file, a hex-editor shows 8 spaces, but that also does not work in a TABLE request to find those same records in the SAP table.

I can create a COMPUTE X/YYMD MISSING ON SOME = IF T001_FMHRDATE EQ ' ' THEN MISSING ELSE T001_FMHRDATE; and that correctly transforms it to MISSING, but that is a pain to manually add to every SAP master.

I could also just ignore the problem and store those values as 1900/12/31 in our datawarehouse, but that will cause issues for reporting (and more importantly, self-service reports). I'd rather not pollute my data warehouse with incorrect data.

What is the origin of this problem? Does the SAP ABAP interface not present non-mandatory fields as such? Does the IBI SAP adapter ignore that information? What is actually stored in the database for those "MISSING" values that are not MISSING?

And, more importantly, what can be done about this?

We're between a rock and a hard place, it seems...

This message has been edited. Last edited by: FP Mod Chuck,

I found weird that your SAP table has a define Not Null date column that has null in it. Are you sure that it's the case ? According to what you've had check, it's not (to a TABLE request on the SAP table, we get no results). But Have you tried using a SELECT not IB TABLE file ?

From what I understand you SQL table does react the proper way. Since your date column is defined as Not Null, if a Null (or missing) value is "inserted" it default it to 1900/12/31 which is correct.
You may need to set the column as Nullable if you don't want to have 1900/12/31 automatically assigned by the RDBMS.

What is actually stored in the database for those "MISSING" values that are not MISSING? -- I would look at the SAP table definition itself from the RDBMS, not IB master file. Could be possible that you column is defined as Null and only the master file has the "mandatory" which has (if I remember well) no real impact on data property.

But I may all be wrong. I'm just thinking out loud...

I suppose I should have mentioned that we don't have direct access to the database. All our queries go through the ABAP layer.
As a result, we cannot verify what the actual database values or constraints are. That does not help...

Also, I'm pretty sure it's not the RDBMS that converts the value to 1900/12/31. More likely, it would throw an error, because the value violates the not null constraint.

I suspect that it's the adapter that converts the value (because the field is marked mandatory in the master, yet contains an empty value), but that also is an unknown.

I'm looking at data that passes through a couple of black boxes and (at least) one of them does something that is undesirable, if not outright incorrect. One of the reasons for asking here is to find out which black box is the culprit, the other is whether there's anything we can do about it.

Chances are we cannot, in which case we're probably looking at rather more data quality fixing than we were hoping for.

Some good suggestions, even if it only served to better define the problem

quote:

we don't have direct access to the database

Working as a blind man...

GL