{"id":4316,"date":"2024-02-27T20:01:39","date_gmt":"2024-02-27T20:01:39","guid":{"rendered":"https:\/\/quebit.com\/askquebit\/?p=4316"},"modified":"2026-01-16T15:28:38","modified_gmt":"2026-01-16T15:28:38","slug":"ibm-planning-analytics-selecting-copying-and-loading-performance","status":"publish","type":"post","link":"https:\/\/quebit.com\/askquebit\/ibm-planning-analytics-selecting-copying-and-loading-performance\/","title":{"rendered":"How Do Selecting, Copying, and Loading Operations Impact Performance in IBM Planning Analytics?"},"content":{"rendered":"

IBM Planning Analytics – Selecting, Copying and Loading Performance<\/strong><\/p>\n

Selecting, Copying and Loading<\/h2>\n

Moving data into, around, and \u201cout of\u201d a Planning Analytics model are examples of common operations and constitute essential practices.<\/p>\n

Loading<\/em><\/strong> usually involves executing database commands to select information from external data sources, perhaps from a transactional general ledger system or from a data warehouse or lake, transforming that information in some way and then loading it into a model. Executing copies<\/em><\/strong> within a Planning Analytics model is also common practice, where typically an existing version or scenario\u2019s data is copied to a \u201cfresh\u201d version to start a new round of planning or perhaps current forecasted amounts are copied from a source cube and loaded into a shared cube for reporting (just a few of examples of copying data). Finally, you will also find views in a model that are routinely used to export<\/em><\/strong> slices of information from the model, to be consumed by external applications and consumers.<\/p>\n

\"\"<\/p>\n

What is the Cost<\/strong>\u00a0 <\/strong><\/p>\n

Each of the above-mentioned operations has a cost <\/em>to perform.<\/em> The higher the cost (the more resources consumed by the operation) the bigger the potential hit to your model\u2019s overall performance. Many factors influence operational cost and are worth understanding.<\/p>\n

Loading data (from external systems) and extracting data (from a Planning Analytics model) may involve executing complex database commands that can cause poor performance. Other factors, such as how a Planning Analytics process establishes connections to external systems, also have the potential to slow your model.<\/p>\n

When copies are \u201cin progress\u201d they can, at minimum, slow performance within a model and sometimes, depending upon how they have been implemented, may even lock the model so no work can be performed by any user and no other task or \u201cthread\u201d can execute until the lock is released, since copies typically establish views and subsets to identify source and target data intersection points.<\/p>\n

Finally, you may find that processes (both loading and copying) may be modifying meta<\/em> data (for example adding new GL Accounts) \u201con the fly\u201d which can establish locks on objects within the model which influence model performance.<\/p>\n

What to Look for<\/strong><\/p>\n

The following is not a comprehensive list, nor does it guarantee<\/em> that you will notice a significant improvement in your model\u2019s performance but, but based upon years of experience, these items are typically simple to implement and will improve your model\u2019s overall health:<\/p>\n

Poor Selectivity and Volumes<\/strong><\/p>\n

Whether you are identifying data in a relational database query<\/strong><\/a> or establishing a cube source view<\/strong><\/a>, it is important to be as explicit<\/em> as possible to \u201chandle\u201d only the data that is absolutely necessary. Make sure you are only reading the data you need to meet your objective as opposed to \u201cover-reading\u201d which is reading a larger amount of data and filtering or skipping unwanted data as you process it. Consider including a \u201cwhere clause\u201d in a select statement or subset insert when querying data and, additionally, make sure you \u201cshorten\u201d processing volumes wherever possible by excluding \u201cstale\u201d data, for example selecting and processing only current period record \u2013 as historical periods may not be necessary as part of a daily update.<\/p>\n

Persistent Objects<\/strong><\/p>\n

TurboIntegrator processes use \u201cViews<\/a>\u201d to establish what data to process.\u00a0 View creation consumes resources and can cause locking. Thankfully, you can use the ViewCreate<\/strong> function to create a temporary view which persists only for the duration of the TurboIntegrator process or chore in which the view is created (when th process completes, all temporary objects are cleaned up). Also, there is no locking associated with a temporary view, as a temporary view is never saved. This can result in improved performance, because there is no need for TurboIntegrator to wait for locks to be released before operating upon a temporary view.\u00a0Converting your processes from using persistent views to temporary views<\/a> if accomplished simply by setting a parameter value.<\/p>\n

SQL Inefficiencies<\/strong><\/p>\n

If you are executing Structured Query Language<\/em> (SQL) commands from within a TurboIntegrator process<\/a>, inefficiently written commands can lead to database \u2013 and your model\u2019s – performance issues. The some of the most common mistakes I have come across include:<\/p>\n