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