There are many data flows
picking up the data from "external database" based source systems in
BW. One of most used DB system is Oracle. To connect BW system to Oracle DB a
technology called DB
Connect is very often used. As Oracle and SAP are different
systems designed by two different companies there are many differences in there
while comparing each other. The differences are there as well as from date and
time related fields representation.
Therefore there are
challenges while integrating data from Oracle DBs into SAP BW. Especially when converting
date and time fields in Oracle and
into format compatible with SAP BW. Let's take an Oracle date format for
example. It has a format as DD-MMM-YY means
it has 7 characters (e.g. today's date is represented by: 03-AUG-15). Whereas
SAP format is 8 characters long in format YYYYMMDD
(see Data Element SYDATUM; Domain SYDATS or Data Type DATS). If we just simply
assign Oracle date field to SAP one it won't get proper data because the fields
are not compatible.
Now; how to solve this? On
SCN there are many discussions (e.g. here
or here) on
this. Most of it suggest parsing value as they come from Oracle and concatenate
it at the end to field in SAP format of date. However this is not proper
approach. The way how Oracle produce the format of the data field depends on national
language settings (NLS) used with the database connection. Therefore the date
field can come in many different flavors as per many configuration settings.
What actually needs to be done is to force Oracle to produce the date field in
desired format. This can be achieved by adding function TO_CHAR into e.g.
SELECT statement while we extracting the data. E.g. a separate view can be
created in Oracle to add TO_CHAR function. Such a SELECT statement would look
like:
select to_char(,'YYYYMMDD') as dat from
where
For more information see:
518241 -
DB Connect in BW for an external Oracle database
