CHOOSING A SOLUTION

only for RuBoard - do not distribute or recompile

The choice as to which solution is most appropriate depends upon the requirements and the type of data warehouse object under consideration.

Retrospection provides a mechanism for freeing the circumstances and dimensions to be regarded as a source of information in their own right. If there is a requirement for true historical analysis to be performed on any components of the dimensional structure, then those components will have a classification for retrospection of true. If there is a requirement to perform state duration queries or transition detection queries, then the most appropriate choice is to provide an existence attribute, in the form of a start time and an end time, that is applied to the object in question. As far as circumstances and dimensions are concerned , the need to establish discontinuous existence is the main reason for allocating a true retrospection. At any point in time, a customer will or will not be active due to the discontinuous nature of their existence.

True retrospection for circumstances and dimensions results from the need for time series analysis relating to their existence. So the existence attribute approach is the only choice in this case.

False retrospection for circumstances and dimensions is similar. It enables the currently existing and no longer existing dimensions to be identified. So it is possible to determine which customers currently exist and which do not. It is not possible to carry out any form of time-based analysis. The best approach for implementing false retrospection for circumstances and dimensions is to use an existence attribute, containing true or false values, as described earlier in this chapter.

Permanent retrospection requires no support at all as the existence of the circumstances or dimension is considered never to change.

Support for relationships that implement dimensional hierarchies is entirely missing from the Type 2 solution, and any attempt to introduce a Type 2 or a row timestamp solution into a relationship may result in very large numbers of extraneous cascaded inserts as has been shown in Chapter 4. It is recommended that use of these techniques is avoided where there are implicit (as implemented by a star) or explicit (as implemented by a snowflake ) hierarchies unless all the superordinate objects in the hierarchy have the property of false or permanent retrospection.

True retrospection in relationships. When retrospection is true in a relationship, if state duration or transition detection analysis of the relationship is required, the relationship must be implemented using an existence attribute with a start time and an end time. There is a slight difference between the existence of relationships and the existence of dimensions. The subordinate customer will always be engaged in a relationship with a superordinate sales area because the participation condition for the customer is mandatory. It is not, therefore, a question of existence versus nonexistence but rather a question of changing the relationship from one sales area to another and the existences pertaining to relationship instances. The discontinuous nature still exists but applies to changing relationships rather than gaps in durations. A row timestamp approach can be used where these requirements do not apply as long as the cascade insertion problem is manageable.

False retrospection in relationships. False retrospection in relationships is best implemented by allowing the foreign key attribute to be overwritten in the subordinate dimension's row. No special treatment is required.

Permanent retrospection in relationships. With permanent retrospection, the foreign key attribute is not expected to change at all, so no special treatment is required.

True retrospection in attributes. The situation with regard to true retrospection in attributes is similar again. If state duration or transition detection analysis is required, then the simplest and most flexible solution is to use an existence attribute with a start time and an end time. Where these types of analysis are not needed, the use of row timestamps can be considered as long the problem relating to cascade insertions is manageable.

False retrospection in attributes. False retrospection in attributes requires no special support, as the attribute can be safely overwritten.

Permanent retrospection in attributes. Permanent retrospection also requires no support, as the attribute will not change.

The purpose of Table 6.1 is to provide a simplified guide to aid practitioners in the selection of the most arppropriate solution for the representation of time in dimensions and circumstances.

Table 6.1. Choosing a Solution

Circumstances/Dimension			Relationships			Attributes
True	State duration or transition detection analysis required?		State duration or transition detection analysis required?			State transition duration or detection analysis required?
	Yes	No	Yes	No		Yes	No
	Existence period	Row time stamp	Existence period	Does the hierarchy change regularly?		Existence period	Is this attribute at the lowest level in the hierarchy
				Yes	No		Yes	No
				Existence period	Row time stamp		Row time stamp	Existence period
False	Existence true/false attribute updated using Type 1 method		Type 1			Type 1
Perm.	Will not change		Will not change			Will not change

only for RuBoard - do not distribute or recompile