Writing YTD data

In many OLAP databases, values are stored in the time dimension on periodic elements such as March 2020. But as users require for certain use cases cumulative or YTD (year-to-date) values, the creator of the time dimension prepares the YTD elements. These add the periodic elements from the beginning of the year to the related month. In the case of March 2020 YTD, this is an aggregation of January 2020, February 2020 and March 2020. These elements are often organized in a separate YTD hierarchy in the time dimension.

The advantage of this approach is that users can decide on the fly if they want to see the periodic or the cumulative values. But as the YTD elements are aggregated elements, the users cannot store values on those elements. The values must be stored on the periodic elements. Using the Splashing functionality on such an element is not an option as only the current period is supposed to change but not past periods.

OLAP supports the transparent redirection of write operations to such elements. This redirection can be enabled on time dimensions with YTD elements. To define a dimension as time dimension can be done in the Advanced Properties of the Edit Database dashboard of EPM Administration or with the XML function, Dimension PutProperties, by setting the ODBOType property to 1. For example:

<Alea:Properties xmlns:Alea="http://www.misag.com">
  <Alea:ODBOType Code="1"/>
</Alea:Properties>

The hierarchy where the redirection of write requests is supported must have a YTDHierarchy property with the RedirectWriteOnYTD attribute set to true. For example:

<Alea:Hierarchy Name="TIME">
  <Alea:Properties xmlns:Alea="http://www.misag.com">
    <Alea:YTDHierarchy RedirectWriteOnYTD="true" />
  </Alea:Properties>
</Alea:Hierarchy>

If the attribute is set to false or does not exist, the write requests are not redirected.

When a single cell write operation on a YTD element arrives at the OLAP Kernel, the kernel first checks if the redirection is enabled. Next, the related periodic element is identified as the last child element of the YTD element. It must be a base element.

The value on the periodic element is then updated in a way that the resulting value on the YTD element matches the entered value. If the resulting value on the periodic element is equal to zero, the target cell is emptied.

Splash requests on YTD elements are handled in an equivalent way.

String values and cell notes are stored on the YTD cells. They are not redirected.

More limitations apply as described in the "Limitations" section.

The creation of YTD elements are related to fiscal year set up of the entity or group for financial reporting and budgeting. In many cases, fiscal year is equal to calendar year but it can be different.

Sample YTD hierarchy

A typical YTD hierarchy contains single years made up from accumulated months. The period elements are on the lowest level.

Example

This table shows the source data for the YTD hierarchy:


Account	01_2018	02_2018	03_2018	04_2018
Liabilities	80	-10	-25	15

This table shows the calculated data in the YTD hierarchy:


Account	01_2018_YTD	02_2018_YTD	03_2018_YTD	04_2018_YTD
Liabilities	80	70	45	60

To change the liabilities for 03_2018_YTD from 45 to 50 a write request can be sent to OLAP with the new value 50. The request will then be redirected to the cell for 03_2018. The earlier value of -25 on this cell will be adjusted to -20.


Account	01_2018	02_2018	03_2018	04_2018
Liabilities	80	-10	-20	15

Consequently, the cell on 03_2018_YTD, Liabilities will be calculated now as 50.


Account	01_2018_YTD	02_2018_YTD	03_2018_YTD	04_2018_YTD
Liabilities	80	70	50	65

The calculated value for the 04_2018_YTD has changed, too. The new result is 65.

Limitations

YTD elements must be aggregated elements. The related periodic element is identified as the last child. It must be an element on the base level. It must have an aggregation factor in the YTD element other than zero.