Fact Data Import Commands

The Fact Data import/export process is more complex than the Meta Data import process. When importing or exporting data it is often necessary to change the "dimensionality" of data – for example, to export data from a 3 dimensional Cube, and then to import that data into a 5 dimensional Cube. (An example of this was shown in the Section on Import and Export). The import script syntax manages this problem with the I+ command, which describes the format of the source data, and the Cube Selection dialog box, which specifies how a given set of source data maps to a destination Cube.

Text-based data import is provided in addition to Excel-based import (also discussed in the Section Import and Export) because it is faster, and much easier to use for Cube-to-Cube data transfers. (It is not designed, however, to be as flexible as the Excel-based data import mechanism.) Users will need to create by hand the data file's header information for data that has not been exported from the PowerOLAP Modeler (PowerOLAP^® automatically writes out appropriate header information when it exports Fact Data). The PowerOLAP Fact Data import text requires this file header that describes the format of the data included in the file.  The following pages list the commands that may be included in the header.

SCHEMA

• The Schema command marks the beginning of the file header. The optional Slice Name parameter is provided for documentation purpose only.

FOLD

• The Fold command specifies any folded Dimensions in the source data. These Dimensions correspond to column labels across the top of a Slice in the Modeler application.
• The Fold command must precede any Column commands that have a type of N, S, or NS, and there may only be one Fold command in a header.

COLUMN

• Column commands describe the type of data in each column of the source data. Column commands are order dependent, with each successive Column command referring to successive columns in the source data from left to right. If no Fold command precedes a Column command it is assumed that there are no folded Dimensions.
• The first argument specifies the type of column:

M - The column contains Dimension members. The second argument of the Column command is the name of the Dimension of the Members.

N - The column contains numeric data values. The remaining parameters are member names which correspond to the Dimensions specified in the preceding Fold command. If the members are not found a fatal error occurs. During import PowerOLAP will try to convert each data value to a number. If it fails it issues a warning.

S - The column contains numeric data values. The remaining parameters are Member names which correspond to the Dimensions specified in the preceding Fold command. If the Members are not found a fatal error occurs. During import PowerOLAP will always write the data values as strings.

NS - The column contains numeric or string data values. The remaining parameters are Member names which correspond to the Dimensions specified in the preceding Fold command. If the Members are not found a fatal error occurs. During import PowerOLAP will try to convert each data value to a number. If it succeeds it will write the data as a number, otherwise it will write the data as a string.

• During import the columns are read left to right potentially modifying the same cell in the destination Cube multiple times per source data row. Missing values in source data are ignored.

STARTINLINE

• The data follows immediately after the StartInline command and uses the same delimiter of the current import script. The data ends when the end of the file is reached, or when the first symbol in a line is "EndInline".

DATAFILE COMMAND

• The data import script also supports file references.  Instead of including the data in the same file, an import script can use a file reference.  This file reference replaces the StartInline to EndInline section. The file name follows immediately after the DataFile command and uses the same delimiter as the current import script. The data in this secondary file must correspond to the data normally found between the StartInline and EndInline section.
• The file name can be either a fully qualified path name or a relative path name based on the initial import file.
• Note that if you are using a Schema file that includes a Datafile command to import data from a file reference, both files need to use the same delimiter.

ENDINLINE

• Used to complete the StartInline section signifying the end of data.

Example Import file headers:

Schema    Slice                                                                      

Fold           Regions                                                               

Column     M                         Accounts                                              

Column     M                         Months                                  

Column     N                          California                                             

Column     N                          Oregon                                 

Column     N                          Vermont                                

Column     N                          Maine                                    

StartInline                                                                

Account1   January                0.139407897        0.401852201        0.801796257        0.652370235

Account1   February              0.783904524        0.937655287        0.923899373        0.827072172

Account1   March                  0.887124288        0.658957207        0.830059668        0.227329357

Account1   April                    0.812941406        0.108872333        0.446512497        0.410558823

Account1  May                      0.309530879        0.114093218        0.720820254        0.387044119

Account1  June                       0.37162136          0.904706988        0.338660513        0.196221734

Account1   July                      0.304975232        0.373084709        0.276257888        0.340046675

Account1   August                  0.809719543        0.466957533        0.20125901          0.776064829

Account1   September             0.944015121        0.383826136        0.767805447        0.82862114

Account1   October                 0.099546865        0.861087302        0.94971556          0.367844024

Account1   November             0.807482664        0.498567923        0.646897538        0.392043745

Account1   December              0.472949158        0.045011316        0.267465083        0.985907521

EndInline