BPC owners often seek to implement automation to make manual administration the exception wherever possible, in order to lower TCO and increase uptime. For dimension automation, new members added to source systems (accounts, product names, etc.) would be automatically inserted into the BPC master data tables. Automating this task could save quite a bit of manual effort over time, and increase system uptime. If your BPC deployment requires manual dimension maintenance, this solution may be of great interest to you.
Recently, I met with a client to address their need to further advance an in-house BPC Microsoft solution to automate dimension maintenance. Their situation is similar to many customers who adopted BPC in the 7/7.5 years and chose to leverage existing known features to ensure success rather than over-reach. Smart move. Learn the product, learn the new process, and evolve.
One of the new BPC features for the Microsoft platform that came along during that time was the SSIS MakeDim task. MakeDim does exactly what its name implies: it makes a dimension by rebuilding the member table (mbr<dimension>) from a source table. The MakeDim task is not a process in itself, even though it can be run from a Data Manager package. In this case, the client migrated to EPM 10 and had goals of automating their dimension load to reduce member errors associated with manual member sheet updates, and integrate new members from their HR system. The client took the chore upon themselves to build the automation process fueled by the excitement that the MakeDim task was easy to setup and run. Quickly they discovered that the MakeDim task didn’t perform all of the necessary steps to update their dimensions completely.
We were asked to help them bridge the gap between what the MakeDim task offered and a complete dimension build solution, which was their ultimate goal. But first, let me define process with respect to this blog post. By process I mean the minimum set of tasks required to complete an end-to-end activity that leaves the system in a stable knowable state. In other words, the user can kick off an established process and return knowing that all tasks completed (whether successful or unsuccessful), and that the process could be run again without inadvertently breaking the system.
To illustrate, let’s take a look at the client’s core needs by way of BPC’s MakeDim task and Column5’s Dimension Builder to see where some of the key differences lie.
This isn’t a complete feature list of the Column5 Dimension Builder or the MakeDim task, but only a list of key activities required by the client. If we look at the MakeDim task itself, we see that indeed there aren’t a whole lot of options other than a few maintenance features.
In conclusion, the MakeDim task provides the engine to rebuild the member table, but that is about it. Several activities typically required by a business user to update a dimension aren’t available in the task. This is very much in the tradition of BPC being a financial development platform.
Putting aside some of the client’s specific requests, in the above comparison table there are 5 key activities that keep the MakeDim task from being an end-to-end process. Looking closer at the table, process member sheet from admin, overwrite members, delete members, backup member sheet, and generate member sheet must be considered in conjunction with the MakeDim task in most any solution without that BPC could be left in an unsatisfactory state from a user perspective. Why?
Process Member Sheet from Admin
MakeDim is disassociated from the member sheet. The Excel sheet is not evaluated for new members or updated fields when the task runs. If the member sheet has new members as input by a user, they will not be picked up by MakeDim. This means that the source table and the member sheet will be out of sync unless a manual or automated set of tasks are put in place to ensure commonality.
Overwrite and Delete Members
MakeDim performs the task of clearing out the member table and replacing the members with those found in the source table. MakeDim assumes that the source table has the complete updated set of members. It’s up to the user to make sure that is indeed the case. It’s not evaluated for new members or updated fields. And, it’s also true that if MakeDim has run and the user exports the dimension to the server, that the existing member sheet will be replaced.
Generate New Member Sheet
The Excel member sheet is not recreated by the MakeDim task after the task has run and the mbr table has updated. This leaves the system in a vulnerable state unless a user manually exports the dimension.
Backup Existing Member Sheet
Now it’s true that the Export Dimension feature found in the Admin console creates a date stamped backup, but it’s not part of the of the MakeDim SSIS task. It’s incumbent upon the user to back up the member sheet prior to running the MakeDim task.
The short story here is that it’s up to the user to take the available BPC features and bind them together manually into a coherent repeatable process to ensure the system is in a state of completeness, ready for the next dimension update. The MakeDim task, while able to run by itself inside of a Data Manager Package, leaves the user with an incomplete process. Its role in the process doesn’t extend beyond its core tasks to take on other import tasks, like processing the member sheet, allowing for sheet updates, and sheet backups. Using MakeDim by itself requires the user to manually complete the process to ensure BPC is ready for the next dimension update. In the case of this client, their initial dimension update packages left the administrators happy with basic member table updates, but unsatisfied with the lack of completeness in the solution.
For ideas on how to create an end-to-end automated dimension build solution, contact Column5.
To learn more from Column5, sign up for an upcoming webcast!
Related Blogs
