By reformatting a field, you can create a more readable, and more usable format for analysis. In some cases, you can also reduce space. For example, convert a date field to a numeric field. You can reformat fields within the ElastiCube using a custom SQL expression.
When using floating points data types (Real, float, double), some decimal numbers may be rounded. If you need full decimal accuracy, try using BigInt field types.
Create a "Date" table using a numeric representation of a Date field instead of the Date field itself to improve query performances. In addition, a numeric representation of a Date can provide the ability of filtering the data by time range.
To convert a Date ("4/21/2012 12:36:56 AM") to an integer (20120421), use the following syntax:
To get the hours and minutes too, use a bigint numeric representation. So, "4/21/2012 12:36:56 AM" will become: 201204210036, using the following syntax: