Relational Databases for Master Data Management

Executive Summary

• The State of MDM at Companies.
• The Benefits and Challenges of Relational Databases.
• Using Relational Databases for MDM as well as for Transactional Data.

Challenges of MDM

Master data management has some very definitive challenges, and those challenges enlarge when the system must be managed in SAP. All of the SAP’s various applications tend to be complex, and it’s hard to find the master data set up in the system quickly.

Secondly, SAP systems have an enormous number of fields and have complex field linkages. SAP has an MDM solution, but it has little to do with what I will cover in this article. It is extremely rarely implemented and has not had credibility very much after multiple problematic implementations. It is an interesting feature of MDM software that, regardless of the vendor, so little MDM software had any impact on resolving the problems with master data. It’s as if the development teams from every vendor (and big-name vendors, by the way from SAS to Oracle to SAP) never really understood the problems of master data. So many of these buzzword-laden applications are just a waste of time and money to implement.

The State of Master Data Management

In many years of SAP consulting, I have yet to see a company do an adequate job at master data management. Also, what I have never seen applied is a relational database. However, relational databases are very good at modeling the type of complexity within SAP systems. Examples of how a relational database can be used for MDM is the following:

1. Metadata Repository
2. Valid Master Data Combinations
3. Valid Transaction Data

Metadata Repository

This provides information about data. If we take the example of material data, this would include the following:

1. Fields that are Part of the Material
2. Field Description
3. What to entering into the Field
4. Whether the Field is Fixed or Variable
5. Table Name
6. Technical Field Name
7. Where the Field is Found in the Application
8. How the Field Should be Set

SAP can show users descriptions of any field. However, it has no way of indicating the type of data above or in a way that can be easily reviewed, sorted, and queried.

Valid Master Data Combinations

SAP ECC (SAP’s ERP system for medium to large to extra-large businesses) is one of the largest ever-written applications. It has an enormous number of combinations of master data elements. If we take ECC, a Purchasing Organization has a  Company Code Assignments. Materials are extended to specific plants. Materials have a Material Type Assignment. The list of valid combinations goes on and on. One of the challenging aspects of SAP is figuring out the valid combinations. This can be found if one knows where to look in SAP, but SAP is not the place to determine this information speedily or efficiently. However, all of these valid combinations, or at least the most frequently checked, can be documented outside of SAP in a relational database.

1. Some valid combinations are easy to document, while others, the number of materials per plant, for instance, are more time-consuming to document and required more maintenance.  Although on the other hand, this type of information can also be exported from SAP to flat files.

Valid Transactional Data

This article is focused on master data. However, the same principle applies to other types of data, even transaction data. I document transaction data for the demo transaction data that I have created. Companies generally won’t spend the time to document normal transaction data — however, transaction data used to demonstrate certain functions is very important to document. In fact, any training curriculum should also include transactional data documentation so that the trainer knows exactly what transaction to go to. Most trainers write down transaction numbers, but I believe that very few use a relational database.

A screenshot below shows the documentation of some of my demo transactions.

However, the Planned Orders the Production Orders are documented, along with the associated Material Number, MRP Area, Location, and other fields. 

Linked Fields

Above, you can see that I have links in particular fields. These links connect this table to other tables, for instance, the location field.

You can get out to the linked record by selecting the link and being brought to this material record. This shows the recorded details for this material. Because the data is selected from a defined table, the errors are relatively well eliminated — and this enforces integrity on the system of recording.

Once data is in a relational database, all types of functionality become available. Here I am applying a filter to this table. Once filters are created, they can be implemented as a view and switched between very quickly. 

Here a hierarchy can be shown, that is, a location, then MRP Type. So at the top line item, four materials are at location 100. And 3 are the PD MRP Type, while 1 is the W MRP Type. Furthermore, this database keeps track of the associations between various tables. So Material P-103 has 2 Sales Orders and 2 Production Orders Associated with it. If I want to see which, I select the row, which will bring up the form entry below.

Now I can transition to any of the objects by just selecting them from links. Also, the Plus Sign allows more entries to be added. 

Another nice feature of online/web databases is that some can show tasks, and the tasks can be assigned to individuals. These can be things that need to be accomplished and added to the database. 

Columns can be shown or hidden, and this applies to any table that is shown. 

Data and associations do not have to view in one way. This is the card view, which illustrates the relationship of what is, in this case, those that are responsible for materials. 

Interestingly, this type of software can also list the tasks that have been performed. Therefore, it can be reviewed what has been done to the database. 

