locked
Logical separation of MDS database objects using different schemas RRS feed

  • Question

  • Hi,

    In the MDS database, the tables, views etc are all part of either the mdm schema or the stg schema. Is it possible to instruct MDS to use a defined schema name instead of mdm/stg such that the models that belong to a particular business group/functional area can be kept logically separate? Please note that we are using MDS 2012.

    Friday, October 26, 2012 9:23 AM

Answers

  • I would say no.  As you have probably seen, the tables/schema's used by MDM are not friendly to play with or manipulate.  You are probably left to using the concept of Models and Versions to accomplish what you are looking to do.  Or throw them on different DB instances entirely but that may be a larger footprint that you are willing to make.
    • Marked as answer by MSBI Dev 2012 Wednesday, November 7, 2012 9:37 AM
    Thursday, November 1, 2012 2:44 PM
  • An MDS database could contain many models, although all subscription views are in the mdm schema.  Adding schemas and synonyms is one way to add logical separation, as requested by the OP.

    This does bring a problem of maintenance, although this could be automated, eg

    pseudocode
    for each model
      for each subscription view
        create schema with same name as model (if doesn't exist)
        create synonym over the mdm view in the new schema
    This step could be included in your build process, or even fully automated using a DDL trigger.
    • Marked as answer by MSBI Dev 2012 Wednesday, November 7, 2012 9:37 AM
    Saturday, November 3, 2012 3:23 PM

All replies

  • I would say no.  As you have probably seen, the tables/schema's used by MDM are not friendly to play with or manipulate.  You are probably left to using the concept of Models and Versions to accomplish what you are looking to do.  Or throw them on different DB instances entirely but that may be a larger footprint that you are willing to make.
    • Marked as answer by MSBI Dev 2012 Wednesday, November 7, 2012 9:37 AM
    Thursday, November 1, 2012 2:44 PM
  • You could create schemas and then synonyms eg
    USE MDS
    GO
    
    CREATE SCHEMA myAppSchema
    GO
    
    CREATE SYNONYM myAppSchema.vw_yourSubscriptionView FOR mdm.vw_yourSubscriptionView
    GO
    
    SELECT * FROM myAppSchema.vw_yourSubscriptionView

    • Edited by wBob Friday, November 2, 2012 5:32 PM
    Friday, November 2, 2012 5:31 PM
  • From what I understand stg schema is used by the new staging process in MDS for loading data into MDS and the mdm contains all the other entity tables.

    So not sure if you will gain anything by trying to separate out into different schemas.


    Nagamani Muralidhar

    Friday, November 2, 2012 7:15 PM
  • An MDS database could contain many models, although all subscription views are in the mdm schema.  Adding schemas and synonyms is one way to add logical separation, as requested by the OP.

    This does bring a problem of maintenance, although this could be automated, eg

    pseudocode
    for each model
      for each subscription view
        create schema with same name as model (if doesn't exist)
        create synonym over the mdm view in the new schema
    This step could be included in your build process, or even fully automated using a DDL trigger.
    • Marked as answer by MSBI Dev 2012 Wednesday, November 7, 2012 9:37 AM
    Saturday, November 3, 2012 3:23 PM
  • Thanks All!
    Wednesday, November 7, 2012 9:36 AM