You can use file reader OLAP DML commands to acquire data from external files in various formats: binary, packed decimal, or text. While you can use some of the file reader commands individually, it is best to place them in a program. You can thereby minimize mistakes in typing and test your commands on smaller sets of data. A program also enables you to perform operations in which several commands are used together to loop over many records in a file. Afterward, you can use the program to refresh your data.
About the File Reader Programs
Table describes the OLAP DML file reader commands.
OLAP DML File Reader Commands
Writing a Program for Reading Files
While reading from a file, you can format the data from each field individually, and use DML functions to process the information before assigning it to a workspace object. Reading a file generally involves the following steps.
The FILEREAD and FILEVIEW commands have the same attributes and can do the same processing on your data. However, they differ in important ways:
Example provides a template for a developing a file-reading program in the OLAP DML.
Template for Reading Flat FilesVARIABLE funit INTEGER "Define local variable for file handle TRAP ON CLEANUP "Divert processing on error to CLEANUP label funit = FILEOPEN('directory/datafile' READ) "Open the file"Read the file with FILEREAD FILEREAD funit . . . CLEANUP: "Cleanup label IF funit NE na "Close the file THEN FILECLOSE funit
Mapping Fields to Workspace Objects
The FILEREAD command maps fields to workspace objects. A source file can be structured with records in any of the following ways:
The target for the data in an analytic workspace is either a dimension, a relation, or a variable. Dimensions can either be maintained by adding new members, or they can be used just to align incoming data with existing dimension members. In standard form analytic workspaces, a variable is typically an attribute or a measure.
Reading Ruled Files
The basic syntax of FILEREAD for mapping the data in ruled files is:COLUMN n WIDTH n workspace_object
The following is an example of four records from a data file. From left to right, the columns are channels, products, customers, time periods, and units. The first column (channels) is 10 characters wide, and the other columns are 11 characters wide.2 13 51 54 2 2 13 51 56 2 2 13 51 57 2 2 13 51 58 2
The following FILEREAD command reads the data from the last column into the UNITS_VARIABLE variable, aligning the values in the other four columns with existing dimension members. The RULED keyword is optional, since it is the default record format.FILEREAD funit RULED - COLUMN 1 WIDTH 10 channel - COLUMN 11 WIDTH 11 product - COLUMN 22 WIDTH 11 customer - COLUMN 33 WIDTH 11 time - COLUMN 44 WIDTH 11 units_variable
Reading Structured PRN Files
The basic syntax in FILEREAD for mapping structured data is:FIELD n workspace_object
The same data file shown previously in "Reading Ruled Files" can be read with the following command:FILEREAD funit STRUCTURED - FIELD 1 channel - FIELD 2 product - FIELD 3 customer - FIELD 4 time - FIELD 5 units_variable
Reading CSV Files
The basic syntax for reading a CSV file is the same as for structured PRN files:FIELD n workspace_object
The following is an example of four records from a CSV file, in which a comma is the delimiter. The fields are the same as the previous data file shown in "Reading Ruled Files": channels, products, customers, time periods, and units.2,13,51,54,2 2,13,51,56,2 2,13,51,57,2 2,13,51,58,2
This file can be read with the following command; the DELIMITER clause is optional in this case, because a comma is the default delimiter.FILEREAD funit CSV DELIMITER ',' - FIELD 1 channel - FIELD 2 product - FIELD 3 customer - FIELD 4 time - FIELD 5 units_variable
Setting Dimension Status for Reading Measures
Whenever you read data values into a variable, you must set the status of each dimension. Typically, the incoming records contain a field for each dimension; when a record is processed in the analytic workspace, the dimensions are temporarily limited to these values so that data targeted at a variable or relation is stored in the correct cell. However, if the records omit one or more dimensions, then you must set them manually before reading the file.
For example, if your file contains data only for the Direct Sales channel for August 2003, and thus does not have fields specifying the channel or time, then your program must limit the CHANNEL and TIME dimensions before reading the file. Otherwise, the data is aligned with the first member of those dimensions (All Channels and Jan-98).
Optimizing a Data Load
Your data will load fastest if the variables in your analytic workspace are defined with fastest and slowest varying dimensions that match the order of records in the source data file. If you have control over the order of records in the source data file, then you can create the data file to match the variables in your analytic workspace. Otherwise, you may need to choose between optimizing for loads and optimizing for queries when defining the dimension order of variables in your analytic workspace.
For example, a data file might have records sorted in this order:
In a workspace variable definition, the order of the dimensions identifies the way the data is stored. The fastest-varying dimension is listed first, and the slowest-varying dimension is listed last.
For this sample file, the data load will proceed fastest if the target variable is defined with TIME as the fastest varying dimension and CHANNEL as the slowest varying dimension, so the dimensions are listed in this order: TIME PRODUCT CUSTOMER CHANNEL. With a composite dimension, the definition looks like this:DEFINE UNITS_VARIABLE VARIABLE DECIMAL <TIME UNITS_CUBE_COMPOSITE<CUSTOMER PRODUCT CHANNEL>>
Having the TIME dimension as the fastest varying dimension outside the composite also provides good run-time performance for time-based analysis, because the time periods are clustered together. This is a best-case scenario, in which the workspace variables are optimized for queries, and the data file is sorted correctly for the fastest loads.
However, if you have a separate data file for each time period, then TIME becomes the slowest-varying dimension for the load. In this case, there is a conflict between the dimension order that optimizes queries, and the dimension order that optimizes data loads. You need to choose which dimension order is best under these circumstances.
If you have a small batch window in which to load data, you may need to optimize for the data load by defining variables with TIME as the last dimension, as shown here:DEFINE UNITS_VARIABLE VARIABLE DECIMAL <UNITS_CUBE_COMPOSITE <CUSTOMER PRODUCT CHANNEL> TIME>
Reading and Maintaining Dimension Members
The records in a data file typically contain fields for dimension values that identify the cell in which the data values should be stored. When all of the dimension values in the file already exist in your analytic workspace, you can use the default attribute MATCH in the dimension field description. MATCH accepts only dimension values that already are in the analytic workspace.
When an incoming value does not match, the command signals an error. Your file reader program can handle the error by skipping the record and continuing processing, or by halting the processing and letting you check the validity of the data file. The test for whether the error is caused by a new dimension member or another type of error is based on the transient value of the ERRORNAME option when the error is signaled.
Example provides a template for error handling that permits processing to continue when a dimension value in the data file does not match a dimension value in the analytic workspace.
When your data file contains all new, or a mixture of new and existing dimension values, you can add the new values and all the associated data to the analytic workspace by using the APPEND attribute in the field description, as shown here:FILEREAD funit - COLUMN n APPEND WIDTH n dimension
Template for Skipping Records With New Dimension MembersVARIABLE funit INTEGER "Define local variable for file handle TRAP ON oops "Divert processing on error to oops label funit = FILEOPEN('directory/datafile' READ)"Open the file next: "Resume processing label FILEREAD funit "Read the file with FILEREAD . . . WHILE FILENEXT(funit) "Or read it with FILEVIEW DO FILEVIEW funit... DOEND FILECLOSE funit "Close the file RETURN "End normal processing oops: "Error label IF funit NE na AND ERRORNAME NE 'ATTN' THEN DO TRAP ON oops GOTO next "Resume processing at next label DOEND IF funit NE na "Close the file on error THEN FILECLOSE funit
Transforming Incoming Values
The FILEREAD command enables you to modify values as they are read into the analytic workspace.
To simply add characters before or after a value, use the LSET and RSET clauses. For example, if the incoming time periods are only the months (such as JAN, FEB, MAR), you can add the year before storing the values in the TIME dimension:FILEREAD funit - COLUMN 1 WIDTH 15 RSET '-04' time
For other transformations, you can use the FILEVIEW command or any of the data manipulation functions of the OLAP DML. The object of these manipulations is the keyword VALUE, which represents the value of the current field. In this example, the incoming values are converted to upper case:FILEREAD funit - COLUMN 1 WIDTH 15 time = UPCASE(VALUE)
Using Relations to Align Dimension Values
If you need to match existing dimension values and a simple transformation cannot create a match, then you can create a relation in the analytic workspace that correlates the two sets of values. Take these steps:
For example, if your Product dimension uses SKUs (stock keeping units) as dimension members, and you want to add data that uses bar codes, then you create a dimension for the bar codes and a relation between the bar codes and the SKUs of the Product dimension. You can populate the relation from a file that correlates bar codes and SKUs.
OLAP Related Interview Questions
|Informatica Interview Questions||Data Warehouse ETL Toolkit Interview Questions|
|PL/SQL Interview Questions||Data Warehousing Interview Questions|
|Testing Tools Interview Questions||SQL Database Interview Questions|
|MySQL Interview Questions||ERP Tools Interview Questions|
|Oracle 11g Interview Questions||Hyperion Financial Management Interview Questions|
|Hyperion Essbase 5 Interview Questions||Database Design Interview Questions|
|Data modeling Interview Questions||Oracle Hyperion Planning Interview Questions|
|Biztalk Esb Toolkit Interview Questions|
OLAP Related Practice Tests
|Informatica Practice Tests||PL/SQL Practice Tests|
|Data Warehousing Practice Tests||Testing Tools Practice Tests|
|SQL Database Practice Tests||MySQL Practice Tests|
|ERP Tools Practice Tests||Oracle 11g Practice Tests|
|Hyperion Financial Management Practice Tests||Hyperion Essbase 5 Practice Tests|
|Database Design Practice Tests|
The Multidimensional Data Model
The Sample Schema
Developing Java Applications For Olap
Defining A Logical Multidimensional Model
Creating An Analytic Workspace
Sql Access To Analytic Workspaces
Exploring A Standard Form Analytic Workspace
Adding Measures To A Standard Form Analytic Workspace
Predicting Future Performance
Acquiring Data From Other Sources
Administering Oracle Olap
Materialized Views For The Olap Api
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.