Kofax Insight Studio > Metrics > Define metrics > MDX metrics > OLAP Date Format

OLAP Date Format

To define the time format, locate the file OlapTimeFormat.xml at C:\Program Files\Kofax\Insight X.X\WcfDataService\bin. Open the file and modify it as necessary. The following rules apply.

  • The file contains the list of time formats that are displayed under the field (underlined in blue in the example below).

  • Insight has predefined time dimensions: years, quarters, months, and days. Define how Insight gets the required data for each of these dimensions from the cube. Use the following example and rules to create your own file for interpreting time values from the cubes.


  • As in the first example, we can use the "SAP BW" format. It only has years, months, and days as time dimensions. It is not possible to drill down to quarters for the metric if this format is selected. For the year dimension, Insight pulls the value from the "0CALYEAR\0CALYEAR.LEVEL01" dimension name from the cube. It is received as a "value" (highlighted in red in the sample screen), ready to be used. For months, the value is pulled from the "0CALMONTH2\0CALMONTH2.LEVEL01" dimension. For days, the value is pulled from the "0CALDAY\0CALDAY.LEVEL01" dimension, which is received in the format <value>.<number>.<number>. Insight only uses the <value>.

  • In another template, the "SAPBW_2" time format has the month dimension with the list of abbreviated month names. So, when the metric needs to be drilled down by months, Insight parses the month name and converts it according to the internal format. For example, see that "FEB" is the second item in the list and Insight treats it as the second month in the year.

  • In some cases, regular expressions can be used in the templates. A regular expression is a pattern that the regular expression engine attempts to match in input text. In the sample above, the "SAPBW_2" has a regular expression for the quarter dimension. For example, the cube provides "20163" for the quarter dimension, which means that out of "\d{4}(\d)" we need to extract the last digit "3" that stands for the third quarter.

  • When an MDX metric is built on the Analysis Services database type, it has several time dimensions, with incorporated time hierarchy. In the metric editor window, the user selects the dimension, the time hierarchy, and the time format to be used for drill down. We have the "Calendar " time format in our sample XML file that displays time hierarchy. The Insight time dimensions point to no dimensions from the outer source. Here, "year" means the first level of time hierarchy, "quarter" is the second level, "month" is the third, and so on. For example, in the "CalendarEx" time template, the data source provides "Q1 CY 2016" for the first quarter of 2016 and Insight retrieves "1" to interpret it as the first quarter. To drill down the metric by days, we use the fourth level in the time hierarchy and then break down the data according to this template according to the days.

Note Modified time formats are stored in the metric document. In order to apply the changes from the XML file, reopen the metric editor screen, reselect the time format, and save the metric.