locked
Subscription View,Schema Modification and Full Text Search in MDS RRS feed

  • Question

  • Hi All,

    There is a restriction in MDS that if you have subscription views created on an entity and you want to add a column to the entity then you first need to drop the subscription view, modify the entity and create the view again.

    We need subscription views to do integration with SSRS and be able to replicate data into another database to be able to do full text search.

    Is there any way we can to do full text search in MDS itself and do SSRS integration without subscription views?


    Thanks, Himanshu.
    Friday, January 28, 2011 1:25 PM

Answers

  • Himanshu,

    This issue has been fixed.  I know in CU4 of MDS that this is not an issue.  You can add a column with an existing subscription view.  You just get a warning to regenerate the view.  You can use the view as is without the new column.  Using "regenerate" will recreate the view for you with the added column.

    Val Lovicz, CTO Profisee

    Wednesday, February 9, 2011 3:45 PM
  • Hi Sha

    Are you sure that you need Full Text Search Queries ?

    Why don't you use the MDS API to do text search on attributes ?

     

    (example on http://social.msdn.microsoft.com/Forums/en-US/sqlmds/thread/1e160096-3792-464e-a846-2694291103d6)

     string searchterm = String.Format(" [Color]='{0}' AND [Shape] = '{1}'", "Red", "Square");
    
    other example: 
    
     string searchterm = String.Format(" {0} LIKE '{1}' 0.5 1 0.32 ", SearchOn, txtMemberFilter.Text);
    
     public Collection<Member> GetMembers(Identifier modelId, Identifier versionId, CustomEntity entity, string SearchTerm)
      {
       EntityMembersGetCriteria emgc = new EntityMembersGetCriteria();
       emgc.EntityId = entity.entityId;
       emgc.ModelId = modelId;
       emgc.VersionId = versionId;
       emgc.AttributeGroupId = new Identifier();
       emgc.PageSize = 10000;
       emgc.DisplayType = DisplayType.NameCode;
       emgc.MemberReturnOption = MemberReturnOption.DataAndCounts;
       if (!string.IsNullOrEmpty(SearchTerm.Trim()))
        emgc.SearchTerm = SearchTerm;
    
       EntityMembersInformation emi = new EntityMembersInformation();
    
       using (var c = MDS_WSConnect.CreateMdsProxy())
       {
        Metadata md = new Metadata();
        OperationResult or = new OperationResult();
        EntityMembers em = c.EntityMembersGet(new International(), emgc, out emi, out or);
        return em.Members;
       }
    }
    

     

    Regards,



    Xavier Averbouch
    Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, February 10, 2011 10:24 AM
  • Hi Himanshu,

    To complete Val answer, here are the link for the latest SQL MDS update (CU4):

    http://support.microsoft.com/kb/2345451/

    and how to install it :

    http://blogs.msdn.com/b/mds/archive/2010/08/25/downloading-and-installing-sql-server-2008-r2-master-data-services-mds-cumulative-updates.aspx

    Regards,



    Xavier Averbouch
    Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, February 9, 2011 3:56 PM

All replies

  • Shalhim,
    You can create full-text indexes on your MDS database tables. Did you encounter any problem for it?

    For SSRS integration without subscription views, I am afraid that currently this cannot be done. I recommend that you submit a feedback to SQL product team at https://connect.microsoft.com/sql and hope this feature will be improved in future.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Thursday, February 3, 2011 9:31 AM
  • Hi Charles,

    MDS Subsciption views are not 'SCHEMABOUND', how do we create full text search Indexes on a view that is not SCHEMABOUND ?

    Thanks,

    Sha

     

     

    Tuesday, February 8, 2011 1:08 PM
  • Sha,

    Yes, you cannot create full-text search index on a view which is not an indexed view. I am not sure what you which information you want to get with full-text search, however since the data of the view actually come from the underlying tables, you may consider create full-text search indexes on the corresponding table columns. However this also depends on what kind of SSRS integration you want to get. May you elaborate your requirements on the integration with your SSRS?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Wednesday, February 9, 2011 3:42 AM
  • Thanks Charles.

    There are two scenarios that our customer is trying to solve.

    1.  Building a Custom Application (ASP.Net) to Query MDS data (attributes) using Full Text Search Queries.

            This is not possible because we cant create indexs on these views.  As i understand,Querying MDS tables directly is not a supported approach as the schema could change in future releases.  We have planned to replicate the data out to a different database and create full text index on these tables for querying. Although this is not an ideal way to handle this, we would like to know if there is any other to handle this.

    2.  Building SSRS report

              We are planning to use subscriptions views as data source for SSRS.  We dont see any issues in this approach.

    Thanks,

    Sha

     

          

    Wednesday, February 9, 2011 11:58 AM
  • Hi Sha K Anand,

    1) You can create custom sql views based on subscription views. (and use full text feature on these custom views ?)

    example: 

    SELECT Code, Name, MyAttribute1, MyAttribute2
    FROM mdm.MyMDSSubscriptionView
    

    By the way, you can also make this custom view updatable and update directly in MDS Tables (not using staging tables), but with some limitations :

    MSDN : Modifying data through a view

    http://msdn.microsoft.com/en-us/library/410e2812-4ebe-48b2-b95f-c7784f1c4336.aspx

    So you can also use entityframework and linqToSQL to read/update MDS data

    http://blogs.msdn.com/b/rickandy/archive/2008/10/04/how-to-create-an-updateable-view-with-ado-entity-framework.aspx

    2) You're right, Subscription views can easily be exposed onto SSRS

    Regards,

     



    Xavier Averbouch
    Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, February 9, 2011 2:08 PM
  • Himanshu,

    This issue has been fixed.  I know in CU4 of MDS that this is not an issue.  You can add a column with an existing subscription view.  You just get a warning to regenerate the view.  You can use the view as is without the new column.  Using "regenerate" will recreate the view for you with the added column.

    Val Lovicz, CTO Profisee

    Wednesday, February 9, 2011 3:45 PM
  • Hi Himanshu,

    To complete Val answer, here are the link for the latest SQL MDS update (CU4):

    http://support.microsoft.com/kb/2345451/

    and how to install it :

    http://blogs.msdn.com/b/mds/archive/2010/08/25/downloading-and-installing-sql-server-2008-r2-master-data-services-mds-cumulative-updates.aspx

    Regards,



    Xavier Averbouch
    Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, February 9, 2011 3:56 PM
  • Hi Xavier,

    As discuused in the thread earlier, MDS subscription views are not SCHEMA BOUND.

    So,  we cannot create a SchemaBound view on top MDS Subscription Views (Indexes can be created only on SchemaBound views)

    Thanks,

    Sha

     

     

    Thursday, February 10, 2011 9:40 AM
  • Hi Sha

    Are you sure that you need Full Text Search Queries ?

    Why don't you use the MDS API to do text search on attributes ?

     

    (example on http://social.msdn.microsoft.com/Forums/en-US/sqlmds/thread/1e160096-3792-464e-a846-2694291103d6)

     string searchterm = String.Format(" [Color]='{0}' AND [Shape] = '{1}'", "Red", "Square");
    
    other example: 
    
     string searchterm = String.Format(" {0} LIKE '{1}' 0.5 1 0.32 ", SearchOn, txtMemberFilter.Text);
    
     public Collection<Member> GetMembers(Identifier modelId, Identifier versionId, CustomEntity entity, string SearchTerm)
      {
       EntityMembersGetCriteria emgc = new EntityMembersGetCriteria();
       emgc.EntityId = entity.entityId;
       emgc.ModelId = modelId;
       emgc.VersionId = versionId;
       emgc.AttributeGroupId = new Identifier();
       emgc.PageSize = 10000;
       emgc.DisplayType = DisplayType.NameCode;
       emgc.MemberReturnOption = MemberReturnOption.DataAndCounts;
       if (!string.IsNullOrEmpty(SearchTerm.Trim()))
        emgc.SearchTerm = SearchTerm;
    
       EntityMembersInformation emi = new EntityMembersInformation();
    
       using (var c = MDS_WSConnect.CreateMdsProxy())
       {
        Metadata md = new Metadata();
        OperationResult or = new OperationResult();
        EntityMembers em = c.EntityMembersGet(new International(), emgc, out emi, out or);
        return em.Members;
       }
    }
    

     

    Regards,



    Xavier Averbouch
    Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, February 10, 2011 10:24 AM