When you import data from CSV or MS Excel files, SQL databases, Google Sheets or REST API, you will have to map your data columns to the flex.bi data cube so you could later analyze those data from different perspectives.
Data mapping is the process where you assign “data columns” from imported data to Dimensions, Measures, 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, count of orders by company, etc.). Properties are dimension member attributes that can be added and further used in the reports if those dimension members are displayed in the report.
On this page:
There are a few simple rules you have to follow while mapping your data.
Rule 1 At least one Measure
The measure is the core element of the data cube and reports defining how dimensions are related to each other and a measure itself. Therefore each data cube should contain at least one measure. Of course, you may have more than one measure in the cube (for example, revenue and count of sold items), just make sure to give them unique names.
A measure is a quantitative value and columns holding decimal or integer values are offered to import as Measures by default. The measure name is generated from the column name but you can adjust it if needed.
- flex.bi recognizes period
.as a decimal separator. Make sure the data source does not have a comma
- For decimal values, the precision (maximum number of digits) and the scale (digits after the decimal point) are set based on data in the column. However, you can adjust both parameters by editing advanced options.
If you do not have any numeric value in your data source, you can tell flex.bi to generate it for this data cube. Chose Append row count measure to add a new column containing the number 1 for each row. Update the name for a row count measure so it would represent the file content.
In many data sources, numbers are used to give unique names consisting only of numbers (for example, registration number, case number, or ID). flex.bi will try to be helpful and offer to import it as a Measure by default; however, the information by nature is "name" not "quantitative value" and you might want to import it as a dimension. For this column, set data type to string and proceed as if you would map the dimension (see next chapter on Rule 2).
Rule 2 At least one Dimension
At least one measure and one dimension are required to create a report.
Dimensions allow you to group and filter data by different perspectives in the report. In a simplified way, you may think of a dimension as a list of values for each column.
Columns holding string values are offered to import as Dimension by default. The dimension name is generated from the source data column name but you can adjust it to represent the column content better.
You can create a hierarchical dimension (one dimension with several hierarchy levels) from several source data columns if one column provides more depth than the preceding column. For example, if you have four columns - Country, State Province, City, Customer Name - you can create one dimension "Customer" with four hierarchical levels. This will let you see a top-level overview of your data and drill into details when necessary.
- Orders sourced data columns from left to right, starting with the top-level and down to the most detailed. You may change column order in flex.bi by dragging and dropping the columns.
- Enter the Dimension name for the first column, and for the rest of the columns, select the same Dimension name from the list.
- Enter unique Level names for all columns.
When you need to import several columns as the same dimension attributes then click edit Advanced option 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.
Rule 3 Import properties
Properties are descriptive attributes of dimension members which can be added and further used in the reports when 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, shirt size, etc. Properties may have any data type, string, text, number, or date.
For instance, if we have a customer's date of birth and some comment about it, we can import both as properties for the customer. Then these values could be displayed in the report when we look at data for this particular customer.
- Orders sourced data columns so that property is on the right of dimension member it describes. You may change column order in flex.bi by dragging and dropping the columns.
- Select the same Dimension name and the same Level name from the list.
- Specify the property name in the Advanced options. In the report, the property will have a prefix with a dimension name for grouping data and improved searchability. In the example below is mapped new property "Birthday" to dimension "Customer"; in the report, you will find this field by name "Customer birthday".
Properties are quite powerful information; you can enable several interaction options in the report.
- If the source data column contains the path (URL) to the space where the original information of the object is, map this column as a property and give the property name URL. This property enables the Go to source option in the report.
- If the source data contains two name columns describing the same object, one name is the full name of the member, and the second - a more user-friendly name, then you can import the user-friendly name as a property with the name DISPLAY_KEY. This property enables the option to change the display name in the report for dimension members.
- Similarly, if the source data column contains a link to the user's avatar, map this column as a property and call it avatar_url. When imported to the user dimension members, this property would enable the new display name option "with avatar" for user dimension members. User dimensions (Assignee, Logged by etc) imported automatically in flex.bi from the Jira site would have this property imported by default.
Rule 4 Only one Time dimension
As in the physical world, there is only one Time dimension for each multi-dimensional data cube. In the same calendar, you can set birthday reminders and schedule appointments.
Columns holding date or datetime values are offered to import as Time dimensions by default, and flex.bi auto-magically will create a Time dimension with dates ordered in a hierarchy Year → Quarter → Month → Day and Year → Week → Day.
Date columns can be imported as measures and properties. 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 the corresponding Time dimension member:
You can import several columns with DateTime as properties with count measures mapped to the Time dimension. Only rows counted with this DateTime will be imported as counter mapped to the Time dimension. Do the data mapping similarly to Rule 3 for importing property and specify the Advanced options:
- Property name – specify property name for a date field. In the report, a dimension name will be added as a prefix to the property.
- Date count measure - specify a measure name that should store the count of imported rows on this date.
- Date count dimension - specify a time dimension that should be used for this measure (by default Time dimension name is suggested).
The following date formats are recognized by flex.bi as date formats:
The following DateTime formats are recognized by flex.bi (currently only the date part of the DateTime value is imported in the Time dimension):
- YYYY-MM-DD hh:mm:ss
- YYYY-MM-DDThh:mm:ss (ISO 8601 DateTime format)
- YYYY.MM.DD hh:mm:ss
- DD.MM.YYYY hh:mm:ss
- YYYY/MM/DD hh:mm:ss
- MM/DD/YYYY hh:mm:ss
Adjust source data
The column order is essential to create a hierarchical structure in the dimension (several hierarchy levels for data) and to import properties. You can drag and drop the columns by "Field name" to change the order.
If you want to apply two different data mapping for one column (for example, import date as property and also as Time dimension) you can clone the column and map each of the columns as you need.
Add calculated column
Add count measure
You can add a count measure to create a new column containing the number 1 for each row that has a value in the originating column and offers to import it as a new measure. It is similar to the Append row count measure option but for a subset of data having values in chosen column.
The example below replaces the decimal separator comma
, with supported decimal separator dot
. for the source data column
return doc.store_sales.replace(",", ".")
Changes in mapping
Correct data mapping sometimes requires several tries with some fails before you got the correct result. Therefore, 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. However, you can add additional measures and properties without deleting a dimension or measure. You can delete the dimension in the report creation screen in the Dimension selection list.
For changes in one measure mapping, you would like to delete the measure. Go to Analyse section and open Measures. Find measure and open to show its content, then select Delete.
In case of massive changes in several dimensions and measure, you might need to delete the cube. Open analyze tab and select the option Delete for a cube you would like to delete the previous mapping.