Difference between revisions of "Analytics Data Model"

From Datonis
Jump to: navigation, search
(→‎Data Model)
 
(26 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
== Data Model ==
 
== Data Model ==
−
The Data Model is divided into 3 sections:
+
The Analytics Data Model is divided into 4 sections:
−
# '''Productivity Data Model'''
+
# '''Productivity by Shift Data Model'''
−
# '''CBM Data Model'''
+
# '''Part Data by Shift Data Model'''
−
# '''Quality Data Model'''
+
# '''Productivity by Hour Data Model'''
 +
# '''Checklists Data Model'''
  
−
=== Productivity Data Model ===
+
=== '''Productivity by Shift Data Model''' ===
−
MINT Productivity data, that is data about performance, is available across two kinds of time dimensions. Data that is rolled up at the shift level for shift level productivity analysis and at the hour level for more granular hourly trends.
+
MInt Productivity data, that is data about performance. This data is rolled up at the shift level for shift level productivity analysis.
  
−
==== '''Productivity data at a shift level''' ====
 
 
Here is a diagram that represents the data model. 
 
Here is a diagram that represents the data model. 
 +
[[File:MINT Analytics Data Model - ProductivityByShift.png|none|thumb|500x500px|MINT Analytics Data Model - WorkCenterShiftFact]]
 +
The main tables are:
  
−
[[File:Workcenter Shift Tables.png|600x600px]]
+
'''ProductivityByShift'':''''' Productivity data is centred around a 'workcenter' (essentially a machine that is performing the task). This table contains the primary shift-level productivity parameters.
  
−
The main tables are 
+
'''DowntimeByShift''': Downtime represents the time duration for which the machine was non-operational. This table contains information about durations and reasons for the downtimes of a workcenter for a given shift.
  
−
'''Workcentershiftfact:''' <Please work with Amit on the naming convention>
+
'''RejectionsByShift''': Rejections are the quality defects that occurred on a workcenter. The table contains information about these quality rejections for a workcenter for a shift and the reasons for it.
  
−
All productivity data is centered around a workcenter which is a machine that is performing the task. This table contains the primary shift level productivity parameters.
+
'''OperatorsByShift''': This table contains information about the operators working on the workcenter for a given shift.
  
−
<Link to detailed documentation where you should describe every column and the information it contains for all tables. Use this as an opportunity to rename columns that sound confusing>
+
'''ProductivityDowntimeByShift''': Performance loss is the metric to highlight the less-than-ideal rate at which the machine was working. If the machine is working slower than the expected (ideal) rate, the user can book a performance loss and associate a reason for it. This table contains the performance loss downtime information.
  
−
'''Workcentershiftdowntimereasonsfact:''' Downtimes represent durations when the machine was non-operational. This table contains information about the downtimes for a workcenter for a shift and the reasons for it if that is available
+
'''ProductionParametersByShift''': The table contains Production parameter data at a shift level. Production parameters are used to store the production quantity in different units based on customer requirements.
  
−
And so on.
+
e.g. Some customers want to measure production quantity in Batch Counts but for their internal assessment, they need Batch Weight. In such cases, MInt will show Batch Count and Batch Weight will be sent to their internal system (say the customer's ERP system).
  
−
Followed by examples of how you will use these tables to build actual reports. 
+
'''UtilitiesByShift:'''
  
−
Here is an example of how you can use the data model to analyse workcenter performance across shifts.
+
Please refer to the following link for [[Data Model Column Descriptions|description of each table column]].
  
−
<Show an actual working example>
 
  
−
Here is an example of how you can use the data model to analyse workcenter performance for a day.
+
'''Examples of using the object model to create a chart:'''
  
−
<Show an actual working example>
+
Here is an example video of how you can use the data model to analyze [[Create your own custom dashboards|average OEE by Workcenters]].
  
−
==== '''Productivity data at hour level''' ====
+
{{#ev:youtube|https://youtu.be/n-W1frXyaJg}}
  
−
1) '''Slot_data''': This contains the production timeline data which includes the uptime, downtime, part booking, downtime reason bookings, etc which is used to compute operational dashboards. This can be used to create slot level reports for e.g. performance per Part per Slot or Part changeover analysis.
 
  
−
2) '''Faas_data''': This contains data rolled up at a greater granularity i.e. Shift or Day level and Part and Operation level. This can be considered as analytics data and most of the multi-day reports in the current MINT application run on this source.
 
  
−
3) '''CBM/Quality/Production data''': This contains hourly statistical data like Cp/CpK, min, avg, max etc. which are essential for condition-based monitoring for the selected parameters. This can be used to create hour level reports like First -our output, Points out of limits per hour trend, Hourly Part Energy, etc.
+
=== '''Part Data by Shift Data Model''' ===
 +
MInt Part Productivity data gives insights about your part level performance. This data is rolled up at the shift level for doing shift level part productivity analysis.
  
−
4) '''Traced Object''': This data model of a traced object is completely use case-specific and it only has a few common fields like: machine_key, part_key, workorder_key, from, to,etc. There are currently no roll -ps happening on this data. This is a very custom data model and is currently '''out of the cope''' of the star schema model proposed below as we attempt to define a generic data model that is applicable to most of the MINT user data.
+
Here is a diagram that represents the data model.
 +
[[File:MINT Analytics Data Model - PartDataByShift.png|none|thumb|500x500px|MINT Analytics Data Model - PartDataByShift]]
 +
The main tables are:
  
−
== Star Schema of the MINT Analytics Data Model ==
+
'''PartDataByShift:''' The Part Productivity data is centred around a part that is being produced on the workcenter. This table contains the primary shift level part productivity parameters.
  
−
=== '''Faas Data''' ===
+
'''PartDowntimeReasonsByShift:''' Downtime represents the time duration for which the machine was non-operational. This table contains information about the downtimes that occurred throughout the duration for which a part was booked within a shift and the reasons for those downtimes.
−
This is further divided into 2 models:
 
  
−
==== '''WorkcenterShiftFact''' ====
+
'''PartRejectionsByShift:''' Rejections are the quality defects that occurred on a workcenter. The table contains information about these quality rejections for a part for a shift and the reasons for it.
−
This model is used to create most of the Productivity related KPIs and Reports in existing MINT. The main dimensions (groupby) of this model are at Workenter, Shift and Working day (Calendar Date).
 
  
−
'''Note -''' Any categorical column can be used as a dimension.
+
'''PartProductionParametersByShift:''' The table contains Production parameter data at a part and shift level. Production parameters are used to store the production quantity in different units based on customer requirements.
  
−
The proposed star schema has 6 main fact tables:
+
e.g. Some customers want to measure production quantity in Batch Counts but for their internal assessment, they need Batch Weight. In such cases, MInt will show Batch Count and Batch Weight will be sent to their internal system (say the customer's ERP system).
  
−
● '''WorkcenterShiftFact:''' Captures all the productivity parameters that can be used to calculate OEE, availability, performance and energy consumption ,etc.
+
'''WorkordersByShift:'''
  
−
● '''WorkcenterShiftDownTimeReasonsFact''': Captures details of all the downtime incidents occurring on a workcenter on a given day.
 
  
−
● '''WorkcenterShiftRejectionsFact''': Captures details of all the rejections on a workcenter.
+
'''Examples of using the object model to create a chart:'''
  
−
● '''WorkcenterShiftOperatorsFact''': Captures operators who have worked on a workcenter on a given day.
+
Here is an example video of how you can use the data model to analyze Part Cycle Times by Shift.
  
−
● '''WorkcenterShiftProductivityDownTimeReasonsFact''': Captures details of all the downtime incidents occurring on a workcenter when it is running on a given day.
+
{{#ev:youtube|https://youtu.be/1V_oVQ-8Pfo}}
  
−
● '''WorkcenterShiftProductionParametersFact''': Captures details of all the production parameters produced on a workcenter when it's running on a given day.
 
  
−
This is the most insightful data model for overall productivity metrics and will let us create a lot of reports on a daily/weekly/monthly basis. This will also allow reports like QOQ comparison of cells/workcenters/shifts on productivity, quality and performance parameters.
 
  
−
'''Note -''' It is possible for the user to select any categorical column mentioned in the table as a groupby to the chart being created (e.g. machine_name, shift_name, category_code, etc).
+
=== '''Productivity by Hour Data Model''' ===
 +
MInt Hourly Data is aggregation of Condition Based Monitoring (CBM), Part Process Quality, and Performance. This data is rolled up at an hourly level.
  
−
Along with the groupby, the user will have to choose a numeric column from the table to view the details. This column will have an aggregation associated with it (e.g. if you select production_quantity; options like MIN, MAX, AVG, COUNT, etc will be shown) to be selected by the user.
+
Here is a diagram that represents the data model.
 +
[[File:MINT Analytics Data Model - ProductivityByHour.png|none|thumb|500x500px|MINT Analytics Data Model - ProductivityByHour]]
 +
The main tables are:
  
−
Additionally, the user can create custom metrics and/or KPIs to add business meaning to the charts (e.g. If the user wants to see specific energy consumption, he can create a metric by taking the ratio of electricity and production_quantity).
+
'''ProductivityByHour:''' Productivity data is centred around a 'workcenter' (essentially a machine that is performing the task). This table contains the primary hourly level productivity parameters.
  
−
==== '''WorkcenterPartRoutingShiftFact''' ====
+
'''CbmParametersByHour:''' Condition Based Monitoring (CBM) is the process of monitoring a key parameter in a workcenter (vibration, temperature etc.). This helps in locating a significant change which may be indicative of a developing fault in a machine. The table contains information about the CBM parameters, their control limits and details of violations.
−
While the model above in (a) only provides roll up to a machine level, this model adds 2 more dimensions to the above model namely: Part and Routing.
 
  
−
Part can be compared to an SKU, Batch, Product (depending on the nomenclature each industry uses).
+
'''ProcessParametersByHour:''' Process parameters are the parameters dependant on the part (temperature of Part-1, temperature of Part-2 etc.). This helps in identifying a significant change that may be indicative of developing a quality defect. The table contains information about the Process parameters, their control limits and details of violations.
  
−
Routing is a set of operations that a Part goes through in its lifecycle from Raw Material to Finished Good (e.g. In a bottling plant, each Part (bottle in this case) goes through Cleaning -> Filling -> Capping -> Sticker Printing -> Packing. These 5 are operations and the complete flow is called routing).
+
'''PartProcessOperationByHour:''' The table contains Part - Operation information for each part at an hourly level.
  
−
[[File:Workcenter Part Routing Shift Tables.png|600x600px]]
+
'''*WorkcenterPartHourlyProdParamsFact:''' The table contains production parameter data at a part and hourly level. Production parameters are used to store the production quantity in different units based on Customer requirements.
  
−
The fact tables in the star schema for this model are:
+
e.g. Some customers want to measure production quantity in Batch Counts but for their internal assessment, they need Batch Weight. In such cases, MInt will show Batch Count and Batch Weight will be sent to their internal system (say the customer's ERP system).
  
−
● '''WorkcenterPartRoutingDailyFact:''' Contains all the parameters (production, rejection, energy) for a part and routing for a day/shift on the machine on which it was produced.
+
'''UtilitiesByHour:'''
  
−
● '''WorkcenterPartRoutingDownTimeReasonsDailyFact:''' Contains all the downtime reason details for each row in the part routing model.
+
'''OperationEnergyByHour:'''
  
−
● '''WorkcenterPartRoutingRejectionsDailyFact:''' Contains all the rejection details for each row in the part routing model.
+
'''PartProductivityByHour:'''
  
−
This data model will allow for reports on part level analytics w.r.t. the machines on which they are produced some of which include performance monitoring, specific energy consumption, Part rejections etc.
+
 
 +
'''Examples of using the object model to create a chart:'''
 +
 
 +
Here is an example video of how you can use the data model to analyze CBM Parameter Violations by Hour.
 +
 
 +
{{#ev:youtube|https://youtu.be/E5tlytK0tjo}}
 +
 
 +
 
 +
 
 +
=== '''Checklists Data Model''' ===
 +
MInt Policy data gives information about your manual policy bookings. For e.g. Safety Checksheets, Part Dimension Checklists, etc. This data is then used for correlations with other process/ productivity parameters or to create dashboards from your manual booking data.
 +
 
 +
Here is a diagram that represents the data model.
 +
[[File:MINT Analytics Data Model - Checklists data.png|none|thumb|500x500px|MINT Analytics Data Model - Checklists data]]
 +
The main tables are:
 +
 
 +
'''Checklists:''' The table contains policy master data along with the schedule of the policy booking.
 +
 
 +
'''ChecklistBooking:''' This table has the actual manually entered booking data for e.g. part length values, checklist values etc.

Latest revision as of 06:12, 27 June 2021

Data Model

The Analytics Data Model is divided into 4 sections:

  1. Productivity by Shift Data Model
  2. Part Data by Shift Data Model
  3. Productivity by Hour Data Model
  4. Checklists Data Model

Productivity by Shift Data Model

MInt Productivity data, that is data about performance. This data is rolled up at the shift level for shift level productivity analysis.

Here is a diagram that represents the data model. 

MINT Analytics Data Model - WorkCenterShiftFact

The main tables are:

ProductivityByShift: Productivity data is centred around a 'workcenter' (essentially a machine that is performing the task). This table contains the primary shift-level productivity parameters.

DowntimeByShift: Downtime represents the time duration for which the machine was non-operational. This table contains information about durations and reasons for the downtimes of a workcenter for a given shift.

RejectionsByShift: Rejections are the quality defects that occurred on a workcenter. The table contains information about these quality rejections for a workcenter for a shift and the reasons for it.

OperatorsByShift: This table contains information about the operators working on the workcenter for a given shift.

ProductivityDowntimeByShift: Performance loss is the metric to highlight the less-than-ideal rate at which the machine was working. If the machine is working slower than the expected (ideal) rate, the user can book a performance loss and associate a reason for it. This table contains the performance loss downtime information.

ProductionParametersByShift: The table contains Production parameter data at a shift level. Production parameters are used to store the production quantity in different units based on customer requirements.

e.g. Some customers want to measure production quantity in Batch Counts but for their internal assessment, they need Batch Weight. In such cases, MInt will show Batch Count and Batch Weight will be sent to their internal system (say the customer's ERP system).

UtilitiesByShift:

Please refer to the following link for description of each table column.


Examples of using the object model to create a chart:

Here is an example video of how you can use the data model to analyze average OEE by Workcenters.


Part Data by Shift Data Model

MInt Part Productivity data gives insights about your part level performance. This data is rolled up at the shift level for doing shift level part productivity analysis.

Here is a diagram that represents the data model.

MINT Analytics Data Model - PartDataByShift

The main tables are:

PartDataByShift: The Part Productivity data is centred around a part that is being produced on the workcenter. This table contains the primary shift level part productivity parameters.

PartDowntimeReasonsByShift: Downtime represents the time duration for which the machine was non-operational. This table contains information about the downtimes that occurred throughout the duration for which a part was booked within a shift and the reasons for those downtimes.

PartRejectionsByShift: Rejections are the quality defects that occurred on a workcenter. The table contains information about these quality rejections for a part for a shift and the reasons for it.

PartProductionParametersByShift: The table contains Production parameter data at a part and shift level. Production parameters are used to store the production quantity in different units based on customer requirements.

e.g. Some customers want to measure production quantity in Batch Counts but for their internal assessment, they need Batch Weight. In such cases, MInt will show Batch Count and Batch Weight will be sent to their internal system (say the customer's ERP system).

WorkordersByShift:


Examples of using the object model to create a chart:

Here is an example video of how you can use the data model to analyze Part Cycle Times by Shift.


Productivity by Hour Data Model

MInt Hourly Data is aggregation of Condition Based Monitoring (CBM), Part Process Quality, and Performance. This data is rolled up at an hourly level.

Here is a diagram that represents the data model.

MINT Analytics Data Model - ProductivityByHour

The main tables are:

ProductivityByHour: Productivity data is centred around a 'workcenter' (essentially a machine that is performing the task). This table contains the primary hourly level productivity parameters.

CbmParametersByHour: Condition Based Monitoring (CBM) is the process of monitoring a key parameter in a workcenter (vibration, temperature etc.). This helps in locating a significant change which may be indicative of a developing fault in a machine. The table contains information about the CBM parameters, their control limits and details of violations.

ProcessParametersByHour: Process parameters are the parameters dependant on the part (temperature of Part-1, temperature of Part-2 etc.). This helps in identifying a significant change that may be indicative of developing a quality defect. The table contains information about the Process parameters, their control limits and details of violations.

PartProcessOperationByHour: The table contains Part - Operation information for each part at an hourly level.

*WorkcenterPartHourlyProdParamsFact: The table contains production parameter data at a part and hourly level. Production parameters are used to store the production quantity in different units based on Customer requirements.

e.g. Some customers want to measure production quantity in Batch Counts but for their internal assessment, they need Batch Weight. In such cases, MInt will show Batch Count and Batch Weight will be sent to their internal system (say the customer's ERP system).

UtilitiesByHour:

OperationEnergyByHour:

PartProductivityByHour:


Examples of using the object model to create a chart:

Here is an example video of how you can use the data model to analyze CBM Parameter Violations by Hour.


Checklists Data Model

MInt Policy data gives information about your manual policy bookings. For e.g. Safety Checksheets, Part Dimension Checklists, etc. This data is then used for correlations with other process/ productivity parameters or to create dashboards from your manual booking data.

Here is a diagram that represents the data model.

MINT Analytics Data Model - Checklists data

The main tables are:

Checklists: The table contains policy master data along with the schedule of the policy booking.

ChecklistBooking: This table has the actual manually entered booking data for e.g. part length values, checklist values etc.

Retrieved from "https://docs.datonis.io/mediawiki/index.php?title=Analytics_Data_Model&oldid=1522"