Link Formula Details

Detailed explanation and examples for the Link formula.

Syntax:
Link([<linked model name>], [<linked member>], <POV>)

Parameter

Usage

<linked model name>

Specifies the linked model name.

A model is available to be a linked model if:

  • The currency conversion setting (enabled or disabled) for the linked model is the same as for the current model.
  • The Date dimension in the linked model has the same granularity as the Date dimension in the current model, or both models have no date dimension at all.
  • Fiscal Year is either disabled in both dimensions, or enabled with the same month shift.
<linked member>

Specifies the linked model account member.

A member can be linked if:

  • It doesn't contain a formula.
  • It is part of the same account type group as the member that contains the Link formula. INC and LEQ use automatic sign switching, and are one account group, while EXP, AST, and NFIN are the other group.
  • It has the same aggregation type as the member that contains the Link formula.
<Point of View>

The point of view is a list of filters, concatenated by the "and" operator, containing selected dimensions and their members. The point of view is used to further restrict data in the linked account member.

Each POV filter contains a dimension name followed by a string list. A string list is either a single string, or a comma-separated list of strings in parentheses.

For this parameter, the all keyword is available to select all members in the dimension.

Dimensions appear in the suggestion list, divided into three groups:

  • Missing Dimensions

    These dimensions exist only in the linked model. You can select none, one, many, or all members of each dimension, multiple dimensions, or even all missing dimensions.

  • Matching Dimensions

    These dimensions exist in both the linked model and current model. You have the same selection options as you have with missing dimensions.

  • Additional Dimensions

    These dimensions exist only in the current model. If there are any additional dimensions, it is mandatory to specify them in a point of view, and you must select exactly one member of each of the additional dimensions.

Example

Link([t.TEST.ModelA:ModelA],[Measure],[d/First_Dimension] = "[all]" and [d/SecondDimension] = "#" and [d/ThirdDimension] = ("M2P1", "M2P3", "M2P2"))

Example

The following example explains the different dimension types. The tables show the fact tables of the corresponding models.
Model_A (current model): sales quantities of products

Account

Date

Country

Product

Value

UNITS_SOLD 2017 US TV 500
UNITS_SOLD 2017 DE TV 150
UNITS_SOLD 2017 GB TV 125
UNITS_SOLD 2016 US TV 450
UNITS_SOLD 2016 DE TV 200
UNITS_SOLD 2016 GB TV 250
UNITS_SOLD 2016 US Radio 60
UNITS_SOLD 2016 DE Radio 50
UNITS_SOLD 2016 GB Radio 70
UNITS_SOLD 2017 US Radio 40
UNITS_SOLD 2017 DE Radio 80
UNITS_SOLD 2017 GB Radio 30
Model_B (linked model): prices of TVs

Account

Date

Country

CustomerGroup

Value

PRICE 2017 US Private 500
PRICE 2017 DE Private 600
PRICE 2017 GB Private 700
PRICE 2016 US Private 560
PRICE 2016 DE Private 620
PRICE 2016 GB Private 650
PRICE 2017 US Business 400
PRICE 2017 DE Business 500
PRICE 2017 GB Business 600
PRICE 2016 US Business 460
PRICE 2016 DE Business 520
PRICE 2016 GB Business 550

The three highlighted rows in Model_B should be linked to Model_A. We can add the following member with its formula to Model_A to get the following result:

TV_PRICE17 = Link([Model_B],[PRICE],[d/Product] = "TV" and [d/CustomerGroup] = "Business"] and [d/Date] = "2017")

Model_A result

Account

Date

Country

Product

Value

UNITS_SOLD 2017 US TV 500
UNITS_SOLD 2017 DE TV 150
UNITS_SOLD 2017 GB TV 125
UNITS_SOLD 2016 US TV 450
UNITS_SOLD 2016 DE TV 200
UNITS_SOLD 2016 GB TV 250
UNITS_SOLD 2016 US Radio 60
UNITS_SOLD 2016 DE Radio 50
UNITS_SOLD 2016 GB Radio 70
UNITS_SOLD 2017 US Radio 40
UNITS_SOLD 2017 DE Radio 80
UNITS_SOLD 2017 GB Radio 30
TV_PRICE17 2017 US TV 400
TV_PRICE17 2017 DE TV 500
TV_PRICE17 2017 GB TV 600

To understand which values are added to Model_A, consider the different dimension types:

  • Missing Dimensions
    • The missing dimension is CustomerGroup.
    • The information about the CustomerGroup dimension is not available in the fact table of Model_A.
  • Matching Dimensions
    • The matching dimensions are Date and Country. These dimensions are used to map the linked values.
    • Filters can be applied to these dimensions. In this example, a filter is applied to retrieve only values from 2017.
  • Additional Dimensions
    • The additional dimension is Product.
    • Exactly one member must be selected for each of these dimensions. Without this information, there would be gaps in the added facts. In this example, the selected value for the Product dimension is TV.