Around 4 years ago when I first joined the company there was a big push internally to maintain and setup safety stock values. This has been a key part of data management and factory planning to meet the ever changing demands and requirements of the hand instrument precision metrology market. (K4)

During this push the company began to invest in more equipment for data display to general factory workers such as TV’s and other computers for such activities. Once this had been put in place there was then a requirement for customised software tooling to display this kind of data.

The displays have been a direct replacement for scheduled print copies of information, as we move further towards ISO 14001 the business aims to reduce paper usage and specifically waste paper usage (K5)

For creating this dashboard there were a few requirements

  • Show the data in a easy visible way for the assembly operatives
  • Communicate with internal databases automatically to refresh the data through the provided means.
  • Low performance impact for less powerful PC’s

8 Wastes

Of the 8 wastes lean manufacturing techniques one of the biggest wastes is overproduction. Ideally this system will ensure that overproduction is reduced issue in the Assembly manufacturing (S7) Although this isn’t an in-depth analysis the scope of the project is limited to this specific area of the business.

Choosing the tech

For this application I decided on the Tauri toolkit, this is an app construction toolkit what allows its users to build software for all desktop applications using web based technologies similar to Electron and has the following requirements

  • Rust-lang
  • Microsoft Visual Studio C++ Build Tools

Given the fact that the data is provided through a Microsoft tool called odbc, this then can be used to interface with a SQL database to request the relevant data which then in turn comes from the internal SAP system Therefore we will need some dependencies for this

  • odbc_api
  • serde & serde_json

The SAP system is used at Bowers for tracking many important things, Here in this case the SAP data system is used to control production and stock so we will be extracting the data from these tables for use. (S10)

In the project there will be a Rust (backend) which will control the data connections and then a web Svelte/Typescript (frontend). The frontend will call exported functions from the rust code and then process/display it.

Although the tooling here is new and novel in the future I plan to outline a good standard operating procedure for internal use of these tools to create other database views and statistical analysis. Other standard operating procedures and formal documents were made use of such as best programming conventions for SQL and the ‘Rust book’ a published document on the language features of the “Rust” programming language. (K10) Failing to adhere to these could result in

  • Sub optimal performance
  • Excess load on the SQL server
  • Downtime or other unplanned failures (S2)

I started creating the app by using the cargo create-tauri-app which creates a blank framework for the application, then selecting the Svelte + TypeScript options, this then generates a blank templated folder structure !()[https://i.imgur.com/45jq6ze.png] Inside of the src-tauri folder is where the backend application code resides. This is where the function to fetch the data will be created

Tauri Code

SQL Query

First the data must be fetched from the database, given that we only need to track a specific set of items and only a certain amount of fields from said items we can use this to craft a SQL query to suit the requirement.

Inside of the SJGRP DSN there are two tables of interest what hold the data required. The first is the ref_materials which is a reference table (Read only) what exports all of the data from SAP to show various pieces of information such as the available stock. The second table of interest is the ref_mat_plant which is another table from SAP which contains alot of status information on the given material such as the safety stock value (K16)

Given this we can construct the query as follows

  • Select the correct fields we are interested in
  • Join the two tables together
  • Filter by the respective part numbers
SELECT
ref_materials.material,
ref_materials.description,
ref_materials.stock,
ref_mat_plant.safety
FROM ref_materials LEFT JOIN ref_mat_plant ON ref_materials.material = ref_mat_plant.material
WHERE ref_materials.material IN
('56-1-1','56-1-2','56-1-3','56-1-4','56-1-5','56-1-6','56-1-7',
'56-1-8','56-1-9','56-1-10','56-1-11','56-1-12','56-1-13','56-1-14',
'56-1-15','56-1-16','56-1-17','56-1-18','56-1-19','56-1-20','56-1-21',
'56-1-22','56-1-23','56-1-24','56-1-25','56-1-26','56-1-27','56-1-28',
'56-1-29','56-1-30','56-1-31','56-1-33','56-1-35','56-1-37','56-1-40',
'56-1-41','56-1-42','56-1-43'

After some trial and error this is the query I constructed to filter out the information and should return a list of responses. I then created a file called query.sql (As this will be the only query used by the application) and included that in the rust code. This extraccts the related manufacturing data and information required for this use case. (S1)

static QUERY: &'static str = include_str!("query.sql");

Using a &'static str type means just that its a global string and the include_str!() macro simply copies in the raw bytes from the text file into the compiled binary at compile time meaning that its effectively the same as putting the string in the code

Creating the Database Function

The function will be called from Typescript in the frontend therefore we must create said function in the backend.

// Here the tauri macro defines it as a callable command from the frontend.
#[tauri::command]
fn fetch() -> String {
    // Todo;
    /* Connect to the database
    Send the required SQL query and parse the response
    Return the response as a JSON formatted String 
    */
}

First we need to connect to the odbc database, reading the documentation from the crate odbc_api provides a few working examples

    let environment = Environment::new().unwrap();
    let connection = environment
        .connect("SJGRP", "", "", ConnectionOptions::default())
        .unwrap();

Although it would be best to make use of the ? operator to propagate the error to the return of the function if we for some reason fail to connect to the Enviroment then we can just panic and crash here as nothing is going to function anyways

Next its time to issue the query

    let conres = connection.execute(&QUERY, ()).unwrap();

Once again unwrapping here is fine as due to reasons discussed earlier. This function then returns the type of Option<CursorImplT>> At this point a connection has been successful so it would be wise in the event of a failure to return a message to the end user indicating some kind of problem

    let conres = connection.execute(&QUERY, ()).unwrap();
    match conres {    
        Some(mut cursor) => {
			todo!(); // Placeholder macro
        }
        None => {
            panic!("No reponse from the database, are the entries missing or is the query incorrect?");
        }

There are other options than using a match statement such as unwrap_or but for this use case verbosity is simpler At this point we have a cursor containing the relevant results ready to be parsed into whatever data structure we require. Making use of serde allows finer control over the data so creation of a struct to hold said data.

#[derive(Debug, Deserialize, Serialize)]
struct Material {
    material: String,	
    description: String,
    stock: String,
    safety: String,
}

Here the struct takes the types as String although it may be wise to parse these as int’s from the rust side of code I elected not to for simplicity’s sake. Above the derive macros generates code to allow the struct to be debug-printed and also serialized and deserialized using Serde.

Parsing the data through the cursor is a little complicated but the general approach is to collect all the arguments into a utf-8 string comma separated and then parse that data as CSV to then convert into a rust struct. This then can be converted into a valid, json formatted string for returning.

There probably is a better way to construct the data directly into the rust style struct but this method was much easier for me to create as its similar to other approaches I have used in the past and the function is only every few minutes to refresh the data.

    match conres {    
        Some(mut cursor) => {
        // First, collect the headline data from the table and then insert it into a Vec<String>, headlines meaning table header data or database field names, 
            let mut headline : Vec<String> = cursor.column_names().unwrap().collect::<Result<_,_>>().unwrap();
            // Write the record to the CSV writer defined earlier. Write_record has the trait bounds defined for Vec<String> therefore we can just pass that in and the comma seperation will be performed automatically :)
            writer.write_record(headline).unwrap();
            // Create a TextRowSet buffer for the cursor, this is probably not needed due to the smaller size of the query but is a passover from example code.
            let mut buffers = TextRowSet::for_cursor(5000, &mut cursor, Some(4096)).unwrap();
            //Bind the TextRowSet to the cursor so that when iterating through the buffer the TextRowSet ensures that the value is paginated correctly avoiding fragmented  lines
            let mut row_set_cursor = cursor.bind_buffer(&mut buffers).unwrap();
            // fetch and loop through each of the row sets, Using While Let Some means that we done have to unwrap the Option<T> from the row_set_cursor as this operation is fallible
            while let Some(batch) = row_set_cursor.fetch().unwrap() {
                // Within a batch, iterate over every row
                for row_index in 0..batch.num_rows() {
                    // Within a row iterate over every column
                    let record = (0..batch.num_cols()).map(|col_index| {
                        batch
                            .at(col_index, row_index)
                            .unwrap_or(&[])

                    });
                    // Writes row as csv once collected a single record line
                    writer.write_record(record).unwrap();
                }
            }
            // Create a string from the writer in a utf8 format
            let data = String::from_utf8(writer.into_inner().unwrap()).unwrap();
            println!("{data}");
            //Construct a new csv reader to parse over the data
            let mut rdr = csv::Reader::from_reader(data.as_bytes());
            //Create an empty vec of  our custom type Materials so that we can push and pop entries 
            let mut materials: Vec<Material> = Vec::new();
            //loop through each of the entries in the new CSV reader and then deserialize it into the serde format.
            for result in rdr.deserialize() {
	            //Calling unwrap here is probably bad and could result in confusing errors in the future, although its unlikely that invalid data will make its way into these reference tables. Possibly if a field name changes on the database we could have a big issue but this is unlikely 
                let record: Material = result.unwrap();
                //Push the created record to the previously empty Vec<Materials> 
                materials.push(record);
            }
            //Format the Vec<T> using serde_json into a valid formatted json, at this point we can assert that everythign is valid as otherwise 
            let mat_list_json = serde_json::to_string_pretty(&materials).unwrap();
            println!("{mat_list_json}");
            return mat_list_json;
        }
        ...

Now that we have the function which fetches all the data we can then add the tauri command macro to the top of the function we just defined.

#[tauri::command]

fn fetch() -> String {
	...
}

fn main() {
    tauri::Builder::default()
        .invoke_handler(tauri::generate_handler![fetch])
        .run(tauri::generate_context!())
        .expect("error while running tauri application");

}

Now that we have created the rust code we can move over to the Svelte and Typescript to create the web interface. Right now we have the default template. The structure is going to be as follows

There is a main App.svelte which contains the top-level page and then the two components for the imperial sizes and metric sizes. Furthermore the table share some common CSS between them so that gets moved out into a separate file too

<script lang="ts">
<!-- Import the two svelte components so that we can call the tags to render them in the later code -->
  import MetricTable from './lib/MetricTable.svelte'
  import ImperialTable from './lib/ImperialTable.svelte'
</script>

<!-- Create a container div and give it some styling so they are split between each half of the page 50/50 -->
<div id="container">
  <div><MetricTable /> </div>
  <div><ImperialTable /> </div>
</div>

<style>
#container {
  display: flex;                 
  flex-direction: row;           
  flex-wrap: nowrap;             
  justify-content: space-between; 
}

</style>

Now for each of the tables the code is going to be very similar

<script lang="ts">
  import { invoke } from "@tauri-apps/api/tauri";
  import { onMount } from "svelte";
  // First we create the MaterialStockData strucure which is the same as the rust struct type
  type MaterialStockData = {
    material: string;
    description: string;
    stock: number;
    safety: number;
  };

Now we can see there is symmetry between the typescript and rust code meaning that we can destructor this data into a html table

#[derive(Debug, Deserialize, Serialize)]
struct Material {
    material: String,
    description: String,
    stock: String,
    safety: String,
}

<body>
  <table class="styled-table" id="table" style="overflow-x:scroll">
    <thead>
      <tr>
        {#each keys as header}
          <th>{header}</th>
        {/each}
        <th style="padding-right: 100px;">saftey stock %</th>
      </tr>
    </thead>
    <tbody bind:this={tableBody}>
      {#each msd as row}
        <tr>
          <td>{row.material}</td>
          <td style="width: 23%;">{row.description}</td>
          <td>{row.stock}</td>
          <td>{row.safety}</td>
          <td>
            <progress-meter>
              <progress-percent
                style="--progress: {getPercentage(
                  row.stock,
                  row.safety,
                )}; background-color:{perc2color(
                  getPercentage(row.stock, row.safety),
                )}"
              ></progress-percent>
            </progress-meter>
          </td>
        </tr>
      {/each}
    </tbody>
  </table>
</body>

Here is the svelte code, it then is the same as for the metric and imperial component.

Once all of the code is complete the UI can be displayed as so. This will then be displayed on the TV in the assembly room for all of the fitters to see and it will automatically refresh every 15 min. The display finally simple shows two tables and then the saftey stock % so that its possibly to evaluate the status of build. This is core engineering manufacturing data through merging two tables together with the SQL (K14) (S4)

Further Upgrades

After this was created the code was then initialised into a git repository. This allows code changes to be monitored changed, pushed and merged for collaboration, this is the widely accepted standard for maintaining granular version control. (S5) (B2)

Difference in display

Initially the first design was to have all of the data in one table. After received some feedback from Assembly Manager Joe Dominik in the form of a professional verbal discussion it was decided that it would be better to split the two tables in half and by imperial and metric category to avoid having to scroll the list (K2) The final solution with the help of communicated from the Manager helped improved the finished concept into a completed idea! (K8) (B7)

Assembly manufacturing

Due to the fact that assembly can be a process it can have a large amount of variance due to internal quality issues, delays from external suppliers and buffer stocks to allow for shielding of market variability the MO5 project was born to create a nice saftey stock level to ensure Bowers can always meet the needs and demands of customers in a short timeframe. Therefore for the assembly process at the finished product level this is where we pool items ready to be packaged (K6) Furthermore this is then a strengthening factor in allowing the organisation to manage and monitor objects of said department (K17)

Human factors

Now assembly operatives have a tangible metric to track the progress and operations of the department at a glance. This can help motivate individuals and improve performance, possibly in the future there will be scope to ensure that other data can be displayed to highlight individuals who exceed performance metrics in an automated fashion. (K13) (S5)

Engineering document

Although the dashboard is not a typical engineering document and this process is fairly agnostic and does not fit within the typical scope of said. The documentation in the form of code comments can be considered an important related document, furthermore the managing of said build records is a formatting and processing of that said data transiently (K14)

Project Timeline

Throughout the project I made multiple sacrifices to create the Minimum Viable Product (MVP) this then allowed publishing and later on feedback from other individuals to create a Standard Viable Product. This technique was very valuable as it allowed a focus on creating something that works well and is robust rather than sacrificing valuable time to obsess over nonspecific details furthermore saving costs. The project can be concluded as a success due to one of these factors. (K15) (S9)

Due to the success and timeline of the project there has been other interest from departments to get corresponding systems to track key data management activities and after discussions there is budget and scope for further projects (B6)

Storage of Data

The software written is stored on the network drive, this is then a fixed location for the source code. A compiled version is generated using the cargo tauri build to then create an installer using the tauri framework. This is then installed on the machine which runs the dashboard display. In the future there could be scope for a Continious Integration approach with updating from a central server, but this is out of scope for a simple local application for this data management activity. (S8) (K21)

Databases

The internal company network provides a number of databases which are managed by the IT Department. These have limited uses as permissions must be finely controlled to avoid security issues, Regardless they are still useful pools of information for various Data Analytics as shown herein. (K22)