By reformatting a field, you can create a more readable, and more usable format for analysis, as well as less space consuming in some cases. For example, convert a date field to a numeric field. You can reformat fields within the ElastiCube using a custom SQL expression. An example:

Numeric Representation of Date fields

Business Case

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

Solution

To convert a Date (“4/21/2012 12:36:56 AM”) to an integer (20120421), use the following syntax:

10000*getyear(Date)+100*getmonth(Date)+getday(Date)

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:

tobigint(100000000*getyear(DateTime)*+getmonth(DateTime)*1000000+getday(DateTime)*10000+100*gethour(DateTime)+getminute(DateTime))