hi,
new to transformer. i'm converting a DMR to cube. now one dimension to fact relationship is like
fact.account_id < dim.account_id_max and fact.account_id > dim.account_id_min
i understand that in the cube measure.account_id will automatically be matched to dimension.account_id
so how can i implement the relationship above?
What does your account dimension look like? Are the levels and attributes the same across a unique range of IDs?
Perhaps it would be possible to derive keys in order to populate your cube, but I'm not really sure without understanding your data.
For example, you dimension might have a unique key that represents a range of IDs such as 00001-00100 and that particular dimension record has a hierarchy and attributes that are appropriate for every account between 1 and 100. Then the next dimension record is 00101-00200, and so forth. Then to populate your facts, you would transform the actual account ID to the appropriate range value so that Transformer could match up to the appropriate dimension.
A real shot in the dark, so maybe others will chime in with better suggestions.
You could do it this way but the question would be which ID would you show to the users?
1> Create a query for Dim as:
Select
Account_ID_Min ,
Account_ID_Max,
...... < other DIM fields >
From Dim
2> Create a query for Fact as:
Select
F.Account_ID,
D.Account_ID_Max
... < other Fact fields >
From Fact F
inner join Dim D
on F.account_id > dim.account_id_min and fact.account_id < dim.account_id_max
In Transformer, the data will roll up based on Account_ID_Max.
There are various properties for every level like Source, Label etc so you might
want to think about which ID to display to the users.