0

I need to store binary data - online status (1,0 or NULL) of about 100k sensors. Data are refreshed every 1 hour and I need to store historical data for 30 days, which gives me 720 records per sensor. Sensor number may vary, some new sensors comes in, some old one are deleted.

I've came with a solution of creating a table with some IDs and 720 BIN columns. I write current data to next column (modulus 720) evey 1 hour. I'm storing current column number in another configuration table. And now querying for data for last 7 days is a nightmare. I did a procedure to calculate all the offsets and generate some dynamic SQL to return what I want, but the thing is now even more complicated, because I need to filter those data ie. by sensor group (joined from another table). I'd like to implement it on a power BI view but it doesn't allow to dynamically filter data retrieved by a storred procedures.

And the question is how to store this kind of data to have easy access to them with views?

2
  • some IDs and 720 BIN columns yikes! How about having a field that tells you the time of measurement for each sensor instead of 720 columns? Jul 27 at 12:57
  • Please construct your questions based on the details in minimal reproducible example. Adding table DDL statements, sample DML statements, and providing desired output via a markup table as shown in the example makes it easier for us to provide great answers.
    – Hannah Vernon
    Jul 28 at 1:23

1 Answer 1

2

how to store this kind of data

In a relational database you use multiple rows, not columns.

eg:

create table SensorStatus
(
   SensorId int not null,
   StatusTime datetime2(0) not null,
   Status bit null,
   primary key (SensorId, StatusTime)
)

Or you could store one row per (day,sensor) and store the status by hour as a bitmask in an int, or as 24 bit columns.

   create table SensorStatus
    (
       SensorId int not null,
       StatusDate date not null,
       StatusByHour int not null default 0x00000000, 
       primary key (SensorId, StatusDate)
    )

or

create table SensorStatus
(
   SensorId int not null,
   StatusDate date not null,
   StatusHour0 bit not null default 0,
   StatusHour1 bit not null default 0,
   ...
   StatusHour23 bit not null default 0,
   primary key (SensorId, StatusDate)
)
1
  • @user13189993 a little unclear how much total data we're talking here. In your post you said "about 100k sensors. Data are refreshed every 1 hour and I need to store historical data for 30 days, which gives me 720 records per sensor" which I took to mean 30 days of data is 720 records * 100k sensors, yielding 72M rows total. If that's correct, 72M rows is not a lot of data. Theoretically there's no amount of data that's concerning as long as your tables are indexed and architected properly. I've worked with tables in the 10s of billions. You shouldn't worry about performance before testing.
    – J.D.
    Jul 27 at 21:45

Not the answer you're looking for? Browse other questions tagged or ask your own question.