SQL Server IOT Database Design Example

CREATE DATABASE DataMartIOT
GO

ALTER DATABASE DataMartIOT SET RECOVERY SIMPLE
GO

CREATE SCHEMA Dim
GO
 
CREATE TABLE Dim.Sensors
(
Id smallint IDENTITY(-32768,1) NOT NULL, -- allows for total of 65536 sensors  
SensorName varchar(255) NOT NULL,
Unit varchar(255),
CONSTRAINT PK_Dim_Sensors_Id PRIMARY KEY (Id) WITH (FILLFACTOR = 100),
CONSTRAINT UX_Dim_Sensors_SensorName UNIQUE (SensorName)
)
GO

CREATE TABLE Dim.Groups
(
Id tinyint IDENTITY(0,1) NOT NULL, -- allows for total of 256 groups; scale up if needed
GroupName varchar(255) NOT NULL,
CONSTRAINT PK_Dim_Groups_Id PRIMARY KEY (Id) WITH (FILLFACTOR = 100),
CONSTRAINT UX_Dim_Groups_GroupName UNIQUE (GroupName)
)
GO

CREATE TABLE Dim.Locations
(
Id tinyint IDENTITY(0,1) NOT NULL, -- allows for total of 256 locations  ; scale up if needed
LocationName varchar(255) NOT NULL
CONSTRAINT PK_Dim_Locations_Id PRIMARY KEY (Id) WITH (FILLFACTOR = 100),
CONSTRAINT UX_Dim_Locations_LocationName UNIQUE (LocationName)
)
GO

CREATE TABLE Dim.Dates
(
Id smallint IDENTITY(-32768,1) NOT NULL, -- allows for total of 65536 records or almost 180 years
DateValue Date NOT NULL,
CONSTRAINT PK_Dim_Dates_Id PRIMARY KEY (Id) WITH (FILLFACTOR = 100),
CONSTRAINT UX_Dim_Dates_DateValue UNIQUE (DateValue)
)
GO
 
-- Populates Date Dimension with dates from 30 days back in time to almost 180 years in the future
DECLARE @loop_iterator AS int = -30 --starting 30 days back in time
DECLARE @date_var AS Date = DATEADD(DAY,@loop_iterator,GETDATE())
WHILE @loop_iterator < 65506 -- maximum for small int; change if using different datatype
BEGIN
   DECLARE @temp_date AS date = DATEADD(DAY,@loop_iterator,@date_var)
   INSERT INTO Dim.Dates VALUES(@temp_date);
   SET @loop_iterator = @loop_iterator + 1
END
GO

CREATE TABLE Dim.Times
(
Id time(0) NOT NULL, -- time(0) for a nearest second precision.
HourValue tinyint NOT NULL, --for drill down reports
MinValue tinyint NOT NULL, --for drill down reports
SecValue tinyint NOT NULL, --for drill down reports
--MSValue smallint NOT NULL, --uncomment this line if you changed the precision to milliseconds
CONSTRAINT PK_Dim_Times_Id PRIMARY KEY (Id) WITH (FILLFACTOR = 100),
CONSTRAINT UX_Dim_Times_Values UNIQUE (HourValue,MinValue,SecValue) –add MSValue column if in use
)
GO
 
--Populating Time Dimension with values
DECLARE @loop_iterator AS int = 0
DECLARE @time_var AS time(0) = N'00:00:00'
WHILE @loop_iterator < 86400
BEGIN
   DECLARE @temp_id AS time(0) = DATEADD(second,@loop_iterator,@time_var)
   INSERT INTO Dim.Times VALUES
(@temp_id,DATEPART(HOUR,@temp_id),DATEPART(MINUTE,@temp_id),DATEPART(SECOND,@temp_id));
   SET @loop_iterator = @loop_iterator + 1
END
GO

CREATE SCHEMA Fact
GO

CREATE TABLE Fact.Readings
(
DateId smallint NOT NULL, --Must be identical datatype to Date Dim
TimeId time(0) NOT NULL, -- Must be identical datatype to Time Dim
LocationId tinyint NOT NULL, -- Must be identical datatype to Location Dim
GroupId tinyint NOT NULL, -- Must be identical datatype to Group Dim
SensorId smallint NOT NULL, -- Must be identical datatype to Sensor Dim
SensorValue decimal(9,2), -- this will cover almost all types of numeric data
CONSTRAINT PK_Fact_Readings PRIMARY KEY (DateId, TimeId, LocationId, GroupId, SensorId),
CONSTRAINT FK_Fact_Readings_Dim_Date_Id FOREIGN KEY (DateId) REFERENCES Dim.Dates(Id),
CONSTRAINT FK_Fact_Readings_Dim_Time_Id FOREIGN KEY (TimeId) REFERENCES Dim.Times(Id),
CONSTRAINT FK_Fact_Readings_Dim_Sensors_Id FOREIGN KEY (SensorId) REFERENCES Dim.Sensors(Id),
CONSTRAINT FK_Fact_Readings_Dim_Groups_Id FOREIGN KEY (GroupId) REFERENCES Dim.Groups(Id),
CONSTRAINT FK_Fact_Readings_Dim_Locations_Id FOREIGN KEY (LocationId) REFERENCES Dim.Locations(Id)
)
GO 

ALTER TABLE Fact.readings
NOCHECK CONSTRAINT FK_Fact_Readings_Dim_Date_Id
GO
 
ALTER TABLE Fact.readings
NOCHECK CONSTRAINT FK_Fact_Readings_Dim_Time_Id
GO
 
ALTER TABLE Fact.Readings
NOCHECK CONSTRAINT FK_Fact_Readings_Dim_Sensors_Id
GO
 
ALTER TABLE Fact.Readings
NOCHECK CONSTRAINT FK_Fact_Readings_Dim_Groups_Id
GO
 
ALTER TABLE Fact.Readings
NOCHECK CONSTRAINT FK_Fact_Readings_Dim_Locations_Id
GO

SELECT DimDates.DateValue, FactReadings.Id, FactReadings.SensorValue
FROM   Dim.Sensors AS DimSensors
  JOIN Fact.Readings AS FactReadings ON DimSensors.Id = FactReadings.SensorId
  JOIN Dim.Dates AS DimDates ON DimDates.Id = FactReadings.DateId
  JOIN Dim.Times AS DimTimes ON DimTimes.Id = FactReadings.TimeId
  JOIN Dim.Groups AS DimGroups ON DimGroups.Id = FactReadings.GroupId
  JOIN Dim.Locations AS DimLocations ON DimLocations.Id = FactReadings.LocationId
WHERE DimDates.DateValue = 'Your Date Value'
  AND DimTimes.Id = 'Your Time Value'
  AND DimLocations.LocationName = N'Your Location Name'
  AND DimGroups.GroupName = N'Your Group Name'
  AND DimSensors.SensorName = N'Your Sensor Name'