BACKGROUND:
Theta joins are not supported by Strategy. Slowly Changing Dimensions (SCD) or report filters should be used in order to address the need for Theta joins.
The only joins supported by Strategy use the = operator. When talking about Theta joins the following operators are used < , ≤ , > , ≥ , ≠. Theta join is an expression used in Relational Algebra and SQL language and is based on the theory of Relational Algebra. When an = operator is used in Relational Algebra the term Equijoin is used.
THETA JOIN (θ-join):
The Theta join operation defines a relation that contains rows satisfying the predicate F from the Cartesian product of R and S. The predicate F is of the form R.aj θ S.bj where θ may be one of the comparison operators < , ≤ , > , ≥ , ≠.
Theta join can be rewritten in terms of basic Selection and Cartesian product operations in Relational Algebra:
As with Cartesian product the degree of a Theta join is the sum of the degrees of the operand relations R and S.
THETA JOIN AND SCD:
Theta Joins might be desired because the need with a change in the underlying dimensional model. The need for a change in the dimensional model might be unexpected and it might occur after a period of time the model has been in use. This is not because the original data model was designed incorrectly, but because the data model has been changing slowly over time.
To proactively tackle the issue with changing Dimensions, the Data Modeller should determine the appropriate change handling strategy in co-operation with the Business Users. Within Strategy the method is to use SCD. SCD is not only Strategy terminology; it is widely in use in Business Intelligence world.
EXAMPLE:
An employee is assigned for a certain department. In this example the Employee data is saved to a denormalized lookup table.
|
|
|
|
|
|
|
|
Before 2008, this employee was a part of Tech Support department, but starting from 2009 the employee is a part of IS department.
|
|
|
|
|
|
|
|
Imagine that the Department row would be just simply overwritten with new department information to the lookup table. Now when the expenses are calculated for Information Systems department there is no method to see what has caused the sudden increase in expenses between the years 2008 and 2009. SCD are providing the means of tackling the issue with information that is changing over time in Strategy dimensional data model. KB11286 discusses about how to implement SCD in Strategy.
ACTION 1:
Slowly Changing Dimensions should be used instead of Theta join concept within Strategy dimensional data model. Consult the following document for more information about SCD KB11286.
ACTION 2:
In some circumstances theta join might be needed when implementing Slowly Changing Dimensions. In most of the cases this need can be addressed by using report filters instead.
ADDITIONAL NOTES:
For more information about how to implement Slowly Changing Dimensions, refer to the following Strategy Knowledge Base technical note:
KB11286 - How to implement slowly-changing dimensions using logical views in Strategy
Literature references:
