Decision Points for use of Mulitple Main Tables in SAP MDM Single Repository
Disclaimer: This blog reflects my personal view on SAP MDM Multiple Main table concept and not my employers view. Please seek professional advice before implementing any solution based on the below blog.
With SAP MDM 7.1, the concept of Multiple Main Tables was introduced to bring the following advantages in SAP MDM Landscape:
Extended Flexibility for Data Modeling and Complex Objects:
- Multiple business objects (=main tables) can reside in one and the same repository. E.g. supplier and product data can be stored in one repository
- Records of different main tables can reference each other (new field type “Lookup [Main]”). E.g. a supplier can be related to several products
- Main tables can share the same customizing/lookup tables. E.g. the main tables “customers” and “suppliers” can be connected to the table “countries” in order to ensure consistent information about the country of origin of both business partners
Support for nested structures:
- New composite data type tuple allows modeling of nested structures. E.g. product capacity information depending on plant and location or multiple addresses of a business partner can hold multiple phone numbers
With this blog, I would like to share my experience on implementing the Multiple Main tables in a single repository. To explain my experience, I will use the following business scenario:
Assume that business requirement is to create a single repository for Customer, Vendor & Product because they reference each other and also share lookup tables like:
- A Customer can also be a Vendor. Business does not want to use the concept of Business Partner Role/Type, as the Target system does not support them. So you need Customer Main Table with a field Vendor Number of type Lookup Main referencing Vendor table.
- Every Product should have a Vendor i.e. Product Main table should have a field Vendor number of type Lookup Main referencing Vendor table.
- There is Hierarchy table like Region Hierarchy having levels like Region/Country/State/City getting referenced in both Customer & Vendor table.
- There are common lookup table like Status, Flags etc.
Following are some challenges in implementing Multiple Main tables in single MDM repository:
- Multiple MDM Developers will have to work on the same repository with multiple Main tables. If you have divided your development team based on the domain like Customer team, Vendor team and Product team, then a very good coordination is required between these teams for development and transports. Unlike in multiple repositories they can work independently.
- Any bug fix or new development requires entire repository to be unloaded causing all domains to be unavailable for business. Unlike in multiple repositories they can work independently.
- During Data load, special care needs to be taken for the dependencies. Like in above scenario you need to load the Vendor Main table data before Customer and Product.
- If the number of record in Customer Main table is like 500K+, during customer data load the entire repository is unavailable causing delay in Vendor/Product data load or use. If the number of records is less, then it is not a matter of concern.
- Any technical issue on the repository like a time consuming query or operation is performed on a single Main table which causes the other Main tables performance slow.
Following are some advantages in implementing Multiple Main tables in single MDM repository:
- Lookup table data needs to be maintained only in one place and is always in synch between multiple main tables. One has to update shared lookup/hierarchy table like Geography hierarchy, only in one repository. As this same table is shared by both Vendor and Customer. Unlike in multiple repositories, you have to maintain such tables in two places causing additional work.
- Repository maintenance becomes very easy for the MDM Administrator, as he has to maintain only one repository. Transport, Archive/Unarchive, Backups, Database setup everything becomes easy as compared to multiple repositories.
- Security or User Management needs to be done in only one repository.
- Data Modeling makes more sense to implement for business users coming from RDBMS backgrounds.
- Overheads of making connections to multiple repositories from other tools are reduced to only one connection setting.
Overall conclusion for implementing Multiple Main tables in single MDM repository:
- Decision on use of multiple main tables in single MDM repository should be based on the business need, number of records, and number of lookup/hierarchy/other tables shared between domains, complexity of data model, development/administration team’s strength and knowledge, etc. Both approach have their own advantages to choose from.
- Development phase and Testing phase is easy in separate repository for each domain.
- Production support or after GoLive phase is easy in single repository for multiple domain.
Thankyou for reading!