Oracle OLAP, partitions and time
One of the reasons I am not writing too much on the blog at present is that I am busy working with a client just about to put a new (small) data warehouse into production. Coupled with the need to concentrate on submitting a white paper to Collaborate 08 by the weekend and I am not getting too much free time. I write this whilst an OLAP cube organised materialized view is building on a VM on my laptop.
One side note from my research on 11g OLAP is about partitioning the OLAP cube along the time dimension.
Often organisations have multiple hierarchies for reporting time, financial reporting may be aligned to a 13 x 4 week period and each week starting on a Saturday and the year starting in August, and other reporting going against the civil calendar. Of course "the day" is common to both calendars and you could build both calendars as a part of a single time dimension.
But if you put both calendars into a single time dimension it may get interesting when you decide to build an OLAP cube. If both hierarchies are used in the same cube and you elect to partition on the time dimension you will only be able to partition on one of the two hierarchies. You specify a granularity for the partition, say fiscal quarter and the fiscal periods, weeks and days will be divided into partitions based on the fiscal period they belong to, fiscal years go into a sort of 'cap' partition, that is the one used to catch the members that don't belong in the other lower partitions. But what of the sales for June? June is not a member of any fiscal quarter so that too goes into the cap partition... which could be very bad for performance.
So don't partition on time, or build separate cubes for fiscal and calendar reporting.
I have noticed the partition advisor in OLAP 11g AWM can give some odd partitioning advice. I had a two hierarchy time dimension for some tests I am doing for a presentation but elected only to aggregate on the civil calendar. But the partition advice was to partition on the fiscal (the default hierarchy, which did not have any aggregates specified for the cube