200,000 members

[Former Member]

We'd like to update a dimension that has 200K members. We do not have Excel 2007. Is there a way to process the members beyond the 65,000 row excel limitation? For Example can I export a dim file from sql to notepad, save the notepad file as a .dim file, and place it on the server and process?

Any body have any solutions?

Thanks,

Matt

[Former Member]

looks like James and I had almost the same thoughts on the same moment

[Former Member]

Matthew,

You might try the following.

When you process a dimension from admin, BPC will take the members from the sql table MBR<dimname> and process them into DIM<dimname>.

So when you update the MBR table directly in SQL, you can then process the dimension from administration so that they will become part of the application(s).

Please make sure that you can't process this dimension using excel anymore (by removing the xls from the adminapp directory).

Another way, and perhaps a better way is to create a ssis package that loads a txt/csv with all information about the members into an extra sql table and then use the ssis admin task to update the dimension automatically.

I have done this before, sent me an email if you want a sample ssis.

Always be carefull, create backups and test the package when you create these kind of tasks.

Hope this helps,

Alwin Berkhout

[Former Member]

You would have to update the member table (e.g. mbrAccount) directly in SQL and use the Admin_MakeDim.dtsx package under the examples folder on the file server as a sample package to perform the processing of the dimension.

From that point on, you would never use the XLS file for the dimension so the best practice is to delete the XLS from the AdminApp folder some someone doesn't accidently process the dimension from the XLS file.