utility metr reading

View previous topic View next topic Go down

utility metr reading

Post  dim67 on Sat May 05, 2012 10:28 am

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,

dim67

Posts : 15
Join date : 2012-05-05

View user profile

Back to top Go down

Re: utility metr reading

Post  ngalemmo on Sun May 06, 2012 6:32 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: utility metr reading

Post  dim67 on Mon May 07, 2012 8:46 am

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,

dim67

Posts : 15
Join date : 2012-05-05

View user profile

Back to top Go down

Re: utility metr reading

Post  ngalemmo on Mon May 07, 2012 1:08 pm

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...
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: utility metr reading

Post  dim67 on Mon May 07, 2012 3:06 pm

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


dim67

Posts : 15
Join date : 2012-05-05

View user profile

Back to top Go down

Re: utility metr reading

Post  TheNJDevil on Mon May 07, 2012 5:57 pm

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.


Last edited by TheNJDevil on Mon May 07, 2012 5:58 pm; edited 1 time in total (Reason for editing : spelling)

TheNJDevil

Posts : 68
Join date : 2011-03-01

View user profile

Back to top Go down

Re: utility metr reading

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum