locked
Bulk update of master data RRS feed

  • Question

  • Hi All,

    Excuse me if I've missed something obvious being a newbee in MDS. I'm facing the following businesscase.

    My MDS system has 1000 + customerrecords of which I want to update a specific field. Would it be possible/do-able to do this in MDS or should I move away from MDS and use SSMS using Tsql update statements. I'd prefer to keep the update-transactions for each specific record.

    Any ideas on this ?

    Your help would be appreaciated.

    Cees

     


    Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.
    Tuesday, September 14, 2010 12:49 PM

Answers

  • Cees,

    1. Yes you're right, that makes 2000 rows for 2 attributes to update on 1000 rows.

    2. SSIS is the right way to do it.

    Regards,


    Marius
    Tuesday, September 21, 2010 9:11 PM

All replies

  • Hi Cees,

    There is a dedicated feature on the Integration area - a set of staging tables with all the stuff: batch handling, validation, tracking etc.

    A good starting point would be here: http://msdn.microsoft.com/en-us/library/ee633726.aspx.
    As for the import itself, it is like you usually would do it: BULK inserts, SSIS etc.

    Hope it helps,

     


    Marius
    Wednesday, September 15, 2010 3:17 PM
  • Hi Marius,

    Thnx for your reply, but just for my understanding.

    Allthough I'm also into SSIS, I was wondering if it is possible to do things like this without using SSIS, simply using some functionality from MDS  ? Is it somewhere in there, or is manual data-entry the only option when using the MDS client ?

    Like to hear from you.

    Cees


    Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.
    Wednesday, September 15, 2010 9:27 PM
  • Hi Cees,

    maybe you could update your data with a business rule, (by using change value or default value)

    you can check this example here:

    http://sqlblog.com/blogs/mds_team/archive/2010/02/03/creating-a-simple-business-rule.aspx

    Regards,

     


    Xavier Averbouch
    Monday, September 20, 2010 8:54 AM
  • Hi again Cees,

     

    Can I have more elements on your demand? It's not very clear what are you trying to do: update a field with a calculated/fixed value, or update it with an external value?

     

    Thanks,


    Marius
    Tuesday, September 21, 2010 1:49 PM
  • Hi Marius,

    Thnx for getting back to me. Forgive me if I'm not using the right words to express myself, not being used to the MDS specific verbs and nouns. In transact sql, let's say this is what I would want to achieve:

    update dim_customer

    set country = 'NL' where country = 'NLD'

    Kind regards,

    Cees

     

     

     


    Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.
    Tuesday, September 21, 2010 2:22 PM
  • Cees,

    That's a perfect candidate for a business rule, as Xavier said two message ago (I'll repost the link - http://sqlblog.com/blogs/mds_team/archive/2010/02/03/creating-a-simple-business-rule.aspx).

    Tipically, you will create a rule based on:
    - a condition of "attribute Country equals  'NLD'"
    - an action of "change value of Country to 'NL'"

    Hope it (finally) helped :)


    Marius
    Tuesday, September 21, 2010 3:23 PM
  • Hi Marius,

    Thnx a lot  for your patience & help. Currently I don't have access to the MDS I was working on, therefor I'll do some reading first, and then will get back to you and Xavier.

    r,

    Cees


    Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.
    Tuesday, September 21, 2010 5:00 PM
  • Hi Marius & Xavier,

    Ok, done some reading. From what I've seen I need to use the attributes staging table (mdm.tblStgMemberAttibute) in the Master Data Services database to:

    • Update attribute values of existing members and collections.

    Getting back to my original question, I now understand that I need to populate this table with 1000 rows having a new value for one of its attributes.  Right ? If I need to modify 2 attributes I probably need 2000 rows ?

    If so, what would be the preferred way to populate this table, SSIS or something else ?

    Thnx for your help sofar.

    r,

    Cees


    Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.
    Tuesday, September 21, 2010 8:59 PM
  • Cees,

    1. Yes you're right, that makes 2000 rows for 2 attributes to update on 1000 rows.

    2. SSIS is the right way to do it.

    Regards,


    Marius
    Tuesday, September 21, 2010 9:11 PM
  • Hi Marius,

    Just one follow-up question. When I think of a datasteward being an MDS end-user, not having SSIS-skills, how would (s)he solve this ?

    Kind regards,

    Cees

     


    Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.
    Wednesday, September 22, 2010 10:30 AM
  • Hi Cees,

     

    you can build your SSIS package for import Data and use SSIS XML Configuration variables

    if you have only a few values to set , you can set any variable in the XML Configuration:

    e.g. a variable with the attribute name and a variable with the value to set

    your MDS datasteward end user will just have to double click on the pre configured SSIS Package in order to import some data and maybe change some values for the variable in the SSIS package XML configuration file before launching this package.


    Xavier Averbouch
    Friday, September 24, 2010 12:26 PM