|Oracle9i OLAP Developer's Guide to the OLAP DML
Release 2 (9.2)
Part Number A95298-01
Reading Data from Files, 5 of 9
Data-reading programs read data from a file, record-by-record, and assign that data to variables, relations, dimensions, and composites in your analytic workspace. When the records in the file contain dimension values, you can limit dimensions to these values with the
FILEREAD command before assigning the data to a variable dimensioned by them.
Suppose you want to update unit sales data for the
product dimension in an analytic workspace. The new sales information is stored in a file called
units.dat, which has the layout shown in the following figure.
FILEREAD command that reads the sample
units.dat file is shown below.
This command is processed in these steps:
districtdimension is limited to the value read. When the value read is not a dimension value of
district, an error occurs.
productdimension is limited.
unitsvariable in the cell corresponding to the district and product read in Steps 1 and 2.
The full program, with commands to open and close the file, is shown next.
DEFINE readit1 PROGRAM LD Read a data file VARIABLE funit INTEGER TRAP ON error funit = FILEOPEN('olapfiles/units.dat' READ) FILEREAD funit - COLUMN 1 WIDTH 8 district - COLUMN 9 WIDTH 8 product - COLUMN 17 WIDTH 6 units FILECLOSE funit RETURN error: IF funit NE na THEN FILECLOSE funit END
You can also use the data-reading commands to read structured PRN files, which are produced by many PC software products. In a PRN file, quoted text or a series of numbers demarcated by spaces or commas constitutes a field of the record. Instead of specifying the column in which a field starts, you can use the
STRUCTURED keyword to specify that you are reading a structured file. You can also use one or more
FIELD keywords to indicate the number of the field you want to read.
Suppose you want to read sales data from the structured PRN file illustrated below.
010195 "TENTS" "BOSTON" 307 50808.96 010195 "TENTS" "ATLANTA" 279 46174.92 010195 "TENTS" "CHICAGO" 189 31279.78 010195 "TENTS" "DALLAS" 308 50974.46 010195 "TENTS" "DENVER" 215 35582.82 010195 "TENTS" "SEATTLE" 276 45678.41 010195 "CANOES" "BOSTON" 352 70489.44 010195 "CANOES" "ATLANTA" 281 56271.40 010195 "CANOES" "CHICAGO" 243 48661.74 010195 "CANOES" "DALLAS" 176 35244.72 010195 "CANOES" "DENVER" 222 44456.41 010195 "CANOES" "SEATTLE" 335 67085.12
The file has
product values in the second field,
district values in the third field, and sales data in the fifth field.
You can limit the
month dimension to the desired month, and then use the following command to read the sales data from the first six records in the file.