Joining two Fact tables???

Page 1 of 2 1, 2  Next

View previous topic View next topic Go down

Joining two Fact tables???

Post  kclark on Fri Aug 13, 2010 9:40 am

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!

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Joining two Fact tables???

Post  ngalemmo on Fri Aug 13, 2010 12:31 pm

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?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Joining two Fact tables???

Post  kclark on Fri Aug 13, 2010 12:53 pm

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.

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Better yet...

Post  kclark on Fri Aug 13, 2010 1:52 pm

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

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Joining two Fact tables???

Post  ngalemmo on Fri Aug 13, 2010 1:58 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Joining two Fact tables???

Post  ngalemmo on Fri Aug 13, 2010 2:06 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Joining two Fact tables???

Post  kclark on Fri Aug 13, 2010 2:36 pm

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?

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Joining two Fact tables???

Post  ngalemmo on Fri Aug 13, 2010 4:06 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Joining two Fact tables???

Post  kclark on Fri Aug 13, 2010 5:22 pm

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...?

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Joining two Fact tables???

Post  kclark on Fri Aug 13, 2010 5:27 pm


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

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Joining two Fact tables???

Post  ngalemmo on Fri Aug 13, 2010 6:39 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Joining two Fact tables???

Post  ngalemmo on Fri Aug 13, 2010 6:41 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Joining two Fact tables???

Post  kclark on Wed Aug 18, 2010 5:14 pm

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

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Joining two Fact tables???

Post  ngalemmo on Thu Aug 19, 2010 12:42 pm

In that case, you need one.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Joining two Fact tables???

Post  kclark on Thu Aug 19, 2010 2:37 pm

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?

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Joining two Fact tables???

Post  kclark on Thu Aug 19, 2010 3:53 pm

To bring a little more clarity to my previous post...I was saying that it looks like Supplier and Customer information is being compiled together. For example, there are 3 different types of Customers (those who receive the orders) and those same three could possibly be Suppliers (those that send the products from their stock). I would make a Customers and Suppliers Dims, correct?

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Joining two Fact tables???

Post  ngalemmo on Thu Aug 19, 2010 4:27 pm

What is the business relationship? Do they own the inventory or does your company?

If you own the inventory and you fulfill orders from their inventory, I would handle them as simply another inventory location. They would still be customers as well, with a specific type or category code identifying them as distributors/fulfillment agents so you can differentiate their orders from normal sales.

If they are buying their inventory and handling thier own sales, then they are just customers like everyone else.

How does your operational system handle it?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Joining two Fact tables???

Post  kclark on Thu Aug 19, 2010 5:39 pm

Good question...which I plan to get more clarity on tomorrow. The company owns the inventory. Here's the reason I say this. A Customer Dim nor a Supplier Dim nor an Inventory Location Dim exists. There's two different FACT tables that represent two of the Inventory Locations...with measures Optimal Quantity, Quantity_ON_HAND, and Quantity_ON_Order. They are "represented" in the Orders Fact like: From_Type1, To_Type1, From_Type2, To_Type2. So if the product is coming FROM Type1 then From_Type1 would have a value and To_Type1 would be null. Almost like booleans but skeys.

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Joining two Fact tables???

Post  kclark on Fri Aug 20, 2010 11:31 am

From what I am understanding is that I am going to have a snowflake because each of the category types has its own dimension... Example:

Customer Dim | Inventory Location Dim
-------------- ----------------------
Customer Skey Supplier_Skey
Customer Type Supplier Type
Insertdate Insertdate

Patient Dim | Type 1 Dim | Type 2 Dim
---------- ----------- ----------
Patient_Skey Type1_Skey Type2_Skey
Customer_Skey Customer_Skey Customer_Skey
Supplier_Skey Supplier_Skey Supplier_Skey
LName Loc_Code LName
FName Company_No FName
Address Service

Then there would be a fact table from the Item Dim and the Inventory Location Dim:

Stock Fact
----------
Stock_ID
Supplier_Skey
Item_Skey
Optimal Quantity
Quantity On Hand
Quantity On Order

Does this make sense?

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Joining two Fact tables???

Post  ngalemmo on Fri Aug 20, 2010 1:09 pm

You don't need a snowflake, just more dimensions on the facts. Are you trying to model logistics?

Look at: http://intelligent-enterprise.informationweek.com/010613/warehouse1_1.jhtml?_requestid=301619

avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Joining two Fact tables???

Post  kclark on Fri Aug 20, 2010 2:16 pm

Yes I am trying to model logistics, great article! I am trying to track obviously the sales orders...how many items per order, how many orders, dollars spent per item per order, etc. Then I am tracking the inventory movement...from one location to another. Then I am also tracking inventory performance by item by location...that's where the optimal quantity, quantity on hand, and quantity ordered measures come in.

So starting from the beginning, I know I need these dimensions: Item, Location, Customer, Time
And an Order fact. I believe for both the Location and Customer Dim I need to use a Unity Dim because I have three different entities (Patient, Agency, Agent) with differing attributes that share a common role (Each can be either a Customer or a Location). Is that correct?


kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Joining two Fact tables???

Post  ngalemmo on Fri Aug 20, 2010 2:45 pm

Right. From the point of view of an order, you have traditional relationships, however, when dealing with logistics, stuff can come from or to anybody, so you need to abstract the existing traditional dimensions to simplify the movement model.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Joining two Fact tables???

Post  kclark on Fri Aug 20, 2010 4:21 pm

A little confused...I assume you are referring to your shipment example. Translating that to StockLocationFact scenario...

I would have Patient, Agent, and Agency Dims instead of Customer and Location. However, the only common attribute is address. Then create the ShippingPoints Dim...what I don't understand is how the where the measures Optimal Quantity, Quantity On Hand, and Quantity On Order come in... If they exist in the ShipmentFact table then which "location" would they apply to; the origin or the destination????

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Joining two Fact tables???

Post  kclark on Mon Aug 23, 2010 10:28 am

Would that be correct?

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Joining two Fact tables???

Post  ngalemmo on Mon Aug 23, 2010 12:20 pm

I don't know. I'm confused too.

If you are tracking sales and inventory performance, I am assuming you have multiple fact tables. Where I am confused is what customer/patient has to do with inventory.

Things like optimal quantity are usually dimensional attributes at the product/location level, and inventory is typically maintained as a snapshot, usually at month end. Inventory movements are its own fact, and may be daily summaries, or if transactional, may have a reference to the receipt or shipping document. Shipments would also be its own fact, and it may carry over dimension FKs from the order.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Joining two Fact tables???

Post  Sponsored content


Sponsored content


Back to top Go down

Page 1 of 2 1, 2  Next

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum