Dimension and Fact 1:1

View previous topic View next topic Go down

Dimension and Fact 1:1

Post  ozisamur on 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

View user profile

Back to top Go down

Re: Dimension and Fact 1:1

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

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

View user profile http://aginity.com

Back to top Go down

Re: Dimension and Fact 1:1

Post  ozisamur on 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

View user profile

Back to top Go down

Re: Dimension and Fact 1:1

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

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

View user profile http://aginity.com

Back to top Go down

Re: Dimension and Fact 1:1

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