Bulk update of master data

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.

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

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

Answer 7

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.

Answer 8

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.

Answer 9

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

Answer 10

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.

Answer 11

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