Fact table design for building monitoring application
2 posters
Page 1 of 1
Fact table design for building monitoring application
I am monitoring the performance of several buildings across the country. Each building contains a datalogger that records hourly data from a variety of sensors. Although 50-100 measurements of 10-15 different types are actually being made in each building, let's assume that the same 6 measurements are made in each building and that the only measurement types are volume, electrical energy, temperature, and pressure. Just to be clear, the "1 hour" volume and electrical energy measurements represent the total volume/energy "flow" over the past hour. On the other hand, the "1 hour" temperature and pressure measurements represent the average temp/pressure over the past hour.
To summarize, let's assume the following 6 "measures" are being recorded every hour in each building:
1) total cold water volume (liters) through a pipe over the past hour
2) total air volume (liters) through a duct over the past hour
3) total electrical energy consumed (watt hours) by the lighting circuit over the past hour
4) total electrical energy consumed (watt hours) by the appliance circuit over the past hour
5) average basement temperature (Celcius) over the past hour
6) average A/C duct pressure (pascals) over the past hour
I'm trying to bring all the data from all the buildings together in a SQL Server relational data warehouse and a SSAS OLAP database for further analysis in Excel and SSRS. However, I'm having difficulty identifying the proper grain and therefore, the proper fact table design. I've tried to draw analogies to financials since all book examples are given in terms of financials, but that seems to have confused me further. While the temperature and pressure examples seem to be period snapshots, it isn't clear to me whether the energy and flow examples are transactions, periodic snapshot, accumulating snapshots, or something else.
Here are some design options that I've explored, although each seems to have its own apparent pitfalls:
Option 1: Create one fact table that includes all measurements that CAN be summed [e.g., volume (liters) and energy (watt hours)] and a second fact table for all measurements that CANNOT be summed [e.g., temperature (Celcius) and pressure (pascals)].
Option 2: Create a different fact table for each distince unit of measure. In other words, one fact table for volume, a second for energy, a third for temperature, and a fourth for pressure.
Option 3: Create a single fact table that includes all measurements of all units, whether or not they can be summed.
Option 4: Before creating the fact table, convert all measurements that can be summed to units that cannot be summed. For example, if the measurement frequency is one hour, convert volume to liters per hour and convert energy to watt hours per hour (or watts).
I'd very much appreciate someone's advice or suggestions. Thank you.
To summarize, let's assume the following 6 "measures" are being recorded every hour in each building:
1) total cold water volume (liters) through a pipe over the past hour
2) total air volume (liters) through a duct over the past hour
3) total electrical energy consumed (watt hours) by the lighting circuit over the past hour
4) total electrical energy consumed (watt hours) by the appliance circuit over the past hour
5) average basement temperature (Celcius) over the past hour
6) average A/C duct pressure (pascals) over the past hour
I'm trying to bring all the data from all the buildings together in a SQL Server relational data warehouse and a SSAS OLAP database for further analysis in Excel and SSRS. However, I'm having difficulty identifying the proper grain and therefore, the proper fact table design. I've tried to draw analogies to financials since all book examples are given in terms of financials, but that seems to have confused me further. While the temperature and pressure examples seem to be period snapshots, it isn't clear to me whether the energy and flow examples are transactions, periodic snapshot, accumulating snapshots, or something else.
Here are some design options that I've explored, although each seems to have its own apparent pitfalls:
Option 1: Create one fact table that includes all measurements that CAN be summed [e.g., volume (liters) and energy (watt hours)] and a second fact table for all measurements that CANNOT be summed [e.g., temperature (Celcius) and pressure (pascals)].
Option 2: Create a different fact table for each distince unit of measure. In other words, one fact table for volume, a second for energy, a third for temperature, and a fourth for pressure.
Option 3: Create a single fact table that includes all measurements of all units, whether or not they can be summed.
Option 4: Before creating the fact table, convert all measurements that can be summed to units that cannot be summed. For example, if the measurement frequency is one hour, convert volume to liters per hour and convert energy to watt hours per hour (or watts).
I'd very much appreciate someone's advice or suggestions. Thank you.
mwassmer- Posts : 4
Join date : 2012-08-01
Re: Fact table design for building monitoring application
You always want to capture facts at the lowest level of detail possible. What that means depends on how the business records and sends these readings.
If each reading (such as temperature) is independent of other readings, then you need to collect it as individual readings with the type of reading/measure as a dimension. You could then create aggregates, to provide a more conventional view, based on business rules and requirements.
If however, you receive multiple readings in a single transaction, then you would want to create columns for each reading and store one row. If different sources provide different reading measures and they are widely divergent, you may consider different fact tables for each kind of source.
If each reading (such as temperature) is independent of other readings, then you need to collect it as individual readings with the type of reading/measure as a dimension. You could then create aggregates, to provide a more conventional view, based on business rules and requirements.
If however, you receive multiple readings in a single transaction, then you would want to create columns for each reading and store one row. If different sources provide different reading measures and they are widely divergent, you may consider different fact tables for each kind of source.
Re: Fact table design for building monitoring application
Yes, each reading is independent of the other readings. If I'm understanding correctly, you are recommending that I put my values for measurements with different units in the same column. In other words, the "value" column for Row could be 67 degrees F, Row 2 could be 2.44 liters, Row 3 could be 32.53 Watt hours, etc. Is that correct?
I've never seen this in an example because each column is always money or count. I've never seen units mixed together in a single column before.
I've never seen this in an example because each column is always money or count. I've never seen units mixed together in a single column before.
mwassmer- Posts : 4
Join date : 2012-08-01
Similar topics
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Building a Recruitment and Admissions fact table
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» Fact Table Design
» Fact table design
» Building a Recruitment and Admissions fact table
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» Fact Table Design
» Fact table design
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum