How does Roambi identify dimensions and measures?
You should read this article if
- You are having issues importing data columns as dimensions or measures
- Your numeric data is not importing as measures
- Your numeric data is not importing as dimensions
In general, please follow the following three guidelines:
All columns with text will import as dimension
All columns with only numbers will import as measure
Mixed data type column will often import as dimension
There are some exceptions to these guidelines and they are listed for the specific file format.
Excel files have the ability to set cell formats. The following is Roambi's logic in parsing the cell formats:
- If this is text (e.g. A, a, "Hi"), this is dimension
- If this is a number (e.g. 1, 3.14, 100), this is a measure
- If value contains decimal point or thousand separator, this is a measure
- If user eliminates decimals and thousand separator, and still keep the format as Number, this is a dimension.
- If this is character string, this is dimension
- If the cell contains numbers, this will also import as dimension
- Percentage, Currency, Accounting
- As long as %, $, decimal point, or comma is present in the value, this is a measure.
- All numbers of the same format regardless if it has decimal points or commas will be imported as measures
- Mixed data types (e.g. A column with 12 and $12) will import as dimension
The logic does not meet my specific use case, what can I do?
In many of the Roambi Views, you can create your own dimension and measures off of the imported fields. If you have a column of numbers such as Product IDs (e.g. 100, 101, 102) that you want to appear as dimension, you can create a new dimension using the product ID column.
If the creating dimensions and measures is not applicable, please review the guidelines above and modify the data source.
Why is Excel Number Format not imported as a measure?
By default, Number format will contain decimal places and will be imported as measures. If you specifically remove the decimal places, Roambi will interpret this as a deliberate action and import it as a dimension. The same logic applies to Excel formats like Accounting, Currency, Percentage. If the default formats of these types are altered so that it does not contain %, $, thousand separator, or decimal point, Roambi will interpret this deliberate action to make it a dimension.
What if I want a column of numbers with no format?
Use the General format.
What if I want a column of numbers with no decimals, but my data entry contains decimal?
If you want to remove the decimals, please note that by using the Excel remove decimal points it will convert the cell to Number. And from above logic, Number formatted cells with no decimals will import as dimension.
As a workaround, you can right click on the cell, and select 'use thousand separator' so that Roambi will import the column as measure.
Analytics Publisher Import Data Dimensions Roambi , KBA , BI-ROM-CLD-PUB-BI , Roambi Cloud publishing clients – BI, Cognos & SSR , How To
SAP Roambi Cloud all versions