New users can be added very easily. Users can be anything from an Administrator to a Member. This is an important aspect of a database used in that it must be easy to set up new users, and they should be able to get to the database with minimal effort. For this reason, it is difficult to beat a naturally web-based database. Now MySQL and ProgressSQL can be hosted, but this is not the same thing as being a thoroughly web-based database regarding the interaction. I am much more of a supporter of relational database applications that have been set up from the beginning with the ability to have the front-end completely web-based. 

SAP has transactions; different individuals will use that. These transactions can be assigned to the specific master and transactional objects, as stored in the relational database tables. 

Once data has been populated, it can be used to generate reports. This report shows how many Material Types are filled in the database. 

Relational Databases

Relational databases have been around for a long time, an enormous amount of information is stored in relational databases, and they form the background of most applications. However, a major limitation of them has been the limitations of the front ends. A relational database or RDBMS can be separated into the actual RDBMS and the front end, accessing it. For example, MYSQL is an RDBMS. However, it may be consulted by any number of front-end applications such as Navicat (discussed below), which can also be used to connect to other RDBMSs such as ProgressSQL. In fact, a front end can virtually connect to an unlimited number of RDBMSs of one type or multiple types.

However, in my view, most of the front ends are primarily focused on database administrators and are too difficult to use for the purpose outlined in this article. The best web-based relational databases hide a bit of the relational database’s complexity and expose only the most direct portions of the relational database functionality to the user.

RDBMS runs the gamut from desktop database like Access to web-based databases to Oracle 11i, which is used to run SAP. I have tested some of these RDBMSs along with some front ends over the years and found the front ends to be too limited in one way or another to be used as a master data management database. Furthermore, the software must be naturally web-based (Access and FileMaker Pro can be ported to the web, but not without considerable effort). Databases like MySQL, ProgressSQL, MariaDB are free, and any web host will provide a large number of MySQL or ProgressSQL, MariaDB databases which are more than powerful enough to maintain any MDM database. However, MySQL uses administration software or a GUI. Two of the better ones are Sequel Pro and Navicat. Sequel Pro is free, and Navicat costs roughly $200 per license. Here is a screenshot of Navicat.

These inexpensive database administration software has greatly improved over the years, and they can connect to many different SQL databases from one interface. 

However, after analyzing all of the options, I conclude that these front ends, which means using the background database application, are not the right fit for documenting enterprise software applications. Access has, in some ways, and even easier to use the front end — Access’s front end is integrated with the database. Still, Access is really more for prototyping than for use as a live database, and Access was never designed to be a web-based database, so there are technical limitations with making it do that. Essentially, Microsoft has stretched it in directions beyond its initial scope over the years.

The problem is that the vast majority of relational databases are focused more on database administrators rather than on resources that require relational database functionality without the desire to become a database administrator. For this, web-based databases are a much better fit. They often do not have anywhere near the complexity of the relational databases discussed up to this point. But documenting enterprise applications does not require the ability to create complex reports, create joins, etc…

They do require a fast and reliable way to create relationships between various tables, navigate between these tables, and show the associations of tables and records within that table within the first table. Another crucial requirement is the ability to have any user access and use the database. For that, the majority of relational databases and their associated front ends are hopeless. With several web databases, users can add data to the database — that is, they would not create new tables — that would be left to the administrator. Still, they could enter information into existing tables.

Spreadsheets for Master Data Management

Some companies document some of their SAP master data in spreadsheets. This is a dead-end for some reasons.

1. Spreadsheets cannot be linked to each other or enforce referential integrity.
2. Spreadsheets quickly become ungainly maintaining this type of information, particularly since the lack of relational capabilities tends to promote the creation of large — or many column tables.
3. Most spreadsheets run into concurrent user issues. Box.net and SharePoint have not mastered concurrent users updating a sheet at the same time. Google Docs has mastered this but falls on the first two bullet points in this section.

There is one exception to the relational limitations of Excel, and this is partially addressed in PowerPivot, which among other things, allows for the creation of relational tables within a single sheet. This is explained in this link. However, I have yet to see PowerPivot used in any of my clients.

Conclusion

Relational databases are compelling and splendid at modeling the connections within an enterprise software application. This helps significantly improve the transparency of the system to users and more advanced system users alike. However, a major reason why relational databases are not used for documenting master and transactional data at companies is that most relational databases and their front ends are more designed for database administrators than for people who want to interact with databases at a more abstract level. For the longest time, relational database front ends were designed for administrators. Still, a new crop of relational databases means exciting opportunities for using them to document multiple facets of enterprise applications. I have personally recorded a large amount of the demonstration data that I use in various SAP systems and things like my most frequently used transactions, material types in SAP, etc.. and I can say definitively that this greatly enhances my understanding of any system that I document. There is a great opportunity within companies to use web databases to document both master and transactional data to allow many users to log in and both see the relationships in an easy-to-use web database and add data to the database.