This post was originally published on this site

Yes, I can hear you, SSAS Multidimensional is so old and unsexy. Still, a lot of the data warehouse projects I work on still use this techonology.

So what is a roleplaying dimension? It’s quite simply a dimension, that plays different roles in regard to a certain fact. Or put a bit differently: one dimension is referenced more than once with different purposes in a fact table. One of the more customary examples is the date dimension, that get’s used for different purposes. For example if you take an order fact table, where you might be interested, when the order was placed, at what time it was shipped and when it was charged. So instead of building three different date dimensions for this purpose (an orderdate, shippdate and chargedate dimension), you build one date dimension and reference it three times:

So, let’s have a look at SSAS. For that, I’ve downloaded the WideWorldImporters (WWI) sample database (the DW or data warehouse edition), which you can find here. If you have a look at the order fact table (Fact.Orders), you can see an example of a roleplaying dimension with the employee dimension, which is referenced as salesperson as well as picker:

Now we create an “Analysis Services Multidimensional” project in Visual Studio, create a Data Source that points to the WWI database, and a Data Source View upon that. Finally, we create a Cube with the wizard, based on the Fact.Orders Table. Just clicking through and using the defaults gives us a Cube, and five dimensions in our project (Date, Employee, Customer, City and Stock Item). If you have a look at the fact table, you can see that we have seven dimensions, since Employee and Date are each reference twice as roleplaying dimensions. The reason, that our modell has recognized the roleplaying part is, that in the WWI database, the references are spelled out as Foreign Keys. If you don’t use Foreign keys, you’ll have to model the relationsships by hand in the Data Source View.

What’s really need about the roleplaying dimensions is, that you can show some attributes in one dimension, but not in another. As an example, we’ll add a field for the sales quota (and add some dummy value), which of course only makes sense for salespeople:

ALTER TABLE [Dimension].[Employee]
ADD SalesQuota DECIMAL(15,2);
GO

UPDATE [Dimension].[Employee]
SET SalesQuota = 10000
WHERE [Is Salesperson] = 1;
GO

Now we will have to update our Data Source View, and our employee dimension. If you deploy and process the cube now, you’ll see, that the new attribute is added to both dimensions that reference the employee dimension. So what we will have to do now, is disable it in the picker dimension. For that we’ll switch to the Dimension View in the Cube, where you have to disable the AttributeHierarchyVisible Flag (or the Visible Flag if you made it a Hierarchy) in the Properties of the roleplaying dimension:

So if you have a look at the cube in Management studio, you’ll see the desired result:

Actually in older Versions, new Attributes were not visible by default for roleplaying dimensions, which really prompted me to write this post, because it took me quite some time to figure out why.