A fact table for each service line of business?

I am in the early stages of building a DW and wanted to get some feedback on how to model multiple lines of business with different units of measure.

We recycle material and break out the items we recycle into various business lines. The material we recycle can be Oil, Tires, or Organic items for example. The material type determines if it is measured in pounds, gallons, or qty. For example Oil is measured in Gallons, Organics in Pounds, and Tires in QTY.

Tires can also be represented as pounds by doing a simple conversion and I will need to allow users to query on the qty of tires along with the weight. Oil is similar in that its natively measured in gallons, but we also report oil in pounds via another conversion. Users will want to see both.

I need to determine the best way to store all of these various units of measure.

Method 1: Multiple Fact Tables Per Product

My first thought was to break each material into its own fact table with only the specific measures it requires. So for Oil I would have fact table with VolumeGallons, and for Organics I would have WeightPounds. They would all share the same dimensions as the grain is the same, but the units of measures are not the same. The grain of the DW at its most atomic level is how the material was Recycled. Oil may be recycled 5 different ways, tires 7 different ways and so on. All recycling methods would be stored in a Dimension tied to each fact table.

Method 2: One large fact Table for all Products With Unit of Measure Columns

If I combine these facts into one fact table, I could have each unit of measure as a column: VolumeGallons, WeightPounds, and QTY. The problem is QTY doesn't apply to anything but Tires. So all other products would have a 0 applied to this column.


Method 3: One large fact Table for all Products with Unit of Measure ID

Similar to Method 2 except now each product has a UnitMeasureID which can be Gallons, Pounds, or QTY. An additional column "Amount" is also added to store the value of the UnitMeasure.

This technique will require quite a few more records in the fact table due to having to store Gallons and Pounds for Oil related products. Also Tire products would also have a weight in pounds and a QTY record. The size could easily be triple Method 2.


Reporting Requirements:

The business wants to compare the various lines to each other across location and time. Things such as:



1. Total pounds recycled across all products

2. Total pounds based on a specific recycling method

3. Total pounds or gallons for Oil related products

4. How many pounds or gallons recycled for a specific state


Method 3 Diagram:

Here is an example of all Services stored into one large fact table with the various Units of Measure causing a duplication of rows. Now for Oil I will have UnitMeasureID which will be in gallons (ID 1) and pounds (ID 2) for each line item.






Is any method any better than the other? Is there a completely different design I should consider?

Thanks for your advice.










Thanks for your advice.

You are in a process business and then you can have a lot of quality measures along the process such as weighing.

When I read you, I see two levels of detail:
- the total measure of process
- the detail measure of the process

I find your approach very valuable as one material can have only one recycle process.

Why not create a total measure fact table (or view) to hold the total values ?
In this case, you can answer all your questions and add the process type as dimension (ie material type).

Success
Nico

Why not to create one fact table with 3 dimensions: Time, Location és BUSINESS LINE?

In the fact table there will be business line specific measures and the total pounds.

If there will be a new business line, it will be much easier to follow it.

There should be a single fact table with appropriate dimensions. Dimensions should include a material dimension (with possibly line of business as an attribute, or line of business as its own dimension), and a unit of measure (UOM) dimension. Quantity would be a measure qualified by the UOM.

If a material comes through in different units, then you should maintain a UOM conversion bridge to allow reporting in any particular applicable unit. It is also common for a business to define a standard unit so they can compare volumes across product lines. This would be another UOM entry with appropriate conversion factors between other units.

ngalemmo wrote:There should be a single fact table with appropriate dimensions. Dimensions should include a material dimension (with possibly line of business as an attribute, or line of business as its own dimension), and a unit of measure (UOM) dimension. Quantity would be a measure qualified by the UOM.

If a material comes through in different units, then you should maintain a UOM conversion bridge to allow reporting in any particular applicable unit. It is also common for a business to define a standard unit so they can compare volumes across product lines. This would be another UOM entry with appropriate conversion factors between other units.

Thanks - The grain of this table is recycling method, which is the most atomic part of our service. We track cost based upon a service, which can contain multiple products (oil and oil filters), and each product can be recycled many different ways.

Now I remember Kimball recommending to always store to the lowest granularity possible and allocating things like shipping to each atomic part of the whole. In my example I have TotalCost, ServiceCost, AdditionalCost, and SalesTax fields in my fact table. Would allocating these costs multiple levels down into various products and then to the actual recycling method be appropriate in this situation?

Simplistically, I would divide the the various cost measures by Product, then by RecyclingMethod to end with a cost per recycling method and forgo the need of a summary table of just services.