Difference between revisions of "Analytics Data Model"

From Datonis
Jump to: navigation, search
Line 1: Line 1:
 
== Data Model ==
 
== Data Model ==
 
The Data Model is divided into 3 sections:
 
The Data Model is divided into 3 sections:
−
# '''Productivity Data Model'''
+
# '''Workcenter Shift Productivity Data Model'''
−
# '''CBM Data Model'''
+
# '''Part Shift Productivity Data Model'''
−
# '''Process Data Model'''
+
# '''Hourly Data Model'''
  
−
=== Productivity Data Model ===
+
=== Workcenter Shift Productivity 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, which 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:Workcenter Shift Tables.png|600x600px]]
+
[[File:MINT Analytics Data Model - WorkCenterShiftFact.jpg|600x600px]] 
  
 
The main tables are: 
 
The main tables are: 
  
−
''''''WorkcenterShiftFact:'''''' 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.
+
'''WorkcenterShiftFact'':''''' 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.
  
−
<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>
+
'''<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>'''
  
 
'''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.
 
'''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.
Line 24: Line 23:
  
 
'''WorkcenterShiftOperatorsFact''': This table contains information about the operators working on the workcenter for a given shift.
 
'''WorkcenterShiftOperatorsFact''': This table contains information about the operators working on the workcenter for a given shift.
 +
 +
'''CHECK'''
  
 
'''*WorkcenterShiftProductivityDownTimeReasonsFact''': Captures details of all the downtime incidents occurring on a workcenter when it is running on a given day.
 
'''*WorkcenterShiftProductivityDownTimeReasonsFact''': Captures details of all the downtime incidents occurring on a workcenter when it is running on a given day.
Line 32: Line 33:
 
# Here is an example of how you can use the data model to analyze [[Create your own custom dashboards|average OEE by Workcenters]].
 
# Here is an example of how you can use the data model to analyze [[Create your own custom dashboards|average OEE by Workcenters]].
  
−
==== '''Productivity data at the Hour level''' ====
+
=== ADD VIDEO HERE ===
−
Here is a diagram that represents the data model.
 
  
−
== Star Schema of the MINT Analytics Data Model ==
+
=== '''Part Shift Productivity Data Model''' ===
 +
MInt Part Productivity data, which is data about part level performance. This data is rolled up at the shift level for shift level part productivity analysis.
  
−
=== '''Faas Data''' ===
+
Here is a diagram that represents the data model.
−
This is further divided into 2 models:
 
−
 
 
−
==== '''WorkcenterShiftFact''' ====
 
−
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.
 
−
 
 
−
The proposed star schema has 6 main fact tables:
 
−
 
 
−
● '''WorkcenterShiftFact:''' Captures all the productivity parameters that can be used to calculate OEE, availability, performance and energy consumption ,etc.
 
−
 
 
−
● '''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.
 
−
 
 
−
● '''WorkcenterShiftOperatorsFact''': Captures operators who have worked on a workcenter on a given day.
 
−
 
 
−
● '''WorkcenterShiftProductivityDownTimeReasonsFact''': Captures details of all the downtime incidents occurring on a workcenter when it is running on a given day.
 
−
 
 
−
● '''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).
 
−
 
 
−
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.
 
−
 
 
−
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).
 
−
 
 
−
==== '''WorkcenterPartRoutingShiftFact''' ====
 
−
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).
 
  
−
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).
+
[[File:MINT Analytics Data Model - WorkcenterPartRoutingShiftFact.jpg|600x600px]]
  
−
[[File:Workcenter Part Routing Shift Tables.png|600x600px]]
+
The main tables are:
  
−
The fact tables in the star schema for this model are:
+
'''WorkcenterPartRoutingShiftFact:''' The Part Productivity data is centered around a Part that is being produced on the workcenter. This table contains the primary shift level part productivity parameters.
  
−
● '''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.
+
'''WorkcenterPartRoutingShiftDownTimeReasonsFact:''' Downtimes represent durations when the machine was non-operational. This table contains information about the downtimes for a Part (when the part was booked) for a shift and the reasons for it.
  
−
● '''WorkcenterPartRoutingDownTimeReasonsDailyFact:''' Contains all the downtime reason details for each row in the part routing model.
+
'''WorkcenterPartRoutingShiftRejectionsFact:''' Rejections are quality defects on a workcenter. The table contains information about these quality rejections for a Part for a shift and the reasons for it.
  
−
● '''WorkcenterPartRoutingRejectionsDailyFact:''' Contains all the rejection details for each row in the part routing model.
+
'''CHECK'''
  
−
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.
+
'''WorkcenterPartShiftProductionParametersFact:'''

Revision as of 06:47, 22 May 2020

Data Model

The Data Model is divided into 3 sections:

  1. Workcenter Shift Productivity Data Model
  2. Part Shift Productivity Data Model
  3. Hourly Data Model

Workcenter Shift Productivity Data Model

MInt Productivity data, which 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.jpg 

The main tables are: 

WorkcenterShiftFact: 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.

<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>

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.

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

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

CHECK

*WorkcenterShiftProductivityDownTimeReasonsFact: Captures details of all the downtime incidents occurring on a workcenter when it is running on a given day.

*WorkcenterShiftProductionParametersFact: Captures details of all the production parameters produced on a workcenter when it's running on a given day.

Examples of using the object model to create dashboards:

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

ADD VIDEO HERE

Part Shift Productivity Data Model

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

Here is a diagram that represents the data model.

MINT Analytics Data Model - WorkcenterPartRoutingShiftFact.jpg

The main tables are:

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

WorkcenterPartRoutingShiftDownTimeReasonsFact: Downtimes represent durations when the machine was non-operational. This table contains information about the downtimes for a Part (when the part was booked) for a shift and the reasons for it.

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

CHECK

WorkcenterPartShiftProductionParametersFact:

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