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 the0CALYEAR\0CALYEAR.LEVEL01
dimension name from the cube. It is received as avalue
(highlighted in red in the sample screen), ready to be used. For months, the value is pulled from the0CALMONTH2\0CALMONTH2.LEVEL01
dimension. For days, the value is pulled from the0CALDAY\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 thatFEB
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 provides20163
for the quarter dimension, which means that out of\d{4}(\d)
we need to extract the last digit3
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 theCalendarEx
time template, the data source providesQ1 CY 2016
for the first quarter of 2016 and Insight retrieves1
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.