OEG Best Practice: Imports and exports and their effects on model performance
Imports are blocking operations. To maintain a consistent view of the data, the model is locked during the import, and concurrent imports run by end users will need to run one after the other and will block the model for everyone else. Exports are blocking for data entry while the export data is retrieved, and then the model is released. During the blocking phase, users can still navigate within the model.
Carefully decide if you let end users import (and export) during business hours
Imports executed by end users should be carefully considered, and if possible, executed once or twice a day. Customers more easily accept model updates at scheduled hours for a predefined time—even if it takes 10+ minutes—and are frustrated when these imports are run randomly during business hours.
Your first optimization is to adjust the process and run these imports by an administrator at a scheduled time, and then let the user based know about the schedule.
Use a saved view
The first part of any import (or export) is retrieving the data. The time it takes to open the view directly affects the time of the import or export.
- Always import from a saved view—NEVER from the default view. Use the naming convention for easy maintenance.
- Ensure the view is using optimized filters with a single Boolean value per axis.
- Hide the line items that are not needed for import; do not bring extra columns that are not needed.
- If you have done all of the above, and the view is still taking time to complete, consider using the Tabular Multi Column export and filter "in the way out." This has been proven to improve some sluggish exports.
Mapping objective = zero errors or warning
Make sure your import executes with no errors or warnings as every error takes processing time. The time to import into a medium-to-large list (>50k) is significantly reduced if no errors are to be processed.
- In the import definition, always map all displayed line items (source→target) or use the "ignore" setting. Don't leave any line item unmapped.
Watch the formulas recalculated during the import
If your end users encounter poor performance when clicking a button that triggers an import or a process, it is likely due to the recalculations that are triggered by the import, especially if the action creates or moves items within a hierarchy.
You will likely need the help of the Anaplan Model Optimization team to identify what formulas are triggered after the import is done and to get a performance check on these formulas to identify which one takes most of the time. Usually, those fetching many cells such as SUM, LOOUKP, ANY, or FINDITEM are likely to be responsible for the performance impact. Speak to your Business Partner for more details on the Model Optimization services available to you.
To solve such situations, you will need to challenge the need for recalculating the formula identified each time a user calls the action.
Often, for actions such as creations, moves, assignments done in WFP or Territory Planning, many calculations used for reporting are triggered in real-time after the hierarchy is modified by the import, and are not necessarily needed by users until later in the process.
The recommendation is to challenge your customer and see if these formulas can be calculated only once a day, instead of each time a user runs the action. If this is acceptable, you'll need to rearchitect your modules and/or formulas so that these heavy formulas get to run through a different process run daily by an administrator and not by each end user. If not, you will need to look at the formulas more closely to see what improvements can be made. Remember, breaking formulas up often helps performance.
Don't import list properties
Importing list properties takes more time than importing these as a module line item. Review your model list impacted by imports, and look to replace list properties with module line items when possible. Use a system module to hold these for the key hierarchies, as per D.I.S.C.O.
Get your data hub
Hub and spoke: Setup a data hub model, which will feed the other production models used by stakeholders.
- It will prevent production models to be blocked by a large import from an external data source. But since data hub to production model imports will still be blocking operations, carefully filter what you import, and use the best practices rules listed above.
- All import, mapping/transformation modules required to prepare the data to be loaded into planning modules can now be located in a dedicated data hub model and not in the planning model. This model will then be smaller and will work more efficiently.
- Try and keep the transaction data history in the data hub with a specific analysis dashboard made available for end users; often, the detail is not needed for planning purposes, and holding this data in the planning model has a negative impact on size, model opening times, and performance.
As a reminder of the other benefits of a data hub not linked to performance:
- Better structure, easier maintenance: data hub helps keep all the data organized in a central location.
- Better governance: Whenever possible put this data hub on a different workspace. That will ease the separation of duties between production models and meta-data management, at least on actual data and production lists. IT departments will love the idea to own the data hub and have no one else be an administrator in the workspace.
- Lower implementation costs: A data hub is a way to reduce the implementation time of new projects. Assuming IT can load the data needed by the new project in the data hub, then business users do not have to integrate with complex source systems but with the Anaplan data hub instead.
This can be the magic bullet in some cases. If you export on a frequent basis (daily or more) from an Anaplan model into a reporting system, or write back to the source system, or simply transfer data from one Anaplan model to another, you have ways to only import/export the data that have changed since the last export.
Use a Boolean line item to identify records that have changed and only import those.
Author David Smith.
Contributing author Guillaume Arnaud.
Strongly agree, respecting these rules can have a very beneficial impact on the performance of imports. And during the audit of the models it’s often identified as an axis of improvement.
In a recent case, thanks to these recommendations we have reduced the processing time by 50 %.
With these simple settings:
- Select only the leaf level for all the dimensions,
- Eventually, filter the view with technical Lines items in Boolean format (to remove null amount),
- Use the 'Show' on only the Line Items (s) that will be used in the import,
- In case of duplicates rows add the ISFIRSTOCCURRENCE(x,y) .