Parent and Child Accounts

View previous topic View next topic Go down

Parent and Child Accounts

Post  Bob Probst on Tue Jun 21, 2011 1:49 pm

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?

Bob Probst

Posts : 18
Join date : 2010-05-26

View user profile http://datajuggler.blogspot.com/

Back to top Go down

Re: Parent and Child Accounts

Post  ngalemmo on Tue Jun 21, 2011 3:06 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Parent and Child Accounts

Post  Bob Probst on Tue Jun 21, 2011 5:12 pm

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.

Bob Probst

Posts : 18
Join date : 2010-05-26

View user profile http://datajuggler.blogspot.com/

Back to top Go down

Re: Parent and Child Accounts

Post  ngalemmo on Tue Jun 21, 2011 5:25 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Parent and Child Accounts

Post  Bob Probst on Tue Jun 21, 2011 8:31 pm

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!

Bob Probst

Posts : 18
Join date : 2010-05-26

View user profile http://datajuggler.blogspot.com/

Back to top Go down

Re: Parent and Child Accounts

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum