How to automatically update QCD indicators? A functional demonstration tool in Python
Introducing Python QCD Updater for automating the updates of Key Performance Indicators (KPIs) in iObeya QCD boards, a tool to demonstrate and exercise our APIs.
![tutorial-01-new-to-iobeya@2x](https://center.iobeya.com/wp-content/uploads/2023/01/title-image-1.png?vcv-dynamic-field=featured_image&alt=&caption=&title=title+image+1)
- Allows you to have the right data already displayed in iObeya, without retyping them manually
- Easy to use, whether you need a one-time update or regular updates
- Only requires Python 3.6 or newer to run
- Processes Excel spreadsheets or CSV files extracted from existing systems like SAP, MES, etc.
- Is provided for free, as part of the developments made by the professional services team using iObeya APIs (compatible with Core and Facade API)
1. Presentation
When managing a production process, it is essential to track and monitor the progress of key performance indicators (KPIs). This helps to ensure that the production is running smoothly, and any issues can be identified and addressed early on.
Python QCD Updater is a command-line tool that can help with this by automatically updating KPIs in iObeya boards, eliminating the need for manual updates for data that is already present in existing systems such as MES, or even Excel.
This will save users from retyping this data into iObeya boards, a time-consuming and error-prone operation, while ensuring data consistency when reviewing the boards and indicators.
Under the hood, the Updater is using many iObeya APIs, for authentication, loading rooms and board, working on raw board data, analysis of QCD data, board period change and finally update of QCD indicators using either Core or Facade API calls.
2. APIs and other possible implementations
The tool presented in this article is actually one of the many ways that iObeya APIs can be used for, in this case specifically targeted for updating QCD indicators and it is an example of a specific implementation.
Amongst the different possibilities that can be used for this same task, we can mention Microsoft Power Automate and its ability to create flows using iObeya’s Facade API provided in its connectors library. As a matter of fact, we already do provide some example flows and documentation in the Power Automate - Getting started article.
However, whatever the chosen implementation, here in Python, you’ll have to deal with the same features and constraints, which are explained in the following sections.
3. Features
Python QCD Updater key capabilities include:
- The ability to update any indicator data in any iObeya board, for any date, with any accepted value
- The ability to compute and update the specific cell (wedge number) based on the target (input) date
- The ability to automatically change the QCD board time period when it’s required (or alternatively to refuse the update if the target dates doesn’t match the current board period)
- A simple and flexible way of specifying indicators in input data.
- Support for a variety of data file formats, including CSV, Excel (xlsx), and JSON
- 3 different running modes:
- single mode where a single specified file is processed
- watch mode, where data files are processed as they are added to a designated folder
- server mode that allows to send HTTP POST requests with JSON data
4. Data input and output
The standard data that must be provided to use the QCD Updater includes a list of individual indicators to update in the form of an array of rows, which includes:
- Names or keywords for room, board, letter, circle (day, week, month, etc.) and indicator
- Target date in a standard format (YYYY-MM-DD)
- The value itself
After the input data is processed, an output result file will be generated with the following information:
- The original input rows are included in the file, it will contain detailed identification data for the indicator that was found based on the input data
- The confirmation of the update was successful
5. Input Data Flexibility
Although the technical side of data in iObeya is made of rooms, boards, letters, indicators, etc., it might be referred to by different names on the business side. So, it is possible to rename the columns used to transmit the data, by CSV or XLSX files, or through HTTP requests.
The script implements a configuration mechanism to redefine the default column names required in the data, replacing them with labels that will better describe their business definition (like site=room, line = board, etc.).
6. Masking the complexity
In order to update a indicator value on a QCD letter in a QCD board with the APIs, the following information must be provided and is used for finding the indicator within iObeya platform:
- The Room name
- The Board name
- The Letter name
- The Circle name
- The Indicator name
In addition, the cell (or wedge) number must be specified along with the value to set. This information might be tricky to specify for the following reasons:
- The exact names of rooms, boards, letters, etc must be provided (case sensitive)
- The wedge number must be deduced from the date specified and the periodicity of the letter
- The specified date might not match the target QCD board period
Therefore, to make things easier, the script will do the following:
- Finding indicator with partial or missing data:
- Using partial names for all elements listed above: keywords rather than names
- Being insensitive to case in order to avoid simple typos
- Accepting empty data for the board, the letter, the circle names and the circle frequency: if the indicator names are unique, they’ll be found in the room
- Computing the wedge number automatically, depending on letter periodicity and target date
- Ensure the consistency of the data by checking/adjusting the board period when it doesn’t fit the date.
7. Dealing with QCD board period
As the input data can target any indicator at any date, it is mandatory to check whether the target board is covering a period of time that contains the input date. As an example, a QCD board could be monthly, currently set on Jan 2022, and the input date could target Feb 1 2022.
In this situation, 3 options are available:
- Update the wedge anyway, but this would lead to a wrong value and is to be proscribed
- Refuse the update
- Change the board period so that it fits the date: it could be a new day, week, month, etc.
Hence, the script offers these two options, with the change period being the default one.
8. Examples
When using the default value for input data columns, this is what is expected as a CSV or XLS spreadsheet:
![Excel QCD table Excel QCD table](https://center.iobeya.com/wp-content/uploads/2023/03/2023-01-13_11h20_48-704x52.png)
As mentioned above, the column titles can be redefined through the script parameters and could be different from these default values.
An actual example of QCD letters could look like this:
![SQDC](https://center.iobeya.com/wp-content/uploads/2023/01/SQDC-1024x314.png)
While the letter ‘Q’ could contain something like this, with 2 indicators defined on the 3 circles.
![indicators](https://center.iobeya.com/wp-content/uploads/2023/01/indicators-1024x481.png)
So, for this example, if we’d like to populate the ‘scrap’ indicator for each of the 3 rings that represent different shifts, we would prepare the data as follows for a target date being 11th Jan 2023:
![excel qcd 3](https://center.iobeya.com/wp-content/uploads/2023/01/excel-qcd-3-1024x117.png)
In this case, we have also simplified the input data by using keywords and lower case spelling, while ignoring the letter name which is not required as the scrap indicator is unique in this board.
9. Running the script
This is as easy as running the following command line, where running parameters would mostly be in the configuration file (URL and authentication parameters) and the filename would be submitted directly on the command line:
python qcdautoupdater.py --configfile <path of your configuration file> --inputfile <name of the xls file>
To run the script in watch mode, the command line could then become:
python qcdautoupdater.py --configfile <path> --mode watch --watchfolder <path to the folder to watch>
All modes and parameters are fully described in a dedicated documentation that is provided on demand, along with the QCD Updater itself.
10. Conclusion
By automating the process of updating KPIs in iObeya boards with data pulled from other systems, Python QCD Updater can help production managers to stay on top of their processes and make more informed decisions, saving time and effort.
Being provided to our customers as a free tool, it can also be reused in different ways such as a source base for API calls in python, and you’ll be able to use it as it is or even to enhance it to maybe further improve the automation.
It’s certainly not the only way to integrate your own data with iObeya, but both as a demonstrator and as a real production tool, we hope that this will be useful to your business while getting more value from iObeya.
The tool is provided in its native python source code form and is available on demand for our clients.
If you are interested in using this tool or have any questions or feedback, please contact us directly at integrations-support@iobeya.com.
Additionally, if you’re not yet a client with us, you might want to book a demo.
Type of integration
Facade API
Tags
Tools
![Excel to iObeya integration](https://center.iobeya.com/wp-content/uploads/visualcomposer-assets/templates/63ff52b148bb69.48345742/assets/elements/integration-excel-to-iobeya.png)