October 27, 2011 at 4:49 am
Hi,
I'm sure I'm nearly there, but can't figure out the last little piece of my puzzle.
I've done a fair bit of searching, and the closest I seem to have come to something approaching my problem is http://www.sqlservercentral.com/Forums/Topic1194903-338-1.aspx, but my case is subtly different, and after looking at this problem for several hours already, I'm struggling to apply the principles to my problem. I'm about to pack up for the night, and will continue tomorrow, but I thought that this *must* have general application, so it may be worth asking the experts 🙂
Additionally, perhaps being on SQL 2008 will provide options for using the hierarchy data (which I'm about to start researching), which isn't covered in any of the topics I *have* found (although I hadn't been looking for them until I saw the discussion mentioned above).
Background is a query for my client, a food manufacturer, they have 'raw materials' (ingredients). They also have 'blends' (pre-mixed combinations of raw materials and/or other blends). Then they have finished product, which are really just special 'blends' that include packaging as part of the bill of materials. So a pretty conventional bill-of-materials hierarchy.
What makes this interesting, and where I've got stuck, is this: The raw materials have nutritional information (e.g. protein, fat, vitamins, minerals, etc; currently around 45 parameters), that they want to total up for their blends (and finished products). So what I have so far:
IF OBJECT_ID('tempdb..#material','U') IS NOT NULL DROP TABLE #material;
IF OBJECT_ID('tempdb..#bom','U') IS NOT NULL DROP TABLE #bom;
create table #material(
name char(2) not null default '' primary key clustered,
v1 float not null default 0,
v2 float not null default 0,
v3 float not null default 0,
v4 float not null default 0
)
go
insert #material(name,v1,v2,v3,v4)
values
('f1', 0, 0, 0, 0),
('f2', 0, 0, 0, 0),
('m1', 0, 0, 0, 0),
('m2', 0, 0, 0, 0),
('r1', 0.8, 0, 0.1, 0.1),
('r2', 0.4, 0.4, 0.1, 0.1),
('r3', 0.1, 0.5, 0.2, 0.2),
('r4', 0.2, 0.5, 0.2, 0.1)
go
create table #bom(
material_name char(2) not null,
component_name char(2) not null,
quantity float not null,
)
go
insert #bom(material_name,component_name,quantity)
values
('m1', 'r1', 1),
('m1', 'r2', 1),
('m1', 'r3', 3),
('m2', 'r2', 2),
('m2', 'r3', 2),
('f1', 'b1', 1),
('f1', 'r4', 1),
('f2', 'm1', 1),
('f2', 'm2', 1),
('f2', 'r4', 2)
go
select * from #material;
select * from #bom;
go
WITH BomComponents(material_name, component_name, quantity, v1, v2, v3, v4, level, path)
AS
(
-- Anchor member definition
SELECT m.name as material_name, CONVERT(char(2), null) as component_name, convert(float,1) as quantity, m.v1, m.v2, m.v3, m.v4, 0 as level, convert(nvarchar(40),m.name) as path
FROM #material m where name like 'r%' -- raw materials
UNION ALL
-- Recursive member definition
SELECT b.material_name, b.component_name, b.quantity, b.quantity * m.v1, b.quantity * m.v2, b.quantity * m.v3, b.quantity * m.v4
,Level + 1, convert(nvarchar(40), path + '/' + b.material_name)
FROM #bom b
INNER JOIN #material m on b.component_name = m.name
INNER JOIN BomComponents bc on b.component_name = bc.material_name
WHERE
level < 5
)
-- Statement that executes the CTE
SELECT bc.material_name, bc.component_name
, sum(bc.v1) as v1, sum(bc.v2) as v2, sum(bc.v3) as v3, sum(bc.v4) as v4
, bc.path
FROM BomComponents bc
GROUP BY bc.material_name, bc.component_name, bc.path
ORDER BY bc.path
;
WITH BomComponents(material_name, component_name, level, path)
AS
(
-- Anchor member definition
SELECT name as material_name, name as component_name, 0 as level, convert(nvarchar(40), name) as path
FROM #material
WHERE name like 'f%' -- finished product
UNION ALL
-- Recursive member definition
SELECT b.material_name, b.component_name, level + 1, convert(nvarchar(40), path + '/' + b.component_name)
FROM #bom b
INNER JOIN BomComponents bc on bc.component_name = b.material_name
WHERE
level < 5
)
-- Statement that executes the CTE
SELECT bc.material_name, bc.component_name, bc.path, level
FROM BomComponents bc
ORDER BY bc.material_name, bc.component_name
which produces this output:
(8 row(s) affected)
(10 row(s) affected)
name v1 v2 v3 v4
---- ---------------------- ---------------------- ---------------------- ----------------------
f1 0 0 0 0
f2 0 0 0 0
m1 0 0 0 0
m2 0 0 0 0
r1 0.8 0 0.1 0.1
r2 0.4 0.4 0.1 0.1
r3 0.1 0.5 0.2 0.2
r4 0.2 0.5 0.2 0.1
(8 row(s) affected)
material_name component_name quantity
------------- -------------- ----------------------
m1 r1 1
m1 r2 1
m1 r3 3
m2 r2 2
m2 r3 2
f1 b1 1
f1 r4 1
f2 m1 1
f2 m2 1
f2 r4 2
(10 row(s) affected)
material_name component_name v1 v2 v3 v4 path
------------- -------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------------------------
r1 NULL 0.8 0 0.1 0.1 r1
m1 r1 0.8 0 0.1 0.1 r1/m1
f2 m1 0 0 0 0 r1/m1/f2
r2 NULL 0.4 0.4 0.1 0.1 r2
m1 r2 0.4 0.4 0.1 0.1 r2/m1
f2 m1 0 0 0 0 r2/m1/f2
m2 r2 0.8 0.8 0.2 0.2 r2/m2
f2 m2 0 0 0 0 r2/m2/f2
r3 NULL 0.1 0.5 0.2 0.2 r3
m1 r3 0.3 1.5 0.6 0.6 r3/m1
f2 m1 0 0 0 0 r3/m1/f2
m2 r3 0.2 1 0.4 0.4 r3/m2
f2 m2 0 0 0 0 r3/m2/f2
r4 NULL 0.2 0.5 0.2 0.1 r4
f1 r4 0.2 0.5 0.2 0.1 r4/f1
f2 r4 0.4 1 0.4 0.2 r4/f2
(16 row(s) affected)
material_name component_name path level
------------- -------------- ---------------------------------------- -----------
f1 b1 f1/b1 1
f1 f1 f1 0
f1 r4 f1/r4 1
f2 f2 f2 0
f2 m1 f2/m1 1
f2 m2 f2/m2 1
f2 r4 f2/r4 1
m1 r1 f2/m1/r1 2
m1 r2 f2/m1/r2 2
m1 r3 f2/m1/r3 2
m2 r2 f2/m2/r2 2
m2 r3 f2/m2/r3 2
(12 row(s) affected)
Raw materials are 'r%', blends are 'm%' (m == mix) and finished products are 'f%'. (this is similar to how the client actually codes their ingredients).
For simplicity, nutritional information is called v1-v4. In practice there are 40-something with actual names (like "Phylloquinione"). 'r%' products have values, we need to calculate the rest from these. I.e. I need to come up with a query that calculates the correct values for v1-v4 for all items that are not 'raw materials'. With that done, I could then (optionally) update the material table with the correct values by calling an appropriate stored procedure from the front-end.
The last query in my code is for my front-end 'tree view' of the BoM hierarchy; I had that going before attempting to calculate the totals.
At one point I thought I'd figured out that I need to do the calculations 'in reverse', which is how I got to the second-last query; I thought I'd be able to build up the totals by working from 'raw materials up', rather than 'finished product down' (does that make sense?).
Lastly, I do have some control over the data structures I am working with; the material codes, names and BoM structure are sucked in from an ERP system that I *don't* have control over, but other than that, it can be massaged any way that helps.
thanks in advance,
October 28, 2011 at 2:19 am
Version 2. getting warmer.
I now have a handle on how the code in the other article works.
So I made a few changes to the test data, and tried again.
/*
-- create & populate tally table
*/
IF OBJECT_ID('tempdb..#tally','U') IS NOT NULL DROP TABLE #tally;
select top(10000) IDENTITY(int,1,1) as N into #tally from master.sys.columns x, master.sys.columns y
/*
-- create & populate the 'materials' table
*/
IF OBJECT_ID('tempdb..#material','U') IS NOT NULL DROP TABLE #material;
create table #material(
id int not null primary key clustered,
name char(2),
v1 float null ,
v2 float null ,
v3 float null ,
v4 float null
)
;
insert #material(id, name,v1,v2,v3,v4)
values
-- finished goods
(1, 'f1', null, null, null, null),
(2, 'f2', null, null, null, null),
(3, 'f3', null, null, null, null),
-- blends (mixtures)
(1001, 'm1', null, null, null, null),
(1002, 'm2', null, null, null, null),
(1003, 'm3', null, null, null, null),
(1004, 'mX', null, null, null, null),
-- raw materials; I made the v1-v4 values add up to 1 to provide myself a simple sanity check
-- by using only whole numbers for quantities in the bom, the final v1-v4 should also add up
-- to a whole number
(5001,'r1', 0.8, 0.0, 0.1, 0.1),
(5002,'r2', 0.4, 0.4, 0.1, 0.1),
(5003,'r3', 0.1, 0.5, 0.2, 0.2),
(5004,'r4', 0.2, 0.5, 0.2, 0.1)
;
/*
create & populate the 'bill of materials' table
*/
IF OBJECT_ID('tempdb..#bom','U') IS NOT NULL DROP TABLE #bom;
create table #bom(
material_id int,
component_id int,
quantity float,
)
;
-- for later sanity checking, all 'blends' must add up to 10, 'finished products' must add up to 100.
insert #bom(material_id, component_id, quantity)
values
-- 'pure blends' - mix of raw materials only.
(1001, 5001, 2),
(1001, 5002, 3),
(1001, 5003, 5),
(1002, 5002, 4),
(1002, 5003, 6),
(1003, 5001, 5),
(1003, 5004, 5),
(1004, 5001, 1),
(1004, 5003, 9),
-- 'finished products'
-- mix of blends and raw materials
(1, 1001, 50),
(1, 5004, 50),
-- blends only
(2, 1001, 20),
(2, 1002, 80),
(2, 1003, 40),
-- raw materials only
(3, 5001, 20),
(3, 5002, 40),
(3, 5004, 40)
;
/*
create a table that appends the v* parameters to the bom data, and describes a hierarchy path for each record.
in an actual database, this could be implemented as a view rather than populating a temporary table
*/
IF OBJECT_ID('tempdb..#bomplus','U') IS NOT NULL DROP TABLE #bomplus;
WITH BomExtender
AS
(
SELECT -- Anchor member definition
convert(int, null) as material_id
, convert(char(2), null) as material_name
, id as component_id
, name as component_name
, 1 as level
, CONVERT(varbinary(20), CONVERT(binary(2),id)) as path
, CONVERT(varchar(20), id) as humanpath
, CAST(null as float) as quantity
, v1, v2, v3, v4
FROM #material
WHERE id in (select material_id from #bom) --
-- name like 'f%' -- finished product
UNION ALL
SELECT -- Recursive member definition
b.material_id as id
, mm.name
, b.component_id
, mc.name
, level + 1
, CONVERT(varbinary(20), path + CONVERT(binary(2), (b.component_id))) as path
, CONVERT(varchar(20), humanpath + '.' + CONVERT(varchar(20), b.component_id)) as humanpath
, b.quantity
, b.quantity * mc.v1 as v1
, b.quantity * mc.v2 as v2
, b.quantity * mc.v3 as v3
, b.quantity * mc.v4 as v4
FROM #bom b
INNER JOIN BomExtender bc on b.material_id = bc.component_id
INNER JOIN #material mc on mc.id = b.component_id
INNER JOIN #material mm on mm.id = b.material_id
WHERE
level < 10 -- stop the madness! (sanity check)
)
-- Statement that executes the CTE
SELECT bc.material_id, bc.material_name, bc.component_id, bc.component_name, level, path, humanpath, quantity, v1, v2, v3, v4
INTO #bomplus
FROM BomExtender bc
ORDER BY level, path
select * from #material;
select * from #bom;
select * from #bomplus;
-- display 'source' data for cte split query below
SELECT CAST(SUBSTRING(h.path,(t.N*2)-1,2) AS int) AS ID,
h.v1, h.v2, h.v3, h.v4,
h.path, h.humanpath, h.material_id
FROM #tally AS t WITH(NOLOCK)
CROSS JOIN #bomplus AS h WITH(TABLOCKX)
WHERE t.N BETWEEN 1 AND DATALENGTH(h.path)/2
;
-- the split query which works so well on neat tree structure, but not so well on a BoM!
WITH cteSplit AS
( --=== Splits the path into elements so we can aggregate costs by ID
SELECT CAST(SUBSTRING(h.path,(t.N*2)-1,2) AS int) AS ID,
h.quantity, h.v1, h.v2, h.v3, h.v4, h.v1 + h.v2 + h.v3 + h.v4 as vall
FROM #tally AS t WITH(NOLOCK)
CROSS JOIN #bomplus AS h WITH(TABLOCKX)
WHERE t.N BETWEEN 1 AND DATALENGTH(h.path)/2
)
,
cteAggregate AS
( --=== Does the required totals by ID.
SELECT ID
,SUM(quantity) as quantity
,SUM(v1) AS v1
,SUM(v2) AS v2
,SUM(v3) AS v3
,SUM(v4) AS v4
,SUM(vall) AS vall
FROM cteSplit
GROUP BY ID
) --=== Display the answer as requested.
SELECT #bomplus.component_id
,#bomplus.component_name
,agg.quantity,agg.v1,agg.v2, agg.v3, agg.v4, agg.vall
FROM cteAggregate agg
INNER JOIN #bomplus
ON agg.ID = #bomplus.component_id
WHERE agg.ID > 0
ORDER BY #bomplus.component_name
I've got some numbers, but they're wrong, and I had a whole lot of duplicates, which I've filtered out by adding a DISTINCT clause for this output:
component_id component_name quantity v1 v2 v3 v4 vall
------------ -------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
1 f1 110 13.3 28.7 11.5 6.5 60
2 f2 170 10.5 10.8 4.6 4.1 30
3 f3 100 40 36 14 10 100
1001 m1 100 9.9 11.1 4.5 4.5 30
1002 m2 100 4.4 9.2 3.2 3.2 20
1003 m3 60 10 5 3 2 20
1004 mX 10 1.7 4.5 1.9 1.9 10
5001 r1 37 29.6 0 3.7 3.7 37
5002 r2 57 22.8 22.8 5.7 5.7 57
5003 r3 36 3.6 18 7.2 7.2 36
5004 r4 100 20 50 20 10 100
On closer inspection however, some of them looked promising.
If I look at just a single, simple product:
WITH cteSplit AS
( --=== Splits the path into elements so we can aggregate costs by ID
SELECT CAST(SUBSTRING(h.path,(t.N*2)-1,2) AS int) AS ID,
h.quantity, h.v1, h.v2, h.v3, h.v4, h.v1 + h.v2 + h.v3 + h.v4 as vall
FROM #tally AS t WITH(NOLOCK)
CROSS JOIN #bomplus AS h WITH(TABLOCKX)
WHERE t.N BETWEEN 1 AND DATALENGTH(h.path)/2
AND SUBSTRING(h.path, 1, 2) = 3
)
,
cteAggregate AS
(
SELECT ID
,SUM(quantity) as quantity
,SUM(v1) AS v1
,SUM(v2) AS v2
,SUM(v3) AS v3
,SUM(v4) AS v4
,SUM(vall) AS vall
FROM cteSplit
GROUP BY ID
)
SELECT
DISTINCT
#bomplus.component_id
,#bomplus.component_name
,agg.quantity,agg.v1,agg.v2, agg.v3, agg.v4, agg.vall
FROM cteAggregate agg
INNER JOIN #bomplus
ON agg.ID = #bomplus.component_id
WHERE agg.ID > 0
I get the right numbers:
component_id component_name quantity v1 v2 v3 v4 vall
------------ -------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
3 f3 100 40 36 14 10 100
5001 r1 20 16 0 2 2 20
5002 r2 40 16 16 4 4 40
5004 r4 40 8 20 8 4 40
Further looking at the intermediate data, and comparing it with Jeff Moden's example (referenced in my original post), I think I have figured out what my problem is.
The simple case above is a 'pure' top-down hierarchy, which is a close analog to the typical 'Sales Figures' example, where each component is only used by one parent, and so the numbers add up correctly.
However, my case is a 'matrix', components can be used by multiple parents, and the numbers involved are affected by a 'quantity per' value, so the default grouping by 'ID' isn't sufficient.
Gonna have to pack up for the day again. Will tackle this again tomorrow.
October 30, 2011 at 6:31 pm
Success !! (well, I'm about 99% sure I have found success, I still have some further testing to do, on 'real' data)
Amazing what a weekend away from a problem can do; within half an hour of looking at this this (Monday) morning, I figured it out.
Turns out it was *simpler* that I thought it would be (which oxymoronically, is kind-of what I expected). I didn't even need to use Jeff's tally-table technique, although without trying Jeff's technique against my data, I would never have seen the number patterns (in the previous posts in this thread) that led me to the solution.
I leave my solution here for the next soul who is looking for a bill of materials solution like mine.
In a nutshell:
o For each component, we need to multiply the (component) 'quantity' by the 'quantity' of its parent (the item one level up the hierarch) to get the *overall* multiplier for each variable.
o The component paths for each material turns each bottom-level raw material into a distinct 'virtual' material for each of its parent materials.
o We sum up the resultant variables' quantities grouped by the top-level material id.
o Since each 'intermediate' material also exists as a top-level material, they don't need additional special treatment.
/Ryan
/*
-- create & populate the 'materials' table
*/
IF OBJECT_ID('tempdb..#material','U') IS NOT NULL DROP TABLE #material;
create table #material(
id char(2) not null primary key clustered,
name nvarchar(20),
v1 float null ,
v2 float null ,
v3 float null ,
v4 float null
)
;
insert #material(id, name,v1,v2,v3,v4)
values
-- finished goods
('f1', 'finished product 1', null, null, null, null),
('f2', 'finished product 2', null, null, null, null),
('f3', 'finished product 3', null, null, null, null),
-- blends (mixtures)
('m1', 'mix 1', null, null, null, null),
('m2', 'mix 2', null, null, null, null),
('m3', 'mix 3', null, null, null, null),
('m4', 'mix X', null, null, null, null),
-- raw materials; I made the v1-v4 values add up to 1 to provide myself a simple sanity check
-- by using only whole numbers for quantities in the bom, the final v1-v4 should also add up
-- to a whole number
('r1','raw materials 1', 0.8, 0.0, 0.1, 0.1),
('r2','raw materials 2', 0.4, 0.4, 0.1, 0.1),
('r3','raw materials 3', 0.1, 0.5, 0.2, 0.2),
('r4','raw materials 4', 0.2, 0.5, 0.2, 0.1)
;
/*
-- create & populate the 'bill of materials' table
*/
IF OBJECT_ID('tempdb..#bom','U') IS NOT NULL DROP TABLE #bom;
create table #bom(
parent_id char(2),
component_id char(2),
quantity float,
)
;
-- for later sanity checking, all 'blends' must add up to 10, 'finished products' must add up to 100.
insert #bom(parent_id, component_id, quantity)
values
-- 'pure blends' - mix of raw materials only.
('m1', 'r1', 2),
('m1', 'r2', 3),
('m1', 'r3', 5),
('m2', 'r2', 4),
('m2', 'r3', 6),
('m3', 'r1', 5),
('m3', 'r4', 5),
('m4', 'r1', 1),
('m4', 'r3', 9),
-- 'finished products'
-- mix of blends and raw materials
('f1', 'm1', 50),
('f1', 'r4', 50),
-- blends only
('f2', 'm1', 20),
('f2', 'm2', 80),
('f2', 'm3', 40),
-- raw materials only
('f3', 'r1', 20),
('f3', 'r2', 40),
('f3', 'r4', 40)
;
select * from #material; -- all products
select * from #material where not (v1 is null and v2 is null and v3 is null and v4 is null); -- raw materials only
-- expanded bom
select b.parent_id, mp.name as parent_name, b.component_id, mc.name as component_name, b.quantity, mc.v1, mc.v2, mc.v3, mc.v4
from #bom b
inner join #material mp on b.parent_id = mp.id
inner join #material mc on b.component_id = mc.id
;
/*
create a table that appends calculated v* parameters to the bom data, multiplying by the appropriate factor at each level
in an actual database, this could be implemented as a view rather than populating a temporary table
*/
IF OBJECT_ID('tempdb..#bomplus','U') IS NOT NULL DROP TABLE #bomplus;
WITH BomCTE
AS
(
SELECT -- Anchor member definition
id as toplevel_id
, name as toplevel_name
, convert(char(2), null) as parent_id
, convert(nvarchar(20), null) as material_name
, id as component_id
, name as component_name
, 1 as level
, CONVERT(varchar(20), id) as path
, CAST(null as float) as quantityper
, CAST(1 as float) as quantity
, v1, v2, v3, v4, v1 + v2 + v3 + v4 as vall
FROM #material
WHERE id in (select parent_id from #bom) --
-- name like 'f%' -- finished product
UNION ALL
SELECT -- Recursive member definition
bc.toplevel_id
, bc.toplevel_name
, b.parent_id as parent_id
, mp.name
, b.component_id
, mc.name
, level + 1
, CONVERT(varchar(20), path + '.' + b.component_id) as path
, b.quantity as quantityper
, b.quantity * bc.quantity
, b.quantity * bc.quantity * mc.v1 as v1
, b.quantity * bc.quantity * mc.v2 as v2
, b.quantity * bc.quantity * mc.v3 as v3
, b.quantity * bc.quantity * mc.v4 as v4
, b.quantity * bc.quantity * (mc.v1 + mc.v2 + mc.v3 + mc.v4) as vall
FROM #bom b
INNER JOIN BomCTE bc on b.parent_id = bc.component_id
INNER JOIN #material mc on mc.id = b.component_id
INNER JOIN #material mp on mp.id = b.parent_id
WHERE
level < 10 -- stop the madness! (sanity check)
)
-- Statement that executes the CTE
SELECT bc.toplevel_id, bc.toplevel_name, bc.parent_id, bc.material_name, bc.component_id, bc.component_name, level, path, quantityper, quantity, v1, v2, v3, v4, vall
INTO #bomplus
FROM BomCTE bc
ORDER BY level, path
;
-- check the intermediate data (copy to Excel to check the sums!)
select * from #bomplus
order by toplevel_id, parent_id, component_id
;
-- sum up and display the totals for each top-level bom item
SELECT toplevel_id, h.toplevel_name,
sum(h.v1) as v1, sum(h.v2) as v2, sum(h.v3) as v3, sum(h.v4) as v4, SUM(h.vall) as vall
FROM #bomplus AS h
group by h.toplevel_id, h.toplevel_name
;
materials list
id name v1 v2 v3 v4
---- -------------------- ---------------------- ---------------------- ---------------------- ----------------------
f1 finished product 1 NULL NULL NULL NULL
f2 finished product 2 NULL NULL NULL NULL
f3 finished product 3 NULL NULL NULL NULL
m1 mix 1 NULL NULL NULL NULL
m2 mix 2 NULL NULL NULL NULL
m3 mix 3 NULL NULL NULL NULL
m4 mix X NULL NULL NULL NULL
r1 raw materials 1 0.8 0 0.1 0.1
r2 raw materials 2 0.4 0.4 0.1 0.1
r3 raw materials 3 0.1 0.5 0.2 0.2
r4 raw materials 4 0.2 0.5 0.2 0.1
just the 'raw' materials
id name v1 v2 v3 v4
---- -------------------- ---------------------- ---------------------- ---------------------- ----------------------
r1 raw materials 1 0.8 0 0.1 0.1
r2 raw materials 2 0.4 0.4 0.1 0.1
r3 raw materials 3 0.1 0.5 0.2 0.2
r4 raw materials 4 0.2 0.5 0.2 0.1
bill of materials, expanded with variable values where applicable
parent_id parent_name component_id component_name quantity v1 v2 v3 v4
--------- -------------------- ------------ -------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
m1 mix 1 r1 raw materials 1 2 0.8 0 0.1 0.1
m1 mix 1 r2 raw materials 2 3 0.4 0.4 0.1 0.1
m1 mix 1 r3 raw materials 3 5 0.1 0.5 0.2 0.2
m2 mix 2 r2 raw materials 2 4 0.4 0.4 0.1 0.1
m2 mix 2 r3 raw materials 3 6 0.1 0.5 0.2 0.2
m3 mix 3 r1 raw materials 1 5 0.8 0 0.1 0.1
m3 mix 3 r4 raw materials 4 5 0.2 0.5 0.2 0.1
m4 mix X r1 raw materials 1 1 0.8 0 0.1 0.1
m4 mix X r3 raw materials 3 9 0.1 0.5 0.2 0.2
f1 finished product 1 m1 mix 1 50 NULL NULL NULL NULL
f1 finished product 1 r4 raw materials 4 50 0.2 0.5 0.2 0.1
f2 finished product 2 m1 mix 1 20 NULL NULL NULL NULL
f2 finished product 2 m2 mix 2 80 NULL NULL NULL NULL
f2 finished product 2 m3 mix 3 40 NULL NULL NULL NULL
f3 finished product 3 r1 raw materials 1 20 0.8 0 0.1 0.1
f3 finished product 3 r2 raw materials 2 40 0.4 0.4 0.1 0.1
f3 finished product 3 r4 raw materials 4 40 0.2 0.5 0.2 0.1
Results of CTE query:
toplevel_id toplevel_name parent_id material_name component_id component_name level path quantityper quantity v1 v2 v3 v4 vall
----------- -------------------- --------- -------------------- ------------ -------------------- ----------- -------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
f1 finished product 1 NULL NULL f1 finished product 1 1 f1 NULL 1 NULL NULL NULL NULL NULL
f1 finished product 1 f1 finished product 1 m1 mix 1 2 f1.m1 50 50 NULL NULL NULL NULL NULL
f1 finished product 1 f1 finished product 1 r4 raw materials 4 2 f1.r4 50 50 10 25 10 5 50
f1 finished product 1 m1 mix 1 r1 raw materials 1 3 f1.m1.r1 2 100 80 0 10 10 100
f1 finished product 1 m1 mix 1 r2 raw materials 2 3 f1.m1.r2 3 150 60 60 15 15 150
f1 finished product 1 m1 mix 1 r3 raw materials 3 3 f1.m1.r3 5 250 25 125 50 50 250
f2 finished product 2 NULL NULL f2 finished product 2 1 f2 NULL 1 NULL NULL NULL NULL NULL
f2 finished product 2 f2 finished product 2 m1 mix 1 2 f2.m1 20 20 NULL NULL NULL NULL NULL
f2 finished product 2 f2 finished product 2 m2 mix 2 2 f2.m2 80 80 NULL NULL NULL NULL NULL
f2 finished product 2 f2 finished product 2 m3 mix 3 2 f2.m3 40 40 NULL NULL NULL NULL NULL
f2 finished product 2 m1 mix 1 r1 raw materials 1 3 f2.m1.r1 2 40 32 0 4 4 40
f2 finished product 2 m1 mix 1 r2 raw materials 2 3 f2.m1.r2 3 60 24 24 6 6 60
f2 finished product 2 m1 mix 1 r3 raw materials 3 3 f2.m1.r3 5 100 10 50 20 20 100
f2 finished product 2 m2 mix 2 r2 raw materials 2 3 f2.m2.r2 4 320 128 128 32 32 320
f2 finished product 2 m2 mix 2 r3 raw materials 3 3 f2.m2.r3 6 480 48 240 96 96 480
f2 finished product 2 m3 mix 3 r1 raw materials 1 3 f2.m3.r1 5 200 160 0 20 20 200
f2 finished product 2 m3 mix 3 r4 raw materials 4 3 f2.m3.r4 5 200 40 100 40 20 200
f3 finished product 3 NULL NULL f3 finished product 3 1 f3 NULL 1 NULL NULL NULL NULL NULL
f3 finished product 3 f3 finished product 3 r1 raw materials 1 2 f3.r1 20 20 16 0 2 2 20
f3 finished product 3 f3 finished product 3 r2 raw materials 2 2 f3.r2 40 40 16 16 4 4 40
f3 finished product 3 f3 finished product 3 r4 raw materials 4 2 f3.r4 40 40 8 20 8 4 40
m1 mix 1 NULL NULL m1 mix 1 1 m1 NULL 1 NULL NULL NULL NULL NULL
m1 mix 1 m1 mix 1 r1 raw materials 1 2 m1.r1 2 2 1.6 0 0.2 0.2 2
m1 mix 1 m1 mix 1 r2 raw materials 2 2 m1.r2 3 3 1.2 1.2 0.3 0.3 3
m1 mix 1 m1 mix 1 r3 raw materials 3 2 m1.r3 5 5 0.5 2.5 1 1 5
m2 mix 2 NULL NULL m2 mix 2 1 m2 NULL 1 NULL NULL NULL NULL NULL
m2 mix 2 m2 mix 2 r2 raw materials 2 2 m2.r2 4 4 1.6 1.6 0.4 0.4 4
m2 mix 2 m2 mix 2 r3 raw materials 3 2 m2.r3 6 6 0.6 3 1.2 1.2 6
m3 mix 3 NULL NULL m3 mix 3 1 m3 NULL 1 NULL NULL NULL NULL NULL
m3 mix 3 m3 mix 3 r1 raw materials 1 2 m3.r1 5 5 4 0 0.5 0.5 5
m3 mix 3 m3 mix 3 r4 raw materials 4 2 m3.r4 5 5 1 2.5 1 0.5 5
m4 mix X NULL NULL m4 mix X 1 m4 NULL 1 NULL NULL NULL NULL NULL
m4 mix X m4 mix X r1 raw materials 1 2 m4.r1 1 1 0.8 0 0.1 0.1 1
m4 mix X m4 mix X r3 raw materials 3 2 m4.r3 9 9 0.9 4.5 1.8 1.8 9
final data set with variable grand totals
toplevel_id toplevel_name v1 v2 v3 v4 vall
----------- -------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
f1 finished product 1 175 210 85 80 550
f2 finished product 2 442 542 218 198 1400
f3 finished product 3 40 36 14 10 100
m1 mix 1 3.3 3.7 1.5 1.5 10
m2 mix 2 2.2 4.6 1.6 1.6 10
m3 mix 3 5 2.5 1.5 1 10
m4 mix X 1.7 4.5 1.9 1.9 10
November 2, 2011 at 5:03 pm
Joe,
I would have responded sooner, but I've spent a couple of days looking after some other clients, and away from this project.
Firstly, thanks for taking the time to actually respond to my problem!
That said, I like to think I understand set theory quite well, and I had a look at your nested sets solution while researching my problem, but was unable to figure out how to apply it *to my specific problem* (I've seen your noob 'cut and paste' code a few times in my reading).
The nested sets solution (and most examples of hierarchy code I've seen) work very well for straight-forward trees, where a leaf belongs to one and only one branch. The challenge I'm having is finding a good model with example code for graphs, where a leaf can be connected to multiple branches, as in a typical 'bill of materials' scenario, and that allows me to do some 'research queries' to find out what 'bottom level' stuff is going into the 'top level' items. (maybe a focus topic for another book?)
e.g. say I have the following (obviously vastly simplified) data:
Cookies Seed Bread
| |
|--------------| |----------|
40 Sugar /\ 40 Flour 1 Yeast | 90 Flour
/ \ 9 Seeds
10 ChocChips 10 Butter
/ | 30 Sugar | 30 Butter
40 Cocoa
Protein | Fat | Carbs | Sugar | Vitamins | Minerals
Sugar: 0 0 0 100 0 0
Flour: 0 0 100 0 1 1
Butter: 20 80 0 0 5 5
Seeds: 50 0 50 0 0 0
Yeast: 0 0 0 0 0 0
Cocoa: 30 40 30 0 5 5
My challenges are:
a) How do I model this into a good database design?
b) How do I, using set-based methods, calculate (for example) how much of each of the 6 'nutritional parameters' I've presented there are in a) a box of cookies and b) a loaf of bread?
I've inherited a BOM table structure from the source ERP system. While I can re-structure it as part of my 'import' process, the structure is simple to understand, and I've yet to find an alternative that I can understand, apply to my situation, and solve my main problem (which is the calculations). i.e. I don't think my problem is the BOM structure itself.
I have continued looking around, and have found some sketchy possibilities, but have yet to find a really good bill-of-materials solution - they all insist on using employee org charts (which I struggle to understand, because I'm self-employed :P). I have found a few articles which hint at being helpful, but I'll need time to digest them.
[EDIT:]
Just to highlight something that perhaps isn't obvious from my diagram. Intermediate products are manufactured in bulk before being used in higher-level products in smaller quantities. My BOM is kind of reflecting 'percentages', when in 'real life', they measure everything in kilograms.
So my client will make, say, 1000kg of chocolate chips, which go into their 'store'. The BOM for the 'Choc Chips', will reflect, say 400kg of Cocoa, 300kg sugar & 300kg of butter.
Then the BOM for the cookies will be for, say 100kg of cookies; and will show 10kg choc chips, 40kg of sugar and 40kg of flour.
This is slightly different from your typical 'sub-assembly', where you have a definition for single item (say a bicycle brake assembly). Having said that, I am planning to rationalize the structure so that everything is a 'unit' (kg), I just haven't got there yet.
[END EDIT]
When I'm *completely* happy with my solution, I will reply to this thread again.
regards
/Ryan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply