Can a FACT table contains Natural Primary keys and text columns

View previous topic View next topic Go down

Can a FACT table contains Natural Primary keys and text columns

Post  samimusleh on Sun Nov 01, 2015 7:26 am

Dear all
We have in OLTP  system some tables have a composite primary key (up to 10 columns ) where some columns are date value others are sequence recreated every year ,
So in the design of the fact table there is a claim that it is possible to keep all primary keys columns in the fact table together with text descriptive columns instead of having a special dimension with huge number of records , where each fact record has a corresponding record in that dimension .
Let me explain this as follows :
1- we are in CUSTOMS , tracking Vehicles (ENTRY/EXIT) at many PORTS (land borders ) for both (CARGO/PASSENGERS)
2- Transactions classified into some CATEGORIES  (about 30 records ) , has a internal sequence number for  record.
3- The table is identified by a sequence number (TRANS_SEQ) - regenerated every year - and current date
4- each PORT has its own sequence , so the primary key is : PORT_CD , TRANS_SEQ, TRANS_DATE
5- number of records more than 90 millions record.

SO we have 2 option for DWH design :
Option 1 :
1- Our dimensions are : D_PORT , D_DATE , D_CATEGORIES , D_VEHICLS .
2- we have another dimension to keep transaction descriptive data : D_VHCL_TRANS, contains the PK columns , the internal sequence , DIRECTION (Entry/Exit) and TRANS_TYPE (Cargo/Passengers) . and it has the same number of record of the fact table (90 millions )
3- fact table has only FK for dimensions and FACT values .
Option 2 :
1- Our dimensions are : D_PORT , D_DATE , D_CATEGORIES , D_VEHICLS , D_DIRECTION (only has 2 values : Entry/Exit ) , D_TYPE (only 2 values : Cargo/Passengers ) .
2- fact table has FK for dimensions , FACT values and PK columns from original table and the internal sequence .

for Option 2 , the claim is it is not accepted to have this number of records in the dimension table  D_VHCL_TRANS

Please Advice Us ,  I am with Option 1

Thanks


Last edited by samimusleh on Mon Nov 02, 2015 11:43 am; edited 2 times in total

samimusleh

Posts : 23
Join date : 2013-03-03
Age : 51
Location : Saudi Arabia

View user profile

Back to top Go down

Re: Can a FACT table contains Natural Primary keys and text columns

Post  BoxesAndLines on Sun Nov 01, 2015 9:57 pm

Can you restate the question? I'm not sure what you are asking.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

more explanation

Post  samimusleh on Mon Nov 02, 2015 1:22 am

Suppose we have in our system the following table:
VHCL_TRANS (Vehicle Transaction ) with columns (PORT_CD N(3) ,TRANS_SEQ N(10) , TRANS_DATE , DIRECTION_CD N(1) , VHCL_CHASSIS C(15) , CARGO_PASS N(1) ,TRANS_CATEGORY_CD N(2) , ... )
The PK is (PORT_CD ,TRANS_SEQ , TRANS_DATE ) , where TRANS_SEQ is a sequence generated every year
regardless of the FACTS for this table , we have designed the FACT table as :
F_VHCL_TRANS (DATE_SRGT N , PORT_SRGT N , DIRECTION_SRGT N , CARGO_PASS_SRGT , PORT_CD N(3) ,TRANS_SEQ N(10) , TRANS_DATE ,VHCL_CHASSIS C(15) , TRANS_CATEGORY_DESC C(30) , ... , FACT_1 , FACT_2 )
where :
- columns end with _SRGT : is a FK for the related DIMENSION >
- PK columns of original table is kept in the FACT table (PORT_CD ,TRANS_SEQ , TRANS_DATE ) as is >
- VHCL_CHASSIS considered as DEGENERATE dimension ( but it is repeted , not unique to the record )
- TRANS_CATEGORY_DESC is a text and considered as DEGENERATE dimension.
- note that the PORT_CD in the original table in kept twice in the FACT , first as FK to relevant Dimension (PORT_SRGT ) , and again with original value (PORT_CD )

I hope this is a clear description to my problem
Thanks

samimusleh

Posts : 23
Join date : 2013-03-03
Age : 51
Location : Saudi Arabia

View user profile

Back to top Go down

Re: Can a FACT table contains Natural Primary keys and text columns

Post  nick_white on Mon Nov 02, 2015 8:41 am

