Adaptive Planning: Using Adaptive as a Data Source
Adaptive Planning can bring in data from all types of data sources through its robust integration framework. You can directly integrate to use Workday, Netsuite, or Salesforce as a data source, or you can build custom cloud data sources, reference JDBC databases, or even reference a custom scripted data source. All of these options allow organizations to use any external systems for their planning. However, the data source we see as extremely useful and underutilized in the ecosystem is using Adaptive itself as a data source.
When you create an Adaptive data source, you can import almost all data and metadata elements in your Adaptive instance to use as data points for creative integration designs. In this blog, we’ll only focus on three of the sources you can bring into the Adaptive Data Source: the modeled source, the dimension source, and the attribute source. After we discuss these data sources, we will demonstrate how these sources can be used to generate unique identifiers for personnel lines.
Modeled Source
The modeled source allows the import of an entire modeled sheet of your choice into the staging table. The import of a modeled sheet contains more info than the columns displayed on the model’s front end. Instead, Adaptive also brings in the dimension names, codes, and a column titled “Internal ID”. This ID represents the unique ID for the modeled row. Keep reading for more information.
Required Setup
You need to add a dimension and level filter in the ‘Edit Parameters’ section of the data source before running the modeled data source loader.
Attribute Source: The Attribute source allows you to bring your current attribute structure into the staging table. All attributes are imported, including Level, Dimension, and Account attributes. Each attribute imported also has an associated internal ID (“Attribute ID”) column shown in the table for easy mapping in case changes are made to attributes. Likewise, each attribute has a “Dimension ID” value that reflects the ID of the dimension associated with the attribute (in the case that it is a Dimension Attribute). Deleted attribute values are also displayed, indicated as such in the “Is Deleted” column.
Dimension Source: The Dimension Source allows the import of dimension values into the staging table. Each dimension value displayed on a row also has attached columns showing dimension attributes assigned to the dimension value. The “List Dimension” column denotes dimensions that exist in a hierarchy or as a flat list dimension. “Keep Sorted” and “Use on Levels” options checked in the Dimensions window are displayed in columns under the same name. Dimensions created as a part of a loader when importing data are noted under the “Auto Create” column. Deleted dimension values are also displayed, indicated as such in the “Is Deleted” column.
Optional Setup: Add a dimension/level to filter by in the ‘Edit Parameters’ section of the data source if you’d like to filter the import by a specific dimension/level.
The Adaptive Data Source in Practice
Now that we’ve outlined different data points you can import into the Adaptive data source, we can apply these different sources to practical applications for your models.
There is often an ask from clients to have Adaptive generate a unique identifier dimension on a modeled sheet for every single row. Sometimes, you can source this unique identifier from Workday as a WID, or reference ID. However, these values are always not available. For example, clients have personnel models where there is a need for unique dimensions on every modeled sheet row. Unfortunately, not all personnel lines will have a position ID (Manually entered ‘To Be Hired’ lines). Using the Adaptive modeled data source, we can create a set of data loaders that:
- generate unique identifiers for each modeled sheet row
- assign those unique identifiers directly onto the modeled sheet.
Using Adaptive Source to generate a unique identifier for Personnel lines (Filled, Open + TBH)
Personnel models are typically a modeled sheet with each row representing either a filled position, an open position, or a To be Hired (TBH). A TBH is a position being forecasted but not created in Workday. The models are populated via report integrations from Workday transferring specific data points for Filled and Open Positions. The TBH lines are populated by FP&A analysts or department heads who are adding their TBHs for a given forecast.
A resulting simplified model will look something like this (bold column headings represent an Adaptive dimension):
This setup works great for most personnel cost forecasting needs. However, clients often want a unique identifier dimension for each modeled sheet line regardless of if the position is in Open status, filled status, or TBH status. This requirement allows for reporting and forecasting granularity down to the individual personnel sheet line regardless of position status. The difficult part is trying to source the dimension. Position IDs are not able to be used because TBHs won’t have a position ID until the position is created in Workday. Most clients find themselves out of luck which restricts the ability to plan at a modeled personnel line level, such as creating an allocation model with allocation assumptions at the personnel level.
This is where the Adaptive data source is beneficial! When you import a modeled sheet into the staging table through an Adaptive data source, we can utilize the Adaptive generated “internal id” for a modeled sheet line to generate unique identifiers. Through a combination of the modeled sheet source, the dimension source, and the attribute data source, a set of integrations can be created to generate unique identifiers for all planning lines in your personnel model, even TBHs. The Adaptive Identifier is able to be used as the dimension to create an allocation model planning at the intersection of time period, Adaptive Identifier, and Project. Here’s a simplified illustration of the integration generating and assigning internal IDs.
After personnel has been refreshed from Workday but before our identifier integration has run:
The staging table when this data is imported through the model source (notice the Adaptive generated Internal ID):
We set the dimension setting to “Create new values upon import”, these dimension values get automatically created when the data is submitted into the modeled sheet.
When the internal ID is mapped to the Adaptive Identifier column on a data loader, we can replace our personnel model on top of itself, except with newly generated internal IDs– making the result post-integration look as such:
Notice that each line, regardless of position status, has a unique dimension that we can use to input allocation numbers against an allocation model.
With some extra integration magic utilizing dimension attributes, the dimension data source, and the attribute data source, we can extend this integration such that the Adaptive Identifier can stay with the position, or worker across versions rather than generating new unique identifiers each forecasting cycle. We are also able to have the Adaptive Identifier change to the Employee’s name once hired rather than the random string of numbers, making entering allocations in an allocation cube easier, however, that’s beyond the scope of this blog.
If interested in implementing an integration like this or discussing the potential of the Adaptive data source further, please reach out to Kognitiv!
