My datawarehouse table just contains a single date SQL column, but I want to be able to drill down using the usual year/quarter/month/day levels.
I could manually create new column using Pentaho Kettle, and then create the levels one-by-one in Pentaho Schema Workbench.
But this is such a common task (I guess everybody creating sales-related cubes has to do it), so I am sure Pentaho developed something to do it in seconds. What is the recommended way of doing it?
I don't know a built in way to do this.
Here's a nice trick I've learned a few years back: Have a table called Time with one row per day/hour, depending on your resolution preferences.
For dates, each such row will contain:
Date (date) - 2010-09-28
Day (int) - 28
Month (int) - 9
Year (int) - 2010
And you can add as many fields as you like: Quarter, Day in week, Day from year start, Week in year, etc.
This table is pre-populated with dates. When your query requires cube like aggregations, you can join the Time table with the data-warehouse table, to create any aggregation you like.
A nice advantage of this approach is that by using outer joins in the query, you can also add empty dates to the report and not only aggregate on dates which contain values. This is an important feature if you drill down to very sparse dimensions.
