I'm working on a shop floor Equipment Data Collection project which aims to analyze production orders historically and with real-time data (HMI close to the operator).

Actual database status:

Data is extracted from different equipment (with different protocols) and placed in an SQL server with the following structure:

• PROCESS table: As the main table, whenever a batch (production unit) is started, a ProcessID is created as well as varied information:

  ProcessID	Room	EquipmentID	BatchID	Program	Operator	Start	End
  209486	Room1	1010	985985	RecipeA	Jim	2022.04.05 13:58:02	2022.04.05 15:58:02

• Equipment family table: For each equipment family (mixers, ovens etc.) a table is created in which its sensor values (humidity, temperature, speed etc.) are collected every 5 seconds. Here is an example the BatchID above, where ProcessID = Mix ID on the equipment family table - dbo.Mixer :

  MixID	EquipmentID	Humidity	Temperature	Speed	DateTime
  209486	1010	2.5	70	250	2022.04.05 13:58:02
  209486	1010	2.6	73	215	2022.04.05 13:58:07
  ....	....	....	....	....	....

So, the database is structured with a main PROCESS table and several equipment family tables that are being created during the project development (dbo.mixer, dbo.oven etc). have the following data flow: SQLServer(source) - RDS Server - Power BI.

Problems of actual status & doubts

With the development of the project, 2 problems arise:

1. MANUAL WORK: Insertion, in the source, of new tables and columns (in existing tables) causes the need of manual alteration in the RDS server and in Power BI. Every time a new equipment communication is developed and is a new equipment family, a new table is created or if we need to introduce a new sensor in an existing table since the sensors are headers of the table.

2. Real-time data The actual architecture makes it difficult to implement real time dashboarding.

With these two big problems we are currently analyzing that the new system architecture should be: SQLServer(source) - DataLake - Snowflake(DataWarehouse) - Power BI (or any application). However, this won't solve the manual work defined in 1). For this problem we are looking to restructure the source to just 2 tables: Process (equal) and Sensors table(new). This new table would be a narrow big big big table with billions of timestamps of all the different equipment sensors (over 60 equipment), structured as follows: . dbo.Sensors:

with a corresponding Sensor Dimension Table (could be created at DataWarehouse) :

So, would it be a better way to restructure source and create this giant tall table rather than continuing current structure? At least it will solve the problem of new table or new columns input. On the other hand, the size of this table will be enormous given that more and more equipment and more sensors are continually being inserted.

Hoping someone might point us in the right direction.