Parent / Child as a referenced dimension

Hi there,

I have a classic Chart of Accounts dimension, implemented as a Parent / Child. This dimension binds to fact_gl_balances with a surrogate key, at the child account granularity. Let's call the dimension dim_gl_structure

So far, so good. I'm not experiencing any of the problems that other people have talked about with Parent / Child and surrogate key. Dunno why...

Now, however, I have another fact table that exists at a finer granularity - fact_gl_transactions. I have a (non-parent / child) dimension that binds to this table with a surrogate key at the transaction number level. It's called dim_gl_transactions. One of the attributes of this dimension is the surrogate key used for the child account in dim_gl_structure - so the dimensions should be able to join to each other.

What I want to do is be able to drill down through the parent / child hierarchy of dim_gl_structure, but go below the child account, into the transactions. I thought this would be easy - I though I'd create a new dimension, based on dim_gl_transactions table , relate it to dim_gl_structure, then use whichever attributes I required from both dims to create a suitable hierarchy.

When I go through the wizard, it detects the related table dim_gl_structure, but doesn't offer me any of the attributes it contains. Is this because it's Parent / Child? Am I trying to do something that is inherently impossible? Is the best answer to hack together a view, and base the new 'combo' dimension on that? And why would this be so hard? I don't get it......

Thanks in advance for any help you can give me.

Hello! I do not know about all your business requirements but I think that this is not about referenced dimension but more about granularity in different measure groups.

The second fact table have a different granularity than the first but it is probably not only about the relation between transactions and account but also about the time dimension that you have not described?

You should be able to aggregate the second fact table into the first fact table by ignoring the transaction key and only aggregate by account. Use the second fact table instead of the first.

I can be wrong but you said that you have the account key in both fact tables?

And i do not know if you have different granularity of measures within both fact tables, one of the few good reasons of using parent-child dimensions.

Are these two tables coming from two different source systems? One financial source and one transaction source system?

HTH

Thomas Ivarsson

|||

I don't know if the wizard will detect a referenced dimension like this. Have you tried setting it up manually in the dimension usage tab?

I was thinking along the same lines as Thomas. Parent child dimensions can really slow down as they get larger, so I would really think hard before adding transactions under the accounts. I would go the way Thomas suggested, maybe creating the view over dim_transaction and fact_transaction so that you can link directly to the account dimension.

|||

OK guys, I took your advice on board and got this working. It may be worth recording my approach, because this is a fairly generic problem

I decided not to go with a parent / child structure for dim_gl_structure, so I rebuilt the dim table to have fields gl_level_1, gl_level_2, etc, and designed the user hierarchies and attribute realtionships manually. I felt like I had a bit more control that way. That gave me dimension dim_gl_structure, which I bound to fact_gl_balance at the account_key granularity. Worked fine. I can now drill up from gl_account all the way up through the Chart of Accounts.

I then built dim_gl_trans table, which had the transaction key, the account key, and the dimension attributes. I put a foreign key between dim_gl_trans.account_key and dim_gl_structure.account_key in the database.

Then I created a dimension based on table dim_gl_transaction. Because of the foreign key, the dimension wizard detected table dim_gl_structure as a 'related table', and offered me all of the fields in both tables for attributes. At this point, you're pretty much there. Create a user hierarchy with transaction_key at the bottom, going up through account_key, gl_level_2, gl_level_1 etc.

[I have a suspicion that this may not work as well if one of the tables involved is a view. i.e. if you'd implemented dim_gl_trans as a table, but dim_gl_structure as a view, the 'related table' bit of this may fall over, and not offer you all of the attributes ]

This dimension(dim_gl_transaction) binds to fact_gl_transaction at the transaction_key granularity. This allows me to do what I Initially wanted i.e dill up from transaction all the way up therough the chart of accounts.

Thanks for your help.

|||

Glad you got it working

Sam Loud wrote:

[I have a suspicion that this may not work as well if one of the tables involved is a view. i.e. if you'd implemented dim_gl_trans as a table, but dim_gl_structure as a view, the 'related table' bit of this may fall over, and not offer you all of the attributes ]

It should not matter if the source is a view or a table as long as the logical relationships in the DSV are defined correctly.