Map your data
Important
Some or all of this functionality is available as part of a preview release. The content and the functionality are subject to change.
Important
This document includes several references to the ISA95 semantic model. Visit the ISA95 website and store to purchase the ISA95 standards.
The Manufacturing data solutions in Microsoft Fabric utilize the ISA95 ontology for structuring and integrating data points across manufacturing systems and ensures interoperability and coherence within the data architecture.
This document includes several references to the ISA95 semantic model. For more information and to purchase the ISA95 standards, visit the ISA95 website and ISA95 store.
Detailed descriptions of the industry standardized data model are provided in the Appendix: Manufacturing data models which serves as a reference for various aspects of manufacturing operations, from resources to work floor operations.
Mapping process
The following sections are intended to help understand the data mapping process using sample data from Contoso, a fictitious bakery factory.
Files in the directory are:
- mds-mapping-local.ipynb
- With this notebook, you can transform the raw data to ISA95 in your local environment.
- Follow the steps mentioned at the top of the notebook.
- mds-mapping-fabric.ipynb
- With this notebook, you can transform the raw data to ISA95 in your Fabric environment.
- Follow the steps mentioned at the top of the notebook.
- In the data folder, you find the following files:
- raw folder has the input Bakery csv files to transform.
- transformed zip that has the example transformed output. (Needs to be generated from notebook, provided as example)
- In the config folder, you find the following files:
- conceptual-mapping.xlsx file has the conceptual mapping.
- config.json is an example generated configuration json file. (Needs to be generated from notebook, provided as example)
- The sample data mapping python folder has the following files:
- create-config.py python file has helper functions to create configuration files.
- transform.py python file has helper functions to make the ISA95 transformation.
The conceptual-mapping.csv file, which includes mapping information, and the raw bakery dataset are the inputs. The configuration JSON files and the transformed Manufacturing data solutions ISA95 dataset are the outputs. For more information and to purchase the ISA95 standards, visit the ISA95 website and ISA95 store.
Example Dataset
To illustrate the mapping process, we use an example dataset: Bakery Shop Raw Data. It's a fictional manufacturing company, with four production lines that are producing different types of bakes and pastries, using baking ovens. This sample is provided in order to give an example of how to convert customer source data to Manufacturing data solutions ISA95 data using provided python notebooks.
The dataset contains six different files: Production details, Consumption, Downtime, Scrap, Schedule, and Telemetry.
A high level of characterization is:
- Four lines (Ovens) of production
- Production order spans 2-3 shifts
- Four Crews organized in two shifts
- Different SKUs of finished product
- Different ingredients used for manufacturing
The goal of this data set is to have a E2E overview of the setup process, being able to query Agent on different manufacturing articles.
Step By Step mapping process
We use four different scenarios for the mapping process to cover the most common use cases. The scenarios help you understand how to set up Manufacturing data solutions and Agent E2E.
- Production results, planned vs actual, Production related KPIs
- Downtime analysis, when and what
- Quality analysis, Scrap rates, and type of defects
- Equipment Telemetry data
We capture all the details of the mapping process in the conceptual_mapping.xlsx file.
The file serves as a configuration file for the Ingestion Script that ingests the data into Manufacturing data solutions. For more information, see Ingestion script.
Scenario 1: Production results, planned vs Actual
We start taking a look at our source data; for this scenario we use four different files: Production details, Scrap, Consumption, and Schedule. We're looking for schedule related columns, searching for things like Orders, Planned quantity, planned Equipment, etc.
In ISA95, there's a specific model for Scheduling, called Operations Schedule. To learn more about Operations Schedule model, refer to Section 6.2, Page 196 of ANSI/ISA95.00.02-2018.
The Scheduling model in ISA95 refers to how production processes are planned, organized, and managed within a manufacturing environment. At a high level, it focuses on generating, tracking, and executing production orders on the shop floor, aiming to efficiently allocate resources such as machines, materials, and labor to meet production goals.
In terms of manufacturing data, the scheduling model involves key elements such as:
- Production Orders: These orders are the central units of scheduling. Each order contains details like a unique ID, description, and status, along with the quantity of products to be made, planned start/end times, and the resources needed (machines, materials, labor).
- Resources: Scheduling involves specifying the machines or production lines required for each order, and the materials to be consumed. This scheduling ensures that all necessary resources are aligned for execution at the planned times.
- Timing: Scheduling manages the planned start and end times for production activities to ensure that production meets deadlines and is aligned with broader business needs.
The Scheduling model ensures that manufacturing operations are consistently linked with higher-level business processes, facilitating real-time visibility and control over the shop floor. This helps in optimizing operations, improving efficiency, and reacting quickly to changes in demand or resource availability.
In the following sections, you find a couple of real world scenarios to help choose which entity is used to map our Source data order entity.
Scenario 1: Two Levels of Orders (Operations Schedule + Nested Operations Request)
High-Level Order (Operations Schedule): A monthly schedule is created for a manufacturing plant. This schedule outlines overall production targets, including high-level objectives like producing 10,000 units of a product across multiple production lines.
Nested Orders (Operations Request): The monthly schedule breaks down into weekly production orders, each targeting a subset of the total units. For example, the first Operations Request involves producing 2,500 units in Week 1 on a specific production line, with defined machine and material allocations for that period.
In this case, the Operations Schedule coordinates the overall plan, and each Operations Request specifies the detailed execution for smaller timeframes or production batches.
Scenario 2: One Level of Order (Operations Request Only)
- Single Order (Operations Request): A daily production order is created directly for a specific product, requiring 1,000 units to be produced on a particular machine. The order includes required quantities, start and end times, and material needs for immediate execution.
This simpler scenario only uses the Operations Request to handle all scheduling at the production order level without a higher-level schedule.
Bakery Dataset Entity mapping #1: Operations Request
Let's look at our Schedule.csv from Bakery Dataset. The OR is the column that contains the IDs of the orders
for Bakery.
There's another Column (SR), that looks like a suborder. Each oven line in the bakery can produce one or two different types of outputs, depending on the SKU being baked. Consequently, a single order can result in one or two different SKUs. However, the raw materials are shared, so the order is considered as one. That makes it a Scenario 2 type of mapping, where there's just one order level.
That gives us the first ISA95 entity that we use: Operations Request. To learn more about Operations Request, refer to Section 6.2.3, Page 202 of ANSI/ISA95.00.02-2018.
Lets take a look at its attributes, by looking at the information in Operations Schedule Model:
Operations request
| ISA95 Attributes | ||||||
|---|---|---|---|---|---|---|
| Attribute | ID | description | operationsType | startTime | endTime | requestState |
| Schedule.csv | OR | OR-Description | -- | startTime | endTime | ScheduleStatus |
Note
Not all ISA95 columns need to be mapped; only those mandatory need to be, and its up to us to choose which ones we need to fulfil our use case; for example, do we have something like related to priority of the order in our source data, and we want to use it for our Agent Experience? Then go ahead and map it to the Priority attribute of Operations Request.
We use -- to indicate that we aren't mapping that specific ISA95 attribute (since it's not mandatory).
For more information, see Section 6.2.3 (table 177), Page 203 of ANSI/ISA95.00.02-2018.
Bakery Dataset Entity mapping #2: Material Requirement
We take a look at the quantities of materials consumed, produced, scrapped, to continue working on the use case.
The materials planned and used belongs to two different models in ISA95: Operations Schedule (quantities planned) and Operations Performance (quantities used). To learn more about operations schedule and performance, refer to Section 6.2.2 (operations schedule) and Section 6.3.1 (operations performance) of ANSI/ISA95.00.02-2018.
We start with the quantities planned. By looking at the model, we can see an entity called Material Requirement. This entity is used to capture things like, materials planned to be produced, consume, etc.
Taking a look at its attributes by looking at the information in Operations Schedule Model:
Material requirement
| ISA95 Attributes | ID | description | storageLocation | storageLocationType | assemblyType | assemblyRelationship | materialUse | quantity | quantityUnitOfMeasure |
|---|---|---|---|---|---|---|---|---|---|
| Schedule.csv | ID"Produced" | -- | -- | -- | -- | -- | "Material produced" | Quantity | quantityUnitOfMeasure |
| Schedule.csv | ID"Consumed" | -- | -- | -- | -- | -- | "Material consumed" | BOMQuantity | BOMuom |
For more information on Material requirements, see Section 6.2.12 (material requirements) and Section 6.2.12 (material requirements attributes) of ANSI/ISA95.00.02-2018..
Notes on the mapping methodology:
We're indicating some fields between quotes (""), which means that we're hardcoding the value in all the rows from the source data. In this case, all rows from the Schedule.csv that contain information on quantity to be produced has the value "Material produced" in the column materialUse. This step is because "Material produced" is an enum (predefined value in ISA95) and is also a mandatory field to be filled. We did the same with those quantities to be consumed ("Material consumed").
In the column id, we're using ID"Produced", which concatenates the actual ID value from the row data with the characters "Produced", to ensure that we don't repeat any ID, and also to have a clear differentiation between consumed and produced by just looking at the ID.
Bakery Dataset Entity mapping #3: Material Actual
We follow with Actual quantities (Actual produced, Actual Scrap...) and use the Operations Performance model:
The Operations Performance model in ISA95 defines how the results of manufacturing operations are captured and reported. It focuses on tracking the actual execution of production, maintenance, testing, and material movements against the planned operations and provides detailed performance metrics. To learn more about operations performance, refer to Section 6.3.1 (operations performance), Page 224 of ANSI/ISA95.00.02-201.
Key elements of the Operations Performance model include:
- Execution Results: Represents the work that was performed in terms of production orders, maintenance tasks, testing activities, and material movements. It includes data such as what was done, when it was done, and which resources (machines, materials, and labor) were utilized.
- Resources Used: The performance model tracks the actual usage of resources, including machines, materials, personnel, and tools. This tracking ensures a comparison of planned vs. actual resource consumption, providing insights into efficiency.
- Timeliness and Quality: It captures whether production activities were completed within the scheduled timeframes and measures the quality of the outcomes. This step involves checking if the products meet the defined specifications or if any defects were identified.
The Operations Performance model ensures that manufacturing activities are aligned with higher-level business goals by providing real-time feedback on the actual operations. It plays a critical role in performance analysis, helping manufacturers identify bottlenecks, inefficiencies, and areas for improvement, enabling more accurate decision-making and process optimization.
Now we proceed with the mapping of Material Actuals: The Operations Performance model ensures that manufacturing activities are aligned with higher-level business goals by providing real-time feedback on the actual operations. It plays a critical role in performance analysis, helping manufacturers identify bottlenecks, inefficiencies, and areas for improvement, enabling more accurate decision-making and process optimization.
Now we proceed with the mapping of Material Actuals:
Material actual
| ISA95 Attributes | ID | description | storageLocation | storageLocationType | assemblyType | assemblyRelationship | materialUse | quantity | quantityUnitOfMeasure |
|---|---|---|---|---|---|---|---|---|---|
| ProductionDetails.csv | ID"Actual-Produced" | -- | -- | StorageLocation | -- | -- | "Material produced" | Quantity | quantityUnitOfMeasure |
| Consumption.csv | ID"Actual-Consumed" | -- | -- | -- | -- | -- | "Material consumed" | Quantity | quantityUnitOfMeasure |
| Scrap.csv | ID"Actual-Scrap" | -- | -- | -- | -- | -- | "Scrap" | Quantity | quantityUnitOfMeasure |
For more information on material actual, see See Section 5.7.6, Page 90 of ANSI/ISA95.00.02-2018.
Now we mapped both Material requirements, and Material actual.
Bakery Dataset Entity mapping #4: Material lot
We identified both Material Requirement and Material Actual. Whenever a Material Actual is generated, normally we have a Lot number associated to be able to track all the items produced. In ISA95, we use the entity Material Lot.
Material lot
| ISA95 Attributes | ID | description | assemblyType | assemblyRelationship | storageLocation | storageLocationType | status | disposition | quantity | quantityUnitOfMeasure | inventoryManagementSystemRefID | MESsystemRefID |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ProductionDetails.csv | Lot | -- | -- | -- | -- | -- | "Approved" | -- | Quantity | quantityUnitOfMeasure | -- | -- |
| Scrap.csv | "Scrap-"Lot | -- | -- | -- | -- | -- | "Scrap" | -- | Quantity | quantityUnitOfMeasure | -- | -- |
For more information on material lot, see Section 5.7.6, Page 90 of ANSI/ISA95.00.02-2018.
In this case, we're generating two types of lots: one for produced items and another for scrap.
Bakery Dataset Entity mapping #5: Material Definition
Material Definition is, as per ISA95, a master data entity. Product names (SKUs) are mainly part of the Material Definition. Also, ingredients and raw materials are captured here.
For more information on material definition, see Section 5.7.4 of ANSI/ISA95.00.02-2018.
Material Definition
| ISA95 Attributes | ID | Description | HierarchyScope | AssemblyType | AssemblyRelationship | InventoryManagementSystemRefID | MESSystemRefID |
|---|---|---|---|---|---|---|---|
| ProductionDetails.csv | Product | -- | -- | -- | -- | -- | -- |
| Consumption.csv | Product | -- | -- | -- | -- | -- | -- |
For more information on material definition, see Section 5.7.4 of ANSI/ISA95.00.02-2018.
We do a simple map here, only capturing the names of both products and raw ingredients from two different files.
Before we go to other signals from the shop floor, like Equipment Requirements and Actuals, let's take a look at 2 important ISA95 entities that are needed to link all these Requirements and Actuals to our overall mapping flow. To learn more about Requirements and Actuals, refer to Section 6.2.8 (equipment requirement) and Section 6.3.8 (equipment actual) of ANSI/ISA95.00.02-2018.
By looking at the 2 provided schemas of both Operations Performance and Operations Schedule models, we can see that all Requirements and Actuals are connected through 2 specific ISA95 entities: Segment Requirement and Segment Response. To learn more about Segment requirements and Segment response, refer to Section 6.2.4 (segment requirement) and Section 6.3.4 (segment response) of ANSI/ISA95.00.02-2018.
Let's take a closer look at these key entities:
Segment Requirement
The Segment Requirement represents the planned or scheduled needs for a specific operation segment. It includes the following key elements:
- Material Requirement: Specifies the materials needed for a particular operation, including quantities and specifications.
- Equipment Requirement: Defines the machinery or tools required to execute the segment, including any specific equipment capabilities.
- Personnel Requirement: Identifies the personnel needed, including skills and roles, to carry out the operation.
- Timing and Sequencing: It outlines when and in what order the segment should be executed within the broader production schedule.
In essence, the Segment Requirement is a detailed breakdown of what resources (materials, equipment, labor) are necessary to meet the production schedule or plan.
Segment Response
The Segment Response captures the actual results of executing the corresponding Segment Requirement. It provides data on:
- Material Actual: The materials consumed or produced during the operation.
- Equipment Actual: The equipment that was used, including any performance or operational details.
- Personnel Actual: The actual personnel who carried out the work, along with any details on hours worked or performance.
- Execution Results: It records whether the segment was completed on time, within the planned sequence, and if it met the specified quality requirements.
In summary, Segment Response is the real-world reflection of the Segment Requirement, capturing what happened versus what was planned.
Linking Segment Requirement and Segment Response
These entities are linked to track planned versus actual execution for operations. For instance:
- Material Actual ties back to the Material Requirement to compare what was planned for consumption or production versus what actually happened.
- Equipment Requirement and Equipment Actual help monitor if the planned machinery and tools were used efficiently or if adjustments were needed.
- Segment Data like production rates, completion times, and quality checks link back to both Segment Requirement and Segment Response to enable performance evaluation and optimization.
This linkage between requirements (what is planned) and responses (what is executed) is crucial for monitoring efficiency, resource utilization, and operational performance in real-time.
Now that we outlined the importance of these two entities, let's find the best way to map it to our Bakery Scenario.
Bakery Dataset Entity mapping #6: Segment Requirement
We start with Segment Requirement:
Segment Requirement
| ISA95 Attributes | ID | description | operationsType | earliestStartTime | latestEndTime | duration | durationUnitOfMeasure | segmentState |
|---|---|---|---|---|---|---|---|---|
| Schedule.csv | "SR_"SR | SR-Description | "Production" | startTime | endTime | Duration | "Hours" | -- |
Segments are virtual representations of our processes; how long they take (duration), when they start and end, which process and its description, etc. We use it to link, by using relationships in our ISA95 data model, to other entities like materials, equipment, people, and so on.
For more information on segment requirement, see Section 6.2.4 (table 179), Page 205 of ANSI/ISA95.00.02-2018.
Bakery Dataset Entity mapping #7: Segment Response
Segment Response
| ISA95 Attributes | ID | description | operationsType | actualStartTime | actualEndTime | postingDate | segmentState |
|---|---|---|---|---|---|---|---|
| ProductionDetails.csv | "SRPS-"SR"-"ID | SR-Description | -- | -- | -- | PostingDate | -- |
| Scrap.csv | "SRPS-SCRAP"SR"-"ID | SR-Description | -- | -- | -- | PostingDate | -- |
For more information on segment response, see Section 6.3.4 (table 205), Page 232 of ANSI/ISA95.00.02-2018.
For Segment Response, we're getting production information from both production-details.csv and scrap.csv.
Now that we outlined the importance of these two entities, let's find the best way to map them to our Bakery scenario.
Note
Later in the process, we will create relationships between these segment responses and their Material Actuals.
We're good to move now to Equipment, which equipment was plan to be used, and for how long, and what were the actual equipments used. Before that, it's important to define the Hierarchy Scope ISA95 entity that helps establish hierarchies in our equipment, starting from companies and plants down to specific machines and even sensors within those machines. To learn more about Hierarchy Scope, refer to Section 5.1, Page 45 of ANSI/ISA95.00.02-2018.
Bakery Dataset Entity mapping #8: Hierarchy Scope
The Hierarchy Scope entity in ISA95 defines where the exchanged information is applicable within the hierarchy of the manufacturing system. Specifically, it describes the level within the role-based equipment hierarchy that is associated with the information being exchanged.
To learn more about Hierarchy Scope, refer to Section 5.1, Page 45 of ANSI/ISA95.00.02-2018.
For example, it could identify:
- A site (for example, WEST-END)
- An area within a site (for example, WEST-END/HOLDING-AREA)
- A specific work center within an area (for example, CHIPPING-BIN #1)
Key points:
- The Hierarchy Scope ensures that the information being exchanged is relevant to a specific site, area, or work center in the manufacturing hierarchy.
- It helps in linking operational and resource information, such as materials or equipment, to specific locations in the hierarchy.
- In some cases, the hierarchy scope can include the full structure, from enterprise down to work center, or it might only specify a work center if the higher levels (site or area) are already implied.
This entity is crucial for situating operational performance, schedule data, or equipment usage within the correct physical or logical context in manufacturing systems.
Hierarchy Scope
| ISA95 Attributes | ID | equipmentID | equipmentLevel |
|---|---|---|---|
| ProductionDetails.csv | "HS-"WorkCenter | WorkCenter | "workCell" |
| ProductionDetails.csv | "HS-"Facility | Facility | "site" |
To learn more about Hierarchy Scope, refer to Section 5.1, Page 45 of ANSI/ISA95.00.02-2018.
For the bakery, we only have two levels of equipment: site (HYD) and workCell (Breadblast04 to Breadblast07).
The Hierarchy Scope entity is used to understand the relationships between equipment, such as which machine belongs to which production line. It also links specific events, like downtime, to specific equipment. Now that we defined our Hierarchy Scope entity, we define our equipment in Bakery.
Bakery Dataset Entity mapping #9: Equipment
Equipment information is considered master data, and only needs to be updated when new equipment is added or existing equipment is modified.
For the bakery, the mapping for equipment is as given in the following table:
Equipment
| ISA95 Attributes | ID | Description | HierarchyScope | equipmentLevel | operationalLocation | operationalLocationType | assetSystemRefID | MESSystemRefID |
|---|---|---|---|---|---|---|---|---|
| ProductionDetails.csv | WorkCenter | -- | -- | "workCell" | -- | -- | -- | -- |
| ProductionDetails.csv | Facility | -- | -- | "site" | -- | -- | -- | -- |
To learn more about Equipment entity, refer to Section 5.5.4 (table 44), Page 69 of ANSI/ISA95.00.02-2018.
Same as Hierarchy Scope, we're getting information of equipment from two different columns in production-details.csv, WorkCenter (containing the production lines) and Facility (containing the site). EquipmentLevel as per ISA95 is an enum value, therefore we use the corresponding enum between ("").
Bakery Dataset Entity mapping #10: Equipment Requirement
In the same way that we defined Material requirements for a given order ("Operations Request" in our ISA 95 implementation), we're now mapping Equipment Requirement, which is meant to define the equipment planned to be used, and -optional- the duration of it.
To learn more about Equipment requirement, refer to Section 6.2.8, Page 210 of ANSI/ISA95.00.02-2018.
| ISA95 Attributes | ID | description | operationalLocation | operationalLocationType | equipmentUse | quantity | quantityUnitOfMeasure | equipmentLevel |
|---|---|---|---|---|---|---|---|---|
| Schedule.csv | "ER_"SR | WorkCenter | WorkCenter | -- | WorkCenter | Quantity | "hours" | -- |
Bakery Dataset Entity mapping #11: Equipment Actual
The EquipmentActual entity in the Operations Performance model captures the actual usage and performance details of equipment during a manufacturing operation. It records data such as the specific equipment used, its operational status, duration of use, and any deviations from planned performance. This entity is essential for tracking how equipment was utilized compared to what was planned, helping assess efficiency and identify issues in operations.
Normally, every segment requirement and Actual has an associated Equipment Actual, indicating all information regarding equipment for that segment.
*Equipment Actual
| ISA95 Attributes | ID | description | SpatialDefinition | operationalLocation | operationalLocationType | equipmentUse | quantity | quantityUnitOfMeasure | assetSystemRefID | MESSystemRefID |
|---|---|---|---|---|---|---|---|---|---|---|
| ProductionDetails.csv | ID"EA_"SR | WorkCenter | -- | -- | -- | WorkCenter | "0" | "hours" | -- | -- |
| Scrap.csv | ID"SCRAP-EA_"SR | WorkCenter | -- | -- | -- | WorkCenter | "0" | "hours" | -- | -- |
To learn more about Equipment actuals, refer to Section 6.3.8 (table 213), Page 238 of ANSI/ISA95.00.02-2018 .
In this case, there was no indication of machine time in our Bakery source data, so we hardcoded "0". Normally, we have machine time, or maybe other dimension related with the equipment, like Cycle time.
Since we're getting production information in two different segments (Segments coming from Production details and Scrap), we need to capture info from both tables.
Bakery Dataset Entity mapping #12: Segment Data
We use ISA95 entity Segment Data to capture any information related to a segment that isn't Materials, equipment, or people. To learn more about Segment Data, refer to Section 6.3.5, Page 233 of ANSI/ISA95.00.02-2018.
While it can capture a wide array of complementary information about a Segment, we primarily use it for specific production reporting details such as shift, team, and crew.
Yes, there's an entire set of entities in ISA 95 called Calendar, but that is more suited for defining and organizing your shifts, teams and all related to calendar information. For a querying experience, like getting information about which specific shift or team was working on a specific day, is more convenient and easier to map if we use Segment Data. To learn more about Calendars, refer to Section 13.6, Page 103 of ANSI/ISA95.00.04-2018.
For the bakery, we're using Segment Data to store Shift and crew.
Segment Data
| ISA95 Attributes | ID | description | value | valueUnitOfMeasure | ExternalReferenceID | ExternalReferenceDictionary |
|---|---|---|---|---|---|---|
| production-details.csv | Shift | "Shift" | Shift | "NA" | -- | -- |
| production-details.csv | "Crew "-Crew | "Crew" | Crew | "NA" | -- | -- |
| Scrap.csv | Shift | "Shift" | Shift | "NA" | -- | -- |
| Scrap.csv | "Crew"-Crew | "Crew" | Crew | "NA" | -- | -- |
To learn more about Segment Data, refer to Section 6.3.5, Page 233 of ANSI/ISA95.00.02-2018.
In this case, we're capturing both Shift and Crew information, for two different sources: Production details and Scrap. This mapping helps us to determine which shift and which crew was working when specific production was reported, or when scrap was reported.
As mentioned with Equipment and Material Definition, our Segment Data mapping can be considered as Master Data, as the only entry is unique names for shifts, and unique names for crews; just a few rows of data are generated here. Later in the process of mapping, we use multiple relationships to link each Shift and Crew in every production run.
As per ISA 95, the model to capture details on Quality is the Operations Test information. To learn more Operations Test information, refer to Section 5.9, Page 126 of ANSI/ISA95.00.02-2018.
Here's a brief description:
The Operations Test Information model in ISA95 defines how testing and quality control data are managed within manufacturing operations. It includes information related to tests performed on materials, products, or processes to ensure compliance with specifications and quality standards. To learn more Operations Test information, refer to Section 5.9, Page 126 of ANSI/ISA95.00.02-2018.
Key components:
- Test Definitions: Descriptions of the tests, including procedures, parameters, and criteria.
- Test Results: Data from actual tests conducted, such as measurements, pass/fail outcomes, and any deviations from expected results.
- Test Resources: The equipment, personnel, and materials used in the testing process.
This model ensures that testing is integrated into operations, enabling real-time quality control and continuous improvement. Now let's see how we use it to map quality-related info from the bakery dataset. We only have one table with quality-related data, Scrap.csv.
Since we aren't interested in test definitions or resources, we only want to map quality results from the shop floor, we use only one entity:
Now let's see how we use it to map quality related info form Bakery data set.
We only have one table with quality related data, scrap.csv.
Bakery Dataset Entity mapping #13: Test Result
Test Results
| ISA95 Attributes | ID | description | evaluationDate | evaluatedCriterionResult | expiration |
|---|---|---|---|---|---|
| Scrap.csv | ID | Description | PostingDate | "Fail" | -- |
To learn more Operations Test results, refer to Section 5.9.8, Page 137 of ANSI/ISA95.00.02-2018.
In description, we normally find customer predefined values that keep repeating, corresponding to each category of quality/scrap issues.
For downtime events, we rely on the Operations Event model.
The Operations Event model in ISA95 captures and tracks significant occurrences during manufacturing operations, such as equipment failures, downtime, and production delays. To learn more Operations event model, refer to Section 5.11.1, Page 146 of ANSI/ISA95.00.02-2018.
Key components:
- Event Types: Defines the nature of events, like downtime, maintenance, or production interruptions.
- Event Triggers: Specifies what caused the event (for example, equipment malfunction, material shortage).
- Event Data: Records the start time, duration, and impact of the event on production.
For tracking downtime events, this model helps log when and why equipment was down, how long the downtime lasted, and the operational impact. It enables detailed analysis of downtime causes, allowing for better maintenance planning, operational improvements, and reducing future downtime.
Bakery Dataset Entity mapping #14: Operations Event
For this mapping phase, we use only one file, Downtime.csv
Operations Event
| ISA95 Attributes | ID | description | effectiveTimestamp | recordTimestamp | operationsEventLevel | Priority | operationsEventType | operationsType | momFunction | enterpriseFunction | category | source |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Downtime.csv | ID"-"WorkCenter | Description | StartTime | -- | -- | -- | -- | "Maintenance" | -- | "Maintenance management" | Category | -- |
To learn more Operations event, refer to Section 5.11.8 (Operations event), Page 158 of ANSI/ISA95.00.02-2018.
We're storing Description in the ISA95 description attribute; this attribute contains the specific failure description, and then we're using Category for the different downtime categories we might have. Although not used this time, the source ISA95 attribute is commonly used to have yet another potential dimension of downtime sources, for example, we could use the source value to differentiate from downtimes that affect availability or not (for OEE calculation in the future).
Hardcoded values were used for fields that weren't present in the bakery dataset; the values aren't mandatory, but we thought it might be good for overall understanding, and perhaps being able to filter these events from other Operations Events that we might want to map in the future that aren't maintenance related, for instance, safety-related events.
As we can see, there's no duration of the downtime mapped here, as we're only capturing the StartTime in effectiveTimestamp. Normalization of the model allows us to map this dimension in the next entity:
Bakery Dataset Entity mapping #15: Operations Event Property
Operations Event Property
| ISA95 Attributes | ID | description | value | valueUnitOfMeasure | externalReferenceID | externalReferenceDictionary | externalReferenceURI |
|---|---|---|---|---|---|---|---|
| Downtime.csv | ID | "Downtime in "WorkCenter | DowntimeDuration | "minutes" | -- | -- | -- |
To learn more Operations event property, refer to Section 5.11.9, Page 161 of ANSI/ISA95.00.02-2018.
In this case, we store the duration in the value column. Since there's no specific column indicating the unit of measure for the value column, we just hardcoded it, knowing that it is in fact minutes.
Telemetry data coming from sensors and machines is common in manufacturing.
We use the ISA95 Equipment Property entity to capture telemetry data. To learn more Equipment Property, refer to Section 5.5.5, Page 70 of ANSI/ISA95.00.02-2018.
Bakery Dataset Entity mapping #16: Equipment Property
Equipment Property
| ISA95 Attributes | ID | description | value | valueUnitOfMeasure | sourceTimeStamp |
|---|---|---|---|---|---|
| Telemetry.csv | tag | "Temperature" | value | "Celsius" | timestamp |
To learn more Equipment Property, refer to Section 5.5.5, Page 70 of ANSI/ISA95.00.02-2018 .
In this case, we're mapping tag as id, value, and timestamp.
Relationships
As per ISA95, entities can have relationships with other entities. Relationships are defined as per ISA95 and defined in full detail within the ANSI/ISA 95.00.02-2018 standard. For a given source entity and target entity, there's only one valid relationship, and the type is set. For example, Operations Request (source) is related to Segment Requirement (target), and the relationship is "Is made up of".
To define all relationships in our dataset, we need to explicitly indicate:
- Source entity name
- Target entity name
- Source join column
- Target join column
- Source join column filter
- Target join column filter
- Source file name
- Target file name
Scenario 1: Production results, planned vs Actual
For Scenario 1, we used the Operations Schedule, Operations Performance, and Operations Test models. Besides these entities, we also used Equipment and Hierarchy Scope models.
Let's identify (in red) what are the entities that we mapped (Red rectangles), and their relationships (red lines)
Hierarchy Scope and Equipment models
Equipment always has an associated Hierarchy Scope. Then Hierarchy Scope defines hierarchy relationships within the equipment.
Let's create the relationships:
Equipment to Hierarchy Scope Relationship
This relationship creates relationships between equipment and their hierarchy scope.
| Source Entity Name | Target Entity Name | Source Join Column | Target Join Column | Source Join Column Filter | Target Join Column Filter | Source File Name | Target File Name |
|---|---|---|---|---|---|---|---|
| Equipment | Hierarchy Scope | ID | ID | equipmentLevel = 'workCell' | equipmentLevel = 'Work Cell' | ProductionDetails | ProductionDetails |
| Equipment | Hierarchy Scope | ID | ID | equipmentLevel = 'site' | equipmentLevel = 'Site' | ProductionDetails | ProductionDetails |
Hierarchy Scope to Hierarchy Scope Relationship
This relationship creates relationships within hierarchies and is used to define our hierarchy of equipment, like Plant → Area (1,2,3) → Production line (a,b,c,d), etc.
| Source Entity Name | Target Entity Name | Source Join Column | Target Join Column | Source Join Column Filter | Target Join Column Filter | Source File Name | Target File Name |
|---|---|---|---|---|---|---|---|
| Hierarchy Scope | Hierarchy Scope | ID | ID | equipmentLevel = 'Site' | equipmentLevel = 'Work Cell' | ProductionDetails | ProductionDetails |
Operations Schedule model
We start top down in the diagram.
Operations Request to Segment Requirement Relationship
When we refer to the ISA95 Operations Schedule documentation, or just at the diagram shown, we can easily check that Operations Request is the source entity, and Segment Requirement is the target entity.
For our previous mapping exercise, we also know that both entities are coming from the same file (Schedule.csv). Since they are in the same table, then whenever both entities are in the same row, we know that a relationship is created and we just choose to use ID as the linking column.
We don't need to specify the type (or name) of the relationship because it gets added automatically as there's only one value, in this case, "Is made up of".
| Source Entity Name | Target Entity Name | Source Join Column | Target Join Column | Source Join Column Filter | Target Join Column Filter | Source File Name | Target File Name |
|---|---|---|---|---|---|---|---|
| Operations Request | Segment Requirement | ID | ID | Schedule | Schedule |
We continue with the remaining entities and relationships using the same methodology:
Segment Requirement to Equipment Requirement Relationship
| Source Entity Name | Target Entity Name | Source Join Column | Target Join Column | Source Join Column Filter | Target Join Column Filter | Source File Name | Target File Name |
|---|---|---|---|---|---|---|---|
| Segment Requirement | Equipment Requirement | ID | ID | Schedule | Schedule |
Segment Requirement to Material Requirement Relationship
| Source Entity Name | Target Entity Name | Source Join Column | Target Join Column | Source Join Column Filter | Target Join Column Filter | Source File Name | Target File Name |
|---|---|---|---|---|---|---|---|
| Segment Requirement | Material Requirement | ID | ID | Schedule | Schedule |
Now, we have special or not straightforward relationships here.
From Equipment Requirement, we see a relationship to Equipment model. This status means that a particular Equipment Requirement can be linked to any entity in the whole equipment model. The same happens to Material Requirement and the Material model.
For example, we might have a Material Requirement of 15,000 units linked to a Material Definition, the product name or SKU, or a Material Lot if the customer needs to generate future material lots in advance..
Let's map these relationships:
Equipment Requirement to Equipment Relationship
Out of the entire Equipment model, we're interested in linking to a particular Equipment Requirement its Master Equipment, so we use the Equipment entity.
| Source Entity Name | Target Entity Name | Source Join Column | Target Join Column | Source Join Column Filter | Target Join Column Filter | Source File Name | Target File Name |
|---|---|---|---|---|---|---|---|
| Equipment Requirement | Equipment | SR | SR | equipmentLevel = 'workCell' | Schedule | ProductionDetails |
In this case, we use SR (Segment Requirement) as the linking column. The reason is that both entities are coming from different tables, and that is the common column.
Material Requirement to Material Definition Relationship
Out of the entire Material model, we're interested in linking to a particular Material Requirement its Master Material, so we use the Material Definition entity.
| Source Entity Name | Target Entity Name | Source Join Column | Target Join Column | Source Join Column Filter | Target Join Column Filter | Source File Name | Target File Name |
|---|---|---|---|---|---|---|---|
| Material Requirement | Material Definition | Product | Product | Schedule | ProductionDetails |
Operations Performance model
We follow the same logic.
Note
The topmost pair of entities, Segment Requirement and Segment Response from different models, are the ones we are using to link both Operations Schedule and Performance models; the business meaning is, for every required segment from the Schedule model, we will have a Segment Response from the shop floor.
Segment Response to Segment Requirement Relationship
Let's find a relationship between entities that are spread across different sources or tables.
By looking at the mapping exercise, we can check that while Segment Requirement is coming from Schedule.csv, Segment Response is coming from both production-details.csv and Scrap.csv.
| Source Entity Name | Target Entity Name | Source Join Column | Target Join Column | Source Join Column Filter | Target Join Column Filter | Source File Name | Target File Name |
|---|---|---|---|---|---|---|---|
| Segment Response | Segment Requirement | SR | SR | ProductionDetails | Schedule | ||
| Segment Response | Segment Requirement | SR | SR | Scrap | Schedule |
As explained before, we look for a common column to link different tables, as IDs are different and can't be used.
Segment Response to Segment Data Relationship
This relationship creates links between Segment Response and Segment Data, which is storing information about actual manufacturing shifts and crews.
| Source Entity Name | Target Entity Name | Source Join Column | Target Join Column | Source Join Column Filter | Target Join Column Filter | Source File Name | Target File Name |
|---|---|---|---|---|---|---|---|
| Segment Response | Segment Data | ID | ID | ProductionDetails | ProductionDetails | ||
| Segment Response | Segment Data | ID | ID | Scrap | Scrap |
Segment Response to Equipment Actual Relationship
| Source Entity Name | Target Entity Name | Source Join Column | Target Join Column | Source Join Column Filter | Target Join Column Filter | Source File Name | Target File Name |
|---|---|---|---|---|---|---|---|
| Segment Response | Equipment Actual | ID | ID | ProductionDetails | ProductionDetails | ||
| Segment Response | Equipment Actual | ID | ID | Scrap | Scrap |
Segment Response to Material Actual Relationship
Again, in this case, we have relationships linking different source tables.
Basically, we have Material Actual, which in this case is storing information about actual production quantities in both production-details.csv (actual production) and Scrap.csv (actual scrap generated), and consumption data from Consumption.csv.
| Source Entity Name | Target Entity Name | Source Join Column | Target Join Column | Source Join Column Filter | Target Join Column Filter | Source File Name | Target File Name |
|---|---|---|---|---|---|---|---|
| Segment Response | Material Actual | ID | ID | ProductionDetails | ProductionDetails | ||
| Segment Response | Material Actual | SR, PostingDate | SR, PostingDate | ProductionDetails | Consumption | ||
| Segment Response | Material Actual | ID | ID | Scrap | Scrap |
To relate these two entities, we use a concatenation of SR + PostingDate to make relationships unique for every different timestamp.
This step seems to be duplicated, but is needed since every time we report scrap, we also report consumption (in this example dataset, consumption isn't tracked at start, but calculated backflushing the production based on BOM, so it's theoretical consumption).
Equipment Actual to Equipment Relationship
Out of the entire Equipment model, we're interested in linking to a particular Equipment Actual its Master Equipment, so we use the Equipment entity.
| Source Entity Name | Target Entity Name | Source Join Column | Target Join Column | Source Join Column Filter | Target Join Column Filter | Source File Name | Target File Name |
|---|---|---|---|---|---|---|---|
| Equipment Actual | Equipment | ID | ID | equipmentLevel = 'workCell' | ProductionDetails | ProductionDetails | |
| Equipment Actual | Equipment | WorkCenter | WorkCenter | equipmentLevel = 'workCell' | Scrap | ProductionDetails |
Material Actual to Material Definition Relationship
Out of the entire Material model, we're interested in linking to a particular Material Actual its Master Material, so we use the Material Definition entity.
| Source Entity Name | Target Entity Name | Source Join Column | Target Join Column | Source Join Column Filter | Target Join Column Filter | Source File Name | Target File Name |
|---|---|---|---|---|---|---|---|
| Material Actual | Material Definition | ID | ID | ProductionDetails | ProductionDetails | ||
| Material Actual | Material Definition | ID | ID | Consumption | Consumption | ||
| Material Actual | Material Definition | Product | Product | Scrap | ProductionDetails |
Also, we want to map, for each Material Actual, a material lot.
Material Actual to Material Lot Relationship
| Source Entity Name | Target Entity Name | Source Join Column | Target Join Column | Source Join Column Filter | Target Join Column Filter | Source File Name | Target File Name |
|---|---|---|---|---|---|---|---|
| Material Actual | Material Lot | ID | ID | ProductionDetails | ProductionDetails | ||
| Material Actual | Material Lot | ID | ID | Scrap | Scrap |
To finish the first scenario, we're still missing one relationship that isn't represented in the previous diagrams—that is, the Material Lot to Material Definition relationship:
Material Lot to Material Definition Relationship
| Source Entity Name | Target Entity Name | Source Join Column | Target Join Column | Source Join Column Filter | Target Join Column Filter | Source File Name | Target File Name |
|---|---|---|---|---|---|---|---|
| Material Lot | Material Definition | ID | ID | ProductionDetails | ProductionDetails | ||
| Material Lot | Material Definition | ID | ID | Scrap | Scrap |
Scenario 2: Quality Results
For this scenario, we use the Operations Test model.
Note
Tracking scrap results is, of course, also quality-related, but that is already covered in the previous scenario; in this Scenario 2, we are more interested in the qualitative quality results rather than quantitative.
In this case, we only look at one relationship: Test Result to Testable Object.
Note
A Testable Object in the Operations Test model is any item (material, equipment, or product) that undergoes testing to ensure it meets quality standards. It serves as the subject for which test definitions, procedures, and results are applied and recorded.
In our scenario, we're performing quality checks at the end of the production line, where materials are already produced and have a material lot associated; therefore, our Testable Object is going to be the entity Material Lot.
Test Result to Material Lot Relationship
| Source Entity Name | Target Entity Name | Source Join Column | Target Join Column | Source Join Column Filter | Target Join Column Filter | Source File Name | Target File Name |
|---|---|---|---|---|---|---|---|
| Test Result | Material Lot | ID | ID | Scrap | Scrap |
Scenario 3: Downtime
For downtime events, we rely on the Operations Event model.
Let's look at logic of the relationships. The Downtime events (Operations Events) are linked to equipment through Hierarchy Scope. At the same time, those events have associated properties (Operations Event Property), like, in this case, the duration of the downtime.
Operations Event to Hierarchy Scope
| Source Entity Name | Target Entity Name | Source Join Column | Target Join Column | Source Join Column Filter | Target Join Column Filter | Source File Name | Target File Name |
|---|---|---|---|---|---|---|---|
| Operations Event | Hierarchy Scope | WorkCenter | WorkCenter | equipmentLevel = 'Work Cell' | Downtime | ProductionDetails |
Operations Event to Operations Event Property
| Source Entity Name | Target Entity Name | Source Join Column | Target Join Column | Source Join Column Filter | Target Join Column Filter | Source File Name | Target File Name |
|---|---|---|---|---|---|---|---|
| Operations Event | Operations Event Property | ID | ID | Downtime | Downtime |
Scenario 4: Telemetry
As explained in the entity mapping phase, we're defining telemetry data as Equipment Properties associated to Equipment; let's define this relationship:
Equipment to Equipment Property
| Source Entity Name | Target Entity Name | Source Join Column | Target Join Column | Source Join Column Filter | Target Join Column Filter | Source File Name | Target File Name |
|---|---|---|---|---|---|---|---|
| Equipment | Equipment Property | WorkCenter | SPLIT(Tag, '_', 0) | ProductionDetails | Telemetry |
Note
In Equipment, coming from production-details.csv, the machine names are stored in the WorkCenter attribute. While Equipment Property, coming from Telemetry.csv contains the name of the machine in a longer string (like BREADBLAST04_Temperature), which is a common name for a tag in OPC UA, for instance. So we need to extract the name of the machine from that tag; that is why we use SPLIT(Tag, '_', 0).