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

Relationship between fact table and dimension tables

3 posters

Go down

Relationship between fact table and dimension tables Empty Relationship between fact table and dimension tables

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

Back to top Go down

Relationship between fact table and dimension tables Empty Re: Relationship between fact table and dimension tables

Post  BoxesAndLines Wed Jan 29, 2014 9:21 am

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

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

Back to top Go down

Relationship between fact table and dimension tables Empty Re: Relationship between fact table and dimension tables

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

Back to top Go down

Relationship between fact table and dimension tables Empty Re: Relationship between fact table and dimension tables

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

Back to top Go down

Relationship between fact table and dimension tables Empty Re: Relationship between fact table and dimension tables

Post  BoxesAndLines Wed Jan 29, 2014 2:12 pm

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

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

Back to top Go down

Relationship between fact table and dimension tables Empty Re: Relationship between fact table and dimension tables

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