Basic Fact Table Design Question

I'm not sure if this is the proper board....but....

I have a sales data from two different cost centers that generate the same data. When setting up my OLAP db, should I:

(1) design two fact tables that would have the same attributes,

(2) one fact table and one dimension table that contained a key field and a sales type field, or

(3) a fact table in which one of the attributes was the sales type field.

Thanks

Do the facts from the different cost centers represent the different instances of the same business process or event? If so, then they should be modeled as a single fact. Another way to approach this is to ask, "Would my users consider these to be the same thing and therefore would expect these to be in the same place in the DW?".

If you model these as a single fact, the cost center would be best handled in a dimension. Sounds like it would have low cardinality. (Is it possible you would ever have more than two cost centers? Many organizations have hundreds or even thousands of cost centers.)

B.

|||Cost center was probably a bad term. The business makes money selling stuff and performing services. They want total sales data and then sales broken between sales and services. The facts are pretty much the same: amount, date, cost of sale/service, etc. I thought a dimension table would work but it's going to be a table with an attribute that has only one of two conditions, plus a key field and a foreign key in the fact table. Seems like a lot of work for a simple A/B field.|||

I think option 2 is the better, but I need to understand some more requirements to decide.

Create a dimension is better, because tomorrow if you have more cost centers, your structure will be more flexible.

But option 3 is not bad...

Regards!

|||

This sounds like a line item fact table. For example, I bring my car in for repairs and what not, I receive a bill (an invoice). On that bill, there will be items for various parts and labor for various services, each with the number of units consumed, a price per unit, and the total cost for that line.

Each item purchased, it's code, name, base price, etc., belongs in a "product" dimension. We may identify each of these products as belonging to a "Parts" or "Service" category, but that catorigation too would belong in the dimension table.

Take a look at the Kimball book, "The Data Warehouse Toolkit", Chapters 2 & 5, for more ideas on this.

Good luck,

B.