Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Data conversion issue with optional fields from SAP/ECC

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Data conversion issue with optional fields from SAP/ECC
 Login/Join
 
Virtuoso
posted
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,


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Virtuoso
posted Hide Post
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...


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Virtuoso
posted Hide Post
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 Wink


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Virtuoso
posted Hide Post
quote:
we don't have direct access to the database

Working as a blind man... Cool Frowner

GL


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Data conversion issue with optional fields from SAP/ECC

Copyright © 1996-2020 Information Builders