Importing With Varied Dimensions
Have you ever faced a scenario where you wanted to import the data from one module to another with a different set of dimensions, and you were not able to successfully achieve it? Usually, such type of data movement can easily be achieved through formulae, but in some cases, writing formula might not be the solution—for example, while taking snapshots or exporting the data out of Data Hub to Spoke Models. Here is the article that might help you in such cases.
Case I: When importing data from 2D to Multi D (as in the case of Data Hub to Spoke Models).
Let’s say we have a Source module with Unique Key (concatenation of codes of all Customer, Account & Region) in Rows and Time in Columns, and we want to Import the data to the Target module having Customer, Account, Region as dimensions in Page Selectors and Time as a dimension in Columns.
Solution: Before we import the data there are few things that need to be created within Anaplan.
Step 1: SYS module for Unique Key is the first thing that you should create which holds all the attributes of the Unique key—in our case, Customer; Account and Region in List format.
Step 2: Populating Customer, Account, and Region in the Source Module by creating the Subsidiary views in the source module by removing the time dimension.
Step 3: Creating Saved View by applying the required filters so that we have the lowest granularity of data available. Here we have Unique Key in Rows, Time in Columns, and Line items in Pages.
Step 4: Now, let’s try to import the data from the above Saved View to the Target Module and see what happens.
This is an import action, and by looking at it, nothing makes sense and it is not possible to map the data from the source to target in this way. So, what are we missing?
The answers lie in the reshuffling or re-pivoting of your Source module and creating a modified Saved view. Let’s go back to Step 3. Pull your Time dimension into Rows along with Unique Key and shuffle your Line items to Columns. Apply the filter and save the view.
Modified Saved View
Perfect Mapping an Anaplan Model builder wishes to look for.
Below Mapping Line Items as Fixed Line Item.
Here you can have Line items as Fixed Line item or Column Headers. If you wish to map line items as Column Headers, there is one extra little thing that you need to do which is ignoring the extra column(s), in our case—Unique list column.
Mapping Line items as Column Headers
Step 5: Run the Import, and it will load the data successfully.
Note: In such cases, it is best to map Line Items as Column Headers because that will be useful when trying to import the data from Source Module with N+n dimensions to Target Module with N dimensions when you have to ignore more than one line item.
Case II: When importing data to the Target module with N dimensions from Source Module with N+n dimensions. Here “n” >=1
Let’s say we have a Source module with Customer, Account & Region as dimensions in Pages and Time in Columns, and we want to Import the data to the Target module having Customer, Account as the dimension in Pages and Time as a dimension in Columns.
Upon Importing we get the following options, and we map the source with the target. As you can see in the below screenshot, Customer, Account, and Line items get mapped with their respective targets, and rest all column headers are being treated as Time dimension which includes the extra dimension (Region) as well. When you run this import you will see some failures. Although the data is loaded successfully, you still should get rid of these failures in your import action.
Shuffle your dimensions across and keep at least Time in Rows. You can have up to three dimensions in Rows, including Time. Once that is done, ignore the Region dimension in your Import mapping and your import will run successfully.
Note: You can also map Line items as Fixed Line items, but it is best to map them as Column Headers because that will be useful when trying to import the data from Source Module with N+n dimensions to Target Module with N dimensions. Here “n” > 1.