How to design a generic fact table?

Hi All,

I would like to keep information from a factory with ~1000 machines.
Moreover, there are different machine types. Let's assume I have 50 types of machine.
Each machine class "produces" a different set of measures (e.g. motor temperature) every 5 minutes.
I know I can create 50 fact tables and put the specific metrics there.
However, I need to simplify the model due to other reasons.
I mean I need to have only one fact table.
How can model it?

Thanks in advance,

LR

Creating a data model based on a required number of tables is not a good idea. Where is this concern coming from? Can it be addressed otherwise, e.g., a view that presents a simplified version, or a schema/namespace to avoid collision with other tables?

How do you intend to query the database given that the machines produce different measures? Would you restrict a result set by referring to a given type of measure, e.g. "select * from readings where machine_type = 'Drill' and RPM > 1000"? Or are you just looking to restrict by machine type and measurement interval and then dump all of the measurables into a text box in a UI? Do you care about all the measurements together from a single machine, or one measurement across all machines, e.g., "select temperature, machine_type from temperature_readings where date_key = ?"

If you're dead set on a single table, the solutions aren't pretty: a plain text column with all the measurables in a string; a serialized data structure like JSON; a column of type varchar array.

Identify the measures, Pick the grain, identify the dimensions. If the measures all share the same dimensions and grain, they can go into the same fact.

All the points in the previous replies are valid. The challenge is that you have many types of measures, which are usually modeled as separate fact table columns. However, let's take a stab at a single-fact table dimensional model and see what we come up with:

The fact table grain is declared "a measurement produced by a machine."

We need the following tables:

DimDate (DateKey, etc.)
DimTime (TimeKey, etc.) (1440 rows for minute resolution or 86400 rows for second resolution)
DimMachine (MachineKey, MachineType, Description, Brand, SerialNo, Building, Location, etc.)
DimMeasurementType (MeasurementTypeKey, UnitOfMeasure, Prescision)
FactMeasures (DateKey, TimeKey, MachineKey, MeasurementTypeKey, Value)

There is still the challenge of picking a suitable data type for Value for all measures. If they are all numeric, perhaps a suitably large decimal data type with enough places on both sides of the decimal would do the job.

Now this may appear to be a simple design, but there are some potential "gotchas":

1. You need to be careful not to aggregate Value across multiple measurement types, otherwise you get a meaningless result.

2. Some measurement types might aggregate as a SUM over time; others (such as temperature) might make more sense as an AVG.

3. Reporting on multiple measure types in the same report can be tricky, typically requiring multiple subqueries and then merging the results. The queries will be at least as complex as when using separate measures/fact tables, and it is much easier to get into trouble!