Eliminate manual maintenance of Member Spreadsheets

[Former Member]

Hey Experts!

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.

[Former Member · Accepted solution]

Sorry, I was not clear.

The process of automatically maintaining dimension members In BPC 7.0NW has been simplified. There is a data manager package, (Process Chain) to bring in Master data. In essence this would automate the maint of dimension members.

I'm unsure if BPC for MS will ever simplify this process.

[Former Member · Accepted solution]

Hmm... So it sounds like I don't need to use the Custom DTS Admin Task at all then. And it seems I can't get around dumping the member data into an Excel Spreadsheet. At least on BPC 5.1...

But if I can automate the population of that Spreadsheet, will that completely automate the processing?

When I update the Spreadsheet manually, I need to click the "Save to Server" link in BPC after I've made changes. Is this link simply saving the Excel Spreadsheet or is it doing other work in the background?

I'm assuming I need to automatically process the dimension after updating the Spreadsheet. So my steps to automate this process are as follows:

1. Replace my dimension.xls with updated version created in SSIS.

2. Populate dimension.xls with data from mbrDimension Table.

3. Run a DumpLoad Task in SSIS to process the cube.

Am I missing anything? Thanks so much for your help, thus far...

Sean

[Former Member · Accepted solution]

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.

Tim

[Former Member · Accepted solution]

Extremely helpful! Thanks so much!

[Former Member · Accepted solution]

Oooh! I'm so close on this, I can taste it.

I do have a bit of experience with SSIS, so I was able get my package to do all of the data transformation and create the Excel file in the exact format needed to process the Dimension. However, I cannot get the Admin Task to process the Dimension automatically.

I'm not exactly sure how the Admin Task should be configured. The way I see it, it can be configured one of 2 ways:

1. I choose the "Process Application" type, point it at my Application, and choose "Full" as the process method. I'm assuming this should process all dimensions within the application. When I run the task using these options in SSIS, it appears to run successfully (At least, the box turns yellow and then green, leading me to believe it's run successfully.)

However, the new member data does not show up in the system. I have to go into BPC Admin, click "Manage Dimensions", choose the Dimension, and click "Process Dimension".

Thinking that maybe I have to kick off the Package withing BPC, I added the Package using the eData>>Organize Package List option within BPC. When I try to run this same package using eData>>Run Package, it Errors out immediately and gives absolutely no error messages or any inclination of why the package has errroed.

2. Instead of "Process Applicaiton", I can choose "Create Dimension", point it at my AppSet, I then select all three checkboxes ("Scheduled Members Process", "Perform a full process of the application...", "Make System available after creating dimension").

In the "New Dimension Name" text box, I input "Area", which is the name of the Dimension I would like to process.

This option fails in both SSIS and within the BPC eData>>Run Package program. Again, I get no messages at all in either application to help me troubleshoot this.

Can anyone shed some light on what I'm doing wrong here? Or at the very least let me know how to track down a log file or anything to give me some idea why this package is failing?

Thanks in advance!!

[Former Member · Accepted solution]

You need the "Create dimension" task type, not "Process application". If you also notice the field "New dimension name" you begin to get a sense of exactly what happens here -- the dimension is totally created anew: the mbrDimension table is dropped and recreated using the schema of the table you indicate in "SQL Source table" -- so be sure that table's schema is EXACTLY the same as your mbrDimension table (which you enter into the "New dimension name" field, minus the mbr prefix).

In my setup, I select the two options "Perform a full process..." and "Make system available..." and I don't use the "Scheduled Member process" option.

And yes, the first time I got this to work, I got a few strange looks from the people sitting nearby as I was dancing around the client's office. "No you don't understand, it actually works!"

There are no debug logs that I could find anywhere. If you're struggling, try executing the package within BI Dev Studio, rather than from eData Run Package. That may give you more of a hint.

Also, check the schema of the SQL Source table. I had problems until I just copied the exact same CREATE TABLE script from the final mbrDimension table, and use it for the source table.

Be sure to include the system-defined columns that are in every mbr table:

[SEQ] [numeric](8, 0) IDENTITY(1000000,1) NOT NULL,
[CALC] [nvarchar](1) NULL,
[DIMCALC] [nvarchar](1) NULL,
[ISBASEMEM] [nvarchar](1) NULL,
[HIR] [nvarchar](50) NULL

Don't put any values into those columns in your source table though. The dim processing routine takes care of all that.

And I owe a big thanks to Joost & Alwin for helping me to figure this one out, the first time I had to set it up. I wasn't able to figure it out without their examples.

[Former Member · Accepted solution]

Tim -

Thank you for your responses. They're extremely helpful.

Ok. So with your help, I've been able to get the Package to run successfully in SSIS. We've forgone all of the Excel Spreadsheet manipulation and I've created a staging Table in the Database, which has the exact same schema as my mbrDimension table and gets updated with fresh data, then runs the Admin Task to process the dimension.

When I run this package in SSIS, it runs successfully. Unfortunately, I don't think it's actually updating the dimension. To validate, Iu2019m querying the UF_GetAreaMember Function within the Database. (If you know of a better way to validate this, please let me know. )

SELECT * FROM [CB_Sales].[dbo].[uf_GetAreaMember] ('cb\userid', 'CB_Sales', 'A370', 'W')

