Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

weather detail

2 posters

Go down

weather detail Empty weather detail

Post  TheNJDevil Thu Aug 14, 2014 9:05 am

Background: I work for a utility that serves mostly rural areas in 7 states. We are to a point where having detailed weather history and forecast data to use for analysis would be helpful. We have a significant percentage of smart meters installed.

Normally I wouldn't blink an eye at importing hourly weather data, but that has always been for a specific project for a specific town. We are looking to consolidate those weather sources into the DW for everyone to use. Something like this:

FactWeather
DayKey
TimeofDayKey
WeatherStationKey
measurements...

dimWeatherStation
WeatherStationKey
Lat
Long
Altitude
WeatherStationCode
WeatherStationName
State
Town
TownAltName

I can think of 300 known weather stations we would use with the potential to be 3-4 times that based on requirement decisions. 30 yrs history for each weather station, plus going forward hourly data.

We will apply dimWeatherStation as a dimension to other facts that use location with an assortment of techniques to select which WS best applies (zip codes, Lat/Long, addresses).

Has anyone else had experience with weather detail? Am I heading in the correct direction? Thanks for any input.





TheNJDevil

Posts : 68
Join date : 2011-03-01

Back to top Go down

weather detail Empty Re: weather detail

Post  nick_white Fri Aug 15, 2014 2:52 am

Hi, sounds like an interesting project.

It would be useful to know what sorts of questions you will want to answer that includes weather information. I'm guessing you will combining this data with other facts e.g. what were the weather conditions when events of Type X occurred?

If this is what you are trying to do then I'd think carefully about how you will combine these datasets - especially regarding the time element. You say you will get hourly readings so if you get a reading @ 11am do you consider that these conditions applied at that location only at 11am or from 10:30am to 11:30am - or some other time period? How would you then align this time period with, presumably, the point in time for the event whose weather conditions you want to know about? Possibly you'd have a time dimension with an hour grain and add this to all relevant fact tables?
If you are trying to correlate events and weather conditions is there a time lag that might need to be identified and subsequently used in your model e.g. when the temperature is > 75 degrees there is a spike in the use of electricity as air conditioning units turn on. However if it takes a house an hour for its internal temperature to match the external temperature you might see the spike an hour after external temperature rose and then drop an hour after the temperature falls

From the perspective of designing your data model I'm not sure what the answer is but these are these sorts of things I'd consider - even if you start with a very simple model initially I'm guessing it could get evolve into something very complex, so you should probably have some thoughts about where you want to get to in the future and how you will get there - so your model can be extended to incorporate new elements rather than having to be re-designed

Regards,

nick_white

Posts : 364
Join date : 2014-01-06
Location : London

Back to top Go down

weather detail Empty Re: weather detail

Post  TheNJDevil Fri Aug 15, 2014 8:55 am

All great things to consider. I know from past weather data experience that as far as what would be considered point in time measurements, it's always the average for that hour. You can also get the highs and lows, for some (like temp and wind gusts).

I was also informed that we get a 6 day hourly forecast that needs to be included. Now they haven't come back with any answers to my numerous questions about that, but would that typically be included a separate fact table? So now I would be considering FactWeatherObserved and FactWeatherForecasted.

Thanks.

TheNJDevil

Posts : 68
Join date : 2011-03-01

Back to top Go down

weather detail Empty Re: weather detail

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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