Fact 1:1 relationship with dimension

[vee_jess]

Hello,

The above is a dimensional model I am working on based on the scenario. I have few questions and will appreciate if someone can help.

1. DimConsignment and FactConsignment will have the same number of records. Not sure if it’s correct to have a fact and a dimension table with the same number of records! If not, what’s the best way to model this? There is also similar relationship between FactParcel and DimParcel.
2. When a factless fact table shares a 1-1 relationship to a dimension table, is it worth having that fact table? Example above would be the FactManifest and DimManifest.
3. Is there anything you would change in the above model to make it better?

[ngalemmo]

What are the attributes of DIM_CONSIGNMENT?

The basic technique is to reduce the contents of the consignment dimension to multiple smaller dimensions and reference those dimensions in the fact. The consignment is reduced to a degenerate dimension (consignment ID).

[vee_jess]

In the current OLTP system the consignment table is the main table and it contains lots of fields. I have tried to separate those fields into their own dimensions and also created a FactConsignment table but there some that, i believe need to be in the DimConsignment table. These are
ConsignmentCode,
consignmentWeight,

CreatedDateTime,
ProcessedDateTime,
DespatchDateTim,
StartPostcoe,
EndPostcode,
OrderDate
OrderNumber,
Etc

[Sponsored content]