Concatenated fact table with BOM

[HarryLouis]

Hi,

I have posted a similar question a while ago but haven't gotten any responses and found a workaround since, but am still eager to learn what people think would be the best practice in my below example.

The case is a manufacturing site that manufactures parts according to a simple, one-level, bill of material and I'm trying to work out if I should concatenate or link the bill of material.

orders:

Product	Quantity
A	500
B	100

Bill of material:

Product	rm_product	use_qty
A	aa	1
A	bb	5
B	bb	5

rm_inventory

rm_product	rm_qty
aa	1000
bb	100

The orders and rm_inventory tables are initially in fact one table, like below:

Product	Qty
A	500
B	100
aa	1000
bb	100

About 10 more fact tables will further be linked in to this, which is why I personally prefer to go with a large concatenated fact table over a link table. My question is twofold; 1) can it be done as a concatenated fact table with linked dimensions and 2) what would be your preference in this example case?

I would be enormously appreciative of your inputs since I have been breaking my head on this for ages and have the feeling i'm missing something very obvious :).

Thanks in advance,

HL

[Gabbar]

Concatenated Table would make it difficult while writing set expressions, 
I personally prefer linking if the table.

[HarryLouis]

Thanks Gabbar, much appreciated. How would you go about it though? I manage to get the proper output when building link table for just the product and rm_product fields. The tricky part comes that I have a date field in the orders table that I also want to move to the link table (for linking correctly later to Forecast / Budget etc.). The table then results in a table like below:

product	order_qty	rm_product	rm_qty
A	500	-	0
A		aa	1000
A		bb	100

While my expected output in the sheet view (without applying aggr. functions) is like below:

product	order_qty	rm_product	rm_qty
A	500	aa	1000
A	500	bb	100

My code currently is below ( a bit messy due to trying different ways of adding in the date from orders):

[BOM]:
Load 
product&'-'&rm_product AS %bom_id,
rm_product AS %rm_id,
product AS %product_id,
product AS PN,
rm_product,
rm_default_qty,
usage_last_6m/prod_last_6m AS ratio
from [lib://***/m_Bill_of_Material.qvd] (qvd);

rm_stock:
load
PN AS %rm_id,
PN AS rm_product,
Qty AS rm_qty
from [lib://***/m_stock.qvd] (qvd);

orders_tmp:
Load
PN AS %product_id,
receive_date&'-'&ship_date AS %date_id,
PN, 
receive_date,
ship_date,
Qty
from */m_orders.qvd] (qvd);

orders:
Load 
%product_id&'-'&%date_id AS %order_date_id,
* 
resident orders_tmp;
drop table orders_tmp;

calendar:
crosstable('date_type',c_date) load distinct %date_id,receive_date AS rcv,ship_date AS ship resident orders;

link_table:
Load distinct 
%bom_id,
%rm_id,
%product_id,
PN,
rm_product
resident BOM;

Concatenate(link_table)
Load distinct %rm_id,rm_product resident rm_stock;

concatenate(link_table)
Load distinct PN,%product_id,%date_id,%order_date_id resident orders;

drop fields %rm_id,%product_id from BOM;
drop fields PN from BOM,orders;
drop fields rm_product from BOM,rm_stock;
drop field %date_id,%product_id from orders;

Thanks, any help is very much appreciated. I have the feeling I'm overlooking something very simple / straightforward.

[Gabbar]

I hope all values of product and rm_product are present in [bill of materials] Table
and also that PN in order table is the Product(little confused in this regard) ;

orders_tmp:
Load
PN AS %product_id,
receive_date&'-'&ship_date AS %date_id,
PN,
receive_date,
ship_date,
Qty
from */m_orders.qvd] (qvd); 
inner Join(orders_tmp)
load  product as %product_id 
from [lib://***/m_Bill_of_Material.qvd] (qvd);



noconcatenate
rm_stock:
load
PN AS %rm_id,
PN AS rm_product,
Qty AS rm_qty
from [lib://***/m_stock.qvd] (qvd);
inner Join(rm_stock)
load  rm_product 
from [lib://***/m_Bill_of_Material.qvd] (qvd);

noconcatenate
[BOM]:
Load
product&'-'&rm_product AS %bom_id,
rm_product AS %rm_id,
product AS %product_id,
product AS PN,
rm_product,
rm_default_qty,
usage_last_6m/prod_last_6m AS ratio
from [lib://***/m_Bill_of_Material.qvd] (qvd);


PS: This is my output from the data you mentioned.

this is how model will look

please accept this as solution, if your query is resolved.

[HarryLouis]

Thanks again.

I have managed to produce a similar, working output (in fact it was the work-around I've used in the past), but where I am having troubles is to develop the model further from here.

For example, after this I am trying to add an Order forecast table in the model, where both the table orders and forecast have a date field. When I bring both date fields to the central table, the PN / rm_product table is not represented in the manner expected any more. Hope this is clearer.

[Gabbar]

Are all the fields that are present in order table also present in order_forecast table, and if all field are there, is there also the forcasting of rm_product rows?

Can you share some sample values of the forecasting table.

[HarryLouis]

Hi Gabbar, the fields present in the order table match the fields in the order forecast table (product and qty). The rm_forecast is generated later by multiplying orders + order_FC with BOM qty. FC table may look for example like.

product	Quarter	FC_qty
A	Q1-2023	1000
A	Q2-2023	1200
B	Q1-2023	700
B	Q2-2023	750

Thanks,

Harry

[Gabbar]

You can just conatenate it in main order table (after its bifurication in order and rm_stock) and add a field as follows :
Load *,'Historical' as DATATYPE resident order;
Concatenate
Load *, 'Forecast' as DATATYPE from forecast;