With SQLExperience as
(Select Project, Details
from JLyonWorkHistory
Where Language='SQL')
Select *
from SQL Experience
Where ExperienceType in ('Unique', 'Relevant', 'Interesting');
| Project | Details |
| PC Forms | The challenge on this one was trying to structure unstructured data. Each WIP job would take a form that would require some data. This was perhaps a material thickness, hardness test, length, etc. Since it was not practical to create a new table for each instance, I designed a database that was linked to a form. Each data element was given an ID and restrictions so that the forms could essentially be recreated from the DB definition. Then to pull the data, I linked the Form ID with the FieldID and the unique instance of that form so that it not only was able to pull all instances of that form, but also track the change history. The data was kept fully normalized (3rd Normal Form) and ran successfully. |
| Total Production Summary | Needed to pull details out of the Oracle tables from the production side. This was to understand based on the routings, how many hours remained, what material was issued, what remained. Overall, a pretty straight forward application, but introduced me to the Oracle back end. |
| Exterran Report | Similar to the Total Production report, this pulled information from both the ASCP as well as the OLTP side of Oracle and compared them. The nice thing about this is that it took all of the planning data and boiled it down to a digestible format. It broke down the daily needs of the hours by discipline so that the headcounts could be aligned properly. |
| Compass CPR | Pulled the data out of NAV. If you have worked with NAV in the past, the data is not as clean as it is with some of the Oracle back end. There needs to be a few more gymnastics to get the correct answer. Additionally, NAV was not providing the proper answer in all cases. In some cases, it would recommend that we repurchase material for a production order that already had material issued. These types of bugs would need to be accounted for to provide the team with the correct output. The initial report walked through this data and corrected it in the program. The next version (listed below) addressed all of this in the SQL. |
| Compass BI Report | In this report, I pulled all of the Supply and Demand out of the NAV database. But one of the critical components of this was understanding the material position at a given time, so I also had to factor in the line of balance at a particular time. This was all done through queries and brought into BI as objects where the reports could be created. Since there was a lot of noise around some of the simulated orders, there were a number of factors that needed to be considered as well as some of the data corrections that needed to be implemented for our version of NAV. |
| BOM to Production Order Compare | This was unique as the Production order is essentially a flat component list for an assembly where the BOM was indented an indeterminate number of levels. A query needed to be created to do a full explosion of the BOM, but only pick out the Items, and not consider components of a sub assembly. Then the Totals of the production order were compared to the BOM to see where the differences were. One of the things to factor in here was that you could have both items missing from the production order, as well as ones added to the production order. So it was not enough to say select * from Prod. order where component not in BOM. |
Some of the work that I have done has been in VBA and Excel. What may not seem like an industrial strength solution offered many advantages for internal use. Some of the files could be stored locally providing a rich history of data. This came in handy with the planning data from the ERP systems which is refreshed daily.
From a technical standpoint, it was expedient, but allowed great flexibility, and has proven well to be a good tool to mock up solutions. Where I have tried this, the solutions have worked for a number of years without issue. The key point here is that the logic has proven to be sound, and whether it is VBA or Python, the logic still functions the same, and where this has been uses, I have not had any logical errors.
In my free time, I have learned Python, and setup my own server in my house that uses Python as a method to connect to a SQL Server database. The language itself is fairly straight forward, and I have found that use of ChatGPT or Grok has helped tremendously in getting some of the error checking or syntax correct. The take away here is that with the proper guidelines, AI can be used to help bring one up to speed on certain technologies such as working with LLM's, statistical analysis, or other areas. Some of you have likely seen similar things, but AI for programming seems to be about the equivalent of Recording a Macro in Excel and expecting flawless output. Right now, it gives you an acceptable answer, but if you want it to be scalable, or more efficient, then you likely will need to go through many iterations, or customize the answer for yourself.
As a substitute for some of the things I would like to try with Power BI, I have found the JQuery library and some of the JScript to be quite useful. In terms of creating a procedure to accept JSON data and walk through it and either graph it, put it into a table, it works quite well. The JQuery library is very helpful in terms of formatting the results to something similar with what you may get in Power BI. The advantage that I am seeing with Ajax and JScript is that unlike Power BI, you can start to feed things back to the DB as well as delete, and other more expansive functions.
Kind of goes with the JScript, but using HTML and DOM allows you to create some very dynamic web pages. Adding some CSS along with JQuery, and you can get a very professional looking web page that interacts with a database. I have done some more detailed things here such as creating images using SVG as a way to plot data although there are other more efficient ways to do this. This also blends into the use of JScript with the use of certain elements such as the data- where you can assign custom values to some of the HTML elements.