MODLRLinking Cubes Using Formula
Cube data can flow from one cube to another using Formula. The key functions for this are the LINK and LINKBY functions.
| Function | Description |
|---|---|
| LINK | The link function returns data from a specified cube at a relative location. If common dimensions exist, they can be excluded entirely from the relative location argument. Syntax: LINK("SourceCubeName", ["Element 1", "Element N"]) Example: LINK("Travel Spend", [ "Total International Spend", "All Trips"]) |
| LINKBY | The linkby function us used when retrieving values based on another value held in this cube. The initial two arguments are the same as the link function. The third argument is the relative location of the value to assist in locating a destination cell in the source cube. Syntax: LINKBY("SourceCubeName", ["Element 1", "Element N"], ["MappedValueStringLocation"]) Example: LINKBY("Travel Rates", ["Flight Costs"], ["Destination"] |
Example of using the LINK function
Consider an Annual Planning Model which feeds planned travel costs from a Travel cube to a central Profit and Loss cube. The travel cube has costs consolidated into Domestic and International Cost measures.
| Profit and Loss Cube Dimensions | Travel Cube Dimensions | Travel Rates Cube Dimensions |
|---|---|---|
| Time | Time | |
| Scenario | Scenario | Scenario |
| Account | ||
| Department | Department | |
| Profit and Loss Measures | ||
| Trips | ||
| Travel Measures | ||
| Destination | ||
| Travel Rates Measures |
INFO
The table layout highlights common dimensions between cubes. It is likely that in a commercial model the Travel Rates cube will also have a Time dimension.
The formula is restricted to only the measure "Amount" and account "12301001". The formula uses the LINK function to create a link to the “Travel” cube using the relative location "International Travel Expenditure" and "All Trips". The specified elements are required as there is no commonality for these dimensions between cubes.
Since both cubes have "Scenario", "Department" and "Time" dimensions, and we want the elements from the source cube to match the target one-for-one, there is no need to include these in the LINK function.
INFO
The Scenario and Time dimensions are hidden in the "Profit and Loss" workview and instead a custom header is used to show the headings.
Example of using the LINKBY function
Consider a Travel planning component to a Performance Management Model. In the example, there are two Travel cubes used.
- Travel Costs – The calculations and data entry is performed here.
- Travel Rates – The travel assumptions accommodation, flights and allowance rates per night.
For the example both workviews will be opened with a vertical break:
Cascading data validation is used for the data entry for Type of travel (Domestic or International) and Destination (a list of Domestic or International Destinations). Both of these validations use the "Destination" dimension which exists within the "Travel Rates" cube.
A formula is applied to Accommodation Costs, Flight Costs and Allowances which pull the appropriate rate for the given Destination and use this with the number of people and in some instances the number of nights.
The Accommodation Costs formula. This takes the rate from the Travel Rates cube for a given destination and multiplies it by the Number of People and the Number of Nights measures.
Explaining the calculated “Accommodation Costs” value.