How to Import from Source to Target with Different Number of Dimensions
Have you ever faced a scenario where you wanted to import the data from one module to another with 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 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 & Time as a dimension in Columns.
Source Module Pic:
Target Module Pic:
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
SYS Module Pic:
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 required filters so that we have 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.
Import Action Pic:
Above pic 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 lies 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 Pic:
Import Action Pic: 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 else 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.
Below 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 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 dimension in Pages & Time as a dimension in Columns.
Upon Importing we get following options and we map the source with the target. As you can see below Customer, Account and Line items got mapped with their respective targets, rest all column headers are being treated as Time which includes our 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.
In order to fix this shuffle your dimensions across and keep at least Time in Rows. You can have up to 3 dimensions in Rows including Time. Once that is done ignore the Region dimension in your Import mapping & your import will run successfully.
Note: You can also map Line items as Fixed Line item 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