on 04-05-2006 12:49 PM
Hi All,
We have two ODS A and B and we want to combine these two ODS's and into C
for example in ODS A we have following fields
1 2 3 4 5
and in ODS B we have following fields
6 2 7 8 9
i am expecting to have a view in my C ods like this when i combine both
1 2 3 4 5 6 7 8 9
but i am getting it as
1 2 3 4 5 in one row and the rest in the second row
the key fields in my C ods are
1 2 6 7
that is 1 2 from A and 67 from B. so can somebody guide me how to achieve this into
one record. without having in two records.
thanks and regards
neel
dear Neel,
to have them in one row, you have to have one or more fields that have same definition (contains some values that same), these fields then use for 'join condition' of the view. in other words if 1 2 3 4 5 6 7 8 9 all are different then you cannot achieve what you want.
hope this helps.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Neel,
Are you saying that ODS A has 1&2 as key fields and ODS B has 6&7 as the key fields? But the field that is matching in these two ODSs is only 2?
This will always give incorrect data since the joining condition is not right.
ODS A will have multiple records with the same value for field 2 and the same will be the case with ODS B. Do you really want an m:n join here?
Would it be possible to add field 1 to ODS B as well? in this case the join on these two fields will return a unique record from ODS A and thus give the right values.
Now you can create update rules only from ODS B to ODS C and in the update rules pick up matching records for fields 1 & 2 from ODS A.
Else you can also consider making an infoset fro the query on ODS A & B
Hope it helps,
Regards,
Nikhil
hi A.H.P. & NIKHIL,
so u mean to say that if i just keep the key fields in ODS C as 2 only and create update rules from B and fill the other fields from A by using a look up in the B update rules.
will this fetch all the fields from both ods without the fear of getting overwritten.
is my understanding right. can i do it the other way round like write the update rules from A and fill the fields from B by using the routine. (is that what you r saying.)
i positively think that this possible. I will try this and get back to you.
thanks.
Neel.
Hi Neel,
No, just having 2 as key in ODS C will not work in any case. You will probably have multiple records with the same value for 2 in ODS A itself - all these will get overwritten.
What you need to do is to add both the key fields in any one of the first level ODS into the other first level ODS.
If you want to create update rules from ODS A then ensure that both the keys of this ODS (1 & 2) are also present in ODS B.
And if you want to create the update rules on ODS B then ensure that both the keys of this ODS (6 & 7) are also present in ODS A.
In the second level ODS you need to have four keys (1, 2, 6 & 7) in order to ensure that no records gets overwritten.
Hope it helps,
Regards,
Nikhil
Srini
Message was edited by: Srini
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Did you make 1 2 6 7 as keys in the C ODS? Why don't you think of a multi-provider?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
67 | |
26 | |
9 | |
9 | |
7 | |
6 | |
4 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.