Concept: Load Metadata into Adaptive Planning

Administrator Guide

Download PDF

Adaptive Planning Design Integrations uses these loaders to import metadata:

Planning Account Loader

Planning Level Loader

Planning Dimension Loader

Planning Attribute Loader

Planning Association Loader

Loaders require a Workday data source with one or more selected Workday reports. The content of the selected reports determines what kind of loader you use.

Guiding Principles

When you create a Workday data source to import metadata to Adaptive Planning, follow these guiding principles:

For anything hierarchical, use these Workday-delivered reports:

Plan Dimensions/Hierarchies example - Cost Center and Cost Center Hierarchy

Ledger Accounts or Ledger Accounts Summaries example - Ledger Account Hierarchy

For additional attributes, create a custom report off of the object. Example:

Supplier and Supplier Group

Company Code on Companies

Make Workday do the work to narrow down your data:

Use groups and filters:

Select Company
first and then Cost Center
.

Select Summarize Detail Rows
for Cost Center.

Add filters where appropriate.

Use a prompt where possible, instead of hardcoding filters when you need different subsets of data.

Use the best report data source (RDS) available by reading their descriptions and understanding their prompts.

Optimized for Performance
selected when possible.

A company-based prompt retrieves data at a company level.

An organization-based prompt could retrieve data at a cost center, supervisory organization, or any other organization level when you filter.

Levels

Levels must load with a connection to the root or topmost level. For example, you must load a level and its parent, grandparent, all the way to All Levels
.

Levels represent a Workday Organization. Levels are hierarchical, drive access and security, and manage workflow. They roll up to a single parent. Because the topmost level comes as part of Adaptive Planning, the topmost level parent ID in the table must be blank. Any levels with a blank Parent ID will roll up directly to the root of All Levels. When you set up a level loader, select Workday IDs (WIDS) as the level IDs and the parent Workday IDs for the corresponding Parent ID. Even though level IDs (WIDS) are unique, don't duplicate level names anywhere in the hierarchy.

Dimensions

Most of the dimensions you load should be flat list dimensions so that you can load and assign attributes to them in Adaptive Planning. When you create your Workday data source, select a custom report that contains attributes.

Accounts

Accounts must load separately with a hierarchical format into Adaptive Planning by account group to the General Ledger.

Each root account requires its own staging table import from a data source, defined by the ledger account summary in the Workday prompts. You can reuse the same Workday report, then generate separate staging tables. Create individual Planning Account Loaders for each of the root accounts.

Set up Filters for Account Hierarchies Prompts

You can use the

Filter

tab for an advanced report. Create a filter or prompts that let you determine the hierarchy and date values when the report runs.

And/Or	*Field	*Operator	Comparison Type	Comparison Value
And	Parent Ledger Account Summaries	Any in the selection list	Prompt the user for value and ignore the filter condition when blank.	Default Prompt
And	Accounting Date	greater than or equal to	Prompt the user for the value	Starting Prompt
And	Accounting Date	less than or equal to	Prompt the user for the value	Ending Prompt

Concatenated Workday IDs

When the WID maps into the loader ID field, data and metadata load to Adaptive Planning with the WIDs in the mapping profile. WIDs and the mapping profile create the bridge that enables drilling and publishing from Adaptive Planning. When you create your General Ledger account structure, determine exactly how you want to plan with your accounts. You might want to plan by ledger account, spend category, or a combination of items. Match your planning accounts to your Workday structure to help with data validation, report creation, and other tasks.

Concatenations initially form in alphabetical order based on the Workday Object Name. You can reorder the elements of your concatenation in the Workday External System when you map many-to-one accounts, levels, and dimensions. As you select elements during mapping, they shift to the top of the list. You can drag and drop them in the order you require.

You can concatenate values by WID to create a single Adaptive Planning column using a delimiter for reports you want to drill into. Select 1 delimiter and use it consistently for all of your data sources. Users can't see WIDs or their delimiters.

Based on your account structure, identify which columns you need to concatenate and create a custom SQL column, with a SQL expression in Design Integrations for them.

