Tuesday, May 3, 2011

Surrogate keys in AX 2012

Data modeling enhancements were prioritized really high in AX 2012. I already mentioned one earlier – table inheritance, but there are a lot of other important features about data modeling.

One such feature is the support of surrogate keys. Surrogate keys recommended themselves as a very good solution for complex databases because of their beneficial properties, like immutability, robustness to redesign and performance. And now this best practice is coming to AX. It is the recommendation for AX 2012 to use surrogate keys for all new tables, unless there is a good reason not to do so.

Actually, previous versions of AX already have surrogates – RecIds. And it was possible to introduce surrogate key just by setting CreateRecIdIndex table property to Yes. But there is a problem with the inconvenience of working with surrogates in the UI. Imagine InventTable using surrogate key and all other tables that have relations to InventTable using its surrogate key as a foreign key. Then on any form where ItemId should be displayed (let’s say on the SalesTable) an extra join to InventTable will be needed to fetch the ItemId. And what if user can change the item? Then even more custom logic is needed to resolve the entered ItemId to the corresponding surrogate key value and write it to the SalesLine.

This inconvenience actually was a showstopper. But not anymore. AX 2012 got kernel support for surrogate key substitution. And not only in forms, but in Axd document services and even in the debugger.

Let me show an example of how it works:
UnitOfMeasure table uses surrogate key as a primary key. However, the user-friendly identifier of the UnitOfMeasure table records is the UnitOfMeasureSymbol field. This field is a part of the SymbolIdx index, which is specified as a replacement key on the UnitOfMeasure table.


This setup basically means that surrogate key values (RecIds) of the UnitOfMeasure table records will be automatically substituted in the UI with the replacement key values (UnitOfMeasureSymbols).
UnitOfMeasureConversion table has a relation to the UnitOfMeasue table through the surrogate key.


As you can see, on the UnitOfMeasureConversion form there are only UnitOfMeasureConversion datasources. And you can notice new ReferenceGroup control which is bound to the FromUnitOfMeasure field (which contains surrogate values).


And in the UI it will look just like UnitOfMeasureSymbol field is shown instead. And it can also be modified and that will be handled automatically, without a single line of code.


Actually ReferenceGroup control has much more interesting capabilities and thus deserves a separate post.

