Bulk Insert, Domain Based Attributes


  • Hi

    I have a product model similar to the ProductSamplemodel that ships with MDS. i.e. Product Category, Product Sub Category entities. Both of these entities have been created to automatically generate a code - as a code does not exist in the business only a description.

    The product entity has both the category and sub category attributes as domain-based attributes which results in storing the code physically and also displaying the description.

    From a bulk insert scenario where the business has a number of new products that they want to add (due to a new range) I was suggesting that they use the Excel AddIn. This would allow them to cut and paste the data into the product entity. However as the category and sub category are domain-based Attributes they will need to know the ID – they will only know the name.

    From a MDS functionality point of view is it possible to get it to look up the code from the name?

    My current understanding on how to achieve this would mean I would need to have both code and name attributes on the Product entity for Category and Sub. The name attributes would be free text and the code attribute would be populated from a business rule. Downside to this approach is that you lose out on the nice dropdown pick list and the user does not know what a valid entry is as they can no longer select one.

    How is this usually implemented or handled in MDS?




    Mittwoch, 4. Dezember 2013 03:14

Alle Antworten

  • They should be able to see both Code and Name in the dropdown of Excel. Both of these are displayed by default. btw your Code could be same as name if its unique.
    Mittwoch, 4. Dezember 2013 14:56
  • I would also be interested in how people have dealt with this issue. It seems the domain-based fields can only be populated with valid data if in the  format code {name} .

    I was wondering if the Data Quality Services might be able to assist via the "Match Data" icon and if anyone has had an success.

    Currently, due to time constraints, am looking to provide an Excel template to preformat the raw data before "combining" as a workaround but this not an ideal solution. And am considering LightSwitch as an alternative as MDS is not wholly established here yet.

    Freitag, 10. Januar 2014 17:32
  • Domain based attribute will take a "Code",  (not "Code {name}" in the Excel field. If that Code is same as Name you are done.

    If you don't know it you could keep it blank to be filled out later via user input.

    Also you could write an Excel formula to Convert whatever value in your original data to valid Code that will be used in the Domain-based field.

    I've tried Match Data before but functionality is very limited. It seems much easier to massage the data for import with SQL , or Excel.


    as a side I never use self generating code, but something that can be generated via formula out of underlying data, so Code can be generated on the fly.

    If not possible then its still something + random to at least get part of the coding. (not sure it that makes sense)

    Freitag, 10. Januar 2014 22:25
  • Many thanks, I can see now that the "Code {name}" is not required, the data validation error in the cell threw me. But publishing with just the "Code", followed by a refresh puts everything back in order.
    Montag, 13. Januar 2014 10:23
  • Kevin

    I have a similar need, however rather than battle with the issue within MDS, I export the data, and then merge it with new products, subcategories and categories etc via  Stored Procedures (which are then executed on a daily basis) via SSIS.

    It is faster and most efficient. We have new products coming in each night and in an effort to retain referential integrity, this was a must. Further, stock balances must be updated on a regular basis against the OLTP tables. Thus this proved the best solution for me.

    I do know that you wanted to do it within MDS, however I do believe that you have better control on your data, doing all the leg work within a stored procedure.

    Should wish to see how I have done this, please feel free to contact me and I shall be glad to share some code with you.

    sincerest regards

    Steve Simon SQL Server MVP

    Dienstag, 21. Januar 2014 17:55
  • you can use ENTITY BASED Staging structure to import the data as you want into MDS through SSIS package.

    here is an example:

    You would require to import data as you want into the staging table (you can find staging table's name under MDS entity definition),

    You would set appropriate ImportType and BatchTag while importing data into staging

    then you would run the batch through stored procedure. this batch will import data into main entity table in MDS



    SQL Server MVP

    Blog:  Twitter:   LinkedIn:

    SQL Server Integration Services 2012 Tutorial Videos:

    Dienstag, 21. Januar 2014 20:21
  • Hi Kevin,

    Another approach (if the one above does NOT suit you).

    Once again, as Reza suggested and  I agree with him totally, this is a job for SSIS.

    For what it's worth...

    Whilst I understand what you are trying to do, may I suggest another approach. What concerns me is users adding data willy-nilly and that YOU have no control as to what is going on. Secondly, what is more disconcerting is the thought of losing relational integrity, especially if you are using derived hierarchies within MDS. 

    I would handle this in a different manner, using SSIS and a filesystem watcher.

    Let the users submit their spreadsheets (with their updates) to a common directory on the server. Implement a .NET FileSystemWatcher (this takes 10 minutes for a Newbie). This file system watcher will launch a DOS batch file on the arrival of a spreadsheet within the given directory. The DOS batch file  fires a DTS exec to start an SSIS package. This package together with SQL Server procedures will ensure that the correct codes are obtained and the attribute data is correctly inserted into the correct Entities(with the correct relationships).

    While this sounds vague, I do it all the time. I am more than prepared to help you get going, should wish any assistance. I KNOW that this is not the answer that you are looking for HOWEVER it is perhaps the most effective.

    sincerest regards

    Steve Simon SQL Server MVP

    • Bearbeitet StefaansMVP Sonntag, 26. Januar 2014 19:37
    Sonntag, 26. Januar 2014 19:28
  • This functionality would be particularly useful while performing an initialisation load of the data.

    I do agree, however, that SSIS is probably a better method from an operational perspective.  Even then, it would be handy to allow matching on the Code or Name.

    Mittwoch, 12. Februar 2014 02:50