Windchill Business Analytics: Programming Journal
Part 01: Program Logic
Last Updated: August 14, 2016
The program itself is fairly simply. I have some SQL queries that give me data and a webpage that displays that data. Getting this to update in real time and to run on it's own is where the programming comes in. I have decided to go with C# for the back end language do to my familiarity of it and simple JavaScript for the front end client portion. The program logic looks like this:
W.B.A. - Program Flow Diagram 01
As you can see from the above picture, the C# program takes SQL queries that I define and converts the results into easy to ready and parse XML files. The reason I am going this route is because the database I am working with does not have such friendly column names nor is it easy to have Oracle give me the exact XML formatting I want. I found it much easier and way more flexible to use a C# program to build the XML however I wanted.
One of the most important reasons for using an external program to handle the SQL queries is data processing and manipulation. Not all queries are simple read and display requests. I want this program to give meaning to the data. Things like trends where you can see what the busiest time of the day is and possibly schedule meetings away from those times. Being able to quantify an employee's currently assigned work rate versus their work completion rate which may assist managers is hiring other people or handing off excess work to less busy employees. This and more become easy to calculate when you have the freedom to manipulate multiple queries that are necessary to get all the right information. For this reason and due to the pain it is to get PL/SQL to give me exactly what I want, C# is the way to go.
Once the XML is generated, JavaScript, based on an interval timer function, will read the XML and check for updates every so often. This may be predefined or possible user adjustable depending on the stress placed on the database. Utilizing AJAX with Deferred/Promise functionality, the HTML webpage that the user is viewing will be updated without needing a full page refresh. I have found through testing that since AJAX is an asynchronous request, Deferred/Promise methods are needed to ensure that the XML data is read completely before the code that is used to display it. With using Deferred/Promise methods, the display code would often run before the XML is finished being read which left the new data not being displayed on time.
One of the most important reasons for using an external program to handle the SQL queries is data processing and manipulation. Not all queries are simple read and display requests. I want this program to give meaning to the data. Things like trends where you can see what the busiest time of the day is and possibly schedule meetings away from those times. Being able to quantify an employee's currently assigned work rate versus their work completion rate which may assist managers is hiring other people or handing off excess work to less busy employees. This and more become easy to calculate when you have the freedom to manipulate multiple queries that are necessary to get all the right information. For this reason and due to the pain it is to get PL/SQL to give me exactly what I want, C# is the way to go.
Once the XML is generated, JavaScript, based on an interval timer function, will read the XML and check for updates every so often. This may be predefined or possible user adjustable depending on the stress placed on the database. Utilizing AJAX with Deferred/Promise functionality, the HTML webpage that the user is viewing will be updated without needing a full page refresh. I have found through testing that since AJAX is an asynchronous request, Deferred/Promise methods are needed to ensure that the XML data is read completely before the code that is used to display it. With using Deferred/Promise methods, the display code would often run before the XML is finished being read which left the new data not being displayed on time.