Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

How to model a source table with several roles?

5 posters

Go down

How to model a source table with several roles? Empty How to model a source table with several roles?

Post  johan 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

Back to top Go down

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

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

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

http://aginity.com

Back to top Go down

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

Post  BoxesAndLines 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.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

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

Post  hang 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

Back to top Go down

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

Post  johan 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

Back to top Go down

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

Post  Vishy 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

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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