Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Simple Solution for Metrics Targets on MSSQL

DZone's Guide to

Simple Solution for Metrics Targets on MSSQL

Check out an example about a database that tracks how many steps you take per day and see how to use a metrics database to keep up with your target steps.

· Database Zone
Free Resource

Learn how to create flexible schemas in a relational database using SQL for JSON.

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

Creating and Designing a Metrics Database

We start with creating a database with a 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 the daily target. Later, we may want to raise the number to 7 km., for example. These “long-term” target values are held in the MetricTargets table.

We need also one-time target values that can be assigned for measurements made on some concrete date. Suppose we have a walking distance target set to 7 km. by default. Now comes a nice and sunny Saturday and we decide to go to hiking. We know that we make an 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 the Measurements table.

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

Tables of metrics 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. It’s time to get deeper.

Finding Target for Date

We have target values in the MetricTargets table and we would like to get the target value for a given date when displaying data. For this, we write a 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 the target value to the Measurements table.

Getting Targets to Measurements Table

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

ALTER TABLE Measurements ADD [Target] AS  
(coalesce([TargetForDate],[dbo].[GetTargetForDate]([MetricId],[Date])))

Now, we have the correct targets available in the Measurements table. 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:

  1. From April 1, the target is 3 km.
  2. From June 1, 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.

Measurements table with target values

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

After attaching Excel to our Measurements table, it is easy to get a chart like this with distances and targets:

Measurements on Excel chart

Blue columns are Distances and the orange line is the Targets line. We can also use a column for Targets but it doesn’t visualize it as well as the line does.

Wrapping Up

Our goal was to come up with a solution for measurements and their target values. With a little tricking around 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 the same functionality without a computed column, as we can use views or plain SQL to mimic the Targets column in the Measurement table. Anyway, our solution is simple and flexible. It is also easy to get the Measurements table to Excel and other applications to visualize our data.

Create flexible schemas using dynamic columns for semi-structured data. Learn how.

Topics:
database ,mssql ,tutorial ,metrics ,sql server

Published at DZone with permission of Gunnar Peipman, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}