Using Data Actions to Copy Data Between Models

Objective

After completing this lesson, you will be able to create and run a data action to copy data from one model to another.

Copy Data Actions Between Models

Data is often needed from several sources to perform the planning process, for example, prices are in one model but the income statement data is in another. Depending on the circumstances, the data may need to be physically copied while in other cases, a look-up will suffice.

Before we get into the specifics of copying data between models, let's consider the options when working with data in multiple models:

Cross Model Copy: Data Duplication:
- The data needs to be physically copied from one planning model to another.
- The data will be modified in the target model for example.
- A snap-shot of the data is needed.
- Example: Copy actual labor hours from HR as the basis for forecast hours.
Combine Data in the Import Process: Data Duplication:
- The data needs to be physically merged.
- Do this in data wangling.
- Example: combine transaction data with exchange rates for immediate conversion.
No Data Duplication:
- Blend data in stories. Example: Add attributes from another source.
- Use advanced formula look-ups via the LINK command.
- Example: Look up labor rates from HR for the labor calculation.

Cross Model Copy with Mapping

In the Mapping area, set how the data will be mapped from the source model to the dimension members of the target model.

For each of the target dimensions listed in this area, you need to either map a source dimension or select a default value. In the following image, you can see that the currency is set to Unassigned. In this example, labor hours are being copied from an HR into an expense model. In the HR model, labor hours aren’t related to currency.

A Cross-Model Copy data action with Source Model (top left), Target Model (top right), and Currency (bottom) highlighted. Currency is set to unassagned.

You don't need to map source dimensions that aren't relevant for the target model. For example, you might choose not to map gender or office location from the Headcount model to the Finance model.

The source and target models can have different dimensions. For example, HR has personal area but Expense doesn’t. In the data action, a constant can be used for personal area.

Note: Cross-model copy steps don’t support copying between classic account models and models with measures, but you can use an advanced formula step with a LINK function instead. For example, for a classic account model that has the same set of dimensions as a target measure model, a simple link function that copies account values to a measure looks like this:

Code Snippet

1
DATA([d/Measures]="Quantity") = LINK([AccountModel], [d/Version]="public.Forecast", [d/Account]="Quantity")

Copy Data from a Weekly to a Monthly Model

When data is copied from a weekly to a monthly model, the data is aggregated from weeks into months. The assignment of a week that begins in one month and ends in another is based on the Auto-Generation Strategy in the data action.

Screenshot of two tables (top) showing weekly data in table (left) and the data copied into the monthly table (right). The bottom of the image shows the configuration.

When mapping date dimensions with different time granularity, the first day of the source member will always be used as a point of reference. For example, this option could be used when copying from a source dimension with month-level granularity to a target dimension with day-level granularity.

If a source dimension month starts on January 1, 2027, this month will be mapped to the day member January 1, 2027 on the target dimension. This pattern will be repeated for all dimension members.

Create and Run a Data Action to Copy Data From One Model to Another

Business Scenario: In the third step of creating the 2025 forecast income statement for your company, you must calculate the labor and benefits. First, you will need to copy the labor hours from HR and then calculate the labor and benefits.

Before you calculate labor values in the expense model, you need to create a data action to copy the labor hours from the HR model into the expense model. To do this, you need to create a cross-model copy data action.

Task Flow: In this practice exercise, you will:

Create a data action to copy between models
Configure the data action parameters, mapping, and filter
Create a blank private version in the expense model
Add the data action to the story and run it

ExerciseStart Exercise

Log in to track your progress & complete quizzes