Dimension more records than fact

Dear Experts,

I am in the process of creating a dimensional model and I'm a bit stuck. Hopefully with your help I will get through this.

The business is about delivery management system.

So far I have 2 fact tables at different grains.
1. Consignment (a group of parcels) or an Order. The measures are delivery value, weight, height, etc
2. Parcel (a group of products). The measures are value, weight, height, etc

Dimension tables I have
1. Retailer
2. Calendar
3. Geography
4. Carrier
4. Consignment
5. Consignment Status
6. Parcel
7. Parcel Status
8. Depot
9. Warehouse

Now, there table called 2 tables that are used for reporting but I am not sure how to model them in because of their grain!

1. Consignment Property. A consignment has many property and this will have a lot more records than the Consignment fact
2. Parcel Status history. A Parcel will have many statuses once it leaves the warehouse until it reaches the consumer.

Can someone please help me on how to design this?

Thanks,
Jay

Any help on this please?

Property, as in address, is a dimension.

Status history can be modeled as a fact (transaction or accumulating snapshot) or as a type 2 dimension. With what you provided, this is the best I can answer.

Thank you for your reply.

If property is a dimension then the fact table to this dimension will be 1-m. Should it not be the other way round, as in dimension to fact relationship is 1-m? The fact grain is at consignment level and a consignment has many properties. I cannot change the grain to be at property level because that will explode the fact table to about 2 billion records.

Status history can possibly be a factless fact i guess but i'm not sure!

If you need more information please let me knwo.

Thanks.

What do you mean by consignment properties? Are these fixed (can they be attributes of consignment?) or do they vary? If it is the latter case it is usually implemented as a bridge between the fact and a properties dimension.

As far a status history goes, this is usually implemented as a separate fact table with an entry for each change in status with appropriate dimensions.

Consignment properties are more like the different places (the route) the consignment has been since leaving the warehouse until it reaches the consumer. The carrier takes the consigment from the retailers warehouse to the consumer via many intermediate destination. Those intermediate destination are the consignment properties. If it's going to be a bridge, what fields does this table contain? Does the granularity of the bridge table matter? Can this also be a separate fact table similar to the status history, without any measure?

Then it would be similar to the package status history. Each movement of the consignment is an event. The fact would be a collection of those events.

What Nick said, capture the data at the lowest grain. You can always aggregate up.

So, I will now have 4 fact tables
1. Consignment
2. Consignment Property - Factless but more granular than Consignment, use to track the route the consignment has taken before reaching destination
3. Parcel
4. Parcel Status history - Factless but more granular than Parcel, use to track the history of the different statuses the parcel has been through

And a bunch of conformed dimensions for all the fact tables.

Please let me know if this the right approach?

Thank you all for your replies,
J.