Data mapping

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 eazyBI 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.

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.

• eazyBI recognizes period . as a decimal separator. Make sure the data source does not have a comma , as a decimal separator as this symbol is reserved; you may fix the data source or adjust source data with custom JavaScript.
• 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 eazyBI 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 woudl 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). eazyBI 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 eazyBI 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. 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.
• 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.
• 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 eazyBI 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.

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 eazyBI 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). 

Date formats

eazyBI recognizes only specific date and DateTime formats. If you set a date data type for a column and there are no valid values (for example, you see "Null"), it means the source data has an unrecognized date format and you might want to update the source data or modify the column value with Custom JavaScript code.

The following date formats are recognized by eazyBI as date formats:

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

The following DateTime formats are recognized by eazyBI (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

Quite often, source data are not well suited for data mapping, the format is not right for the data type, a numeric column for the measure is missing, the column order does not support the hierarchy or property creation or you would like to add some logic based on the available fields. In those cases you might want to adjust the source data by reordering or cloning columns, adding the count measure, adjusting column values with JavaScript, or creating new calculated columns based on source data.

Reorder columns

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.

Clone column

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

You can add a calculated column to create a new source data column with new content based on values in the originating data source columns. Use JavaScript code to describe the logic for processing and/or combining source data values (note you can refer to only the original data source column in the code).

eazyBI uses basic JavaScript with the https://underscorejs.org/ library. 

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.

Add custom JavaScript code

You can use JavaScript to adjust and prepare the source data for data mapping. For each column, edit the Advanced options to adjust the column data. This option might be useful if source data has a date that does not match the recognized format or decimal values separated by a comma. Or use JavaScript to clean up data and extract only the key values from the source column. For example, if a column contains an array of values, but you would like to get only one specific attribute value from that array.

eazyBI uses basic JavaScript with the https://underscorejs.org/ library. 

The example below replaces the decimal separator comma , with supported decimal separator dot . for the source data column store_sales:

return doc.store_sales.replace(",", ".")

If the column name you would like to reference consists of several words separate with spaces like store sales, then use square brackets to address the column and get the value:

return doc["store sales"].replace(",", ".")

Changes in mapping

Correct data mapping sometimes requires several tries with some fails before you get the correct result. Therefore, you would like to clean up the previous attempts and always start from a clean spot.

For changes in a 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.