Joining two Fact tables???

Yes there are multiple facts here. What I was trying to say earlier was that a CUSTOMER can also be an INVENTORY LOCATION. For example, 3 types of "customers" can place orders: a patient, a location that needs supplies, or an agent that needs supplies for seeing patients. Two of the "customers" can also be "inventory locations". For example, if a patient needs items then the agent would supply the patient from their stock. Or a location can send supplies to another location or agent or patient. I wanted to know how should I design this scenario, store all three in a Customer Dim and two in a Location Dim or use the Unity Dim.

So just focusing on the product/location attributes (Optimal Quantity)...The current design, has 2 similiar fact tables for the product/location information. AgentLocation and AgencyLocation. One represents one inventory location and the other fact represents another. Each has the Item_ID and the Location_ID (Agent_ID or Agency_ID) in it. The Optimal Quantity, Quantity On Hand, and Quantity On Order are measures in BOTH facts. The only difference between the facts is that one is Agent and the other Agency. I do not believe this is correct...

Things like optimal quantity are usually dimensional attributes at the product/location level

Were you saying here to use product/location association as Dimensions instead of Facts? Keep the two associations, AgentLocation and AgencyLocation, but as Dimensions and not Facts?

Does the scenario make sense?

...store all three in a Customer Dim and two in a Location Dim or use the Unity Dim...

Yes, yes and probably.

It depends on the fact. For orders, they are customers. For inventory they are storage locations. If you are tracking shipments and the from's and to's can be either a customer, storage location and possibly vendor then a unity dimension would help. If these are new structures, it would be best if all the base dimensions have mutually exclusive surrogate primary keys, this way you can use the same PK in the unity dimension. This would allow you to use any of the dimensions when looking at shipment for particular queries, such as "what did I ship to customers" you could join the ship to key to the customer dimension. If that is not possible, include a type code in the unity dimension and a FK to the appropriate dimension (yes, it is a snowflake) so that if you are doing a role specific query (such as just customers) you can access additional attributes from those dimensions.

The thing is, depending on the role, the attributes you collect are different. So you need the traditional role dimensions. If an entity is both a customer and a storage location, the business looks at these differently in the context of what transaction is taking place.

store all three in a Customer Dim and two in a Location Dim

That is the hard part for me because...

depending on the role, the attributes you collect are different. So you need the traditional role dimensions

I don't know how to design that or I can design it, I just want to make sure I understand it. So in order to compensate for all 3 types of customers and their attributes, would I include ALL attributes in the Customer Dimension? So for example, a customer record that is a location type would not have FName or LName populated, correct?

No.

I assume you have an operational system. The system handles orders and has customers. I would also assume those customers include agents, no? They all go into the customer dimension.`You would not put locations in the customer dimension.

Do you also have an inventory system that tracks movements between locations? Same thing... locations go into a dimension and are tied to the movements.

The unity dimension does not associate the two... you still have customers and locations... it simply allows either to be referenced by the same foreign key. (which is handy for modeling shipments) If you need to associate a customer with their inventory location, you need to add a structure that does that (factless fact or bridge).

Sorry, I miswrote. I did not mean I would put a location in a customer dimension. Remember, there can be three types of customers: patients, agents, and agencies. An agency is not a person, it is a place which means it does not possess a first name. So in the customer dimension, for a record that is an agency (as a customer), I would not have the FName or LName fields populated, correct?

Yes. I would use a bridge table to associate Location Dim and Item Dim with Optimal Quantity, Quantity On Hand, and Quantity On Order as measures. Correct?

An agency has a name... doesn't it? You need to store something, but what you put where is a decision you make with your business users.

Yes, of course...my main question is that all three types of customers do not share the same attributes. Like an Agent and Patient have a Sex attribute yet an agency would not have that field populated...it would be null. Do ALL the attributes in the Dimension need to be populated? If so, I will need to create separate dimensions for Patient, Agent, and Agency.

No, you don't need separate dimensions. Having null or non-applicable columns in some cases is fine.

In extreme cases, where there are vastly different attributes, such as an Account in a banking warehouse, it is common to see sub-type dimensions (share the same PK as the primary dimension) for certain types of accounts, such as mortgages or loans.

Thank you! You have been most helpful. I am going to take this apply to my design. I'm sure I'll have another post about something else...please excuse my ignorance. :o)

Nick - the link to your article on Unity Dimensions is no longer valid - when IE merged with InfoWeek they dropped it (all articles prior to 2004 were not carried forward).

Any way we could get a more current link or copy of this article?

Thanks.

-Seadog

Don't know. I may have it buried in a directory somewhere, or maybe Google has it cached.

Nick;
Each link I use points me to the same URL, saying the document no longer exists.

If you're not able to dredge up the orig article, if you would, could you instead describe the mechanics of a Unity Dimension/ I believe it will solve a problem we have, in that many dimensional entities may play a similar role, and what I don't want to do is create an army of dimensions based on each and every possible role.

Thanks.

Hey guys, I have the same issue and am currently stuck. I tried the suggestions here but I am being reverted to the same results. I hope I find a permanent solution to this problem as it is affecting production at the moment. I guess its back to the drawing board for me..

Herman Reid
Vita Mix Coupon | Vitamix Recipes | Vitamix

For reference, the linked article The Unity Dimension is in the Archive.org Wayback machine at http://web.archive.org/web/20080926133810/http://www.intelligententerprise.com/010613/warehouse1_1.jhtml

Wayback machine? Cool. Thanks for the link.