What would be the best way to store one valuer per hour per day per customer?
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 &gt;= StartDate and time &lt; EndDate


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