Joining two Fact tables???

Page 2 of 2 Previous  1, 2

View previous topic View next topic Go down

Re: Joining two Fact tables???

Post  kclark on Mon Aug 23, 2010 2:50 pm

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?

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Joining two Fact tables???

Post  kclark on Tue Aug 24, 2010 11:05 am

Does the scenario 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 Tue Aug 24, 2010 12:18 pm

...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.
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 Tue Aug 24, 2010 4:02 pm

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?

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Joining two Fact tables???

Post  ngalemmo on Tue Aug 24, 2010 4:44 pm

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).
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 Tue Aug 24, 2010 5:12 pm

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?

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Joining two Fact tables???

Post  ngalemmo on Tue Aug 24, 2010 5:20 pm

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.
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 Tue Aug 24, 2010 5:38 pm

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.

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Joining two Fact tables???

Post  ngalemmo on Wed Aug 25, 2010 12:00 pm

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.
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 25, 2010 1:18 pm

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)

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Unity Dimension

Post  seadog2010 on Wed Jan 26, 2011 6:00 pm

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

seadog2010

Posts : 23
Join date : 2010-03-04

View user profile

Back to top Go down

Re: Joining two Fact tables???

Post  ngalemmo on Wed Jan 26, 2011 8:32 pm

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

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

View user profile http://aginity.com

Back to top Go down

Unity Dimension

Post  seadog2010 on Thu Jan 27, 2011 11:09 am

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.

seadog2010

Posts : 23
Join date : 2010-03-04

View user profile

Back to top Go down

Re: Joining two Fact tables???

Post  herman reid on Fri Feb 04, 2011 4:33 pm

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

herman reid

Posts : 2
Join date : 2011-02-04
Location : USA

View user profile http://www.blenderreviewer.com

Back to top Go down

Re: Joining two Fact tables???

Post  apirie on Tue Jul 28, 2015 8:12 pm

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

apirie

Posts : 1
Join date : 2015-07-28

View user profile

Back to top Go down

Re: Joining two Fact tables???

Post  ngalemmo on Wed Jul 29, 2015 7:16 pm

Wayback machine? Cool. Thanks for the link.
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 2 of 2 Previous  1, 2

View previous topic View next topic Back to top

- Similar topics

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