If you import your data from CSV, MS Excel, SQL databases, Google Sheets or REST API, you will have to map your data columns to flex.bi data cube.

Data mapping is the process where you assign “data columns” from imported data to DimensionsMeasures, and Properties of a multi-dimensional data cube (for supported applications it is done automatically so you don't have to).

Simply put “Dimensions” are different ways to see your data (i.e., by month, by the company, by type, etc.), but “Measures” are the numerical facts describing those dimensions (i.e., revenue by month). Properties are dimension member attributes which can be added and further used in the reports if those dimension members are displayed in the report.

There are a few simple rules you have to follow while mapping your data:

Rule #1 – At Least One Measure


There must be AT LEAST ONE “Measure” (a “Dimension” with a name “Measures”), holding decimal or integer values. You can have more than one “Measure,” but remember to give each of them a unique name in the “Level or Measure” row. Read more...

Rule #2 – Only One Time Dimension


There can be ONLY ONE "Time" dimension for each multi-dimensional data cube. For this dimension, choose the column that holds chronological date stamps for your data. flex.bi will auto-magically create “Day,” “Week,” “Month,” “Quarter,” and “Year” hierarchies of your “Time” column.

You can import several columns with DateTime as properties with count measures mapped to Time dimension, only rows count with this DateTime will be imported as counter mapped to Time dimension. See more in Rule #4 - Import properties.

You can, of course, import more than one dimension, holding DateTime values, but those columns should be named differently (not Time), and no hierarchies will be created automatically for these columns. Still, you’ll be able to use these values in your reports and calculations. Read more...

Rule #3 – Hierarchical Order


You can create hierarchical “Dimensions” from your data, such as:

“Country” > “State Province” > “City” > “Customer Name”

This will let you see a TOP level overview of your data and drill into details when necessary.

To do that you have to enter the same “Dimension” name (i.e., “Customers”) for all hierarchical levels and a unique name for every hierarchy level in the “Level or Measure” row. Read more...

IMPORTANT! To make your hierarchical structure work, your data columns have to be ordered from left to right, starting with TOP level and down to the most detailed. For example, your first column would be “Country,” the second column, “State Province,” and the third column “City,” and so forth. 

Rule #4 - Import properties

Properties are dimension member attributes which can be added and further used in the reports if those dimension members are displayed in the report. 

Values usually are imported as properties if they are specific for each member (not classified), for example, description, comments, additional information, specific dates etc. that might be not valuable to analyze all data by.

For instance, if we have a customer date of a birth attribute in data set and some customer comment, we can import these both as properties for the customer. Then these values could be displayed in the report if Customers dimension Fullname level would be selected in rows.

Define the property in data mapping for the Customer comment and Customer Birthdate:

Specify the property name in the Advanced options:

It is possible to use a date field as property and also define a new measure to count values over time. For instance, if we have some transaction cancellation date, we can add this date as a property in data mapping and use advanced options to define a new measure that would count all entries with the same date to corresponding Time dimension member:

Property values are visible in reports when the dimension is expanded to the respective level of hierarchy where the property is defined:

If the Date count measure and Date count dimension options are specified for a date  Property, it is possible to use the new measure to count the Property value by Time dimension:

Additional Dimension Attributes

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 longer name that should be displayed as dimension member name (if not specified then key column will be used as name column). But keep in mind that 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 in a hierarchy.
  • Source ID column – if incremental import is used then specify exactly one Source ID column that is a unique identifier of the source data row.
  • 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).

Changes in mapping

Correct data mapping sometimes require several tries with some fails before you got the required result. You would like to clean up the previous attempts and always start from a clean spot.

For changes in one dimension mapping, you would like to delete the dimension from all the cubes you are using this dimension. You can add additional measures and properties without deleting a dimension.

You can delete the dimension in report creation screen in the Dimension selection list.

In case of massive changes in the several dimensions, you might need to delete the cube. Please open analyze tab and select an option Delete for a cube you would like to delete the previous mapping. 

Please export all reports / dashboards before deleting cube. Deleting cube will delete any calculation, reports, and dashboards from your account.