Creating SSIS Package for updating dimension memebers

[Former Member]

As per the requirement ,In SSIS Package we have loaded the data from excel file to a temp database table , and then called Admin_Task (create dimemsion) for processing the dimension where we specified the Source table and the New dimesion Name.

Source Table : Temp Table

New Dimension Name: Name of the diemsion

The SSIS Package gets executed succesfully within few seconds.

The data is coming in the MBR_newdimension table( name of the new dimension is specified in the admin_task.)

When we try to open the dimension through admin console(BPC for adminsinstrator) it shows the old data only.

How to get the updated data in the dimension

[james_lim · Accepted solution]

All,

I would like to answer your questions.

First, SSIS package for making dimension package, WE HAVE TO USE TEMP table instead of loading data into MBR table.

Reason is... If something goes wrong, you can't rollback to old mbr table.

Admin Task of BPC MS will backup MBR table before process it and if error happens, it will rollback from backup table.

If we load data into mbr table directly, we can take this benefit. That's why we need to use temp table which is same structure as MBR table.

Second, when you open the member from admin console, it is not coming from MBR table. it is old EXCEL member worksheet.

That's why you can see old worksheet.

The problem is... Usually user doesn't want to install excel in the application server so how can BPC program create excel worksheet without excel? That's why some customers create their custom script task to create CSV file from table and open it from excel and create updated membersheet.

Now we may use latest XML format for excel 2007 but some users still are using excel 2003.

I hope it helps you.

Thank you.

James Lim

[Jeffrey_Holdema]

I have moved this thread to the BPC MS forum. Notice the sticky [note|; at the top of the FPM - General (PCM, FC, Other) Forum whereby we announced new dedicated forums for BPC which are the proper place to post your questions regarding BPC in the future.

[Jeffrey Holdeman|http://wiki.sdn.sap.com/wiki/display/profile/Jeffrey+Holdeman]

SAP BusinessObjects

Enterprise Performance Management

Regional Implementation Group

[Former Member]

Did you find any luck to resolve this issue? If yes, could you please let us know how you resolved it?

[Former Member]

Hi Abhishek,

If you are using BPC version below 7.0 MS, the dimension member sheet doesnt get updated on updating the dimension members at back end. As a work around, you can create a SSIS package to update the Dimension member sheet in Admin templates on server, with members in mbrtable.

If you are using BPC 7.0 MS, we have Export function in the manage dimension tasks, which will update the Member sheet with members of mbrTable.

Kranthi

[Former Member]

Is there a reason why you did not load directly into the dimention table rather than to a temp table?

Also, check the log of the job ; usually it not that informative, but ust give a check.

Ravi Thothadri