Viadeo

jeudi 10 mars 2011

SQL 2008 R2 Master Data Services : Bulk Data Insert with API

In this article, I will talk about Master Data Services.
You can find the source code of this example on my MDS project on Codeplex:
http://mdsmanager.codeplex.com  

Special thanks to Suzanne Selhorn at Microsoft for help and support and her new great book about Master Data Services that you can find on Amazon :
http://www.amazon.com/Microsoft-Server-2008-Master-Services/dp/007175623X

Update 30 april 2011
a part of a answer message from Tyler Graham about synchronous /asynchronous operations:
" I wanted to ensure you understood that EntityMembersCreate and its partners calls are synchronous calls that will return any and all errors founds in the MDS load. (More so in Denali where some problems have been fixed)  ModelMembersBulkMerge and any calls with the ‘Bulk’ keyword leverage staging and will return success no matter what the disposition of the records as the use of staging is asynchronous."


thanks to Tyler for this useful remark!


Note : 
__________________________________________________________________________________
EntityMembersCreate : only new member records with unique codes can be created with this operation. If a member code already exists, an error will be thrown in the returned error collection and the member will not be created or updated.

EntityMembersMerge : Many users have been confused by this operation and believe it has something to do with record survivorship or match merge functionality. This operation does nothing to look for possible matches or provide any survivorship functionality; it only allows users to create and update records simultaneously within MDS.
Information source : 
book: Master Data Services "Implementation and Administration" by Tyler Graham and Suzanne Selhorn
__________________________________________________________________________________
So, what's the point:

you want to insert a big number of members in a MDS entity, with API (MDS WebService)
first solution :


Second Solution :


___________________________________________________________________
So let's see the 2 solutions in details :


First solution  : (slow, not recommanded)
inserting each member with API method EntityMembersCreate

example:
 private void btTest_Click(object sender, EventArgs e)
        {
            if (lstModels.SelectedItem != null && lstVersions.SelectedItem != null)
            {
                string pEntityName = (ucManageEntities1.lstEntities.SelectedItem as CustomEntity).Name;
                string pModelName = (lstModels.SelectedItem as Identifier).Name;
                string pVersionName = (lstVersions.SelectedItem as CustomVersion).Name;
                List<CustomMember> lst = new List<CustomMember>();

                for (int i = 1; i <= 4000; i++)
                {
                    MemberIdentifier mi = new MemberIdentifier() { Code = "Code" + i.ToString(), Name = "Name" + i.ToString() };


                    Member m = new Member() { MemberId = mi };
                    m.Attributes = new Collection<Common.ServiceReference1.Attribute>();
                    Identifier attId = new Identifier() { Name = "myTestAtt" };
                    m.Attributes.Add(new Common.ServiceReference1.Attribute() { Identifier = attId, Value = "AttValue" + i.ToString() });

                    CustomMember cm = new CustomMember(m);

                    lst.Add(cm);
                }

                EntityMemberCreate(pEntityName, pModelName, pVersionName, lst, true);
            }
        }


        public EntityMembers EntityMemberCreate(string pEntityName, string pModelName, string pVersionName, List<CustomMember> pMdmMembers, bool bCreate)
        {

            International international = new International();


            EntityMembers entityMembers = new EntityMembers();
            System.Collections.ObjectModel.Collection<Member> members = new System.Collections.ObjectModel.Collection<Member>();
            entityMembers.Members = members;
            entityMembers.EntityId = new Identifier() { Name = pEntityName };
            entityMembers.ModelId = new Identifier() { Name = pModelName };
            entityMembers.VersionId = new Identifier() { Name = pVersionName };
            foreach (var mdmMember in pMdmMembers)
            {

                Member member = new Member();
                MemberIdentifier memberIdentifier = new MemberIdentifier();
                memberIdentifier.Code = mdmMember.Code;
                memberIdentifier.MemberType = MemberType.Leaf;
                memberIdentifier.Id = new Guid();
                memberIdentifier.Name = mdmMember.Name;
                member.MemberId = memberIdentifier;
                member.Attributes = new System.Collections.ObjectModel.Collection<Common.ServiceReference1.Attribute>();
                foreach (var myAttribute in mdmMember.mbr.Attributes)
                {
                    Common.ServiceReference1.Attribute attribute = new Common.ServiceReference1.Attribute();
                    attribute.Identifier = new Identifier() { Name = myAttribute.Identifier.Name };
                    attribute.Value = myAttribute.Value;
                    attribute.Type = AttributeValueType.String;
                    member.Attributes.Add(attribute);

                }

                members.Add(member);

            }
            if (bCreate)
            {
                using (ServiceClient c = MDS_WSConnect.CreateMdsProxy())
                {
                    DateTime dtBefore = DateTime.Now;
                    OperationResult or = c.EntityMembersMerge(international, entityMembers);
                    DateTime dtAfter = DateTime.Now;
                    TimeSpan ts = dtAfter.Subtract(dtBefore);

                    MessageBox.Show("members merged : " + entityMembers.Members.Count() + "\r\n" + "time elapsed (seconds):" + ts.TotalSeconds.ToString());

                }
            }
            return entityMembers;
        }

