Simple solution for metrics targets on MSSQL

June 26, 2017December 14, 2019 Gunnar Peipman 585 Views 0 Comments

I needed flexible solution to keep measurements with target values in SQL Server database. After some playing with tables and functions I came out with simple, clean and flexible solution that fits also well for many other scenarios besides the one I had to support. This blog post summarizes my work and provides all SQL stuff needed to reproduce it.

Creating and designing metrics database

We start with creating database with minimal set of tables needed to track measurements:

Metrics – in this table we define metrics like weight, distance, speed or whatever you like to measure.
MetricTargets – table for target values,
Measurements – table with measurements and their one time target values.

Metrics may have target values that will be valid from given date. Suppose we are measuring the distance walked daily. For some time we may have 5 km as daily target. Later we may want to raise the number to 7 km, by example. These “long-term” target values are held in MetricTargets table.

We need also one-time target values that can be assigned for measurement made in some concrete date. Suppose we have walking distance target set to 7 km by default. Now comes nice and sunny Saturday and we decide to go to hiking. We know that we make additional effort on this day and hiking path is way longer than 7 km. We set our expectation for this day to, let’s say, 20 km. This is the one-time target we keep in Measurements table.

NB! Although my sample data is about walking distance the same solution can be used for other types of measurements too. It’s generic enough to support many other scenarios. I chose walking distance just because it is clear enough for everybody and it helps to explain one-time targets very well.

Here is the table structure of our database.

Here is the SQL-script to create the tables.

CREATE TABLE [dbo].[Metrics](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](25) NOT NULL,  CONSTRAINT [PK_Metrics] PRIMARY KEY CLUSTERED  (
	[Id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
  ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
CREATE TABLE [dbo].[MetricTargets](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[MetricId] [int] NOT NULL,
	[FromDate] [date] NOT NULL,
	[Value] [float] NOT NULL,  CONSTRAINT [PK_MetricTargets] PRIMARY KEY CLUSTERED  (
	[Id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
  ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[MetricTargets]  WITH CHECK ADD  CONSTRAINT [FK_MetricTargets_Metrics] 
FOREIGN KEY([MetricId]) REFERENCES [dbo].[Metrics] ([Id])
GO
 
ALTER TABLE [dbo].[MetricTargets] CHECK CONSTRAINT [FK_MetricTargets_Metrics]
GO
 
CREATE TABLE [dbo].[Measurements](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Date] [date] NOT NULL,
	[MetricId] [int] NOT NULL,
	[Value] [float] NOT NULL,
	[TargetForDate] [float] NULL,  CONSTRAINT [PK_Measurements] PRIMARY KEY CLUSTERED  (
	[Id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
  ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[Measurements]  WITH CHECK ADD  CONSTRAINT [FK_Measurements_Metrics] 
FOREIGN KEY([MetricId]) REFERENCES [dbo].[Metrics] ([Id])
GO
 
ALTER TABLE [dbo].[Measurements] CHECK CONSTRAINT [FK_Measurements_Metrics]
GO

Now we are done with tables and relations and it’s time to get deeper.

Finding target for date

We have target values in MetricTargets table and we would like to get target value for given date when displaying data. For this we write simple scalar-valued function.

CREATE FUNCTION [dbo].[GetTargetForDate]
(     @MetricId int,

    @Date date
)
RETURNS float
AS
BEGIN     DECLARE @Result float
 
	SET @Result = (
		SELECT TOP 1  Value
		FROM
			MetricTargets
		WHERE
			MetricId = @MetricId
			AND FromDate <= @Date
		ORDER BY
			FromDate DESC
	)
 
	RETURN @Result
END

With this function in place we can finish the task and bring target value to Measurements table.

Getting targets to measurements table

Target value for metric can be from MetricTargets table but it can also be one-time target inserted with measurement. To have target available in Measurements table we use computed column called Target. It returns either one-time target for given row or target from MetricTargets table.

ALTER TABLE Measurements ADD [Target] AS

(coalesce([TargetForDate],[dbo].[GetTargetForDate]([MetricId],[Date])))

Now we have correct targets available in measurements table and it’s time to try out how it works.

Trying out

Now let’s add some data to tables and see the measurements table. There is one metric called Distance and it has two target values:

From 1st of April the target is 3 km,
From 1st of June the target is 5 km.

The script adds also some measurements.

SET IDENTITY_INSERT [dbo].[Metrics] ON  GO
INSERT [dbo].[Metrics] ([Id], [Name]) VALUES (1, N'Distance')
GO
SET IDENTITY_INSERT [dbo].[Metrics] OFF
GO
SET IDENTITY_INSERT [dbo].[MetricTargets] ON  GO
INSERT [dbo].[MetricTargets] ([Id], [MetricId], [FromDate], [Value]) VALUES (1, 1, CAST(N'2017-04-01' AS Date), 3)
GO
INSERT [dbo].[MetricTargets] ([Id], [MetricId], [FromDate], [Value]) VALUES (2, 1, CAST(N'2017-06-01' AS Date), 5)
GO
SET IDENTITY_INSERT [dbo].[MetricTargets] OFF
GO
SET IDENTITY_INSERT [dbo].[Measurements] ON  GO
INSERT [dbo].[Measurements] ([Id], [Date], [MetricId], [Value], [TargetForDate]) VALUES (1, CAST(N'2017-05-01' AS Date), 1, 4.22, NULL)
GO
INSERT [dbo].[Measurements] ([Id], [Date], [MetricId], [Value], [TargetForDate]) VALUES (2, CAST(N'2017-05-02' AS Date), 1, 2.2, NULL)
GO
INSERT [dbo].[Measurements] ([Id], [Date], [MetricId], [Value], [TargetForDate]) VALUES (3, CAST(N'2017-05-03' AS Date), 1, 12, 10)
GO
INSERT [dbo].[Measurements] ([Id], [Date], [MetricId], [Value], [TargetForDate]) VALUES (4, CAST(N'2017-05-04' AS Date), 1, 3.8, NULL)
GO
INSERT [dbo].[Measurements] ([Id], [Date], [MetricId], [Value], [TargetForDate]) VALUES (5, CAST(N'2017-06-01' AS Date), 1, 4.2, NULL)
GO
INSERT [dbo].[Measurements] ([Id], [Date], [MetricId], [Value], [TargetForDate]) VALUES (7, CAST(N'2017-06-02' AS Date), 1, 3.7, NULL)
GO
INSERT [dbo].[Measurements] ([Id], [Date], [MetricId], [Value], [TargetForDate]) VALUES (8, CAST(N'2017-06-03' AS Date), 1, 6.2, NULL)
GO
SET IDENTITY_INSERT [dbo].[Measurements] OFF
GO

Now let’s open the metrics table and see what’s inside.

For measurements in May we have 3 km as target by default. On 3rd of May we had hiking day and we set target for this date to 10. The actual distance – we can be proud – was 12 km. Target column shows correctly 10 as target value. For June we have target for distance 3 km and it’s correctly shown in Target column.

After attaching Excel to our measurments table it is easy to get the chart like this with distances and targets.

Blue columns are distances and organge line is targets line. We can also use column for targets but it doesn’t visualize it so well as line does.

Wrapping up

Our goal was to come out with solution for measurements and their target values. With some little tricking with functions and computed columns we got the target values to our measurements table. With not much effort we are able to support targets based on start date and also one-time targets assigned to a measurement for given date. It’s not hard to have same functionality with out computed column as we can use views or plain SQL to mimic the targets column in measurement table. Anyway our solution is simple and flexible. It is also easy to get measurements table to Excel and other applications to visualize our data.