Once you save your SQL column and data source, you can reference the column in loaders.

If you want to perform additional SQL logic on your columns, use a separate SQL column before you concatenate. To make troubleshooting easier, separate your logical steps.

Concatenating for Accounts with Blank Values

Spend Category isn't a mandatory worktag for certain expenses. To handle nonexistent objects, pass a blank value by concatenating with

[blank]

and include it in your WID concatenation. The delimiter will parse the full string with the blank value into the appropriate location.

Example: Ledger account 6000 might not have a spend category on every journal line. This account could have 3 child accounts:

6000_SC123

6000_SC124

6000_

The account

6000_

doesn't have a spend category, so the blank value can load to it.

Concatenate with

[blank]

for publishing or drilling through to Workday for a

Null

Spend Category.

Concatenate with

[ignore]

to ignore the spend category for drilling through to Workday when you want all spend categories.

Concatenating Account Display Names

Repeat the same concatenation logic used for your WIDs to build the Account Display Name that shows up in the Planning Data Loader. The Account Display Name mapping shows up in

Account Mappings

, so make sure that the name includes enough elements to help you map the data.

Account Codes and Labels

When you load accounts in the Planning Account Loader, you must indicate an account code, account name, and WID. Account codes:

Must be unique.

Can't contain spaces.

Can't contain special characters other than underscores.

To build an acceptable account code, use the reference ID associated with each element of the account. Ledger account codes work well because they follow the ledger account ID Workday uses. If you add a spend or revenue category, verify that it uses a reference ID in a format acceptable for an account code. Reference IDs often use

SC123

RC012

You can build an account code that matches your WID logic, using the underscore to delimit your account codes. Construct the account code mimicking your Account WID SQL column, modifying the column selections to match.

If the

Reference ID

field isn't available or formatted for an account code, use the most identifiable field as the code to reduce the possibility of duplicates.

Use the Adaptive Planning-specific SQL command

TO_ACCOUNT_CODE

to output a text string that removes any spaces or special characters not allowed in account codes.

Account Labels

Specify an account label when you build an account loader. Users will see and use the account label. You might want to mirror your concatenation in the WID using the

(Label)

column field. Use a

CASE

statement to modify the label when a concatenated spend category isn't required.

Mapping Accounts into a Loader

You can create your metadata or Planning Data Loader after you complete your data source.

Planning Account Loaders must specify the Adaptive Planning root account they load to, such as Revenue or Cost of Sales. Your data source must:

Contain the account and all of its Parents, connected to the root or topmost Account.

Contain a Parent ID is blank for topmost Accounts.

Load to a root account that is already in Adaptive Planning.

You can't create or delete root accounts, but you can rename them. Create separate loaders for Revenue, Expenses, and Cost of Sales for more control of incoming accounts. Use the Workday report parameters to ensure you pull in accounts Adaptive Planning expects. Planning Account Loaders require the

Account WID

Account Code

, and

Account Label

When you create a Planning Data Loader, use the WID in the

Source Id Column

and the account code or Ledger Account ID in the

Source Account Code Column

. The

Source Display Name

will help you map items in the

Data Mapping

tab of the Planning Data Loader.

Associations

You can use associations as a way to provide Workday roles to Adaptive Planning users for data security. Associations support Workday core organization assignments and memberships. Loading associations helps you maintain organizational access for users in Workday and sync it into Adaptive Planning. Use the Planning Association Loader for dimension and level associations, or level ownership. Only load associations or level ownership after:

You completely load all other metadata.

You complete user sync at least once.

Load associations for each dimension with a separate loader. Load level ownership in a separate loader from your level associations. Level association and level ownership require different custom report fields and prompts.

Report Fields and Prompts for Associations

Add these report fields and prompts to the

Plan Dimensions/Hierarchies Report

For dimension or level association:

Adaptive Planning System Users for Assignable Role

report field.

Assignable Role

report field.

Assignable Role Reference ID

report field.

We strongly recommend editing this value in Workday to make it human readable before you load assocations for the first time.

Assignable Role for Dimension Type