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

Dimension and Fact 1:1

2 posters

Go down

Dimension and Fact 1:1 Empty Dimension and Fact 1:1

Post  ozisamur Thu Nov 13, 2014 1:31 pm

I have an Sales table. In the sales table OLTP system holds the Ship Address, Ship City, Ship District as textual attribute like this:

SALES

SALES_ID | CUSTOMER_ID | SHIP_ADDRESS_1 | SHIP_ADDRESS_2 | SHIP_CITY | SHIP_DISTRICT | SHIP_PHONE_NUMBER ...
1 | 1 | Xyz | Abc | KLM | Pxy | 44989702314
2 | 3 | Xyz | jbc | vLM | Pxy | 44121202314
3 | 1 | Xyz | gbc | nLM | PxZ | 44989002314
4 | 1 | Xyz | Abc | KLM | PUy | 44981202354


If I want to create a dimension like SHIP_ADDRESSES, I should get all distinct values related with the shipping ( SHIP_CITY, SHIP_DISTRICT ... ) then assign an ID.
Customers just filter by SHIP_CITY.

What do you suggest ?
1. Create a Factless Fact table which holds SALES_ID | SHIP_ADDRESS_1 | SHIP_ADDRESS_2 | SHIP_CITY .... ( just ship info and sales id )
2. Create a Dimension table : SHIP_ADDRESSES : SHIP_ADDRESS_SK | SHIP_ADDRESS_1 | SHIP_ADDRESS_2 .. And then in the fact table I should SHIP_ADDRESS_SK
3. Do nothing. Behave all ship attributes as degenerate dimension

Thanks.

ozisamur

Posts : 30
Join date : 2014-10-27

Back to top Go down

Dimension and Fact 1:1 Empty Re: Dimension and Fact 1:1

Post  ngalemmo Thu Nov 13, 2014 3:54 pm

Option 2 would be standard practice.

If you expect analytics to focus on city/district it is not uncommon to create an additional dimension for that (while preserving those attributes in the address dimension). It helps improve performance as queries would most frequently use the smaller dimension table.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Dimension and Fact 1:1 Empty Re: Dimension and Fact 1:1

Post  ozisamur Thu Nov 20, 2014 10:34 am

So, what should be the technique for dimension loading? Should I get Distinct all fact text columns and create a record then assign a SK to that record. Then this SK should be assigned in the fact table detail. Is this what should I do?

Or SK of dimension table ( address ) should be the ItemId of sales?

ozisamur

Posts : 30
Join date : 2014-10-27

Back to top Go down

Dimension and Fact 1:1 Empty Re: Dimension and Fact 1:1

Post  ngalemmo Thu Nov 20, 2014 2:27 pm

If address is coming in on the fact (not through a master data feed), then build it as a junk dimension where the address itself is the natural key. This gives you a table with each unique address and a surrogate PK which is used in the fact table.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Dimension and Fact 1:1 Empty Re: Dimension and Fact 1:1

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