How To Design a 1-to-N relationship between 2 Data Srcs in Transformer

[patakya]

hi,

I have a cube I'm trying to build against 2 data sources:
1)a main data table, employee level, with measures
2)a lookup table that each row of the main table should be able to join to all rows in this one, having a matching key.

the relationship is main data (1) to lookup (N).

However, when I build this, if I try to display a measure from main table 1 after drilling into lookup 2, I always get zeroes (it's out of scope).

Any ideas? (....other than saving my main table data for each combination in table2, thus duplicating ~1,500 times)

thanks!...

[Cannondale23]

in both data sources make the column name that joins them the same name, transformer gets its association between datasources by common column names... hope this helps

[patakya]

hi,
yes, I've already done that.
the data measure (BX_BUDGT1_AMT) shows scope that does reach into the common key (ACRYear_BudgetID), but doesn't reach into the next level (TO_CURR), which is the outer join (N-side). Attached pic

[Cannondale23]

send a pic w/ both datasources open.. thanks

[patakya]

hi,

here they are:

[Cannondale23]

TO_CURR  has to exist as a column name in both datasources in order for transformer to associate dimention map with data source and  measure.. good luck

[patakya]

ah - but that was my whole point - to have a 1-to-N relationship.

wherein, there would be an EXTRA key field on table 2 (in this case, TO_CURR), so that I could see table1 data for every matching TO_CURR
inside ACRYear_BudgetID(the common key).

Can you not do that in transformer?

[Cannondale23]

if you play around w/ it based on my comments you should get the result set in powerplay that you are talking about (after you get it think about allocation)...if you want send a mocked up powerplay example of the desired cross tab based on your current transformer model (in case I missed something) thanks .. take care

[patakya]

hi,

still not working, I'll send it out. if it triggers anything else, great. If not, thx for your time..

[Cannondale23]

yep you're right the current data model doesn't support what you're trying to do.

you can seperate out two levels into seperate dimensions in transformer and then in powerplay ask users to nest the two dims as rows then stack measures as columns then zero suppress to get desired results based on your screen shot, but I think you were trying to stay away from that.
also nesting dimesions with lots of categories at lowest level of detail is not advised in bigger cubes (250mg) just from experience they time out

rates usually cant be summerized ...change masure roll up from sum to avg

I'll mull over for a couple of days just for kicks then if I come up with a creative solution I'll post...