Volume and Weight in Same Fact Table

[thekeel]

Is it appropriate to include two facts that are not of the same type? In my example, I may need to have both Volume (Gallons) and Weight (Pounds) in the same fact table. I am building a DW around products that we recycle, these products can be Oil or Tires for example.

Oil is measured in Gallons and Tires are measured in Pounds. We have been converting gallons to pounds and only storing pounds, but I was curious if just storing both facts in the fact table would be appropriate also.

The table would then look like this:

LocationID, DateID, RecyclingMethodID, ProductID, ServiceNumber, VolumeGallons, VolumePounds

Now with this setup, I will have 0's where Gallons or Pounds do not apply to the ProductID. Otherwise I would break this up into 2 fact tables (one for Volume and one for Pounds). Is any approach better than the other?

Thanks

[Jeff Smith]

You could build a measure dimension that described whether the measure was Gallons or pounds or a count. I think you want to avoid a fact table with different measures based on the unit of measure for a couple of reasons. One, is that it will result in empty space. Secondly, you have to worry about nulls or 0s in the one or the other measure. Image writing a query where you wanted to know all of the instances when the measure was either > 100 pounds or 100 Gallons. You'd have to include (or is not null) in the filter if you had nulls. And if you used 0s, imagine a query where you wanted the average size?

[thekeel]

Hi Jeff,

Here is one of the designs I came up with, which I think is similar to what you are recommending:



In this example I have a UnitMeasure table which stores measurement values such as Gallons, Pounds, QTY. My fact table just has one column titled "Amount" that will vary based on UnitMeasureID.

If the UnitMeasureID was referencing Gallons, it would have the gallon amount, same with pounds, and QTY.

This will create a significant amount of additional rows as I need to measure Pounds for everything (oil, tires, organics), Gallons for a subset of products (oil), and QTY for Tires only. Will this save space vs columns with empty values?

As for the query - that is an excellent point, that would be much cleaner if I just had an "Amount" column and an attribute describing what that amount is.

Thanks for your advice!
J

[meb97me]

i guess one option would be to continue to store all items as weight in a single FACT column

then against the product dimension you could store a density value and use a scoped calculation to turn a subset of those products back into volume if you wish to see volumes as well as weight

Do you also records items by units rather than weight/volume because if so i could see that being a problem.

[thekeel]

i guess one option would be to continue to store all items as weight in a single FACT column

then against the product dimension you could store a density value and use a scoped calculation to turn a subset of those products back into volume if you wish to see volumes as well as weight

Do you also records items by units rather than weight/volume because if so i could see that being a problem.

We do record tires as a QTY as well as a weight. QTY in this sense doesn't apply to anything else as we don't track # of Barrels of Oil for example.

I need to be able to query:
total gallons for the volume based services
total pounds for weight based services
total pounds for weight based and volume based (via conversion or actual stored value)
total qty for tires and pounds for tires

[VHF]

A generic amount field introduces the danger of getting a meaningless result if the amount is unintentionally aggregated across measure types (ex: pounds + gallons.)

I like the simplicity of just putting the three different measures in a single fact table: Pounds, gallons, qty. Store a zero or null for non-applicable measures. This ensures that the measures will always aggregate (sum) correctly no matter how the data is sliced/diced.

[ngalemmo]

There is nothing wrong with maintaining both weight and volume measures in a fact. However, I would include a unit of measure dimension with two FK references, one for each measure.

[thekeel]

There is nothing wrong with maintaining both weight and volume measures in a fact. However, I would include a unit of measure dimension with two FK references, one for each measure.

Thanks - when you say add a FK for each measure, are you referring to removing the weight and volume columns and creating a row for each transaction's unit of measure and just storing an "Amount"?

[Jeff Smith]

You could put the unit of measure information in the product dimension particularly if the unit of measure was always the same for a given product. If you had different units of measures (number of tires and pounds of tires), you could still put it the product dimension, but it would add a few more rows to the table.

