Role-Playing Relationships

Whenever you create a relationship to a dimension, whether from a fact table to a dimension or from one dimension to another dimension, an instance of that dimension is added to the cube model. In some cases, the same dimension may be referenced in more than one context in the same cube. A role-playing relationship is what differentiates multiple instances of the same dimension in a cube.

Basics of Role-Playing

For example, suppose that you want to create a relationship between the orderdatekey and shipdatekey keys in a fact dataset and the Day level in the Date Month and Date Week hierarchies in the Date dimension. The results would look like the top four relationship lines of the model on this Cube Designer canvas:

Figure 1. Role-playing relationships: the Date dimension is in relationships to orderdatekey and shipdatekey in a fact dataset. The top four relationships trace to the Month and Week Hierarchies of the Date Dimension.

The Date dimension is in relationships with orderdatekey and shipdatekey. Each relationship is labeled with a role-playing name.

You first drag orderdatekey to the Day level. The Create a Relationship dialog appears. In that dialog, under Relationship Settings, the Role-Playing Template appears. Use the dimension variable {0} to insert the role-played dimension name by setting "Order {0}" (or something else appropriate) as the role-playing prefix.

Figure 2. The Create a Relationship Dialogue with the Role-Playing Template

To dictate where the role-playing name will appear, place the dimension variable {0} where you wish the input to appear. Valid inputs for the Role-Playing Template are as follows:

• "Order {0}" = Prefix
• "{0} Order" = Suffix
• "Order {0} Sales" = Prefix and Suffix

note

Note: If the user enters text into the role-playing text box that does not include the dimension variable {0}, then AtScale will assume that the input is a prefix and append {0} to the end of the text when saving. The role-playing feature is backwards compatible with role-played dimensions from previous versions of AtScale. No migration steps are necessary when upgrading from versions previous to AtScale 2020.2.0. After upgrading to I2023.4.1 or later, you need to upgrade the role-playing relationships in your current models to remove duplicates. For more information, see Changes Related to Upgrades.

Setting a Role-playing prefix

To set a role-playing prefix append the dimension variable {0} to the Role-playing name in the role-playing template dialogue.

1. Drag orderdatekey to the day level of the Date Month Hierarchy within the date dimension.
2. Enter "Order {0}" into the role-playing template dialogue.
3. Click save.
4. Double click the role-played relationship (orange line) to open the Edit a Relationship dialogue if desired.

Figure 3. Setting a role-playing prefix

The prefix "Order " will be appended to every attribute of the Date dimension for queries involving order dates. To preview the changes, pin the Library panel to the side bar and search for your role-playing relationship. Additionally, the Cube Data Preview area displays the Date attributes all prefixed with "Order ":

Figure 4. Role-playing prefix "Order {0}" on the Date Month Hierarchy in the Cube Data Preview tab.

If you set Order Year, Order Quarter, Order Month, and Order Day as the rows to use in Cube Data Preview, and set orderquantity as the column, the Design Center generates this MDX query for the preview. The query also uses the "Order " prefix.

SELECT
{ [Measures].[orderquantity1] }
ON COLUMNS,
NON EMPTY Hierarchize({ [Order Date Dimension].[Order Date Month Hierarchy].[Order YearMonth].Members,
[Order Date Dimension].[Order Date Month Hierarchy].[Order Quarter].Members, [Order Date Dimension].
[Order Date Month Hierarchy].[Order Month].Members, [Order Date Dimension].[Order Date Month Hierarchy].
[Order DayMonth].Members })
ON ROWS
FROM [Internet Sales Cube]

Setting a Role-Playing Suffix

To set a role-playing suffix, prepend the dimension variable {0} to the input in the role-playing template dialogue.

1. Drag shipdatekey to the day level of the Date Month Hierarchy within the date dimension.
2. Enter "{0} Ship" into the role-playing template dialogue.
3. Click save.
4. Double click the role-played relationship (orange line) to open the Edit a Relationship dialogue if desired.

Figure 5. Setting a role-playing suffix

Figure 6. Role-playing suffix "Ship {0}" on the Date Month Hierarchy in the Cube Data Preview tab.

If you set Year Ship, Quarter Ship, Month Ship, and Day Ship as the rows to use in Cube Data Preview, and set orderquantity as the column, the Design Center generates this MDX query for the preview. The query also uses the "Ship " suffix.

SELECT
{ [Measures].[orderquantity1] }
 ON COLUMNS,
SUBSET(
Hierarchize(
{ [Date Dimension Ship].[Date Month Hierarchy Ship].[YearMonth Ship].Members,
[Date Dimension Ship].[Date Month Hierarchy Ship].[Quarter Ship].Members,
[Date Dimension Ship].[Date Month Hierarchy Ship].[Month1 Ship].Members,
[Date Dimension Ship].[Date Month Hierarchy Ship].[DayMonth Ship].Members }),
0,
300)
ON ROWS
FROM [Internet Sales Cube]

Though this example demonstrates the creation of role-playing relationships between a fact table and a dimension, you can also create role-playing relationships between a dimension and another dimension. To do so, on the main cube designer canvas you double-click the heading of the dimension that the relationships will be created from. On the dimension canvas, add the second dimension from the project library, which you can open by clicking the Library icon on the canvas toolbar. Then, create the role-playing relationships as described in the foregoing example.

Setting a Role-Playing Prefix and Suffix

To add a suffix and prefix to your role-playing relationship, place the dimension variable {0} between the two inputs you wish to see.

1. Drag orderdatekey to the day level of the Retail hierarchy within the date dimension.
2. Enter "Order {0} Finance" into the role-playing template dialogue.

Figure 7. Setting a role-playing prefix and suffix

Figure 8. Role-playing prefix/suffix "Order {0} Finance" on the Date Month Hierarchy in the Cube Data Preview tab.

Role-Playing Dimensions: In Cube Data Preview, UDA, Perspective, and Calculated Measure Dialogues

When referencing a role-playing dimension with a prefix, suffix or both within the Cube Data Preview, Perspective, User Defined Aggregate, or Calculated Measure dialogues, you will see the expanded role-playing syntax.

Figure 9. Role-playing suffix "Order {0} Finance" on the Date Month Hierarchy in the Create Calculated Measure dialogue:

The role-playing expansion applies to the dimension, hierarchy, and level component in the canonical syntax. Given Order {0} Finance, the role-played dimension appears expanded as such : [Order Date Dimension Finance].[Order Date Month Hierarchy Finance].[Order YearMonth Finance]

Recursion

Role-playing becomes recursive when you create role-playing relationships both between a fact table and a dimension and between that dimension and another dimension.

For a simple example, suppose that you have a fact table and two dimensions.

Fact table: fact_auction_transactions
Among other fields, each transaction recorded contains keys for buyers and for sellers.

Dimension: Person
Among other attributes, each person has a name, a key for a billing zipcode, and a key for a shipping zip code.

Dimension: Geography
Among other attributes, this hierarchy includes zip codes.

On the main cube designer canvas, you create two role-playing relationships from the buyer_ID and seller_ID keys to the Person level of the Person hierarchy.

You double-click the Person dimension to open the canvas for working with that dimension. You add the Geography hierarchy to this canvas. Then, you create two role-playing relationships from the billing_zip_ID and shipping_zip_ID keys to the Zip Code level of the Geography hierarchy in the Geography dimension.

You then have four copies of the Zip Code attribute:

• Buyer Billing Zip Code
• Buyer Shipping Zip Code
• Seller Billing Zip Code
• Seller Shipping Zip Code