Link Modeled or Cube Sheet Data to Another Account
Audience: Administrators who manage the model.
A linked account lets you use data stored in an account from a cube or modeled sheet to populate a general ledger account, custom account, or standard cube account.
- The destination account receives the data of another account. You change the setting in the destination account to link to another account.
- The source account holds the data that you see in the destination account. You may need to change some settings in the source account to make it available to your destination account. Accounts that are destination accounts cannot be source accounts.
- Linking the accounts for actuals is an option. Consider the pros and cons. See the last section of this article.
Destination Accounts for Links | Source Accounts for Links |
---|---|
General ledger Custom Standard cube |
Any modeled account Any non-metric cube account |
Prerequisites
- See Concept: Linked Accounts, Example: Link Accounts for Expenses, and Example: Link Accounts for Dimensions and Revenue.
- Required permission: Model Management Access > Model.
- To link accounts, you may have to change multiple account settings that affect how data is tracked and displayed in sheets, reports, and charts.
If you change a destination account to Link, you delete all plan data currently in the account.
Navigation
To link accounts, you may need to edit the source and the destination account:
To get to the custom, general ledger, or assumption accounts:
From the nav menu, select Modeling. From the Accounts menu, choose: General Ledger Accounts, Custom Accounts, or Assumptions.
To get to cube or modeled accounts:
Basic Steps
- Confirm the destination account is leaf level.
- Match the account Type setting of the source and destination account.
- Verify the Time rollup setting of the source and destination account if necessary.
- Check and change other settings in both the destination and source account.
- Change the Type of the destination account to Link:
- Choose the cube or modeled sheet and account.
- Optional. Choose dimensions to filter the data.
- Save.
Confirm the Destination Account is Leaf Level
You can only link destination accounts that are leaf level. See Concept: Accounts for more on the account hierarchy.
A leaf level account has no child accounts. 4100 Product Revenue is a rollup - not a leaf level. This account has an expand arrow next to it. 4110 Product Revenue - Tops, 4120 Product Revenue - Bottoms, 4130 Product Revenue - Accessories are leaf level accounts. They do not have child accounts. You can set these as destination accounts for your link.
Match the Account Type Settings
The destination account must match the source account's type: periodic or cumulative. See Change the Account Type Setting.
Check the source and the destination account and update if necessary:
- From the account tree, select the account.
- In the settings, find the Type:
- If they're the same, link the accounts.
- If they're different, the source account won't be available from the Links to drop-down.
- If necessary, change either the source or the destination account's Type setting so they match.
If the linked account is a general ledger account, you can only change the account Type setting of the source account. - Save your changes.
If you change an account's type, you affect how that account displays data and rollup data in sheets and reports.
Match the Time Rollups
Match the accounts by changing the Time rollup for either the source or the destination account. Matching a general ledger account's Time rollup setting is different because the Time rollup isn't visible.
Time Rollups for General Ledger Accounts
- From the general ledger account list, highlight the desination account and look at the account Type setting. If the setting is :
- Cumulative: the Time rollup is Value in last period.
- Periodic: the Time rollup is Sum of rolled-up values.
- Look at the Time rollup setting of the source account:
- If it's the same as the general ledger's (based on the general ledger's Type), link the accounts.
- If it's different, change the source account's Time rollup to match the general ledger account:
- Value in last period if it's cumulative.
- Sum of rolled-up values if it's periodic.
- Save any changes.
Custom, Modeled and Cube Accounts
- Check the Time rollup settings of the source and destination accounts.
- If the Time rollup is:
- The same, link the accounts.
- Different and you want to:
- Keep the destination account's Time rollup setting: Change the source account's Time rollup setting to match the destination account's.
- Keep the source account's Time rollup setting: Leave the settings as they are. Once you link the accounts, the destination account's rollup changes to match the source.
- Save any changes.
Check and Change Other Settings
In the destination account:
- Remove any master formula in the Data Type section.
- Change the Data Entry Sheet Type setting to Standard (cannot be cube-entered)
Change the Type to Link
You're ready to link the accounts.
- From the account list, highlight the destination account.
- For Type, select Link and select Yes:
- Choose the source sheet and account.
- Once you confirm, you see more fields:
- Links to: Choose the sheet and the source account.
- Linked filters: Choose dimensions from the sheet to filter the data (see the following section: Use Dimension or Attribute Filters to Limit Data).
- Select the Edit link next to Linked to. These drop-down menus appear:
- Select a model: Choose a cube or modeled sheet, also known as models.
- Select an account: Choose from the list of accounts in the cube or modeled sheet you selected. The menu is empty until you choose a sheet.
- Once you confirm, you see more fields:
- Save.
Use Dimension or Attribute Filters to Limit Data
Filters limit the data from the source account by attributes or dimensions that are in the source account's sheet. If you're pulling data from a revenue cube account, you can filter by a product category dimension, such as Tops. Only the revenue tagged with that category populates the destination account. See Concept: Linked Accounts.
- From the account list, select the destination account.
- In the account details, select Edit next to Linked filters.
For each available dimension or attribute in the source sheet, you see a drop-down menu. Each menu is labeled with the name of the dimension or dimension attribute; in this case, color, customer, group, product, and size.
Each drop-down includes a list of all the values for that dimension or dimension attribute. In this case, the product categories are Tops, Bottoms, Accessories and Services:
- Choose one value for each dimension. If you don't want to filter by a dimension, don't choose anything from the drop-down.
- Save.
Link or Unlink the Accounts for Actuals Versions
You can link some accounts for actuals and planning and other accounts for only plan versions. Certain accounts are better left unlinked for actuals:
- If you import actuals data from another general ledger source, not the modeled or cubed account in Adaptive Planning.
- If the actuals data for your destination account is not modeled in {{corp}}.
- If you don't have actuals for the source accounts.
Use the Actuals overlay setting in your destination account to make your choice:
Enable actuals for link: With this option, you enter or upload data separately for the source and destination accounts in actuals versions. You can also leave either or both accounts blank in actuals.
Show actuals if the linked account has them: This option only works if the source account is a cube account or if it's a modeled account that has actuals.
- If the source account has actuals, you enter data in the source account of actuals versions and it populates the destination account in actuals versions, like it does in plan versions.
- If it doesn't, the accounts remain unlinked for actuals versions. You enter or upload data separately for the source and destination accounts for the actuals versions.
Best Practice: If the source account is a modeled account, keep the accounts unlinked for actuals. Most modeled sheets are for planning purposes, and you wouldn't use them for your actuals data. Keep the actuals unlinked so that you can import or enter data for the general ledger in your actuals versions.
If you link the accounts for actuals versions, you delete any actuals data currently stored in the destination account. You will also lock the destination account in sheets so you can't edit or upload data into the account. The only way to populate the account is through the source account.
Find Out if Your Source Account has Actuals
- Go to Modeling > Model Management, and from the Sheets menu, select Level Assigned Sheets or User Assigned Sheets to find the modeled sheet.
- In the list of sheets, select the Edit link next to the modeled sheet.
- Select Columns and Levels.
- From the toolbar, select Sheet Properties.
- Choose the Settings tab.
- If the Available in actuals version is checked, the accounts in this sheet have actuals data. If it's not checked, the accounts are hidden from actuals and you can't link the account for actuals.
- Change the setting if you choose and select OK.