[VHF]

There is nothing wrong with maintaining both weight and volume measures in a fact. However, I would include a unit of measure dimension with two FK references, one for each measure.

If there are measures such as PoundsProcessed, GallonsProcessed, and QuantityProcessed in the fact table, what is the benefit of the UOM dimension (and resulting multiple FK references)? It seems like those measures are self-documenting and as long as each measure field is used only for that one thing there is no need to slice/dice/select/group based on UOM...?

[thekeel]

There is nothing wrong with maintaining both weight and volume measures in a fact. However, I would include a unit of measure dimension with two FK references, one for each measure.

If there are measures such as PoundsProcessed, GallonsProcessed, and QuantityProcessed in the fact table, what is the benefit of the UOM dimension (and resulting multiple FK references)? It seems like those measures are self-documenting and as long as each measure field is used only for that one thing there is no need to slice/dice/select/group based on UOM...?

This is the approach I have been leaning towards - its easy to query and reduces the single column "Amount". Is there any real downside to this method as opposed to including a "UnitMeasureID" for each row?

[Jeff Smith]

There are a couple of issues with different columns for different units of measure in a fact table. When one column is populated with an actual value, then the other columns would have be populated with with a 0 or a null. Either option causes problems. 0 cause problems when Averaging the column and nulls create problems with filters.

Secondly, if you had a lot of columns for measures, you could increase the size of the fact table without increasing the information in the fact table. Say you had 5 dimension keys and 5 measures with everything being defined as an integer. That's 40 bytes per record. The alternative is 6 dimension keys and 1 measure, which is 28 bytes. That's a 30% reduction in space.

Plus, you can do more with a single column and a measure dimension than with several measure columns. With the single measure column, you can always create a view with measures flattened out, but it's much harder to do the opposite. The single measure design can do everything the multi measure design can do and more and it takes less space. Plus, with the single measure design, you can add additional units of measure without a model change.

[hang]

Have you considered to store the conversion factor directly in the fact. Storing the unit factor information in the dimension is risky and makes the calculation more complicated, as the factor could change over time. Providing the on-spot factor in the fact can avoid duplicating the relevant measures and is more straightforward to fact users. A view can bring all the necessary measures onto the surface easily by simple calculations without involving any dimensions.

[thekeel]

There are a couple of issues with different columns for different units of measure in a fact table. When one column is populated with an actual value, then the other columns would have be populated with with a 0 or a null. Either option causes problems. 0 cause problems when Averaging the column and nulls create problems with filters.

Secondly, if you had a lot of columns for measures, you could increase the size of the fact table without increasing the information in the fact table. Say you had 5 dimension keys and 5 measures with everything being defined as an integer. That's 40 bytes per record. The alternative is 6 dimension keys and 1 measure, which is 28 bytes. That's a 30% reduction in space.

Regarding space - if I wanted to store a transaction of 30 Gallons of Oil, I would have to create an additional row for this line item, right? One for Gallons, and one converted to Pounds. Would I still be saving space with that method?

Now I guess I could create a conversion table and do the conversion at run time, but would rather use space than a decrease in performance.

[VHF]

That's a 30% reduction in space.

Now wait a minute! If each item processed needed only a single measure, then you realize the 30% space reduction. But in this case many items will need multiple measures (pounds and gallons, pounds and quantity). This would require multiple fact records to represent all the information. If 50% of the facts needed a second fact record, there would be no space savings.

I still hold that having separate measure fields (PoundsProcessed, GallonsProcessed, QuantityProcessed) is the cleanest solution. I agree that one needs to be careful when doing aggregations (sum, avg) on measures that are not always populated, but one also needs to be careful when using a generic amount measure to not inadvertently aggregate different types of measures. It seems like a design flaw to have the possibility of adding pounds and gallons!

I'll have to do a little digging, but I can't think of any examples in Kimball books that mix units in a single measure field. Sure, there might be different types of values (ex: revenue, expenses) that are dimensional but the base UOM is always the same (ex: dollars). Does anyone have a counter example?

