Warehouse Logistics System, Sales and Activity
2 posters
Page 1 of 1
Warehouse Logistics System, Sales and Activity
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.
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
Re: Warehouse Logistics System, Sales and Activity
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.apermag wrote:- amounts like "invoice amount", "cost amount" might have to be "replicated"
hang- Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia
Re: Warehouse Logistics System, Sales and Activity
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
Re: Warehouse Logistics System, Sales and Activity
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.
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
Re: Warehouse Logistics System, Sales and Activity
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
Similar topics
» In Sales system how to design dimensions where city describes Order and Customer
» Modeling Invoice Level Sales With a Volatile Sales Org
» data warehouse and data warehouse system
» Is it a best practice that Data warehouse follows the source system data type?
» Dimensional modelling for Sales and Marketing (opportunity, quotes, sales orders)
» Modeling Invoice Level Sales With a Volatile Sales Org
» data warehouse and data warehouse system
» Is it a best practice that Data warehouse follows the source system data type?
» Dimensional modelling for Sales and Marketing (opportunity, quotes, sales orders)
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|