Problem Statement:
In today’s world, a company has many suppliers for various goods & services, in particular with the rise of SaaS tools. There is a need to analyze expenses per vendors and plan on them.
But with the multiplication of systems, there can be many challenges. Different naming, no ID, too many items for business users. Worry not, there is a way.
Solution:
The core of the solution is this: you need two Vendor dimensions:
- one to connect your external system into, we’ll call it Vendors Staging
- one to analyze & plan, Vendors
The technical point is how to match these two, automatically and manually to give you full control
Vendor Staging:
Here’s what it looks like:
The goal is to have the green column mapped to the Vendor dimension. For that we are doing the following formula:
if(isblank('Vendors Staging'.'Manual Mapping'),ITEM('Vendors Staging'.'Simplified Name',UPPER(Vendor.'Simplified Name')),'Vendors Staging'.'Manual Mapping')Meaning: if there is a manual mapping, take it else try to find in the Vendor list for the Simplified Name.
Automate the mapping between Vendors Staging → Vendor
But what is simplified name ? We often see various naming convention, additional space, legal addendums that will make the mapping of Vendors Staging to Vendor fail.
So I’ve come up with a solution to clean the data to make sure matching happens automatically in most cases.
Simplified name is based on a logic that will look to remove text string that are polluting the name. To be dynamic, the search is based on a dimension called Finder List, like this:
The property “To Remove” formula’s is the following:
if(find('Finder List'.Name,UPPER('Vendors Staging'.Name)[add:'Finder List'])>0,'Finder List')[remove lastnonblank:'Finder List']Here we are using the magic of multi-dimensionality and creating a multi dimensional search of each item in Finder List x Vendors Staging
And if we FIND() that string, we know we can remove the length from the Vendor name to get or simplified name.
Which is what we do finally for the formula of Simplified Name:
UPPER(if(isblank('Vendors Staging'.'To Remove'),'Vendors Staging'.Name,(left(TRIM('Vendors Staging'.Name),len('Vendors Staging'.Name)-'Vendors Staging'.'To Remove'.Length-1))))So each item you add into Finder List will be searched and removed from the name. No need for a complex IF statement !
note: using TRIM() to make sure we remove any leading/trailing space + UPPER() to only work on uppercase
A few checks:
You can easily create a view that will show you the missing automated mapping to allow your admin to do it manually, by just filtering on blanks
Also, note that in your transaction list, where you effectively transform Vendor Staging to Vendor, make sure to put a default value to an “All other Vendors” so that numbers do aggregate, even when Vendors Staging isn’t mapped yet. Like this:
IF(isblank(Transaction.Vendor Load.Auto mapping),Vendor."All Other Vendors",Transaction.Vendor Load.Auto mapping)
The Vendor dimension
Thanks to that logic, your vendor dimension can be a clean list of unique Vendors that will make sense for analysis & business leaders to plan on. You can, and should, have a “Simplified Name” logic on it as well, just like on Staging.
Make sure to create the new Vendors when mapping the Staging (you can do so directly from the drop down)
As you can see, you can also have groups of suppliers, when individual ones don’t make sense.
Final result:
Here’s an example of an app built with this logic. Ask your us to get it if you need more explanations
