Skip to main content
Adaptive Insights
Knowledge @ Adaptive Insights LLC, a Workday company.

Pull Data from a Modeled or Cube Sheet into Another Account

Explains how to link an account to cube or modeled accounts and how to use dimensions as filters.

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.

Linked accounts graphic

Destination Accounts for Links Source Accounts for Links

General ledger

Custom

Standard cube

Any modeled account

Any non-metric cube account

 

Before You Begin

  • See Linked Account Overview and use case scenarios: Expenses, and 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.

How You Get There

To link accounts, you may need to edit the source and the destination account:

To get to the custom, general ledger, or assumption accounts:

Navigation Icon5.png From the nav menu, click Modeling. From the Accounts menu, choose: General Ledger Accounts, Custom Accounts, or Assumptions.

To get to cube or modeled accounts: 

Navigation Icon5.png From the nav menu, click Modeling. From the Sheets menu, select either Level-Assigned Sheets or User-Assigned Sheets. Click the Edit link next to the cube or modeled sheet. Click the Cube Account link or the Modeled Account link.  

Basic Steps

  1. Confirm the destination account is leaf level.
  2. Match the account Type setting of the source and destination account.
  3. Verify the Time rollup setting of the source and destination account if necessary.
  4. Check and change other settings in both the destination and source account .
  5. Change the Type of the destination account to Link:
    1. Choose the cube or modeled sheet and account.
    2. Optional. Choose dimensions to filter the data. 
  6. Click Save.

Confirm the Destination Account is Leaf Level

You can only link destination accounts that are leaf level. See Accounts Overview 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. 

Leaf level account

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:

  1. From the account tree, click the account. 
  2. In the settings, find the Type:

Check the account 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.
  1. 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.
  2. 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

  1. 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.
  1. 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. 
  1. Save any changes.

Custom, Modeled and Cube Accounts

  1. Check the Time rollup settings of the source and destination accounts.

 Time rollup

  1. 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.
  1. 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)

Data On Sheets Setting

Change the Type to Link

You're ready to link the accounts.

  1. From the account list, highlight the destination account. 
  2. For Type, click Link and click Yes:
    Select Link and confirm
     
  3. Choose the source sheet and account.
    1. 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).
        Links to and Linked Filters
    2. Click the Edit link next to Linked to. These drop-down menus appear:
      Choose model and account
      • 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.
  4. Click 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 Linked Accounts Overview.

  1. From the account list, highlight the destination account. 
  2. In the account details, click  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.
    Dimension filters
    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:
    Dimension values.png
     
  3. Choose one value for each dimension. If you don't want to filter by a dimension, don't choose anything from the drop-down.
  4. Click 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 Insights.
  • If the actuals data for your destination account is not modeled in Adaptive Insights.
  • If you don't have actuals for the source accounts.

Use the Actuals overlay setting in your destination account to make your choice:

 LinkedAccount Show Actuals.png

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 account's remain unlinked for actuals versions. You enter or upload data separately for the source and destination accounts ub 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

  1. Go to Modeling > Model Management, and from the Sheets menu, click Level Assigned Sheets or User Assigned Sheets to find the modeled sheet.
  2. In the list of sheets, click the Edit link next to the modeled sheet.
  3. Click Columns and Levels.
  4. From the toolbar, click Sheet Properties Sheet Properties.  
  5. Choose the Settings tab. 
    Sheet setting enabled for actuals
  6. 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. 
  7. Change the setting if you choose and click OK
  • Was this article helpful?