Require suggestions regarding implementing a Bridge Table

[karan_das]

Hi , Good Day ,

I am a part of a team designing a solution for a major supply chain logistics provider in North America in the BI space. I have currently designed a dimensional model to capture the KPIs shortlisted by our client. However, need help regarding one particular arena; concerning Bridge Tables.
This concept is a little new to me so wanted to clarify whether what I am doing is fine or not. So here goes ..

I have created a Fact table called as FACT_SHIPMENT with relevant measures at the shipment level that the client wants to capture. I also have a PARTY Dimension that is linked to the FACT_SHIPMENT. The PARTY attribute hold details of any PARTY, that is, it can be a shipper, a consignee, a debtor and even a 3PL to whom the shipment was assigned to. I have to capture ALL relevant details related to ONE shipment record in the FACT_SHIPMENT table.
That is, for a given record in the fact table,I want to capture : The Shipper, Consignee, debtor (the party who pays for the shipment) and the 3PL involved (Optional). I do not want to have 4 relationships between the dimension and the fact.
By googling and doing a little bit of research on the net, I came across the concept of a Bridge Table that has the capacity to resolve M:N cardinality between a Fact and a Dimension. I feel that this seems to fit my case, but then I am no expert in dimensional modeling.

Moreover, I was under the impression that it is best to keep the dimensional model as highly de-normalized as possible, does this not make the performance a tad bit slower ?

Let me know your thoughts on what can be the best approach.

Thank & Regards,
Karan

[ngalemmo]

"I do not want to have 4 relationships between the dimension and the fact. "

Why? You have 4 distinct roles for parties. Bridges are used to resolve M:M relationships of peers.

[karan_das]

Hi ,

Thanks for the reply.
I felt that using 4 relationships between the Fact and the dimension would be an unclean approach. I always believed that we must restrict relationships between two tables to a maximum of 'double association' and not more than that (I vaguely remember having leanrt something like this in University) . But is this my misconception ?
Do you feel that that having four relationships is a better idea (In this scenario) ? Moreover, If i use 4 relationships ,the 4th FK in the Fact (3PL one) may have a large number of NULLs ... because by analyzing our client's source data , it seems they have their own fleet of power units and trailers that they prefer to use.

Also, to add on to my problem stated earlier, I am faced with one more issue: One shipment may be a part of more than one linehaul manifest (case occurs when a shipment is to be delivered ata location / city farther away than the linehaul's final destination) and one manifest has multiple shipments. So in this case can we use a Bridge between a FACT_SHIPMENT and MANIFEST dimension ? The Manifest dimension stores the details of who created it and when etc .. I wish to keep the FACT_SHIPMENT have "One Shipment entry- One Record". This is because the measures I have used in the fact should not be repeated just because only the manifest changed.

So here, we can have a 'Manifest' having N number of probills and a Probill which can be part of any number of manifests (usually single digits).

So here usage of bridge is more reasonable ?

Thanks and Regards,
Karan

[ngalemmo]

I've never heard the term 'double association' and am not clear what that would mean in a dimensional model. The thing is you have 4 clearly defined contexts relating to party. These should appear as individual dimension foreign keys. As for the optional one, you should never use null foreign keys. Instead, have a 'does not apply' row in the party dimension and reference that row.

As far as logistics go, treat shipment and manifest as two separate facts. They represent two different things for different purposes. Make sure each have all necessary dimensions as well as a shipment reference in the manifest fact so they can be related.

[karan_das]

I've never heard the term 'double association' and am not clear what that would mean in a dimensional model. The thing is you have 4 clearly defined contexts relating to party. These should appear as individual dimension foreign keys. As for the optional one, you should never use null foreign keys. Instead, have a 'does not apply' row in the party dimension and reference that row.

ok. Will do. The Does Not Apply / Invalid Row is a good idea. Will follow this approach of 4 relationships. Thanks !

As far as logistics go, treat shipment and manifest as two separate facts. They represent two different things for different purposes. Make sure each have all necessary dimensions as well as a shipment reference in the manifest fact so they can be related.

So, I would want to clarify this here , because I could not fully understand how to design this part. You are saying that I create a FACT_MANIFEST apart from the already existing FACT_PROBILL. Now, I have a Dimension SHIPMENT that is already associated with the FACT_PROBILL, but if i am to associate that with FACT_MANIFEST, that basically means that one single Manifest record will be repeated as many times as there are probills that are being carried in that manifest, and all the info for that manifest (such as created dttm, who created it etc.. ), I can store in a separate MANIFEST dimension.
Am I correct ?
Thanks
Karan

[karan_das]

Can anyone please confirm if my view in the precending post is correct ?
Thanks,
Karan

[herostime]

I think your dialogue is very interesting.

[Sponsored content]