Warehouse Logistics System, Sales and Activity

View previous topic View next topic Go down

Warehouse Logistics System, Sales and Activity

Post  apermag on Sat Dec 24, 2011 4:55 am

Howdy,

I have a question which is in my mind for some days while working in a new BI initiative within a company with a WLS.

They want to measure 2 areas: sales and activity, coming from the same source. Sales is the typical order to order line transactional system. Nothing special in here, as we have Order -< Order Line. But the problem is with the activity.

Usually, WLS systems split an order line in "splits". This is due to products with high volume and the total amount of units can not be together in one tote. This means that we have Order -< Order Line -< Split. But this happens around 5% times, I mean, usually the Order Line to Split is a 1 to 1 relationship.

The initial thought is: 2 different business processes, 2 different facts. But as time is passing, I am banging my head whether they should be together in one fact table. 95% dimensions are common between both facts, and both sales and activity are extremely related.

My concerns if I place everything into one table would be:

- amounts like "invoice amount", "cost amount" might have to be "replicated" if we want to analyze these amounts from both activity and sales angles (when talking about sales, we have the concept of "credits" given to customer when they return products. when talking about activity, credits donīt exist.

- count of order lines. The fact that in some small percentage there will be split granularity, how do I get the # Order Lines? I wouldnīt like to introduce a COUNT DISTINCT measure due to performance problems of the function itself.

But if we split the fact tables:

- same information is replicated in 90-95% cases. Waste of storage.
- when drilling across data between sales and activity, this will create 2 queries instead of one. Performance issue.
- if we want to analyze at an order level, unless we add a Degenerated Dimension conformed to both fact tables we wonīt be able to do it (order number is stored in the fact as it is just a number, there are no other details)

Well, my question is simple: whatīs your opinion about both cases?

Thanks, appreaciate any answer or advice on this.

apermag

Posts : 17
Join date : 2011-06-28

View user profile

Back to top Go down

Re: Warehouse Logistics System, Sales and Activity

Post  hang on Sat Dec 24, 2011 7:52 am

apermag wrote:- amounts like "invoice amount", "cost amount" might have to be "replicated"
Well if the additive measures are duplicated, it is the sign of mixing grains in the fact table. Separating grains in different fact table can avoid the risk of double counting. In your case, the degenerate dimension is the connection between two fact tables.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Warehouse Logistics System, Sales and Activity

Post  apermag on Sat Dec 24, 2011 1:52 pm

hang wrote:
Well if the additive measures are duplicated, it is the sign of mixing grains in the fact table. Separating grains in different fact table can avoid the risk of double counting. In your case, the degenerate dimension is the connection between two fact tables.

In this case, it wouldn't be a problem as they would be 3 columns associated with amounts. These amounts would be replicated anyway in separate fact tables. I meant to say that they might want to measure possible missing sales associated with the activity i.e. i couldn't take a product due to out of stock, so i can associate an amount on that.

My main concerns are more on the duplication of data. 95% of data would be an activity and a sale. just 5% of data would be "special" they way I mentioned. That's why I wouldn't like to replicate the same data between 2 fact tables while I can get benefits of having the same data in the same fact table. Also, both activity and sales measures will be queried together.

Thanks a lot for your answer hang.

apermag

Posts : 17
Join date : 2011-06-28

View user profile

Back to top Go down

Re: Warehouse Logistics System, Sales and Activity

Post  hang on Sat Dec 24, 2011 5:38 pm

I am not quite sure about why the 3 amounts have to be duplicated, unless they are order amount instead of item amount. However I still see mixed grains in your one table model in terms of multiple activities on one order item.

Can you store the activity count in the Order-Order Line fact table and store 5% multi-activity sales in the activity fact table with DD as conformed dimension, so that you may avoid the duplication and also be able to drill down to activities when the count is greater than 1.


hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Warehouse Logistics System, Sales and Activity

Post  apermag on Sun Dec 25, 2011 8:49 am

hang wrote:I am not quite sure about why the 3 amounts have to be duplicated, unless they are order amount instead of item amount. However I still see mixed grains in your one table model in terms of multiple activities on one order item.

Can you store the activity count in the Order-Order Line fact table and store 5% multi-activity sales in the activity fact table with DD as conformed dimension, so that you may avoid the duplication and also be able to drill down to activities when the count is greater than 1.

Thanks again hang

Forget about these 3 amounts, I've explained it very bad They are all order line/split amounts (depending on level). When I said about duplicating them is because we have the "credit" on it, and depending on how business wants to measure we would need to sum invoiced amount from the activity point of view (no credits) and from the sales point of view (with credits added), but this can be achieved by added a "Order Line is Credit Count" = 0 or 1 without having them in the same row but in different columns.

I am concerned as well about multiple activities for order line as they are different granularities, but i am concerned too of having 95% rows replicated in 2 fact tables and add DDs at an order & line level to drill across both of them, as they are going to be presented together most of the times. This means a huge amount of storage will be duplicated, and KPI's will have to be constructed using conformed dimensions when attacking both facts.

I can't believe I am thinking about this on Christmas day...


apermag

Posts : 17
Join date : 2011-06-28

View user profile

Back to top Go down

Re: Warehouse Logistics System, Sales and Activity

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