PerformancePoint Server - An ETL Tip

Anyone running a Financial Model within PPS Planning in all likelihood updates the actuals from an ERP System within the business. Under normal circumstances this will entail (amongst a myriad of other things) the following:

• Synchronize MG Tables to the Stage Area.
• By way of some ETL tool (Normally SSIS) bring in your actuals.
• All Deletes, Inserts and Updates are then written into the MG Table with the appropriate BizSystemFlag. 200 for Inserts, 300 for updates and 400 for deletes.
• The data is then loaded from staging to the RefDB.
• The model is processed.

Now one of the biggest problems in this entire process is the time it takes to Synchronize the MG tables to the Staging database and then if you have an enormous number of records, the inserting of these to the RefDB. (We have 22 million records in our MG table).

So we have decided to shortcut the process. We created our own ETL SSIS packages to move the data into the MG tables in the Stage database. This method completely negates the effort of synchronizing the MG table to the staging database as we join across the 2 databases in order to detect any updates or inserts. As the data always comes from a LOB system we never do any deletes. For new records we just insert them into the Stage database with the BizSystemFlag of 200. For updates we fetch the existing record from the RefDB into the StageDB and insert an additional record with the BizSystemFlag of 300. You cannot insert a record of type 300 or 400 without the accompanying 100 record. If you do the load will fail and you will get errors on those records.

By adopting this methodology we have reduced our update of PPS to under 5 minutes and it is run every half hour so that our reporting will be up to date. Another advantage of not synchronizing is that your indexes on the MG table in the staging DB are not dropped. Saves a lot of time.

- Paul Steynberg