Joining two Fact tables???

I am having trouble joining two fact tables. I have read the different topics and see that the bridge table would be my logical option. The subject I have is Orders. I can place an Order which may contain many items...as well as, the items can be a part of many Orders. Currently, I have two fact tables Orders Fact and Items Fact. Each fact can be its own star schema. I need to join the Order ID with every Item ID. So I created Dim tables for each of the Facts. Now I have:

ItemDetail_Fact Item Dim
--------------- ---------------
(PK)ItemDetail_ID (PK)Item_ID
(FK)Item_ID Description
Skey InsertDate
Skey
Skey
Price...

OrderDetail_Fact Order Dim
--------------- --------------
(PK)OrderDetail_ID (PK)Order_ID
(FK)Order_ID Type
Skey Source
Skey Destination
Skey InsertDate


I have no idea where to go from here or if this is the right direction...weighting factor??? Also, I have measures that come from other fact tables...but I'll crawl before I walk. Please help!

You don't join fact tables.

Facts are combined by summarizing each set of measures individually by common dimensions then joining the summarized sets on those dimensions.

But, from the looks of the model, that is not your problem. Why do you want to join these in the first place and why do you have an Item Detail fact table? Is it to track price history? And if it is, what is the point of joining it to the Order facts?

Why doesn't your order fact table have price (or the ability to derive price) already? Doesn't an order fact include the quantity and what was charged for the item?

I am reengineering another's design...I am just as confused. Originally, it was just the Item Fact and Order Fact. There was not a common dimension between the two so it was really a shot in the dark to create the Dims. Item and Order need to be combined because it is a many to many relationship:

OrderedItem Fact
-----------------
Order_ID
Item_ID
Quantity Rec
Price

From that combination, we get the price from the particular item and the quantity requested comes from a different fact. So essentailly, it could be:

OrderID|ItemID|Quantity_Rec|Price
-----------------------------------
1|1|20|2.75
1|2|10|3.50
2|1|20|2.75
2|3|15|1.50

So, we have a record for each Order AND Item on the order. To me, it seemed like an OLTP structure...also, i didn't think joining two facts was correct so that's why I am trying to reengineer it.

My original thinking is to change the Item Fact to Item Dim?

Item Dim
---------
Item ID
Description
Vendor_ID
Category_ID
Other S keys...
List Price (measure)

Just confused how to link that to the Order_Fact

I think you need to start from the beginning. What is it you are trying to model? What business event do the facts represent? What are the measures?

With the understanding that a fact table represents a business event or state, what is the Item Fact supposed to represent? What are the measures? Dimensions provide context to the facts

From what I can gather, you are modeling sales orders. So the business event is getting and/or changing the order. Measures would be (at minimum) the item quantity and the value (extended price). The rest are dimensions... item is a dimension not a fact, it is context for the quantity and value. So is customer, various dates, shipping addresses, etc...

In a well designed sales order star schema, you almost never have an order dimension table. You usually break down attributes found in the order header into smaller dimensions. You store the order number as a degenerate dimension (i.e. store the raw value) in the fact table.

