Balancing With MITS - Problems?

[precisonline]

Having faced this issue a couple of times in the past few months, I'd like to explain why Prelude reports often don't balance to external data warehouses (like MITS or Cognos) without some heroics.  This is not an indictment of either product, but rather to explain why this happens and how we've historically resolved it.

For a typical Prelude client, much of the information that goes into MITS comes from the ORDER.HISTORY.LINE file.  This file contains details of invoiced order lines, including the product number, quantity shipped, cost, price, that sort of thing.  Other data elements like product line, major group code, and several customer fields are not stored in this file but are rather stored externally (in the PRODUCT and CUSTOMER files, among others) and are referenced from those external locations.  This provides a number of benefits throughout the application, but for reporting, it creates a complication.

The complication is this: Assume product A is in product line PL-A.  An order is placed for this product and then is subsequently invoiced, so the details of the sale of that item are reflected in a record in ORDER.HISTORY.LINE.  Then, let's say a data warehousing product comes along and takes a snapshot of that sale.  Now, we have a copy of this information both in ORDER.HISTORY.LINE and also in the data warehouse (most likely both in detail form and also aggregated into the summaries).

Later, the product moves to product line PL-B.  Nothing about the earlier sale has changed, but because of the external file relationships, any reports of that sale on the Prelude side will reflect that product in the new product line PL-B, not PL-A as it was at the time of the sale.  The data warehouse, however, picked up that data when the product was in PL-A, so the data warehouse will include that sale in product line PL-A, but Prelude will reflect it in product line PL-B.  If the product line for that product is changed again, every sale of that product (from inception to current date) will automagically reflect the new product line, not the product line at the time of the sale.  This is also true for several customer, contact, ship to, and ship via fields that are externally referenced from ORDER.HISTORY.LINE.

To resolve this, we've identified the translated attributes that are needed for balancing to a data warehouse, and have a trigger that updates these attributes in a record stored parallel to the ORDER.HISTORY.LINE record.  Then, by using this external information in place of the translated information both for Prelude reporting and for data warehouse extraction, we have a stable system of record from which to balance both the Prelude and external reports.

We've always been concerned regarding trigger performance, but by using very clean and optimized code we've done this several times without the client ever noticing any change in performance.  Also, considering that this new record is easily indexed, we've been able to actually reduce overall system overhead with this technique.

[Tom Pellitieri]

This may not always be a problem, depending on your point of view.

From an accounting standpoint, our company keeps track of the product line that was charged for the sale.  If a product changes product lines, we would report the sales prior to the change date on the old product line, and those after the change date on the new product line.  Otherwise, our accounts are out of balance.  The product line used at the time of billing is stored in Attribute 115 of ORDER.HISTORY.LINE on our system.

We haven't moved to MITS primarily because we have product lines that are shared by multiple departments.  At month end, we have to use a journal entry to move the appropriate sales to the correct department for our financial reports to credit the appropriate groups.  Since we use the Department number in ORDER.HISTORY to separate these, we have a method to tie ORDER.HISTORY.LINE to the General Ledger after the fact.

--Tom Pellitieri

[precisonline]

As long as you're keeping the product line in the ORDER.HISTORY.LINE record somewhere - and this is a customization for you, is it not? - then you've mitigated one issue, certainly.  But there are several additional translated fields that can create some balancing problems.  Major group, customer type, and customer price code come immediately to mind.

There are many reports from files like NCUS-SHP-PRD where product line is again translated based on product number.  Any reports from this file (and files like it) exhibit the same "moving pline" behavior as described earlier.  This is then complicated by other translates on translates, like major group, etc.

I'm pleased you have a solution in place for the moving pline problem in OHL, but I'd recommend being specifically watchful for reports out of NCUS-SHP-PRD and other aggregate files.

And Tom, my thanks and congratulations - you have officially captured the first non-Kevin post!

[Tom Pellitieri]

Actually, we don't use NCUS-SHP-PRD for any reports, because we found (back on Version 10) that once data was posted there it was not easy to repost it if corrections were needed.  We frequently have salesrep number entry errors because we have multiple salesreps assigned to individual customers based on the product group.

--Tom