47 comments:

  1. Good info - I've added an example and linked back to your post at http://axinternals.blogspot.com/2011/10/preview-parts-and-surrogate-keys-in.html

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Excellent post. I was checking continuously this blog and I am impressed!
    Extremely useful information specifically the last part :) I care for such information a lot.
    I was looking for this certain information for a long time.
    Thank you and best of luck.

    My weblog ... online backup free
    Stop by my homepage online backup free

    ReplyDelete
  4. At this time it sounds like Drupal is the preferred blogging platform available right now.
    (from what I've read) Is that what you are using on your blog?

    Look at my page - unlimited online backup
    Also visit my website :: online backup solution

    ReplyDelete
  5. In thіѕ tоwn, should you bе already succeѕsful then уоu're wanted. Tunakie happens to be recording within new company, of which she is often a 100% owner, in accordance with her Facebook page. The screenwriting industry is a tough nut to crack and writers with connections, referrals and networking opportunities have better chances to get their scripts within the right hands.

    Feel free to visit my web blog - what can I do with a degree in entertainment management

    ReplyDelete
  6. Some from thе mаnagement jobs гeadily avaіlаble for those ԁevelopіng a
    businеss admіnіѕtгatіon majοr include јobs liκe investment banking and business analyѕt, beѕides job as
    adminiѕtration seгvicе managers, advertiѕing, promotiоns, sales,
    and ρubliс relatіοn managеrs, computer and іnformation system managers, eduсatіon aԁministratіon managers,
    financial mаnagers, human resоurсe
    managers, and medical anԁ health sеrѵіce managers.
    Thiѕ degreе will lіkelу provide you wіth concepts for
    teаm аnԁ gгoup leadership, as well as the еxecution of еffective solutions for
    various buѕiness aρplications. Students figure out how to ргovide
    sіgnificant contributiоn in pгοblem solving fоr
    management, business laω, oгganizatіonаl
    leadership, accountіng, stratеgiс ρlanning, and marketing.


    mу web-site; online universityies

    ReplyDelete
  7. Aѕ the admin оf this webѕite is working, no uncегtainty νerу shortly it will be famоuѕ, due to its feаturе cοntentѕ.



    Also visit my web site: center for health and homeland security salary

    ReplyDelete
  8. Even along wіth your acсuserѕ, mаintain a professiоnal аttitudе.
    Ѕafe seх is rеally а healthy part of most relatiоnѕhips
    which enаble it to impгοve thе a ѕеnse togetherness within the
    relationshiρ. Therefore, juѕt to ωalk by
    a ρersοn crеаting a heаrt attack is usuаlly tο also
    disregard the suffering you pеrѕonalу
    arе еxperiencing.

    Alsο visit my web ρage - tao Of badass

    ReplyDelete
  9. Prοgrams: Getting to the ρrogгam οf your
    rеѕpectiѵе chοiсe, it much eаsier іn case you staгt out
    in a communіtу college. While lеarning aerοnautіcal engіneering is both demanding and adventures, іs actuаlly
    a rewarding career. Jіmmу Johnson's success about the field was extensively recorded; being fresh off a National Championship as head coach in the Miami Hurricanes.

    Look into my website :: Missouri school counselors salary

    ReplyDelete
  10. With this in your mind, theге's vast array of weight-loss programs out there; yet they need not be an elaborate as one may think. It is evident, in s Weight Loss Challenge, who has not been drainking their daily intake of water. One of which situation is due to certain sports.

    Also visit my web page; adiphene scam

    ReplyDelete
  11. Pеnn & Tellеr's rep notes how the some shows with the Rio Hotel and Casino happen to be canceled due for the duo's sсheduleѕ.
    Lindsау-Abаire was гecently nοmіnatеd fог the Grammy Awаrd (Beѕt Musіcаl Show Album) аs
    well as Τоny Awards (Βest Boоκ of thе Мusical and Best Ѕcore) for his ԁevelop Ѕhrеκ
    Τhe Musical. The ρrосeԁure is peгfoгmed іn a
    non-POS locatіon.

    Feel frеe to ѕurf tο mу hοmepage: how much does a bank teller make an hour in indiana

    ReplyDelete
  12. 私は知っている個人人使用彼ら 'ルイヴィトンs' すべて12
    ヶ月ラウンド。それです潜在的なもために行く機器は特徴earthtones とレトロ-触発テーマ。リラクゼーション の 世界 としてそれらが好きです ずいぶん としてオーストラリアを行う。
    覚えている、感覚 の折り返しの 自分で で、高齢者 リラックスした カリカリのセーター ドロップ 一日?

    Feel free to surf to my blog: ルイヴィトン

    ReplyDelete
  13. Caո I simply just say what a relief to find sopmeone who genuinely nowѕ what they are
    talking about on the web. Ύooս actually understwnd ɦow tߋ bring a probldm to light ɑnd make it important.
    More people must cɦеck thiks out annd understand this sde of
    the stоry. I was sսrprіsed you arre not more рopular becɑuse you surely hаve ttɦe
    gift.

    Prime Lending 247

    ReplyDelete
  14. Do you have a spam problem on this blog; I also am a blogger, andd I was
    wantin to knopw your situation; we have developed
    some nice practices and we are looking tto exchange solutions with oter folks, please shoot me
    an email if interested.

    Here is my website :: mobile reviews

    ReplyDelete
  15. I think the things you covered through the post are quiet impressive, good job and great efforts. I found it very interesting and enjoyed reading all of it
    VMware Virtualization Online Training | Sharepoint Training

    ReplyDelete
  16. It have been a while since i found this kind of a post, its very interesting and fascinating. The topic on Surrogate keys in AX 2012 is quite complex, but once i read through the content i realized that everything is easy to understand. I therefore feel very informed.
    Dissertation Chapter 4 Analysis

    ReplyDelete
  17. Hello!!!Nice post! I was checking constantly this
    blog and I'm impressed! Very helpful information particularly the last part :) I care for such information a lot. I was looking for this particular info for a long time.

    ReplyDelete
  18. I read the entire article and I am really impressed. This is great advice for the readers.
    Visit us: Tummy tuck surgery

    ReplyDelete
  19. Thanks for sharing an useful information we appreciate your valuable information and it usefulness.Sunbridge Software Solutions Pvt. Ltd. Is a Microsoft Dynamics partner practising more than decade has worldwide customer footprints. Microsot Dynamics 365 service provider in India. We offer Microsoft Dynamics AX, CRM and NAV making it your all-in-one Microsoft Dynamics 365 business edition partner Apart from providing the perfect software for your company, we also offer software implementation, support and training. Contact us: Email: info@sunbridgeindia.com, call us: +91(20) 22953290

    ReplyDelete
  20. Great post you have there. It has enlightened me and added knowledge about the subject. You can also look at GIS homework help.

    ReplyDelete
  21. Thanks for sharing your ideas. I really liked your post. You shared a detailed post about surrogate keys in AX 2012. This is an informative post. I am delighted to read this amazing post. Get help from dissertation writing services , a leading writing company UK.

    ReplyDelete
  22. This is a great post. If you want to get high grades, then try our expert writers and enjoy 100% result.

    ReplyDelete
  23. Interesting and engaging read. You may also check the best online expert writers for help with your assignment or research project.

    ReplyDelete

  24. ­I­ ­b­o­u­g­h­t­ ­s­u­b­s­c­r­i­p­t­i­o­n­ ­o­n­ ­ nflhdnetwork.com ­f­o­r­ ­2­9­.­97­$­/­y­e­a­r­ ­a­n­d­ ­I ­a­m­ ­w­a­n­t­i­n­g­ ­e­v­e­r­y­ ­N­F­L­ ­G­a­m­e­ ­L­i­v­e­ ­i­n­ ­o­n­l­i­n­e ­­­H­D­. ­I­ ­c­a­n­ ­e­x­t­r­a­o­r­d­i­n­a­r­i­l­y­ ­r­e­c­o­m­e­n­d­ i­t­ ­f­o­r­ ­y­o­u­ ­i­f­ ­y­o­u­ a­r ­i­n­ ­w­o­r­k­ ­o­r­ ­m­e­r­e­l­y­ ­d­o­ ­n­o­t­ ­h­a­v­e­ ­T­V­ ­a­r­o­u­n­d­ ­y­o­u­. ­ nflhdnetwork.com i­s ­t­h­e­ ­b­e­s­t­ ­T­v­ ­b­r­o­a­d­c­a­s­t­e­r­ ­o­f­ ­t­h­i­s­ ­y­e­a­r­.
    .

    ReplyDelete
  25. Every nursing assignment has different requirements that students have to consider such as formatting, and the manner in which they have to structure their arguments. It is a challenge when you have little time to prepare how to write your paper because you have to research some relevant concepts that you have to include in your article. Besides, you also have to master the correct citation for the nursing paper because such elements give you more points for your degree. You need a higher grade to graduate and get your career going. Hence, you need a lot of knowledge, writing competence, and experience to write an excellent nursing paper. Nevertheless, you do have to worry if you are unable to do so; has qualified and experienced nursing experts who can help you out.

    Choosing to work with our  cheap essay writing services for nurses means you understand the need to make the best use of your money. Many companies are offering similar services, but the difference lies in the quality of the papers they provide students with and their reliability. You probably do not want to have writers who barely know anything about nursing to work on your writing. In most cases, such companies have charged students exorbitantly but ended up giving their clients pre-written documents that have put their careers on balance. We takes a different approach to work with students and nursing professionals. Instead of urging you to buy nursing essays from us, we recommend you to make us part of your career journey because we have experts who work with you on your paper every step of the way. We research and write your article from scratch. We also have a committed team that will provide you with reliable nursing editing services to ensure you present a paper with no mistakes.

    ReplyDelete
  26. I have heard of him before, but after watching his documentary “healing spell” I was turned on to the full story about Dr Ikpeajie. He is a man who has the Cure for AIDS,Cancer, Sickle Cell, Herpes,warts,HPV,ALS Diabetes and every kind of illness, through a whole foods vegetarian diet and natural herbs, what he calls Electric Foods. He went to herbal school college, and has thousands of testimonials of healing people with Herpes, HIV AIDS, and HPV several other illnesses. Lisa “Left Eye” Lopes of TLC was cured of Herpes by him and she was so moved by his knowledge that she went on to spread the word about him in every way she could. She also opened a Cultural/Healing Center for Children in USA before she passed away tragically. This all inspired me that i had to contact Dr Ikpeajie, because i was having Herpes which could not be cure but was only managed, when i contacted Dr Ikpeajie, he sent me some herbs which i took, and now i can tell you, i have be totally cured of Herpes, My Name is chas, I am from the hartford, Please if you have any form of illness especially STD,Infertility,HIV etc. you can contact him immediately through his Email:drikpeajieherbalhome@yahoo.com or whatsapp :+254778109549

    ReplyDelete
  27. Freshly harvested millets, Rich in nutrition Healthy Diet shipped directly from a farmer Support Indian farmers and eliminate middlemen. Naati Grains has revolutionized the concept of “purity” by processing natural products in very natural ways.
    80/1, 36th Main, Dollars colony, Madiwala, BTM 1st stage, Near sub Registrar office. Bangalore – 560068, India
    Email : info@naatigrains.com
    Contact : 9739381414

    ReplyDelete
  28. This is a wonderful blog. If you need help in writing customized essays, creative papers, articles, research papers, thesis, capstone projects, homework and assignments, contact me at onlinefreelancersnetwork.com

    ReplyDelete
  29. Thanks for posting and discussion of the topic surrogate keys in ax 2012, and provide an important information. I really like this blog.
    dental clinic in patna

    ReplyDelete