How to model a source table with several roles?

View previous topic View next topic Go down

How to model a source table with several roles?

Post  johan on Thu Feb 09, 2012 6:44 am

I have a source at hand which has tables that have several roles e.g.
The table 'businesspartner' contains the common fields of all businesspartners.
Besides this table you have a table 'Customer' which contains a businesspartner_id and extra fields that are only valid for a customer. Then you have several other tables like 'Supplier' etc. The same applies to these tables.
When it comes to dimensional modelling I am not sure if I should put all the fields of businesspartner, customer, supplier and so on in one table dim_businesspartner and reference this table from fact_tables with businesspartner_customer_id and businesspartner_supplier_id or create separate dims for all those different business partners (e.g. dim_customer).

Thanks in advance for your help.

johan

Posts : 5
Join date : 2012-02-09

View user profile

Back to top Go down

Re: How to model a source table with several roles?

Post  ngalemmo on Thu Feb 09, 2012 10:32 am

You can do it whichever way makes sense. If there is a significant disparity between columns depending on role, you could continue with the sub-type cluster arrangements like the source. Or you can put it in a wider single table or three independent tables, the latter being the least desireable choice, but not by much.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: How to model a source table with several roles?

Post  BoxesAndLines on Thu Feb 09, 2012 2:41 pm

Unless you are building a Party dimension I would split the tables out. There's nothing worse than selecting a role and filtering on an attribute that doesn't apply to that role. If you have views, you can manage this somewhat, but in the end, at the table level, no one knows which attributes vary by role without diving into the ETL code.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: How to model a source table with several roles?

Post  hang on Fri Feb 10, 2012 6:22 pm

Totally agree with B&L. They should be separate dimensions. However the business-partner sounds like a flag in customer dimension, if there is no attribute that is specific to business-partner and inapplicable to customer.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: How to model a source table with several roles?

Post  johan on Wed Mar 07, 2012 4:25 am

Thanks for your responses. While moving on it seemed right to save some attributes in the business partner dimension.
Our solution is for example:

Fact_Invoice has a Customer_ID which links to dim_Customer.
That same Customer_ID also links to dim_Businesspartner.

The user can join both dim tables using one ID to find businesspartner info (generic) and customer info (specific).

johan

Posts : 5
Join date : 2012-02-09

View user profile

Back to top Go down

Re: How to model a source table with several roles?

Post  Vishy on Thu Mar 08, 2012 2:36 am

The user can join both dim tables using one ID to find businesspartner info (generic) and customer info (specific).

Dimensions should be connected via fact and not directly.

Second thing is I would advocate the same as B&L and Hang mentioned.
Keep in mind that having all the details in a single table then there would be duplication of the things as there is a hierarchy. So even when you put a filter you might end up getting duplicate rows (due to attributes) and will be forced to use DISTINCT.


Vishy

Posts : 75
Join date : 2012-02-28
Location : India

View user profile

Back to top Go down

Re: How to model a source table with several roles?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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