Representation of data values
Null values. Oracle implements a concept
of null values that does not commonly occur in mainframe applications
written in COBOL. A null value means that "data is not available."
Every data type in Oracle has a specific internal value that
represents a null value; for numeric data, a zero and a null are
not the same; for character data, all blanks and null are not
the same. If declarative integrity constraints are being used,
null values are not allowed in the primary keys of a table; although
a compound key can contain a data value that is all blank, it
cannot contain a column that has a null value.
Some mainframe applications do not distinguish between a null
value and a "default value." For example, a field that
is initialized to all zeros and then over-written if actual data
is available must be interpreted very carefully when it is represented
on the data warehouse.
Dates. All dates in the data warehouse will
be stored as Oracle dates (which can be displayed in many different
formats according to a user's needs). Internally, Oracle stores
the time of day with every date.
Dates must go through a date-editing routine in the extract process
to assure that illegal dates (e.g., February 30th) are not submitted
to Oracle for loading.
When the mainframe application stores the date and a time of
day, the data from the date and the time fields will be collapsed
into one Oracle column.
Full dates from a mainframe application system are stored as
the date and time as of midnight of the date given on the mainframe
(i.e. the zeroth minute or the beginning of the day).
When a mainframe application stores only the year and month,
the corresponding date in the warehouse will be the Oracle date
either of the first or last day of that month. The meaning of
the data element should determine which of the two approaches
is used. This calculation should be noted in the warehouse documentation.
When the mainframe application only records a year, the warehouse
will show the year as a number.
Other characters. All other characters
will be translated from the EBCDIC to the ASCII equivalent. The
two character sets do not really match completely, but the characters
that are used on the mainframe (the alphabetic and numeric characters)
all do have an ASCII equivalent.
Dates with illegal months will be shown as null in the warehouse.
Dates with legal months and years but illegal days will be handled
consistently: the 0th day of the month will round up to 1 and
large numbers will be rounded down to the last day of that month.
Special dates such as '000000' on the mainframe will be represented
as null values in the warehouse.
Metadata should indicate data elements that are inconsistently
used (e.g. used by only one campus, etc.).