Master Data Services

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Master Data Services

Master Data Services

Rate Topic

Display Mode

Topic Options

Author

Message

BU69

Posted Monday, November 01, 2010 1:01 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 20, 2012 1:35 AM
Points: 133, Visits: 280

I'm investigating MDS and have a question regarding keys and MDS.

First of all, is this the right forum to post this question in?

I am putting data from two systems into MDS, I had previously used my own master data DW to house this merged data where I had give each merged entry it's own SQL generated key which will then be used to identify this piece of data from then on accross other systems that use the master data as the two systems that I match data from use different identifiers to identify the same entity so a common identifier was needed, I then do incremental updates to the master data and the same ID is used.

What I want to know is how does MDS handle generating and maintaining surrogate keys, where do you set this up and how or does this have to be done from some other mechanism before data input into MDS?

Please ask me to elaberate more if needed.

Thanks

BU69

Post #1014054

Suzanne Selhorn [MSFT]

Posted Monday, November 01, 2010 1:59 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, January 14, 2011 11:29 AM
Points: 3, Visits: 5

I think what you're referring to might be the "Code" value in MDS. Every member in MDS has a name and code. Code is required but can be any value you want. Codes are used to differentiate members but don't necessarily correspond to keys in the MDS database backend.

In MDS you can create a business rule that sets an attribute value to a generated value. So for example, if your codes go from 1-100 right now, you can use a business rule to make all new members start with 105 and increment by 5 (or by any number you choose).

If you are looking for a way to use MDS to map members from two different systems, you can use a domain-based attribute (basically a picklist) which contains your list of sources. So Member A comes from Source X, and Member B comes from Source Y, etc.

Let me know if what I'm saying doesn't make sense or if you need more info.

There are MDS-specific forums here that you can post to if you'd like:
http://social.msdn.microsoft.com/Forums/en-US/sqlmds/threads

Suzanne Selhorn
Tech writer on the MDS team at Microsoft

Post #1014100

BU69

Posted Monday, November 01, 2010 2:39 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 20, 2012 1:35 AM
Points: 133, Visits: 280

So, what I think you are saying is that I need to use a domain based attribute as a lookup list so, for example, I would have a list of employees with unique self generated codes in the domain based attribute which also contains the IDs from each system that match, so when emplyee BU69 was updated from system A it uses the domain based attribute to look up the code that will be the front facing ID for the employee in MDS when the data is exposed using the views and the same if it was updated from system B or C.

My question would be on this though, how would it handle new records, could I use the business rules to manage if a new record or an employee that wasn't in the domain based attribute was found then add a new attribute with a new ID? Maybe I need to manage new records outside of MDS before putting them in though to make sure all systems have the correct mapping before giving them codes. I think I have a lot to think about.

Are there any standards for doing this when data is taken from several systems and require new a new code or ID?

Thanks for the link and for answering.

BU69

Post #1014134

Suzanne Selhorn [MSFT]

Posted Monday, November 01, 2010 2:57 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, January 14, 2011 11:29 AM
Points: 3, Visits: 5

Hmmm. A lot of questions here--hope I can answer them.

You can get creative with how you do this--there is no "one right way" for how to do things. You can use business rules to concatenate attribute values and to populate values based on other values, like this example.

For each employee, you might have:
Name, Code, Source System, Code in System A, Code in System B
Suzanne, 100-SysA, SysA, 100, None
Bob, 125-SysB, SysB, None, 125

In this case, Source System is a domain-based attribute, while Code in System A and Code in System B are free-form. (The whole point of making something domain-based is so you can group by it, and you don't need to group by code in this example.)
Code is a concatenation of the system code and the system name.

There is a "defaults to" business rule that allows you to set new default values. So you can set new members to default to a numeric code or a concatenated value, whatever you'd like.

I'd encourage you to post this on the MDS forum though if you'd like other people's ideas.

Hope this helps!

Post #1014150

BU69

Posted Monday, November 01, 2010 3:14 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 20, 2012 1:35 AM
Points: 133, Visits: 280

Thank you, it's given me a lot of ideas, I'll post it tomorrow.

Post #1014158

BU69

Posted Tuesday, November 02, 2010 2:16 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 20, 2012 1:35 AM
Points: 133, Visits: 280

I've post this question in the MDS forum: http://social.msdn.microsoft.com/Forums/en-US/sqlmds/thread/891ccef8-110b-4839-bdf2-a25188d4c5d6

Post #1014322

Ian C0ckcroft

Posted Thursday, January 17, 2013 1:57 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:23 AM
Points: 842, Visits: 741

Hey guys, just wondering how you do look ups? or if you should?
what if suzanne and bob had role information. which is kept in a role entity.

suzanne, fighter pilot
bob, master diver

The role entity has auto generated codes. When loading the employee entity, the role code for the corresponding code needs to be looked up.
whats the best way to do this?

regards

Ian Cockcroft
MCITP BI Specialist

Post #1408219

« Prev Topic | Next Topic »

Permissions