Part of my job is teaching (I’m in the same team as Roman, so you can read his post if you’re interested in what I do). I’ve taught several SAP MDM classes the past year, done god-knows-how-many conference calls with customers, consultants and partners about MDM, and by now I know that no matter who the audience is one of the things I’m going to spend the most time on will be qualified lookup tables. Explaining the main table and flat lookups is pretty easy, so are taxonomies and hierarchies, but when people are confronted with qualified lookup tables for the first time they tend to get a certain facial expression which I like to call the “wait… what was that again?” face, or the “is that the qualifier or the non-qualifier?” face. As much as I am amused by knowing that this face is going to come, and the warm fuzzy feeling I get from being a 100% sure it’s going to happen (it is not so often that something is that certain), I’d like to try to explain this concept to a wider audience, and by a wider audience I mean SDN.
What is it good for?
Qualified lookup tables let you solve triangles. It’s OK, you don’t need to check your monitor, that’s what I wrote — it lets you solve triangles. You get a triangle when you connect three dots, or better yet to keep this focused on MDM rather than on 2nd grade Euclidean geometry, 3 types of data. Let’s take a Customer record in a regular DBMS as an example. We start with a simple, flat notion of a Customer:
Notice that we have an “address” field — putting an MDM system into place is (also) about making sure data is visible, easily accessible and normalized, so I think we should split that single field into a set of fields like “street”, “city”, “country” etc. We also realize that people could have more than one address, so we take the addresses out of the Customers table:
Now we have a Customer which can have any number of Addresses, and we have a connecting field (known as “foreign key” in DBA-ish) connecting the two tables by the customer’s ID. If we keep it like this we won’t be able to distinguish between a home address, business address or the address of the customer’s favourite bar. We need to maintain address types. We could add a field called “type” in the Addresses table, but then anyone could come in and just put in a type — we might end up with entries which have “home address” and others with “residental address”, both meaning the same. So we want to have a fixed (and controlled) list of address types — let’s add a third table and connect it to the Addresses table:
Now we have a triangle — we have three types of data which relate and depend on each other:
If we take any one of the corners of out the triangle the data loses it’s context. Take the Customer out and we’re left with a list of addresses we know nothing about. Take the Address Types out and we’re left with a list of addresses and people and no knowledge of how they relate. Take the Addresses out and we’re left with a list of Customers and the types of addresses they have.
So how do I fold this triangle into SAP MDM?
How do we take this triangle into MDM? We can’t model this using fields in the main table because we need multiple addresses for each customer. A flat lookup table won’t do it here as well (in most cases anyway), even a multi-valued one — a lookup table usually has a many-to-one relationship with the main table: for example, a lookup table holding a list of Countries — each record in the Countries table will be referenced by many records in the main table. Flat lookups are designed for values which need to be shared by many main table records. Will a small set of addresses be shared by all main table records? Probably not if we’re dealing with a list of Customers, Suppliers or Employees. What will be shared though is the small list of address types. Our triangle has: a main table record, a relatively small set of shared values (in this case, address types) and values which make sense when connected to both the main table and the set of shared values (in this case, the address itself).
That’s where the qualified lookup table kicks in. A qualified lookup table is built from two types of fields — non-qualifiers and qualifiers. Non-qualifiers represent the corner in the triangle which has a relatively small set of shared values. In the example’s case, an address type. The qualifiers represnt the corner in the triangle which is relevent to specific main table records and one of the shared values — in this case the address. The third corner of the triangle is always the main table record. To sum it up:
- First corner: main table record. The link to the main table is done by a lookup field to the qualified lookup table.
- Second corner, repeatable set of values (non-qualifier): address type
- Third corner, (qualifier): address field
OK, I have the table, but how do I work with it?
What does that mean when we enter or view the data? Let’s see how the record for Bender Bending Rodriguez might look like in Data Manager:
We see the “Name”, a simple text field, and the “Address” field, which is a lookup to the qualified tables “Addresses”. Let’s take a look at the “Addresses” qualified lookup table:
Notice that we see both the non-qualifiers and the qualifier fields, but only the non-qualified field (“address type” in this case) is editable (had we had more than non-qualified field they would all be editable). It’s as if the Addresses table behaves like a simple flat lookup table when viewed by itself — a simple list of values which will be shared by many records. The qualifiers, all the rest of the fields, are locked because they have no context in the Addresses table — what would it mean if we put the street name “Hamburgerstrasse” for the record selected in the screen-shot? Nothing — the street must be connected to a main table record, a Customer in this case and and an address-type to have meaning.
Back to the main table, look at the rectangle showing the information from the qualified lookup field — by default it only shows display fields, which in this case means only the types of addresses selected for this specific record. If we hover the mouse over that rectangle we’ll see some more information:
and if we double-click it we get to the quailfied lookup entry pop-up screen.
We see the available lookup records — these are in fact the records we saw when we switched to view the Addresses qualified lookup fields, which means these are the values stored in the non-qualifiers. We can add and remove types of addresses — a record may have any number of any type of address. When we add a type, for example “Favourite Bar”, we can enter information for the qualifiers, those field which make sense only when connected to a main table record and a non-qualifier. If we add two “Home” addresses each can have it’s own values for the City, Street, Country, State and Postal Code qualifier fields.
Qualified lookup tables let you model relationships that would require several DBMS tables in an elegant, flexible way — no need to hassle yourself with foreign keys or managing “relationship” tables. It might take a few moments to get used to the concept, but it’s super-powerful. Not just for modeling, also for retrieving the information — it’s very easy to search by non-qualifier or qualifier value. For example, search for all home addresses, or all addresses with the street “1 Infinte Loop” in them.
Besides modeling your data into a qualified table and working with those tables in Data Manager you also need to import and syndicate information to and from qualified lookup tables — that will be my next post, alongside some more advanced usage examples.