Can a FACT table contains Natural Primary keys and text columns

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

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

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

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.

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

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

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.

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

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

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

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.

Thank you Mr. BoxesAndLines

good advice