Parent-child dimension in Framework manager

[dacke14]

Hi,

I have a wish to be able to build a DMR-modeled dimension that is built towards a parent-child table, i.e it should be dynamic in its depth instead of dependant upon how many levels has been added in the dmr-model. I am afraid this is impossible. The only way to read a parent-child dimension table is to first flatten it out using some recursive statements and then model the DMR to that view/table.

Anyone else thought about this? Please let me know if you have any thoughts.

regards
David

[MFGF]

Hi,

Parent-child dimension structures do not lend themselves at all well to star-schema based models - consider this:  how would you link your fact items to your dimension if you do not have a fixed lower level with a granular key?  How would the underlying relationship between the Fact and Dimension Query Subjects be defined?  Remember, DMR is basically just defining a "virtual" olap structure on top of your real database(s), so there are numerous constraints to what you can do.

Flattening the Parent-Child into a fixed set of levels seems to be the most effective approach - if I had the choice, I would use Data Manager for this because of its inbuilt functionality.

Regards,

MF.

[blom0344]

I think you answered the question yourselve quite effectively. However, even if you could model on parent-child structures the load (at runtime) would be prohibitive. There is nothing as 'heavy' as a recursive query of on-known depth. As already mentioned this action has to be performed in the ETL-phase or otherwise as an additional step in the DWH. 1 or 2 levels deep can be achieved by non-recursive views, but these tend to become pretty complex very fast..

[dacke14]

Thanks for the input, I have myself seen the problems you're mentioning, but I have actually heard from people that it should be obtainable. I have scratched my head on this one. We just have to stick with the current solution and add more levels as dimensions grow deeper.

[blom0344]

I am afraid this is impossible. The only way to read a parent-child dimension table is to first flatten it out using some recursive statements and then model the DMR to that view/table.

Well, this is your own conclusion I guess and as I mentioned earlier, firing a recursive query as part of the DMR solution will KILL performance altogether.