What would be the best way to store one valuer per hour per day per customer?

Question

We have a table that stores a value per each hour per each per day per customer. A simplification of the table would be like this (removed some extra fields and the indexes):

CREATE table #timevalues (
dateinsert date,
customerid int,
hour0 int,
hour1 int,
....
hour23 int
)

The problem is that this table is huge, and altough we have an index with the dateinsert and customerid that allows us for a fast seek of the record, retrieving the record also takes time. Our issue is more with the speed and being future proof than with the space. What would be a recomendation to improve this? We were thinking about replacing the column format to store a json instead of the fixed columns, but the result would be less optimal I guess. We regularly need to get stuff like:

• Daily totals customer XXX in last month
• Average per hour per day

Answer 1

If the values don't change too often, I would use and Slowly Changing Dimension (Type 2) approach. In this case, you will store a new row only when your value changes. This is mostly common used on Data Warehouses to track historical data.

In a nutshell:

1) Change your table to something like this:

CREATE TABLE dbo.CustomerHistory (
CustomerHistoryKey int identity (1,1),  -- PK and surrogate key
CustomerId int NOT NULL,
Value int,
StartDate datetime NOT NULL,
EndDate datetime NOT NULL 
)

ALTER TABLE dbo.CustomerHistory ADD  CONSTRAINT PK_CustomerHistory  PRIMARY KEY CLUSTERED 
(CustomerHistoryKey)

CREATE UNIQUE NONCLUSTERED INDEX [BK_CustomerHistory] ON dbo.CustomerHistory 
(CustomerId, StartDate)

CREATE UNIQUE NONCLUSTERED INDEX [IX_CustomerHistory_currentRow] ON dbo.CustomerHistory 
(CustomerId, EndDate)
WHERE EndDate = CAST('99990101' AS datetime)

• Avoid using nulls on StartDate and EndDate. Instead, choose a lower and higher date value, such as 19000101 and 99990101

2) Continue running your routine every hour to check if Value was changed, but only add a new row for those customers with changes. To add a new row, do:

• DECLARE @ChangeDate datetime = GETDATE()

• Set the current row EndDate equals to @ChangeDate

• Add a new row with the updated Value with StartDate equals to @ChangeDate and EndDate equals to '99990101'

How it works:

• You will store much less data if your values doesn't change to often

• If you need to create a relationship pointing to a specific point in time, use CustomerHistoryKey

• If you need to check one value in time, you can search by CustomerId and time >= StartDate and time < EndDate

Check the following link for an quick overview: https://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2:_add_new_row

Answer 2

Normalizing your table would be an improvement:

CREATE table #timevalues (
  date_hour datetime2,
  customerid int,
  hour_val int
)

It would allow to simplify your code too. Compare your formula for the average per hour for the previous week to this:

SELECT datepart(day,date_hour) d,
       datepart(hour,date_hour) h,
       avg(hour_val) a
  from #timevalues t
  where date_hour between dateadd(day, DATEDIFF(day,0,GETDATE()), -7)
                      and dateadd(day, DATEDIFF(day,0,GETDATE()), 0)
  group by datepart(day,date_hour), datepart(hour,date_hour)
  order by datepart(day,date_hour), datepart(hour,date_hour);