Also, it runs very quickly (2-3 seconds) and when I process the dimension within the application it's more like 20-30 seconds, which makes me feel like it's not really connecting to BPC. But I'm not sure.

Secondly, I then tried to load the package into the Data Manager and run it from there. On doing this, the package immediately fails (less than 1 second). Of course, there is no error message or any log whatsoever. Can you think of any ideas why this would be?

Since my package isn't seeming to work, I decided to come at this from a different direction. I figured I just want to see if I can get the Admin Task to process the dimension without any of the other overhead. So, I made a copy of the Admin_MakeDim.dtsx that is available under the /EXAMPLES directory. This sample package prompts the user for the Dimension, Source Table name and processing options. I give it my Dimension Name ("Area"), Source Table ("mbrArea_Load"), choose yes and yes and run it.

This package seems to complete successfully. However, like before, it doesn't seem to have actually processed the dimension. No values in the GetAreaMember function and it processed in less than 2 seconds. Is there some setting I'm missing when working with the Sample Packages provided?

Again... Thanks for all of your help with this.

[Former Member · Accepted solution]

Ok. I have finally got the Admin Task to Process a Dimension Successfully. Very many thanks to Tim Klem and Jimmy Reaves for their advice. I wanted to update this thread with my learnings in case anyone is going through the same frustration I did.

My task was seeming to run successfully in both SSIS and within the Data Manager. What I discovered was that although the MakeDim seems to run "successfully", it is actually failing, but incorrectly posting a successful status. This happens when it completes very quickly (less than 10 seconds) and is caused by there being one or more issues with data in the staging table.

Some possible data issues could be duplicate IDs, invalid ParentH1 values, or special characters in the ID or ParentH1 values.

To resolve this, I inserted a Derived Column Task to load my Staging Table in SSIS. To clean up the data in each column, I replace any nulls with Blank Spaces (" ") and trim the field to ensure there are no extra spaces before or after the values within the field. Here's a sample expression:

ISNULL(ID) ? " " : TRIM(ID)

In addition to doing the above, I found that my existing data transformation process was using 2 separate staging tables to prepare the data for BPC. I was incorrectly using the first staging table in the process and once I started pulling data from the final staging table, everything started processing correctly.

One other piece of advice that was to be my next step was to backup the current Member Spreadsheet and then to copy the contents of my staging table directly into the Excel sheet through the Admin Console. I was told this is a good troubleshooting task because the Admin Console always gives the most detailed messages because instead of one step, it breaks everything out into multiple steps and you can see which one is failing.

That's all I've got. Hope this helps someone down the road!!

Thanks!

[Former Member · Accepted solution]

Thanks for the post-mortem, that's great advice about bringing your staging table into the Excel member sheet and processing via admin console, for debugging purposes. I'd forgotten that I had to do that myself.

[Former Member]

Dimensions and members;

This type of dimension maintained is fairly common, (automated dimension maintained). You can find a lot of specifics if you search the forum here, (sorry I don't have specifics).

Version 7 is much better at handling this.

Template;

There is not an auto update method for reports/templates. This is dependent on the design of the templates and how they expand based on the dimension structure.

Hope this helps.

[Former Member]

On BPC 5.1 and earlier, to update the member sheet automatically, it takes a bit of custom MS DTS programming to set up and maintain. I create an XLS "template" member sheet with all the member properties set up as column headers, and then create a couple tasks in your DTS package. 1 task copies the template into the adminapp\dimension.xls file (replacing the earlier version), and the next task pumps data from the mbrDimension table into the sheet. You need an Excel connection type in your DTS package too, to do this. Be aware that there are risks in doing so. For starters, any admin user may accidentally open the member sheet and make changes there, process them, and apply them "successfully" -- but only until your automated process runs again.

On 7 NW this happens differently, since the member sheet doesn't really exist in a permanent state -- it's generated automatically from the master data when you make the request to "maintain dimension members."

Either way, you need to think very carefully about how the entire dimension gets built. I find it's easiest to do this all via custom SQL programming on the backend, where I can control how the dimension gets built, what information comes from the source system (member ID, parent, any other master data that can feed into dimension properties) and what needs to be added internally (other dimension properties that are used in logic, input schedules, etc.).

I normally take an "all or nothing" approach to automation of dimension maitenance -- once it's set up with an automated interface of new members, there can't be any manual fiddling of certain properties in the member sheet. There's no way to control / prevent / validate your admin users from modifying things on the member sheet that they shouldn't.

So in some projects, where I don't think the client is really ready to assume those risks, I won't even create the member sheet as the last step of the automated dim processing. It's just too much of an attractive nuisance, they're certain to try to change something and mess things up in the process. (Plus, they need to maintain all the other dimensions manually, so it's understandable, from a usability standpoint, for a new admin to think that they can change this dimension via the same member sheet procedure. And there never is enough training it seems, so better safe than sorry.)

David - I'm curious about your comment regarding version 7 being better -- are you referring specifically to 7 NW, or are there also enhancements to the DTS tasks that ship with 7 MS? I haven't looked at 7 MS carefully yet, and I'd also be curious to hear about peoples' experiences with 7 NW and master data integration.

Regards,

Tim