Text Data Sources

Unknown macro: {scrollbar}

Text Data Sources allow the user to obtain information from text files. These files must contain a row for each record, and columns for each of the fields corresponding to the record. Access to these Data Sources provides a mean of extracting data from any system without direct access via JDBC/ODBC.

This section includes:

Basic steps needed to add a new Text Data Source to a model.
A description of the Properties Pane with its different sections (Description, General, Fields and InputSets).
Steps needed to fully define a Text Data Source.

Defining Text Data Sources

Use the Add File command or the Insert File command to define a text data source.

To define Text Data Sources, you must be on the File System branch within Sources, or on any of the Text Data Sources already defined.

To define a text data source:

Select the Sources branch in the Design tree.
Select the Text Data Source branch.
Add a new data source using one of the two methods:
- To add a new data source at the end of the list, choose the Add File command.
  The data source is added and the Properties Pane for the new source is displayed.
- To add a new data source right after another data source, select it and choose the Insert File command.
  The data source is added and the Properties Pane for the new source is displayed.
Complete the information on the Description, General and Input Set tabs of the Properties pane.
After you define the text data source, you must add fields to the definition.

The Text Data Source Properties Pane

The properties pane for text data sources includes four tabs:

Description
General
Fields
Input Sets

The Description Tab

Property	Description
Name	Enter the data source name
Description	Enter a description of the data source

The General Tab

Property	Description
Dimension	Turn this checkbox on to indicate that the Data Source contains information for use in creating Dimensions (or Building Metada phase). The cube creation module, O3 Builder, uses this information to decide whether the source is relevant for Dimension creation or if it is only a Measures source.
Verify Before Construction	Turn this checkbox on to verify the existence of the file in the specified directory before proceeding with the cube build. If verification fails, the building process is interrupted with the following build completion status: UNAVAILABLE SOURCE: <source name> You can define a build action for this status. See Model Properties.
Access Path	Define the file name and access path for the Data Source or choose the Browse button and use the Browse Files dialog box to select the access path and file name. It's possible to use a parameter for the path or file name.
Decimal Separator	Enter the decimal separator used in source files, or choose one from the drop-down list
File Format	Indicate whether the file format is fixed width or if the file uses delimiters. If it is a delimited file, indicate the field delimiter by selected a predefined delimiter or indicating a customized delimiter.
Skip Lines	When extracting data from a source for the cube, you can exclude a specified number of initial lines in the file. In general, we advise that you include the field names in the first line of the files, as a means of simplifying their subsequent definition using the Autofill feature. In this case, the option indicates that the first line should be skipped when loading as it does not contain significant data.
Tuples by record	It indicates that a number of "n" tuples must be generated in the cube from each data source record. The default value is 1. See About Tuples by Record.

The Fields Tab

Property	Description
Fields	Use the New Field button to add a field, indicating the file columns; that is, the record fields to use later on in the definition of Dimensions and Measures. For each field, declare the field name, the column number within the file and the field's data type. When choosing a field name, bear in mind that all references to the field made in the definition of the model take place through the name you declare here. Data types are: String, Integer, Double, and Date. You can also delete a field by selecting it and choosing the Delete button.
Autofill	Use the Autofill option to automatically fill in the fields with their corresponding column numbers. The field values of the first row of the file will be used as field names. All data types default to String. Once you autofill the fields, you can further customize the Data Source configuration by changing names, and revising data types.
Preview	Use the Preview button to preview the contents of the text source. This does not display all records in the file.
Local Table	If you turn on the Local Table check box, the property pane updates to include the Key and Include Field columns for each field. You can enter the table name. For each field it should be indicated if it's part of the table and if it's part of its key. See About Local Tables.

There are several different date formats to choose from for date fields. Be sure the selected date format matches the text file contents.

For example:

DD/MM/YY is Day/Month/Year
DDMMYY is DayMonthYear
MM/DD/YYYY is Month/Day/Year

The Input Sets Tab

Property	Description
Input Set	A list of input sets displays. Turn on the check box for each input set to include the data source in. See Defining Input Sets

Adding Fields to a Text Data Source

After you have added a text data source, you must define the fields in the source.

To add fields to a text data source:

Select the Sources branch in the Design tree.
Select the Text Data Source branch.
Choose the Fields tab of the Properties pane.
To autofill the fields, choose the Autofill button.
The fields populate and you can edit them if you wish.
The type of each of the fields must be indicated manually
To add a field, choose the New Field button.
A new row adds to the table.
Complete the Name, Field Number and Type fields.

Note
Use the Preview button to preview the content of the text source. This displays only the first rows in the file.

Unknown macro: {scrollbar}