I have just been hired to replace an access database with a SQL database, reporting to be done in SSRS. The company in question manufactures hydraulics everything from individual components to complete pieces of equipment. They have an issue they would like me to resolve on top of the migration for years they have problems getting their pricing to work correctly. The access database has thousands (9000 approx) of formulas in a pricing/markup table which is called anytime their ERP program needs to be apply pricing/markup costs for a particular components or entire pieces of equipment being manufactured. A BOM (Build of Materials) team is regularly updating price lists for the manufacturing team so price comparison won't work. The issue is that formulas aren't always applied to correctly (normally they aren't applied when they should) due logic flaws in the legacy ERP system. The company wants to try find a way to flag a formula that perhaps isn't being utilized as often as it should. The legacy system in questions is due for an update when they have cash and the business stomach for it.
1. Group every formula to into an individual component and/or pieces of equipment as soundly/tightly as possible. Then every time the formula is called it's counted and stored in a separate table. Then at the end of the month one could tally the amount of components/equipment being manufactured and the amount of formula calls doing a statistical analysis on that.
Any advice is always appreciated