Dimension and Fact 1:1
2 posters
Page 1 of 1
Dimension and Fact 1:1
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.
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
Re: Dimension and Fact 1:1
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.
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.
Re: Dimension and Fact 1:1
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?
Or SK of dimension table ( address ) should be the ItemId of sales?
ozisamur- Posts : 30
Join date : 2014-10-27
Re: Dimension and Fact 1:1
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.

» Dimension Design with intermediate tables between fact and dimension
» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|