Eliminate manual maintenance of Member Spreadsheets
On a fairly regular basis, we are forced to add additional members and change member data for our Dimensions within BPC. Currently, this is a very manual process and my team is likely spending hours each month to keep up with the changes. Each time we have a change needed for a Dimension Member, we have to go into the Admin Tools, open up the necessary Excel Spreadsheet, Save the changes to the server, maintain the dimension properties, and then process the dimension.
I've been researching for the past few days how we can eliminate this manual process. I've just discovered the "Custom DTS Tasks" available to me and more specifically, the Admin Task. BPC Data Manager Help File for Using the Ev4DTSAdmin Task states the following:
+"During normal operation, BPC creates a dimension table during the validation of the member sheet in Excel. This table is the source for processing a dimension into the application.
Using this task, you can set up your source system to produce a replicated member table rather than via Excel. That member table can then be processed into an BPC application by this task."+
With my limited knowledge of BPC, this sounds like it may be the answer to my issue. It sounds like I should be able to set up this task to look at a SQL Table for Member population rather than having to constantly manage those Excel Files. However, I've found very little documentation on how to properly set this up.
So, my questions are:
1. Are my assumptions with regards to this task correct? Can I use this task to eliminate the need to constantly maintain those Excel files?
2. Has anyone actually done this? If so, is there any detailed documentation on it or examples available anywhere?
3. The Task itself only has an option to "Create Dimension", but not update. How does the task work? Does it re-create the dimension from scratch each time it runs?
Any help is greatly appreciated. Thanks in advance!
P.S. We're running BPC 5.1 with SQL Server 2005.
Tim Klem replied
Sorry if I wasn't clear before -- you definitely need the DTS admin task, to process the dimension automatically. Otherwise, the admin user needs to proces it manually in the admin console.
The XLS member sheet is not required if you want to automate the dim maintenance. It's entirely optional, it adds complexity and risks, and I wouldn't really recommend it unless you're familiar with DTS already, or have a great interest in learning more on that tool. (And it's a great tool, I think.)
What you absolutely need is:
1. A DTS package that controls the whole process, which an admin can execute on demand from eDataa menu, or can schedule as a lights-out 4 AM job. This package controls the items below.
2. A source table (or flat file) that contains your master data - a list of SKUs or accounts, for example. This could be connecting in a separate DB server, with a direct connection from your app server's SQL Server / SSIS client if you wish. Or if it's a flat file, you need a DTS connection manager to the file.
3. Load that master data into a custom staging table in your appset database.
4. Manipulate it as required to load it into a second staging table with a schema that's identical to your mbrDimension table. I would program this as a stored procedure, and this gives you flexibility to add parent members to the dimension, add dim properties, etc. Sometimes it's useful to pull some of that other info from add'l custom tables that support the dim building process, so that the setup is reasonably configurable -- you don't want the stored proc to need re-coding every time they want to change something that needs changing every month.
5. The BPC admin task running the dim build option, where you reference that second table.
That's all you need.
What I was proposing in my earlier post, was an optional step to output the second staging table to an XLS file. This allows an admin to look at the data in the format of a member sheet, but really isn't required since they can get almost all the same data in an BPC for excel report using EVDRE for the expansion, and a whole bunch of EVPRO's to get all the properties.
Hope that helps.