Sql Server Analysis Services Parent Child with non-unique key

Question

I'm currently building our Data Warehouse, primarily using Ralph Kimball's methods and guidance.

We are using the Microsoft stack for this (so SSIS, SSAS).

I am a bit stuck deciding how to handle BOMS (Bill of Materials) which is effectively an unbalanced hierarchy.

The BOM handles assemblies which are a collection of parts. Each part can have it's own child parts and each part can also appear more than once in different assemblies.

I'm trying to use a DimBOM table as follows...

Now in SSAS I can join the table to itself (ChildItemNumber to ItemNumber) and create a dimension. The dimension will pick up the relationship and create a parent-child link.

The problem is, The ItemNumber in this case is not necessarily unique (because a child item can be a parent itself). If I try to process the dimension SSAS warns about a non unique attribute key.

Is there a way of handling this, short of reverting to an exploded hierarchy e.g.

Answer 1

Hi Jon,

I think you might be confusing two things here which are the parts and the assemblies. one of the key notions in a Parent Child Dimension is that though a father may have many children and grandchildren, a child may only have one parent. so, i think the parts may be a Parent Child Dimension dimension of their own like:

parent key, child key, business key, name, amount null, 45, A5286, connection rod, 45, 51, B1452, bolt, 2 45, 52, B5874, rod, 1 (if you need 2 bolts and 1 rod to build a connection rod)

and assemblies may be another Parent Child Dimension: parent key, child key, business key, name, amount 655, 745, E2497, Motorbike, 2 745, 874, E7482, engine, 1 (if you need 1 engine to build a motorbike)

and they can connect pehaps in a sort of fact where: child key part, child key assembly, amount 45, 874, 3 (if you need 3 engine rods in one engine) always try to connect at the lowest relevant level.

in any case, look at adventure works parent child dimension (the enterprise soloution has a few of them) and also look at the relational table and data of them.

hope it helped you find an answer that's relevant for you, ella