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.

22 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. This is axapta ERP blog for Technical and functional fields and includes Microsoft Dynamics Axapta tutorials and Dynamics Axapta Coverage. This blog also contains x++ code help for Ax developer and solution of technical and functional daily issues. This blog is specific for Microsoft dynamics programming. Enterprise portal, SharePoint services, business connectors and Enterprise Resource Planning applications and sql database.It will help to get Microsoft Business Solutions.


    axapta ERP blog

    ReplyDelete
  14. 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
  15. 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
  16. Hi there, after reading this awesome paragraph i am
    too cheerful to share my knowledge here with mates.

    Also visit my site; cheap biz review

    ReplyDelete
  17. Hi, i read our blg from time tо time аnd і ownn a similar one аnd i wаs just curious if you get a loot of spam comments?

    If ѕo hoա do уou ѕtop it, any plugin ߋr anjything you caan recommend?

    I gеt so much latelу it's driving me madd ѕo any assistance is very mucɦ appreciated.



    Feel free tօ visit my homepage - buy phentermine (thephen375blog.yolasite.com)

    ReplyDelete
  18. Please let me know if you're looking for a writer for your
    weblog. You have some really good articles
    and I feel I would be a good asset. If you
    ever want to take some of the load off, I'd
    really like to write some material for your blog in exchange for a link back to mine.
    Please shoot me an e-mail if interested. Cheers!

    Here is my web blog Belinda Broido

    ReplyDelete