We expect a lot out of our software. We want our ERP systems to store large amounts of data and make information easily accessible to users. We want our business intelligence software to generate meaningful reports and insights on-demand. All of our software needs to work fast, and we refuse to compromise on security. With powerful tools like Microsoft Dynamics GP and PowerBI, these demands are met — but don’t be fooled, there’s a lot going on behind the curtain to make it work.
The key to making this all work is through the variety of pick-list options and data coding that reside in your ERP system. Your ERP system and business intelligence software tie all information together via specific reference fields such as “CustomerID” or “VendorID”. In the computer science world, we call that relational data and data normalization, and it’s the reason your software makes your staff so productive and insightful. Here’s how it works...
What Are Relational Data and Data Normalization?
If you’ve never heard of this term, that’s okay. It’s the underlying structure of your ERP system that allows you to identify meaningful information. For instance, you could store all your transaction data in a Notepad document, but that doesn’t help you build reports or predict things like inventory needs. Your ERP system identifies certain data sets and spits it back to you in a digestible and meaningful way. Then, it feeds that information to your business intelligence software to build your dashboard.
Relational data and data normalization have been around since The Beatles broke up in 1970.
It was originally developed to reduce data redundancy and improve data integrity, but it has been further developed and enhanced for years to make ERP systems and, more recently, business intelligence software work flawlessly.
How Does Relational Data Work?
The concept of relational data is that all "related" data should be grouped together, and then linked through the use of unique keys, such as “CustomerID” or “VendorID.”
Relational data also helps users eliminate re-keying by having specific fields already available in a new window, rather than having the user create a table of fields necessary for every new entry. This specific technology can help tie similar records under one larger record.
ERP users see this technology in new contact records. Often, businesses will have multiple contact records with the same address because they’re associated with the same business. It’s important to keep all of the contact records individually in the ERP system. Thanks to relational data technology, users can have multiple address entries associated with an individual customer under the same business. Because they’re tied to the same business, the address field populates with the same address, saving valuable time by avoiding additional data entry.
If a business doesn’t want to have these fields ready to go in their ERP system, they can update the fields that are available in that record. Users can do this by entering the window settings and updating the customer table with the fields unique to that customer in it. Then, adding a second table that includes addresses linked to the customer table by the CustomerID.
Here’s what that looks like side-by-side:
The above customer table contains information that is unique to the customer, including first name, last name and birthday. The second table contains only address elements, plus two additional keys. The “AddressTypeID” field allows users to have multiple address types, including billing and shipping. The “CustomerID” field allows users to tie a particular address record back to a particular customer. With the keys set up as shown above for each contact record, users can have many different addresses linked to any one particular contact record.
How Does This Help Users With Daily Processes?
All ERP system and business intelligence users have seen this technology in action, but they just don’t realize it. Relational data helps users manage inventory, and take care of accounts payable and accounts receivable processes with ease. Here’s what it looks like for a typical order:
Your purchasing/inventory manager, for instance, utilizes this technology every time they open their ERP system. Inventory windows might include a master inventory table, a master supplier table, and a link table indicating availability. A sales order header table might include the sales order number, the date and a link to a particular customer.
By using a separate customer table and the key in the sales order table, your customer service or order entry team will not have to re-key customer’s information every time that customer orders from your organization.
Whatever that customer orders, the product information is already in your ERP system and identifiable via a specific SKU or “ProductID.” That way, when the order goes through, all the product information, including price, is immediately available to your customer service or order entry team. When the order makes its way over to the purchasing/inventory manager, they can see how many units have been sold, product availability, and more in order to fulfill the order and keep inventory in stock. They can do this by exporting a custom SmartList view to Excel for reporting, or they could simply view it on their business intelligence dashboard.
The business intelligence software knows how to pinpoint the information you need from your ERP system. In this case, the purchasing agent may want a dashboard to view the amount of POs for a specific vender. The software would put this information, real-time, on the dashboard by looking at the information attached to a specific “VendorID.” Different roles in the organization will want to see different reports and KPIs.
Here’s what it looks like in the relational data table view:
In the diagram, it’s easy to see the level of detail that can be prepopulated, or adjusted in one specific order.
The “Orders” table is the first window users see. It contains only common information for the complete order. Clicking through the “OrderID” field brings up another window filled with the “OrderDetails” table. The “OrderDetails” table has one record for every line item in an order. It includes a link to the “OrderID” record and a link to the “ProductID” window. The “OrderDetails” record indicates necessary inventory information such as the specific quantity of that individual product and the unit price for the product being ordered. The “Product” window shows details about the actual product.
All of these tables and relational data pieces allow users to tie a web of information to one order, or one customer, without rekeying. Without relational data, order entry would take so much longer to add customer orders, inventory management would be a nightmare, and everyday processes would take forever.
The relational data capabilities in your ERP system and business intelligence software allows you the ability to do quick, detailed searches. Using searches, accounts payable clerks can find what invoices are due, and purchasing staff can check on low inventory.
All in all, relational data is the reason your software works, and why your employees are so productive.
Need Help?
Understanding relational data can be complicated, but using your ERP system or business intelligence solution shouldn’t be. Contact our team here or call us at 410.685.5512 to discuss how your organization can get more meaningful insights out of your software.