Everything in you Fact table that is not a measure should either be a numeric surrogate key or a degenerate dimension.

I don't understand why you have PORT_CD at all, let alone twice. You should just have the surrogate key to PORT_SRGT Dimension - and you shouldn't be re-using the natural key (PORT_CD) as the SK even if it is numeric.
TRANS_DATE should be an SK to your date dim.
Do you not have additional vehicle attributes apart from the chassis number? If you do I would consider putting them all in a Vehicle Dim
TRANS_CATEGORY_DESC - is this just free text or is it constrained to a set of values? I would suggest that 30 chars is the at the top end of what I would put in a Fact DD column so (if you have no other Category attributes that you could use to create a Category Dim) I would consider putting this in a junk text dimension - especially if it is going to be rarely queried for

I'm still not entirely sure exactly what the issue is that your asking for help on but it seems to be that you are trying to replicate a single record in your source system as a single record in your target system. If that is the case then it's probably the wrong approach as a record in an OLTP system naturally breaks down to multiple Dimensional records and a fact record in a Dimensional model.

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Can a FACT table contains Natural Primary keys and text columns

Post  samimusleh on Mon Nov 02, 2015 11:16 am

Dear Mr. nick_white
Thank you , you have been helpful
I have Edited the original message with more explanation , Please refer to the beginning , and sorry for not being clear enough

Thanks

samimusleh

Posts : 23
Join date : 2013-03-03
Age : 51
Location : Saudi Arabia

View user profile

Back to top Go down

Re: Can a FACT table contains Natural Primary keys and text columns

Post  samimusleh on Mon Nov 02, 2015 11:19 am

BoxesAndLines wrote:Can you restate the question?  I'm not sure what you are asking.

Dear Mr. BoxesAndLines
I have edited the original message , Please kindly refer to the beginning for more explanation

Thanks

samimusleh

Posts : 23
Join date : 2013-03-03
Age : 51
Location : Saudi Arabia

View user profile

Back to top Go down

Re: Can a FACT table contains Natural Primary keys and text columns

Post  nick_white on Mon Nov 02, 2015 4:36 pm

I would make DIRECTION (Entry/Exit) and TRANS_TYPE (Cargo/Passengers) as degenerate dimensions if each field can only have 2 values. Alternatively you could put both in a single junk dimension as it would only have 4 records.
Do you actually have a reporting requirement that uses the transaction number? If you do, please can you explain what the reporting requirement is and how you intend to use this attribute?

If you don't need it (and you move the two other attributes to their own dimension) then I think the need for this large dimension goes away.

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Can a FACT table contains Natural Primary keys and text columns

Post  samimusleh on Tue Nov 03, 2015 12:39 am

Mr. nick_white
for the transaction number , we need the natural key ( Original primary key ) values to refer to the original record at it's source table .

Thanks

samimusleh

Posts : 23
Join date : 2013-03-03
Age : 51
Location : Saudi Arabia

View user profile

Back to top Go down

Re: Can a FACT table contains Natural Primary keys and text columns

Post  nick_white on Tue Nov 03, 2015 4:22 am

Do you need the natural key because you have to update the fact table? If not, then what is it being used for?
Assuming you just need it to update the fact table, then the following is probably a solution:
In your staging area create a permanent lookup table that holds the compound natural key and a generated SK
Add this generated SK as a column to your fact table - where it will act as the PK for the table

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Can a FACT table contains Natural Primary keys and text columns

Post  samimusleh on Tue Nov 03, 2015 5:55 am

Mr. nick_white
Thank you , This is convenient and a good solution


samimusleh

Posts : 23
Join date : 2013-03-03
Age : 51
Location : Saudi Arabia

View user profile

Back to top Go down

Re: Can a FACT table contains Natural Primary keys and text columns

Post  BoxesAndLines on Tue Nov 03, 2015 8:55 am

Don't forget to redesign your vehicle dimension after you do this. You really shouldn't have a 1-1 relationship between the fact and dimension tables. Anytime I see the word transaction in a dimension, it's a red flag.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Can a FACT table contains Natural Primary keys and text columns

Post  samimusleh on Tue Nov 03, 2015 9:05 am

Thank you Mr. BoxesAndLines

good advice

samimusleh

Posts : 23
Join date : 2013-03-03
Age : 51
Location : Saudi Arabia

View user profile

Back to top Go down

Re: Can a FACT table contains Natural Primary keys and text columns

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