Price (current list price) is typically an attribute of the item dimension. Actual price charged is usually a derived measure (extented price / quantity) in the order fact table, although some choose to store unit price in the fact (unit price is a non additive measure... it really isn't very useful as a measure and easy enough to derive if you need to show it).

I don't get your OLTP comment. The goal in designing a fact table is to capture information at the lowest level of detail attainable (atomic fact table). This provides the greatest opportunity for analysis. If you need to produce summary tables (or cubes) for performance reasons, these are created from the atomic tables afterward. In this case the order fact whould have at least one row per order line (order line number would be a degenerate dimension as well), and more if you are capturing changes and want to provide retrospective (historical) analysis.

kclark wrote:My original thinking is to change the Item Fact to Item Dim?

Item Dim
---------
Item ID
Description
Vendor_ID
Category_ID
Other S keys...
List Price (measure)

Just confused how to link that to the Order_Fact

Yes, Item is a dimension. A dimension table has at least two keys, a surrogate primary key and a natural key. The natural key is the business identifier (in the case Item number or SKU) and serves as an alternate key to the table (to locate the row and get the primary key during load).

You store the foreign key to the item in the fact table. Basically, the only thing a fact table should contain are: foreign keys to dimensions, degenerate dimension values and measures. (There are other things to support behind-the-scene loading and auditing and such, but from a logical perspective, just these three types of items).

Dimension tables should not contain foreign keys to other dimension tables. This is snowflaking and would hamper query performance. Store item attributes as values in the item dimension rather than normalized references to other tables. For example, item category code and description should be columns in the item dimension, not FK references to an item category table.

Thank you for being so nice!

I meant by my OLTP statement that the associative table that was previously designed reminded me of a relational database and not a data warehouse. I knew that the snowflaking would occur with the foreign keys in the Dim but I think that's why Item was a fact first to slice the Item by Category/Subcatery, Formulary, Vendor, and Routine which are Dimensions themselves. For example, the Amount Spent on Non Routine items. Another reason is because an Item can belong to multiple categories and subcategory combinations. Are you saying for each of the item attributes (Category/Subcatery, Formulary, Vendor, and Routine) store them as a code from the other Dimension table but not as a reference to it (FK)...right?

Right. As far as the multiple cateories go, you would handle that with a bridge table with product key being one of the keys.

Ahhhh, yes the infamous Bridge table... My plan was to create a Category Dim and Subcategory Dim with ID and Name fields for each. Then one to many join to a Bridge table:

Cate_ID
Subcate_ID
Item_ID

Now, I would also new a one to many relationship from Item Dim to Bridge table correct? Also, would I need a weighting factor here...?

Store item attributes as values in the item dimension rather than normalized references to other tables. For example, item category code and description should be columns in the item dimension, not FK references to an item category table.

I would still keep the dimension tables even if I have no relationship created between them correct???

kclark wrote:Ahhhh, yes the infamous Bridge table... My plan was to create a Category Dim and Subcategory Dim with ID and Name fields for each. Then one to many join to a Bridge table:

Cate_ID
Subcate_ID
Item_ID

Now, I would also new a one to many relationship from Item Dim to Bridge table correct? Also, would I need a weighting factor here...?

A weighting factor would depend on how and what you plan to report of the bridge. It doesn't hurt to have one, as its use in queries is optional.

kclark wrote:

Store item attributes as values in the item dimension rather than normalized references to other tables. For example, item category code and description should be columns in the item dimension, not FK references to an item category table.

I would still keep the dimension tables even if I have no relationship created between them correct???

If you still have a need for them as a dimension, then yes.

ngalemmo wrote:

kclark wrote:Ahhhh, yes the infamous Bridge table... My plan was to create a Category Dim and Subcategory Dim with ID and Name fields for each. Then one to many join to a Bridge table:

Cate_ID
Subcate_ID
Item_ID

Now, I would also new a one to many relationship from Item Dim to Bridge table correct? Also, would I need a weighting factor here...?

A weighting factor would depend on how and what you plan to report of the bridge. It doesn't hurt to have one, as its use in queries is optional.

The reason I ask about the weighting factor is because when the dollars spent per item is sliced by category / subcategory it is not correct...double counting I believe

In that case, you need one.

So how is that determined...by a group_key as well? My bridge looks like this:

ItemCate_Skey (PK)
Item_Skey (link from the Item dimension)
Category_Skey (link from the Category dimension)
Subcategory_Skey (link from the Subcategory dimension)
Weighting_Factor

Also, I read some information about Inventory data warehousing...in a previous thread. You mentioned there are 3 areas SalesOrder, Shipping, and Invoicing. I am seeing a possibility of a spin off with the shipping area. The original design has two fact tables for Stock_Location and Stock_Customer(an Agent may place an order or receive one). It contains the same measures Optimal Quantity, Quantity on Order, and Quantity On Hand. Essentially, it sounds like a pre-order scenario. I believe I can aggregate this into one fact table with SalesOrderFact as it has shared dimensions with Location, Item, and possibly Time...would I be correct in that assumption?