Just in case anyone else needs to create a “Live Efficiency” project, I’m sharing the basics of my method.
We’ve wanted a “Live” efficiency shop screen for some time, and I hacked one together recently. “GOAL” here is the ProdStandard.
This screen is displayed in all departments and gives production employees a live view of how many hours they have currently worked on a job over the standard. This is accurate to within ~5m.
The method of putting this together comes down to a few steps, I’m abusing pandas and hosting the resultant data on our internal webserver (not SSRS!).
Summarize the table to determine employee division of labor (4 would .25 hours applied)
Resulting summary values can be multipled by our timeframe interval to get minutes, and /60 to get total active hours. Apply previously applied labor and voila.
I’ve got a Jupyter notebook that does this as well that I could sanitize and share if someone else needs to do this.
I’m sure this isn’t the best method, but it works and I’ve never seen another Epicor shop with something similar.
If anyone has a better or similar solution, I would love to see it and compare notes!
This is awesome! Great job!
I would love to learn more about this. I don’t know about pandas or Jupyter notebooks though.
The closest I had gotten to live data exports was using Executive Queries/Cubes, but this was only once a day and still didn’t work perfectly.
We use SSRS with a 90 second auto refresh. The worked minutes will turn yellow as they get closer to scheduled minutes and turn red once they go over scheduled minutes.
Do you have to accommodate for jobs being worked on by multiple employees concurrently, or employees working on multiple jobs? That was my biggest hurdle, doing that calc live.
We didn’t split out the labor hours or scheduled minutes when they are being worked on by multiple employee’s concurrently. We are grouping by job, assembly and operation then attaching that data to the employee’s currently working on that job\assm\op.
Maybe you could consider a refresh that is a little less frequent. In the cloud we are finding some company have auto refresh dashboards running, and the result is causing lots of extra SQL noise that slows down the system. one customer had billions of records returned via BAQs per day. All this traffic could be reduced by simply reducing the frequency down to every 5 minutes. (IE… do you REALLY need something updated every 90 seconds?)
Yeah true, thanks Tim.
When we first introduced these dashboards, leadership would audit the names on the board compared to the number of people working in that area and if someone’s name wasn’t on the board the employee would be asked why. I would then get calls saying “Bob” logged into a job and still hasn’t shown up on the board.
