Stage 3: The hierarchies of the time dimension
The study of sales month by month is certainly useful, but it is restrictive.
We shall therefore rename the month dimension, and call it, for example Time. The positions of the Time
dimension might be months, but also days, periods or years. Thus sales management will be able to analyse the results
of different Outlets according to more or less detailed levels of time.
To orient ourselves amongst all of the positions of the time dimension, we simply need to create a hierarchy. In our example this hierarchy has four levels which are respectively: day, month, period and year. We can now quite naturally link the 18 May 2000 to May 2000, and then to the second period of 2000 and then to 2000. To describe data in a hierarchy, we speak about children, parents, siblings, ancestors.
In the same way it is possible to create a hierarchy for the Outlet dimension. For example we might group them by county, region and country. Normally it is even possible to define several hierarchies in the same dimension. A second hierarchy in the time dimension might have the three levels of day, week and year. Here the 18th May 2000 is joined to Week 19 2000 and then to 2000.
The only constraint in defining a hierarchy is that the data must be organized according to a series of cascading one-to-many relationships. That is to say, each position can have only one parent in any one hierarchy.
For example let us suppose that we want to create an outlet hierarchy to show which categories of shoes are sold (sport, town, children's). We would now need to forbid the store management from diversifying! It would be better therefore to position this hierarchy in the Style dimension, as a style of shoe belongs to only one category.
The new relational data model is shown below:
The measures Quantity and Total Value Tax Exclusive are now dimensioned by Time, Style and Outlet. Each outlet provides daily data, and the OLAP database looks after the consolidation of the data in the different hierarchies. As long as the consolidation consists of calculating totals, as is the case here, integrated functions allow this operation to be completed swiftly and very easily.
The results of these hierarchical aggregations are stored in the database in positions corresponding to the upper levels of the hierarchy.