How to model a source table with several roles?
5 posters
Page 1 of 1
How to model a source table with several roles?
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.
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
Re: How to model a source table with several roles?
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.
Re: How to model a source table with several roles?
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- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: How to model a source table with several roles?
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
Re: How to model a source table with several roles?
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).
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
Re: How to model a source table with several roles?
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
Similar topics
» Dimensional model - denormalized source
» Dimensional Model from a Hierarchical Data Source
» How to Model Single-Table Parent-Child Relational Table in a Dimensional Schema
» Attribute data held as rows in source - how to dimensionally model???
» How do you properly model with a bridge table
» Dimensional Model from a Hierarchical Data Source
» How to Model Single-Table Parent-Child Relational Table in a Dimensional Schema
» Attribute data held as rows in source - how to dimensionally model???
» How do you properly model with a bridge table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum