Facts vs Dimensions in a cube
Dimensions are the axes used to organise and categorise the data.
Facts are the data points you want to analyze. They are numeric and are aggregated using functions like SUM, AVERAGE, COUNT, etc.
Refer to Create / Amend /Delete a fact for details.
When Info-Explorer builds a cube from a SQL query, it assumes String columns in the queries will become Dimensions and Numeric columns in the queries will become Facts.
Dimensions are displayed in black letters in the Available Fields Area.
Facts are displayed in blue letters.
You might want some numeric columns (Facts by default) to be a Dimension (for example Fiscal Period). In this case you can edit the Field, and switch the type from "Fact" to "Dimension".
Can Database String columns be a fact?
You might want some Dimensions (string columns) to be a Fact.
Info-Explorer only sees the database field type - which is a string. If you want the string column to be a Fact, you will need to convert the string to a number in the SQL Query (using cast or convert).
This is only a valid option if the underlying values are always numeric.
Note: If the values are not numeric, you may need to use a different formula in the query, such as the length of the string, and use this as a fact.
Example: In Sage 300, optional fields are always stored as Strings in the database, and the optional field type tells Sage whether to display the field as a String, Date, Number or Boolean. You need to convert the datatype in the SQL Query.
