Excel and CSV files

Before you can import a file you must first create it.

To do so you will need either a spreadsheet program like Microsoft Excel if you want to use .xls files for importing or you can use a .csv file that can be opened with a similar spreadsheet program or any text editing software.

Preparing a file for import

A correct file requires you to:

1. Create a header for your file, that lists all the column names
2. Add data for those columns and make sure that data does not change in format.
   
   

An example file is displayed below:

This file contains 4 columns:

1. Time - Will be connected to the time dimension by the date specified.
2. Account - Will be the account number to which the data will be connected.
3. Object Type DEPT - Will be the lower level members of the Object Type DEPT that the data will be connected to.
4. Income Budged - Will be the data that will be added to flex.bi.

Importing the file into flex.bi

Once you have the file all you have to do is to upload it to flex.bi like you would any other file by uploading it through the Source Data tab in flex.bi

Then press on the Preview (Button) next to your uploaded file.

A new page will appear:

In the new page you have to

1. Select the cube you wish to import the data into by selecting the cube name from the "Cube" drop down list
   
   
   
2. Selecting the amount of header rows you have in your file by selecting the amount from the "Header rows" drop down list (for the example file the amount should be set to 1 header row). Once you change the amount you will be prompted to reload the page.
   
   
   

3. Then you have to select the data types for each of the columns being mapped. Each data type is used for the following data:
   a. String -  used for fields that need to be interpreted as text, that is, you wont be doing any mathematical calculations with them.
   b. 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.
   c. Decimal - use for fields that need to be interpreted as decimal numbers (1.0, or 2.5, or 3.9 or 20000.99), you plant to do mathematical calculations with these.
   d. Date - use 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 number, DD - full day number), an example can be seen in the example file.
   e. Datetime - use 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)
   
   

   Account numbers are not interpreted as numbers but as text, so make sure to set the data type for the account number as string (text).

4. Then you have to select the dimensions for each of the columns that they are going to be mapped to.
   a. Always make sure that the date/time is mapped to the Time dimension
   b. Always make sure that the account numbers are mapped to the Account dimension
   c. Always make sure that any object types/customer classes/item classes are mapped to their appropriate dimensions
   d. Always make sure that number values that you plan to do calculations with are mapped to the Measures dimensions
   
   
   
5. Under Level or Measure enter the name you want to see for the measure or level in the column. In the example our Income Budget will be mapped to the measures dimension, because we plan to do calculations with it and we have manually entered the name for the measure as Income Budget, you can name this measure what ever you want.
   
   
   
6. Under the Advanced Options you will have to choose one of many advanced options to make the import function correctly.
   a. ID column - check this if you want to use this column as an identifier column
   b. Key column - check this column if you want it to be one of the main columns the data will be bound to
   c. Name column - check this column if you want to use it as one of the name columns
   d. Ordinal column - check this if you want to use it as a column to order your data by
   e. Skip missing - check this if you want to skip the row that doesn't contain any entries for this column
   f. Delete missing - check this if you want to delete the row that doesn't contain any entries for this column
   g. Property name - enter a property name if you want to attach this row as a property to one of the key columns, the properties are usually used to add some kind of text to the data, like a name, phone number, e-mail address.
   
7. Press Start Import

You should now be able to view the data you have added in the appropriate cube under the appropriate dimension -> Select members -> User defined.

The added measure or level can then be used in reports and in calculated members.