Second Solution : with API method ModelMembersBulkMerge (fast, recommanded)
example:

 private void btTest2_Click(object sender, EventArgs e)
        {
            if (lstModels.SelectedItem != null && lstVersions.SelectedItem != null)
            {
                string pEntityName = (ucManageEntities1.lstEntities.SelectedItem as CustomEntity).Name;
                Identifier pModelId = (lstModels.SelectedItem as Identifier);
                string pModelName = (lstModels.SelectedItem as Identifier).Name;
                Identifier pVersionId = (lstVersions.SelectedItem as CustomVersion).Identifier;
                string pVersionName = (lstVersions.SelectedItem as CustomVersion).Name;
                List<CustomMember> lst = new List<CustomMember>();

                for (int i = 1; i <= 4000; i++)
                {
                    MemberIdentifier mi = new MemberIdentifier() { Code = "Code" + i.ToString(), Name = "Name" + i.ToString() };


                    Member m = new Member() { MemberId = mi };
                    m.Attributes = new Collection<Common.ServiceReference1.Attribute>();
                    Identifier attId = new Identifier() { Name = "myTestAtt" };
                    m.Attributes.Add(new Common.ServiceReference1.Attribute() { Identifier = attId, Value = "myValue" + i.ToString() });

                    CustomMember cm = new CustomMember(m);

                    lst.Add(cm);
                }
                Collection<EntityMembers> colEntMembers = new Collection<EntityMembers>();
                colEntMembers.Add(EntityMemberCreate(pEntityName, pModelName, pVersionName, lst, false));

                DateTime dtBefore = DateTime.Now;

                //filling new staging batch with entityMembers
                Collection<Identifier> colStaging = ModelMembersBulkMerge(colEntMembers);
                //initiating the staging process
                Collection<StagingBatch> colBatches = StagingGet(colStaging, true, true, true, false);
                //triggering staging
                ProcessUnbatchedStaging(pModelId, pVersionId);

                DateTime dtAfter = DateTime.Now;
                TimeSpan ts = dtAfter.Subtract(dtBefore);

                MessageBox.Show("members (bulk) inserted  : " + colEntMembers.First().Members.Count() + "\r\n" + "time elapsed (seconds):" + ts.TotalSeconds.ToString());


            }



        }
        //triggering staging for a specific model and version
        public void ProcessUnbatchedStaging(Identifier modelId, Identifier versionId)
        {
            using (ServiceClient c = MDS_WSConnect.CreateMdsProxy())
            {
                OperationResult or = new OperationResult();
                c.StagingProcess(new International(), true, new StagingUnbatchedCriteria() { ModelId = modelId, VersionId = versionId }, out or);
            }
        }

        //get information related to a staging batch
        public Collection<StagingBatch> StagingGet(Collection<Identifier> stagingBatch, bool ReturnAllCriteria, bool ReturnMembers, bool ReturnAttributes, bool ReturnRelationShips)
        {
            Collection<StagingUnbatchedInformation> colUnbatched = new Collection<StagingUnbatchedInformation>();
            Collection<StagingBatch> colBatches = new Collection<StagingBatch>();
            using (ServiceClient c = MDS_WSConnect.CreateMdsProxy())
            {
                OperationResult or = new OperationResult();
                colBatches = c.StagingGet(new International(), false, new StagingResultCriteria() { All = ReturnAllCriteria, Attributes = ReturnAttributes, Members = ReturnMembers, Relationships = ReturnRelationShips }, new StagingSearchCriteria() { StagingBatches = stagingBatch }, out or, out colUnbatched);

                return colBatches;
            }
        }
        public Collection<Identifier> ModelMembersBulkMerge(Collection<EntityMembers> modelMembers)
        {

            Collection<Identifier> colBatches = new Collection<Identifier>();
            using (ServiceClient c = MDS_WSConnect.CreateMdsProxy())
            {

                OperationResult or = new OperationResult();
                or = c.ModelMembersBulkMerge(new International(), modelMembers, out colBatches);


            }
            return colBatches;
        }

Aucun commentaire:

Enregistrer un commentaire