On the other hand, it is quite common to have multiple measure fields in a fact table (ex: qty ordered, qty shipped, amount, net weight, gross weight). The only thing particular about this case is that not all of the measures apply all of the time.

Having three separate measure fields (PoundsProcessed, GallonsProcessed, QuantityProcessed) keeps the grain of the fact table clear--one fact record = one batch of something processed. With the "dimensional measure" approach, what would the grain of the fact table be?

[thekeel]

Now wait a minute! If each item processed needed only a single measure, then you realize the 30% space reduction. But in this case many items will need multiple measures (pounds and gallons, pounds and quantity). This would require multiple fact records to represent all the information. If 50% of the facts needed a second fact record, there would be no space savings.

I still hold that having separate measure fields (PoundsProcessed, GallonsProcessed, QuantityProcessed) is the cleanest solution. I agree that one needs to be careful when doing aggregations (sum, avg) on measures that are not always populated, but one also needs to be careful when using a generic amount measure to not inadvertently aggregate different types of measures. It seems like a design flaw to have the possibility of adding pounds and gallons!

That is my thought - the number of rows will grow dramatically if I have the "Amount" column. The grain of this sustainability table is how much of a product was recycled and how it was recycled, which could be up to 7 recycling methods for Oil, which means 7 transaction rows for Gallons and another 7 transaction rows for Pounds.

Where as if I use the column approach, I may have 7 transactions by recycling method, but each unit of measure is in its own column.

I'll have to do a little digging, but I can't think of any examples in Kimball books that mix units in a single measure field. Sure, there might be different types of values (ex: revenue, expenses) that are dimensional but the base UOM is always the same (ex: dollars). Does anyone have a counter example?

On the other hand, it is quite common to have multiple measure fields in a fact table (ex: qty ordered, qty shipped, amount, net weight, gross weight). The only thing particular about this case is that not all of the measures apply all of the time.

Having three separate measure fields (PoundsProcessed, GallonsProcessed, QuantityProcessed) keeps the grain of the fact table clear--one fact record = one batch of something processed. With the "dimensional measure" approach, what would the grain of the fact table be?

This has been my biggest issue - I cannot find much information on a situation like this. I can find tons of info on different measures of the same type such as pounds to tonnage, but not differences like volume and weight.

[hang]

Personally I don’t like the single measure idea as it is too confusing to users and therefore defeats the purpose of data warehousing. I think QTY is a typical additive measure and should be a dedicated column applicable to all products. If the product is a measured (weighed/volumed) item then QTY should be 1.

The weight/volume is normally stored in product dimension as a dimension attribute. However if you want to make it an additive measure in the fact, you should store it in a separate field and nominated a base unit and store the conversion factor in the fact as well. I don’t mind if you store different units in separate fields as long as there are not too many such measures where factor columns could reduce the total number of measures significantly.

In the product dimension you may have an attribute indicating if the item is measured for weight/volume or not, or you may have a mini-dimension that contains the flag along with other low cardinality attributes. For unmeasured (itemised) product, the measured value should be a “0” to cater for correct aggregation even without checking the IsMeasured flag. For AVG aggregate, you may need to do a CASE check to exlude the count for 0's, or use NULLIF function (ANSI SQL99) to nullify the 0 values so that AVG can work properly. “0” value in this case could also indicate the product is an unmeasured item. But for clarity, a “IsMeasured or IsScaled” flag in either product dimension or mini-dimension is better practice. You may even have a 3 value attribute: “W” for weighed, “V” for volumed and “N” for unmeasured products. Don’t use minus value for inapplicable measures in this case as they may produce incorrect aggregations.

[Jeff Smith]

Why would a record have multiple units of measure? It's either a count, wieght, or volume. You could have conversions of a unit of measure - pounds/kilos, gallons/liters, etc. But I don't think something can be weight and volume. And if it can be, then yes, there should be different fields.

