Master Data Types (MDT) database design is one of the fundamental building blocks of any IBP implementation, in my experience as an IBP consultant.

Therefor, I would like to briefly illustrate two alternative ways to model a requirement which is often important for clients. Namely ability to limit/restrict the possibilities of a data entity based on one or more other data entities. The two alternative ways are: using Attribute Checks; or by using Compound MDT.

We’ll take a dummy example, just to focus on the interlinking of data in database design. For simplicity, we’ll assume that all MDT will be uploaded via the Data Integration app.

Imagine a client who has the need to capture product Demand based on:

• Product
• Location

Further more, any Demand can hold some additional hard-coded Comment.

To keep it easy, let’s assume there is only 1 Product (a Ball):

PRODUCT (Simple MDT)

The user uploads the following CSV, into the Simple MDT PRODUCT:

We will not focus more on the Product here. A Location, on the other hand is composed of two separate entities:

• Region
• Position

Let’s look at these entities in detail. We assume there are only 2 Regions (A and B):

REGION (Simple MDT)

The user uploads the following CSV, into the Simple MDT REGION:

Equally, there are only 2 Positions (North and South):

POSITION (Simple MDT)

The user uploads the following CSV, into the Simple MDT POSITION:

Now, we would like that the user can only upload a Location (which is a combination of Region and Position):

• only if the Region of that Location exists in the MDT REGION; and
• only if the Position of that Location exists in the MDT POSITION.

One way to meet this requirement is to use Attribute Checks. SAP Help Portal documents Attribute Checks in IBP as follows:

“To help to ensure the quality and consistency of your data, you can set up attribute checks that are carried out when you upload master data types to SAP Integrated Business Planning. […] When you upload the values from your CSV file, the system checks whether the values in your file are permitted and rejects any records that are determined to be illegal.”

Let’s put it into practice via our example:

LOCATION (Simple MDT with Attribute Checks)

As a result of the two Attribute Checks (represented by the green lines in the scheme above), a user would be able to upload Location values A/North (consisting of Region A + Position North), A/South, B/North and B/South as a Simple MDT:

But if a user would attempt to upload Location C/South (consisting of Region C + Position South) this would result in a rejection error due to the Attribute Check on REGIONID (the top green line in the scheme above), since Region C is not contained within the Simple MDT REGION.

The error generated by IBP would be of rejection code 600 (“Validation of foreign key failed”):

Notice that Location is also a Simple MDT here.

Alternatively, we can satisfy the same requirement by designing Location as a Compound MDT. The scheme would be as such:

LOCATION (Compound MDT)

The Simple MDT REGION and POSITION would now become components of Compound MDT LOCATION.

And when attempting to upload Location C/South, the error generated by IBP would be of rejection code 106 (“Foreign key violation”):

Note that we do not need to add any Attribute Checks ourselves in this case, since we are using a Compound MDT LOCATION. A Compound master data type always inherits the keys (the blue cubes with letters “PK”; signifying Primary Key; in the schemes above) of their component master data types and can’t contain any other key attributes. When uploading data into the Compound MDT, IBP will automatically check to see if the (values of the) keys of the Compound which you try to upload exist in the (values of the) keys of the component.

We have now seen how we can use either a compound MDT, or a Simple MDT with Attribute Checks, to check in one or more other MDTs whether or not the attribute values are present in those other MDTs.

To drive the point home, let’s say that contrary to the above, the client would like to manually limit/restrict the allowed Locations (that is to define which combinations of Region + Position are valid Locations). Meaning for example, that Location B/South (even though Region A exists, and Position South exists) would not be an allowed value for a Demand.

In such a case, instead of uploading the 4 Location values mentioned above, the user would upload only 3 values into the Location MDT:

Note that Location can still be designed either as a Simple MDT (with Attribute Checks):

Demand (Compound MDT); Location (Simple MDT with Attribute Checks)

… or as a Compound MDT:

Demand (Compound MDT); Location (Compound MDT)

In either case, if one would like to upload Location B/South into the DEMAND Compound MDT, IBP would generate an error of rejection code 106 (“Foreign key violation”):, since the DEMAND Compound MDT automatically checks for the values in the keys of its component LOCATION, and the combination REGIONID B and POSITIONID South does not occur in LOCATION MDT:

Values which would succeed in upload, would be:

I hope this was a clear illustration of some of the concepts behind Compound MDTs, and Attribute Checks.

It is important to bear in mind that in IBP, a Compound MDT has further rules not mentioned here (for example: it must contain all key attributes of the component MDTs, which must be set to key, and it can’t have any additional key attributes).

If you are now feeling puzzled about whether you should model your data as a Compound MDT or as a Simple MDT with Attribute Checks, I would like to conclude by saying that in a Compound MDT you can use the non-key attributes of the components as root attribute on a planning level to complete the attributes of the Compound MDT. So opt for Compound in case you would like to glue all of the components together, in the sense that you may use any of them all over the planning model. Using a Simple Master Data with Attribute Check does not give rise to such possibilities. What does this mean in our example? Let’s say the Simple MDT PRODUCT had an additional attribute called “Product Family” (a non-key attribute). DEMAND is a compound MDT, wherein Region originates from the LOCATION MDT, while “Product Family” originates from the PRODUCT MDT. We can create a planning level with roots “Product family” and Region, and it can use attributes from the Compound DEMAND MDT in that planning level. This is something that can be done only using a Compound MDT.

Where Compound MDT and Simple MDT with Attribute Checks act the same is in the deletion of data. If you delete a value in a key attribute of the components, the MDT built on top of it (such as the Compound MDT) will also be deleted. In the scheme above, that means that if I delete Region A in Simple MDT REGION, all the combinations with Region A in Compound MDT LOCATION will also be deleted at once; along with their Planning Objects. This behavior also occurs for Simple MDT with Attribute Checks. See also a warning on this topic in the Configuration Guide:

“Caution: Master data types that are joined by an attribute check behave like compound master data types with respect to the deletion of data. For example, if you have set up an attribute check for the LOCID attribute of the RESOURCE master data type with LOCATION as the check master data type and you delete a location, all resources that reference the corresponding LOCID will also be deleted, as well as all planning objects that include the relevant key figure data.”

A special thanks to Gabor Mittweg for the interesting collaboration and clarifying insights on this topic.

As a thought experiment, I would like to ask you whether or not DEMAND (in the graphic above) could equally be designed as a simple MDT with Attribute Checks. Feel free to reply in the comments, along with your ideas and experiences on Database Design in IBP. Would you appreciate some foundational directives from SAP on MDT Databased Design in IBP? Do you feel that the standard database normalization rules used in relational databases are best practice for IBP projects as well? I am looking forward to learn from you.

If you want to stay informed on IBP, you can always follow the IBP topic page in SAP Community, follow the tag IBP on the Blogs section, for related information, and ask and answer questions about the topic IBP in the Questions section of SAP Community. If you would like to stay updated when I post new content, you can also follow my SAP People’s profile Vincent Verheyen (“Follow” button at the top-right).