I’m currently working with Microsoft Learning, writing a course on designing BI solutions with SQL Server 2012. Obviously, this is a huge subject to try to cover, and raises a whole bunch of really interesting design considerations. One of the new things BI developers need to consider with SQL Server 2012, is whether to use a “traditional” multidimensional data model, or whether to use the new-fangled tabular model. In most cases, from an end-user’s perspective (no pun intended), there is little to pick between the two. In fact, in an Excel PivotTable, most users will struggle to spot any difference. However, for the cube developer, there are some significant differences. There are some things you can do easily in multidimensional projects (or indeed, things that are done automatically for you by SQL Server data Tools) which require (sometimes extremely complex) custom development in a tabular model. Other things are relatively straightforward to accomplish in both models, but require different implementations. An example of the latter is the implementation of role-playing dimensions. You can do this in both models, but there are some differences.
Role-playing dimensions are used to create multiple cube dimensions that are based on the same underlying dimension in the database. The classic example is a date dimension in which each member represents a calendar date. In your cube, you may have a a Sales Order measure group that is related to the Date dimension by multiple keys, for example an Order Date and a Delivery Date. Another example might be an Address dimension that is related to a Shipment measure group by both an Origin key and a Destination key. This multi-use of the same underlying dimension means that the dimension table is defined only once, but users can use it to slice the data by different keys – so for example, a user could view sales by order date or by delivery date (or both).
OK, so first, let’s see how a role-playing dimension is implemented in a multidimensional model. I’m using the AdventureWorksDW2012 sample database, which contains a FactResellerSales table that is related to a DimDate table using three key columns – OrderDateKey, ShipDateKey, and DueDateKey. When I create a data source view from the data warehouse tables in the multidimensional project, all three of the relationships are detected as shown here.
Using the wizard to create a cube automatically detects the multiple relationships, and results in a single DimDate dimension in the database but three role-playing dimensions in the cube (Order Date, Ship Date, and Due Date) as shown here.
The role-playing dimensions are really just references to the same DimDate dimension, but aggregations will be calculated based on each relationship. I’ll go ahead and add a hierarchy to the DimDate dimension:
When a user browses the cube in Excel, each of the three role-playing dimensions is available for them to slice the sales data, and all three of these dimensions have the same Calendar Date hierarchy that I defined for the base DimDate dimension:
Now let’s compare the experience with a tabular model. When I import the same tables into a tabular model project, the relationships are detected, and I can create the same hierarchy as before in the DimDate table. However, notice that two of the relationships are shown as dotted lines, while one is shown as a solid line.
This indicates that although the relationships have all been detected, only one of them is active at any one time. When a user browses the model in Excel, they only see one DimDate dimension, which will show aggregations for the active relationship (in this case, Order Date, but there’s no easy way for the user to tell that from the user interface):
The solution to this problem is obvious. So obvious in fact, that it took me a while to figure it out! The answer is to import the same table multiple times, and rename it appropriately:
After you’ve imported one copy of the table for each role-playing dimensions, you simply delete the inactive relationships from the original table, and create new ones to join the relevant keys in the fact table to the new dimension tables. You’ll also need to create duplicates of any hierarchies you want to appear in all of the dimensions.
Now when users browse the model, they’ll see all three dimensions, and as long as you’ve assigned appropriate names to each copy of the table, it should be obvious what each dimension represents.