Hi, I want to know what is the Teradata recomended way of implementation hierarchies. Should hierarchies be implemented in base layer in the form of Group or in aggregate layer as flat lookups or normalized dimension tables may be?
Hi, It really depends on the Architectural constraints of the company you are implementing in. For one of the major communication companies in Australia, their data model constraint was that the cLDM had to be implemented with as little change as possible.
Physicalised tables of denormalised structures was not endorsed. This then meant semantic views were needed to support a denormalised hierarchy (i.e. Recursive Views).
cLDM inherantly supports hierachies. For instance, it contains in its Product/Offering facet tables such as: Product_Related Product_Product_Group_Assoc etc.
When performance is a problem, you may choose first to look at indexing strategies. If that still does not supply suitable performance, then physicalising may be a valid alternative (but should remain a last resort).
Short answer, try to support it in your base layer, as introducing unnecessary tables creates extra overhead that may be entirely avoidable. Cheers