Relationship between fact table and dimension tables

View previous topic View next topic Go down

Relationship between fact table and dimension tables

Post  trinhpv on Wed Jan 29, 2014 3:15 am

Hi everybody,
I am designing a dwh( in SQL server) about call center in our company and it have tranfered data from postgres.Example,my postgres database includes tables:
status (id int, name nchar, desc nchar)
Lead ( id int, phone_number char, Tsr_id int...)
tsr (id int, name nvarchar)
Phone_Call(id int, tsr_id int, duration float, status_id int, Lead_id)

So, should i build a dwh as:
status_dim ( id int, name nchar, desc nchar) tranfered data from status table in postgres to
Lead_dim ( id int, phone_number char, Tsr_id int...) tranfered data from lead table in postgres to
tsr_dim (id int, name nvarchar) tranfered data from tsr table in postgres to
PhoneCall_Fact( id uniqueidentifier, status_id int,lead_id int, tsr_id int, duration float)

or:
status_dim (code uniqueidentifier, id int, name nchar, desc nchar)
Lead_dim ( code uniqueidentifier, id int, phone_number char, Tsr_id int...)
tsr_dim (code uniqueidentifier, id int, name nvarchar)
PhoneCall_Fact( id uniqueidentifier, status_code uniqueidentifier,lead_code uniqueidentifier, tsr_code uniqueidentifier, duration float)

Please let me your advice to optimize the performance and the speed for my dwh. What is best way should i do in both of them?

thank you very much


trinhpv

Posts : 2
Join date : 2014-01-29

View user profile

Back to top Go down

Re: Relationship between fact table and dimension tables

Post  BoxesAndLines on Wed Jan 29, 2014 9:21 am

First one. The second one won't work since the FK's are not unique.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Relationship between fact table and dimension tables

Post  trinhpv on Wed Jan 29, 2014 11:57 am

BoxesAndLines wrote:First one.  The second one won't work since the FK's are not unique.

Thank for your reply! Could you advice me about the id of dimension tables?Example, should i use the id of status table from postgres and tranfering it into the id of status_dim or using the new id with Indentity for id of status_dim??
Thansks & Regards

trinhpv

Posts : 2
Join date : 2014-01-29

View user profile

Back to top Go down

Re: Relationship between fact table and dimension tables

Post  hkandpal on Wed Jan 29, 2014 12:09 pm

Hi ,

one way is to create a new seq id for the dimension table and for tracking you may keep the id fro the source table either in the stage or in the dimension as it will help in tracking any data quality issuses.

thanks

Himanshu

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Relationship between fact table and dimension tables

Post  BoxesAndLines on Wed Jan 29, 2014 2:12 pm

What hkandpal said. You don't want to use source system identifiers as your DW identifiers.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Relationship between fact table and dimension tables

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