Dimension more records than fact

View previous topic View next topic Go down

Dimension more records than fact

Post  vee_jess on Mon Oct 27, 2014 6:40 am

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

vee_jess

Posts : 7
Join date : 2014-10-27

View user profile

Back to top Go down

Re: Dimension more records than fact

Post  vee_jess on Mon Oct 27, 2014 12:19 pm

Any help on this please?

vee_jess

Posts : 7
Join date : 2014-10-27

View user profile

Back to top Go down

Re: Dimension more records than fact

Post  BoxesAndLines on Mon Oct 27, 2014 3:37 pm

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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Dimension more records than fact

Post  vee_jess on Mon Oct 27, 2014 4:18 pm

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.

vee_jess

Posts : 7
Join date : 2014-10-27

View user profile

Back to top Go down

Re: Dimension more records than fact

Post  ngalemmo on Mon Oct 27, 2014 4:49 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Dimension more records than fact

Post  vee_jess on Mon Oct 27, 2014 5:05 pm

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?

vee_jess

Posts : 7
Join date : 2014-10-27

View user profile

Back to top Go down

Re: Dimension more records than fact

Post  ngalemmo on Mon Oct 27, 2014 5:54 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Dimension more records than fact

Post  BoxesAndLines on Mon Oct 27, 2014 10:45 pm

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

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

View user profile

Back to top Go down

Re: Dimension more records than fact

Post  vee_jess on Tue Oct 28, 2014 4:21 am

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.

vee_jess

Posts : 7
Join date : 2014-10-27

View user profile

Back to top Go down

Re: Dimension more records than fact

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