How can i create a hierarchical dimension for dates in ActivePivot?

I'm a newbe in ActivePivot and i want to create a dimension with DimensionType = time, where the dates a shown in hierachical manner. E.g. for 30.01.2013 i need one level for the year -> 2013 (sort descending), one level for the month (also sort descending) -> 1 and one level for the days (also sort descending) -> 30, 29, 28, ...

Viewed via ActivePivotLive should look like:

and so on.

I went through the ActivePivot sandbox project, but i didn't find anything that helps me. The TimeBucket dimension which i've found in the EquityDerivativesCube makes something similar but the buckets are created in a different manner.

How can i solve this problem?

Solution

The TimeBucket dimension in the ActivePivot Sandbox application defines a custom bucketing based on financial time periods. Creating a standard year > month > day hierarchy is actually simpler and seamless in ActivePivot. In the description if the schema you need to declare three fields (one for year, one for month and one for the day).

<field name="Year" indexation="dictionary" />
<field name="Month" indexation="dictionary" />
<field name="Day" indexation="dictionary" />

And then you need to declare a dimension that references those fields.

<dimension name="Time">
    <level name="Year" />
    <level name="Month" />
    <level name="Day" />
</dimension>

Then ActivePivot will build the time hierarchy incrementally, by introspecting the loaded records.

This will work automagically if the input records (objects) already contain a Year attribute, a Month attribute and a Day atribute (For instance if the input records are POJOs with getYear(), getMonth() and getDay() methods). If that is not the case and that for instance the input records only have a date attribute, you can either transform your records before puutting them into ActivePivot, or inject a calculator in ActivePivot (com.quartetfs.biz.pivot.classification.ICalculator) that will on the fly compute the three fields from the date. Look at the ActivePivot Sandbox application for an example of calculator.

Extracting those fields is usually done with standard Java code:

    Date date = new Date();
    Calendar calendar = Calendar.getInstance();
    calendar.setTime(date);

    System.out.println("Date: "  + date);
    System.out.println("Year: "  + calendar.get(Calendar.YEAR));
    System.out.println("Month: " + calendar.get(Calendar.MONTH) + 1);
    System.out.println("Day: "   + calendar.get(Calendar.DAY_OF_MONTH));

About the ordering of members in the level of a dimension, ActivePivot per default uses the natural ordering of java objects (those that implement java.lang.Comparable interface) so dates and integers will be sorted from the lowest to the greatest. You can easily reverse that by declaring a "ReverseOrder" comparator on the target level(s).

<dimension name="Time">
    <level name="Year">
        <comparator pluginKey="ReverseOrder" />
    </level>
    <level name="Month">
        <comparator pluginKey="ReverseOrder" />
    </level>
    <level name="Day">
        <comparator pluginKey="ReverseOrder" />
    </level>
</dimension>