As long as the measure has the same data type, then why can't multiple units of measure be in the same field? You're not using the field other than how it's defined. Your not changing the grain of the fact table. It stores the data acurately. It handles changes gracefully.

[thekeel]

Why would a record have multiple units of measure? It's either a count, wieght, or volume. You could have conversions of a unit of measure - pounds/kilos, gallons/liters, etc. But I don't think something can be weight and volume. And if it can be, then yes, there should be different fields.

In our case, we need to report Gallons of Oil as Pounds of Oil also. Everything will have a poundage, but not everything will have a volume (gallons), nor will everything have a quantity as quantity in this example is specific to the number of tires. We don't track quantity of barrels for oil, so it doesn't apply at this time unless you want to consider an oil service as quantity of one.

[Jeff Smith]

If all of the measures are populated, then that's different. I was under the impression that only 1 measure would be populated. But a problem comes into play when some of the measures are populated and some aren't.

And space does translate into performance.

[thekeel]

But a problem comes into play when some of the measures are populated and some aren't.

What types of issues do you foresee with this model? I really want to get it right, so I'm interested in all opinions either way.

Thanks for your advice.

[Jeff Smith]

If the measure columns aren't populated, you end up with nulls or 0s. It has an impact when running averages or applying filters on the measures.

Also, let's say you have pounds, gallons, liters for oil. Sometimes the Gallons column is populated. Sometimes the Liter column is populated. And sometimes the pounds columns is populated. Sometimes all three are populated and sometimes only 2 are populated. How much oil is there?

I would think you would need some way to sum a uniformed measure for oil. Maybe have a conversion from pounds of oil to Gallons or liters.

You might be able to have a UOM dimension that had a conversion based on the product.
For example:
OIL, Pounds, 1 Pound, 10 gallons, 42 liters
OIL, Gallons, 0.1 Pounds, 1 gallon, 4.2 liters

This could be in your UOM measure dimension (it's OK for Product to be in the UOM dimension and in the product dimension because you are using Product i the UOM as a way of assigning the dimension key).

Just a suggestion. The way I look at it, somewhere someone is going to have to convert everything (at least at the product level) to same unit of measure. You can either leave it up to each individual user to come up with their own conversion method or you can create 1 conversion method for everyone to use.

[ngalemmo]

There is nothing wrong with maintaining both weight and volume measures in a fact. However, I would include a unit of measure dimension with two FK references, one for each measure.

Thanks - when you say add a FK for each measure, are you referring to removing the weight and volume columns and creating a row for each transaction's unit of measure and just storing an "Amount"?

No. One row, two measures. The units of measure dimension is assuming there isn't a consistent unit used for weight or volume. You would have one dimension reference for each measure.

The other reason for the UOM dimension is to handle conversions between units. If all of that is handled before hand and you always have consistent units then you can do without it.

But, in my experience, companies tend to have a 'standard unit' for accounting purposes that is based on some conversion factor that varies by product. Also products themselves can be ordered, shipped and invoiced in different units. It is not uncommon to need a means to convert units when reporting.

[VHF]

Also products themselves can be ordered, shipped and invoiced in different units. It is not uncommon to need a means to convert units when reporting.

Which could be handled either by a UOM dimension or by an attribute in the product dimension, correct? (The later case only if an individual SKU is only sold/shipped/invoiced using a single consistant UOM.)

[ngalemmo]

Also products themselves can be ordered, shipped and invoiced in different units. It is not uncommon to need a means to convert units when reporting.

Which could be handled either by a UOM dimension or by an attribute in the product dimension, correct? (The later case only if an individual SKU is only sold/shipped/invoiced using a single consistant UOM.)

Its usually a combination of product and unit of measure. For example a customer may order cases but they ship pallets. The number of cases in a pallet depends on the size of the case, and the size of the product would determine the number of units in a case. So each product could have different conversion factors between units.

[Sponsored content]