November 1, 2010 at 1:01 pm
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
November 1, 2010 at 1:59 pm
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
November 1, 2010 at 2:39 pm
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
November 1, 2010 at 2:57 pm
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!
November 1, 2010 at 3:14 pm
Thank you, it's given me a lot of ideas, I'll post it tomorrow.
November 2, 2010 at 2:16 am
I've post this question in the MDS forum: http://social.msdn.microsoft.com/Forums/en-US/sqlmds/thread/891ccef8-110b-4839-bdf2-a25188d4c5d6
January 17, 2013 at 1:57 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply