A fair number of our clients run Jedox models down to a day level. Retail Merchandising models for example, typically require a Daily Sales Cube. Same for the Sales component of a Manufacturing model. Daily Sales models are on the large side for Jedox – in my experience, anywhere between 4 – 8GB in memory, depending on the dimensionality and the volume of data loaded. Like all Jedox models, density is your friend. Keeping the data model efficient and compact will ensure rules and access times to the data are optimised.

A Time dimension down to an individual date level is therefore a critical component to building these types of models. But having the dates in a dimension is not nearly enough – you need the consolidations and attributes and measures to make it a truly workable within the model. For example, you will most probably require Month-To-Date (MTD) calculations (or sometimes Week-to-Date). However, MTD consolidations in a Date dimension are not desirable. You can potentially end up with hundreds (or even thousands) of crazy consolidations that can confuse the end user. It is usually far more efficient to utilise either a measure to hold the Month to Date values for a particular measure, or use a separate “Time Index” dimension.

Time Index Dimension

A separate time index dimension allows you to work in conjunction with a Date dimension to calculate MTD or YTD values. Year to Date is especially problematic – when you are dealing with a Daily model, users require “Date Year-To-Date” calculations – not just the year-to-date consolidation of the months.

When employing a Time Index dimension, data is loaded against the specific date in the Date dimension:

and the element “Date” in the Time Index dimension:

The MTD, YTD (and potentially LTD) can then be calculated and stored against the appropriate elements. MTD, YTD and LTD values are typically calculated in 2 ways: you can do the calculation in the SQL if your data is coming from a relational source (fastest) , or you can use groovy and loop through your cube data to perform the calculation (slowest). I have seen the MTD calculation also done in rules, but this would be inefficient for all but the smallest models.

The key element here is MTD. If you are running out of time/resources on the server, both YTD and LTD can be back calculated on your templates using attributes and the MTD values. But if you have the option, if it certainly nice to have these elements calculated for your end user by the system.

A word of warning – as you are essentially hardcoding the MTD / YTD data in the cube, be prepared for the size and load time of the model to grow.

Building the Date Dimension

So, back to the Date dimension. I have attached an ETL Project which builds a date dimension based on parameters (start and end year) and the calendar extract. It also builds for you automatically a bunch of attributes (next, prior) . For the Prior attribute, if the element is base, it will give you the prior date, month, prior month, etc. It also calculates the Last Day of the Month attribute which can be useful if you are rolling back exactly one month from any point in time, for example. You will note that the Date elements are all numeric. It is much easier to perform calculations on a numeric than a string on a template. There is a few different formatting attributes that you can modify to suit your date format.

Download the Date project here.