Data Mapping

In data mapping screen you can enter data cube name where you would like to import your data. If this is a new data set then enter new cube name (e.g. Sales cube if you are importing sales transactions). You can also import a new file in existing data cube - in this case you can use either existing cube dimensions and measures or specify new ones (which will be added to existing cube during import).

It is recommended that CSV file has a header row with column names - flex.bi will try to detect if your CSV file has header row or not. If flex.bi has detected it incorrectly then, please, change the number of header rows (e.g. specify 0 if you do not have any header row).

flex.bi will try to detect each source file column data type based on first 100 rows of the source file. Detected column types (string, integer, decimal, date or datetime) are shown below each column name. If some column type is detected incorrectly then you can change it (e.g. change from integer to string if this column might contain also non-integer values).

Each data type is used for the following data:

• String - used for fields that need to be interpreted as text, that is, you will not be doing any mathematical calculations with them.
• Integer - used for fields that need to be interpreted as whole numbers (1 or 2, or 3, or 20000), you plan to do mathematical calculations with these.
• Decimal - used for fields that need to be interpreted as decimal numbers (1.0, or 2.5, or 3.9 or 20000.99), you plan to do mathematical calculations with these.
• Date - used for fields that need to be interpreted as a date, the format used for these fields needs to correspond to one of the following options - YYYY-MM-DD or YYYY.MM.DD or DD.MM.YYYY or YYYY/MM/DD or MM/DD/YYYY (where YYYY - full year, MM - full month, DD - full day), an example can be seen in the example file.
• Datetime - used for fields that need to be interpreted as date with time attached to it, the format use for these fields needs to correspond to one of the following options : YYYY-MM-DD hh:mm:ss or YYYY.MM.DD hh:mm:ss or YYYY/MM/DD hh:mm:ss or MM/DD/YYYY hh:mm:ss (where hh - hours in 24 hour format, mm - minutes, ss - seconds).

Column mapping to dimension and dimension level

Next you need to map source file columns to cube dimensions and measures.

By default flex.bi will suggest to map string columns to dimension with name which is derived from column name (underscores replaced with spaces, each word is capitalized). If necessary then you can enter different dimension name that you would like to use in flex.bi.

You can map each column to separate dimension but sometimes it would be better to map several columns to the same dimension but different hierarchy levels. E.g. in this example it would be better to map country, state_province, city and fullname to the same Customers dimension with hierarchy levels Country (highest hierarchy level), State Province (one country expands to several states or provinces), City (one state or province expands to several cities), Customer Name (one city expands to several individual customers).

! It is important that in source file hierarchy level columns are always in the sequence from highest hierarchy level to lowest hierarchy level.

When you map several columns to the same dimension then you can select existing dimension name from list for next columns (delete suggested default dimension name to see list of available dimension names).

If you map several columns to the same dimension then it is mandatory to specify dimension hierarchy level for each column. By default hierarchy level name will be generated from column name (in the same way as default dimension name). If necessary, you can enter different dimension hierarchy level name.

Ignore columns

If you would like to ignore some columns during the import (do not want that these columns imported), you should leave dimension selection blank for this column.

Time dimension

Date or datetime column by default will be mapped to Time dimension. Time dimension after import will automatically create year, quarter, month and day hierarchy levels, as well as additional weekly hierarchy with year, week and day hierarchy levels. Therefore, you should not provide any dimension hierarchy level information in source file mapping as time hierarchies will be created automatically.

You should use one of the following date formats in your CSV file that flex.bi will recognize as date format and you will be able to import it as Time dimension:

• YYYY-MM-DD
• YYYY.MM.DD or DD.MM.YYYY
• YYYY/MM/DD or MM/DD/YYYY

The following datetime formats are recognized (currently only date portion of datetime value will be imported in Time dimension):

• YYYY-MM-DD hh:mm:ss or YYYY-MM-DDThh:mm:ss (ISO 8601 date time format)

• YYYY.MM.DD hh:mm:ss or DD.MM.YYYY hh:mm:ss

• YYYY/MM/DD hh:mm:ss or MM/DD/YYYY hh:mm:ss

Measures and integer & decimal columns

Numeric columns (integer and decimal data types) by default will be mapped to Measures dimension and you need to specify measure names for these columns (by default derived from column names). Measures are values that you would like to analyze across other dimensions and which will be aggregated by other dimensions' hierarchy levels.

If you have other integer values (most typically e.g. identifier columns like customer ID or order ID etc.) in source file columns, do not map them to Measures - either enter corresponding dimension name or leave dimension name blank to ignore it during the import.

Advanced options

When you need to import several columns as the same dimension attributes then click Show options and specify the following options:

• ID column - if attribute is unique integer ID attribute of dimension member (like issue id attribute in this example). If ID column is not specified then dimension members will be identified by key column and IDs will be automatically generated for each new key column value.
• Key column - if attribute is unique numeric or string key of dimension member (like issue number here)
• Name column - if attribute is a longer name that should be displayed as dimension member name (if not specified then key column will be used as a name column). But keep in mind that the name column still needs to be unique within dimension (therefore, in this case we will not use title as name as it might not be unique).
• Ordinal column - if this attribute should be used to order dimension members.
• Property name - specify if attribute should be imported as additional property for dimension member which is identified by ID or key column (like issue title in this example).
• When importing date or datetime field as dimension property you can specify additional options:
  • Date count measure - specify measure name that should store count of imported rows in this date (in this example Issues created measure will show the count of issues that were created in created_at date or Issues closed will show the count of issues that were closed in closed_at date).
  • Date count dimension - specify time dimension which should be used for this measure (by default Time dimension name is suggested).

Mapping errors

When you have completed columns mapping to dimensions and measures then click Start import button to import source file data into specified data cube. If there will be any missing information in the source file columns mapping, you will get error message about missing information and columns with errors will be highlighted: