utility metr reading

Hi Folks,

I am new to the forum. I have attended Dr. Kimball's DW class.

I am designing a dim model for meter reads. The meter reads data come in 3 flavors and 3 grains.
Flavors = register reads, intervals reads, default reads
Grains = monthly, 15 minutes intervals, daily

the question I have is do I need 3 dim tables and 3 facts tables or one dim with read_type column and 3 facts?
I would highly appreiate all inputs.

thanks,

What do you want to do with it? Track consumption?

You don't have three grains, just different context. Grain affect how the measures can be aggregated, if the read measure reflects the consumption since the last read, you do not have a grain problem. It's just like tracking ATM withdrawals. If one customer withdraws daily while another monthly, it doesn't matter, does it?

You may want to include another measure, interval since last read, if you need to calculate average consumption on a daily basis.

One fact table.

Hi,

Yes I am tracking usage. but for the same account in my fact table I will have transactional data (15 minutes interval)
and summary data (daily) and snapshot data monthly. Would not it be a vailoation of fact grain?. so for the same meter id
I will have all 3 combination of grains.

what about “ReadingType” column to denote the type of reading? should I include it in fact or dim? I thought it belonged in dim, but not sure.


thanks in advance,

Summaries and snapshots go into different fact tables. Reads (the transactions) go into a single fact regardless of frequency. When storing reads, store the net change from the previous read. Read type should go in a dimension somewhere...

Hi,

Thanks for the prompt response. I like to share with you what the interval file looks like as well as the register one for the same meter id on the same day.

interval
+++++
-
-
-


-


-


-


register read - once at the end of day
++++++++++++++++++++++++++++
-
-
-
-





-






-
-





-





please let me know if you still think they should go into the same Fact table.

Thanks in advance

The smallest transactional data should be stored. Snapshots can then be created from that transactional data. If you are getting 15 minute data reads for each meter, you are looking at 96 entries per day per meter. If you need a full day accumulation, you can add a row for each meter to the daily snapshot for each day based on the total in the transactional fact.

If your interval data is not reliable, then using it runs into many other issues. Example, if meter 123 sends:
Timestamp|Reading|MeterID
5/7/2012 15:00:01.342|12345.6|123
5/7/2012 15:15:00.628|12348.4|123
5/7/2012 15:45:04.545|12355.3|123

What happened to the 15:30 reading? Also, my example shows an actual timestamp of the read and the meter reading (not the difference since last good read). This is where it can get a little complicated. You have to handle missing reads, meter changes, ect. I guess it's just something else to consider.