Parent and Child Accounts

[Bob Probst]

I've been asked to use our existing warehouse to automate a very specific analysis.
Account Dimension contains all accounts SCD2
Fact table grain: Account x Day loaded nightly

Some Accounts have Child Accounts that draw funds from them as needed but a Child Accounts can also have many parent accounts.
So Parent Account 123 has children ABC, CDE & EFG. But child ABC might also have a parent 345.
So when child ABC needs funds, it will see if parent 123 has them and draw from it. If not it will draw from 345.

tldr: classic many to many relationship

I only need to see one level deep: Parent and all of its children
I don't want to change the grain of our existing fact table.

This sounds like a job for a Bridge table but I don't want to complicate the design. Are there any other structures out there that could be useful?

[ngalemmo]

Within a relational database, many-to-many relationships can only be resolved by a bridge (aka: associative entity). There are no other structures that would work.

[Bob Probst]

I thought as much. Since the parent/child relationship can change over time I'm thinking that the bridge would need to include the time dimension id to be accurate.

Code:

PARENT_CHILD_BRIDGE
=================
time_dim_id
parent_acct_id
child_acct_id
relationship_description
etc

Then join to the fact by time and parent acct id

Am I over-complicating this?

I'm also having to consider how to integrate this with our BO Universe designer. I won't have all queries running from Account to Fact via this bridge, only those queries that ask for child data.

[ngalemmo]

Not sure what the time key is all about... usually you just have an effective and expiration date.

As far as BO goes, create an alias of the account dimension for child (or parent) and relate it to the fact through the bridge. Define a context that includes both parent and child and the bridge. BO will not join through the bridge unless you select attributes from the alias that is related through the bridge.

[Bob Probst]

Right. I got it worked out earlier (in my head at least) time_id is redundant as you suspected.

You describe exactly the solution I had as "best" (for now). Thanks for the confirmation!

[Sponsored content]