QCD Spreadsheet Extended File Format
For the QCD Excel/CSV Import service
![tutorial-01-new-to-iobeya@2x QCD Spreadsheet Extended File Format](https://center.iobeya.com/wp-content/uploads/2024/07/Untitled.png?vcv-dynamic-field=featured_image&alt=QCD+Spreadsheet+Extended+File+Format&caption=&title=QCD+Spreadsheet++Extended+File+Format)
Introduction
This article is an extension of the first article on the same subject (QCD Spreadsheet File Format) that must be read first, and that explains the default structure of the file to import.
This article will focus only on the extensions of this default format, which is itself related to the newer version of the iObeya Services API.
These extensions will allow for more stable input data files, independent of the names of the elements on the indicators path.
Problem with names
The default format refers to indicators by using only the names, for room, board, letter and indicator.
This is convenient for the users as they know the names of their elements, but these names are also prone to change over time as the room owners have all the freedom to do so.
Unfortunately, such changes would have to be reported into the input data, which might not be as trivial as it seems when different teams are involved into the automatic production of the input data: if the data is extracted from an internal system to be pushed into iObeya, the names of the elements should be immutable so that the structure of the generated file never needs to be changed, or the new names should be communicated to the teams in charge of the file production.
The format extensions that we introduce here are meant to overcome this caveat by offering new possibilities for defining the path to the indicator, through unique identifiers (ids) or tags (data context).
Using unique identifiers or ids
Every element in iObeya is identified by a ‘globally unique identifier’ or ‘GUID’, which is unique, and no elements will ever have the same identifier.
Hence, these identifiers never change (they are immutable) and can be used as an absolute reference to any iObeya object (room, board, letter, etc).
Therefore, by using identifiers instead of names, one can be guaranteed that the value will be valid as long as the pointed object is live (not deleted) in iObeya.
However, be careful, as when rooms, boards or letters are duplicated, new ids are assigned to them.
In the input file, you will be able to specify the following elements by their ids, and the table below shows what columns to use for ids of the different objects and where to find them in iObeya.
Target | Column | Found in |
---|---|---|
Room | roomId | The room URL (the last part of the URL is the ID of the room) |
Board | boardId | Menu: Board/More Actions/Display information/Copy technical id The board URL (the last part of the URL is the ID of the board) |
Letter | letterId | Letter Menu / Display information / Copy technical id |
Spreadsheet example:
Data Context Tags
This is another way of designating rooms or boards, by “tagging” them with a ”, that has been previously defined in iObeya, through the administration console: data contexts are global to the platform and must be carefully managed at platform level and are defined by object . For more information on that feature, please see this other article: Data Integration based on Business Context.
Tags have several advantages over ids, but also other constraints:
- As for ids, the Data Tags will be unique and considered to be immutable enough (as they are managed by an administrator of the platform, the users cannot change them by themselves)
- , unlike ids, tags can be assigned to and an object can also have several tags assigned to it.to be reassigned manually.
- Finally, tags can only be assigned to rooms, boards, text and gauges. Letters and other elements (such as cards) will become taggable in a future version of the APIs.
This mechanism allows for different uses cases and more scalability when rooms and boards are based on identical models and tagged in the appropriate way.
A typical example would be a hierarchy or QCD boards across different rooms, to model a standardized way of working with KPIs across several manufacturing facilities or plants: by using a combination of standard board and tags, the file input data can be generated in a very generic and automated way.
To use this mechanism in the input file, you will have to specify the following columns:
Target | Column | Found in |
---|---|---|
Room | roomTag | Menu: Settings/Room Integration Data/Add Integration Data |
Board | boardTag | Menu: Settings/Board Integration Data/Add Integration Data The board URL |
Spreadsheet example:
Mixing columns
It is also possible to mix columns with name, id or tag, where it’s allowed. This might maybe be useful in certain situations, and it would depend on the actual use case, for example:
- A single user who wants to import KPI data in his board would probably prefer to use names, as he’s in full control of both the boards and the input spreadsheet.
- If the data is pulled from another system with a mechanism in between to generate the input file for iObeya, ids would then be preferred as this would allow the room owner to change names without breaking the automation or having to ask the mechanism to be modified.
- Finally, if the goal is to update KPIs across a standardized/normalized hierarchy of QCD boards, with a maximum of flexibility, then data tags would be preferred
Depending on the desired flexibility and on how the input data is produced (automatically vs manually), different combinations of selectors would be used.
The table below summarizes all the columns available for each type of target, by order of precedence (in the case where a target would be defined with both name, id or tag, which would be probably a mistake)
Target | 1 | 2 | 3 |
---|---|---|---|
Room | roomId | roomTag | roomName |
Board | boardId | boardTag | boardName |
Letter | letterId | letterTag | |
Indicator | indicatorName |
Spreadsheet example:
Of course, such a flexibility might not prove to be useful, and it will belong to you to design your own input file in the most practical way, depending on your needs and use cases.
Looking for more?
- Do you want to learn more about this connector capabilities? Browse the iObeya connector documentation
- You are using another RPA/iPaaS platform than Power Automate? Browse the Facade API documentation to discover the API services used with Power Automate
- Interested to have an overview of the integration capabilities of the iObeya platform? Access the integrations page
If you have any questions or would like more information about using Power BI and iObeya for your business, please don't hesitate to contact us at integrations-support@iobeya.com. Our team of experts is available to provide additional guidance and support, and can also offer personalized demonstrations of these powerful tools to help you get the most out of them. We look forward to hearing from you and helping you achieve your business goals with iObeya.