semi-additive facts
3 posters
Page 1 of 1
semi-additive facts
I'm working a multi-dimensional model for energy savings that occur when a user makes a change to their home (such as getting a new shower head or replacing an appliance). The granularity of my fact table is changes incurred by fuel type (i.e. gas, water,electric) for the home change . One of my facts is savings in units of energy: "energysavings". However, this fact is semi-additive, because it really makes no sense unless you are dimensioning the data by fuel. So, if you are dimensioning by customer, it does not make sense to add energy savings for kilowatts and gallons of water together. The other fact is "dollarsavings" and that is fine across all dimensions, just not sure how to deal with the semi-additive fact.
sample fact table
fuelkey
energysavings
dollarsavings
sample dimension (fuel)
fuelkey
gas/water/electric/oil
I apologize in advance if my explanation is not clear enough. Please feel free to ask questions if this is not clear. Thanks for any advice you can offer
sample fact table
fuelkey
energysavings
dollarsavings
sample dimension (fuel)
fuelkey
gas/water/electric/oil
I apologize in advance if my explanation is not clear enough. Please feel free to ask questions if this is not clear. Thanks for any advice you can offer
lFarrington- Posts : 1
Join date : 2014-01-22
Re: semi-additive facts
It is a matter of user education more than anything else. They need to know if they are using energy savings, the must include the fuel type.
There are other methods used in other industries to work around this particular problem. For example, in consumer packaged goods manufacturing they introduce the notion of a 'standard carton' and provide conversion factors for each product. They then store both the raw quantity and the standard carton quantity and use the latter for cross product volume comparisons. I would imagine you can do something similar with energy (joules or BTU for example). With water, probably not.
There are other methods used in other industries to work around this particular problem. For example, in consumer packaged goods manufacturing they introduce the notion of a 'standard carton' and provide conversion factors for each product. They then store both the raw quantity and the standard carton quantity and use the latter for cross product volume comparisons. I would imagine you can do something similar with energy (joules or BTU for example). With water, probably not.
Re: semi-additive facts
I have been approached with a similar fact. The dollarSavings is fine and works great, but I split out each EnergySavings type into individual fields in the fact. The reporting wants to see the totals for all 3 (Gas, Water, Electric) listed separately. As in, we helped our customers save (4010 THM, 3000 gallons, and 3500 KWH) in Dec 2013 vs Nov 2013 thru our energySavings initiative. I have yet to be asked if these values can be combined in any way.
TheNJDevil- Posts : 68
Join date : 2011-03-01
Similar topics
» How best to model Timesheet facts against Sales Order facts
» Modeling Help: Semi-Additive Measures?
» Non additive facts in fact table
» non-additive facts in a fact table?
» Non additive and additive measures
» Modeling Help: Semi-Additive Measures?
» Non additive facts in fact table
» non-additive facts in a fact table?
» Non additive and additive measures
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum