Implementing roleplaying dimensions

The roleplaying dimension is one of the most common scenarios we face in data modeling. The term was inherited from multidimensional modeling within the SQL Server Analysis Services Multidimensional. Before jumping to the implementation part, let’s take a moment and understand what the roleplaying dimension is. When we create multiple relationships between a fact table and a dimension for logically distinctive roles, we use the concept of a roleplaying dimension. The most popular roleplaying dimensions are the Date and Time dimensions. For instance, we may have multiple dates in a fact table, such as Order Date, Due Date, and Ship Date, which participate in different relationships with the Date dimension. Each date represents a different role in our analysis. In other words, we can analyze the data using the Date dimension for different purposes. For instance, we can calculate Sales Amount by Order Date, which results in different values...