on 06-30-2011 10:54 AM
Hi gurus,
I have this delta scenario:
DSO A consolidates and look up records from DSO B and C to the target DSO X. My question: if there are deltas in DSO C, how are these reflected in DSO A?
A pseudo example:
DSO A: doc_id, item_id, amount
DSO B: doc_id, customer_id, delivery_add
DSO C: doc_id, contract_id, discount_rate
DSO X: doc_id, item_id, customer_id, delivery_add, amount, contract_id, discount_rate
Is it possible to trigger a lookup in DSO A if there are changed records in DSO C?
Could you please provide some info on this type of delta scenario?
Thanks in advance.
Regards,
Meng
Hi Joon,
You can acheive your requirement By Creating Intermediate Delta DSO "DSO D".
Explanation: -
Create DSO D, which will contain DOC_ID as key field and
Date, Timesheet as Data field.
Now your Design will be
DSO A, DSO B and DSO C will feed the data to DSO D.
and then DSO D will feed the data to DSO X.
All load are delta load.
Here between DSO D to DSO X transformation you can use expert routine, which will look up all three DSOs -
DSO A, DSO B and DSO C.
Benefit of above design,
Well managed Data flow
Well managed Delta capability
Only 1 transformation has routine - So easy to maintenance.
Easy for support, etc....
Regards,
Ashish
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Ashish,
Thanks for your suggestion. How could we handle the retroactive changes in this case? e.g. the validity of a contract in DSO C has changed: 01.01.2009 - 01.05.2011 --> 01.02.2009 - 15.03.2011?
How are we going to correct the outdated records in DSO D as well as DSO X? This is actually the crux of the issue..
Thanks in advance!
Regards,
Meng
Hi Joon,
Its very clear...
As I have said DSO A, DSO B and DSO C will feed data to DSO D.
So if some Document will change to in DSC C, it will come to DSO D by Delta load.
From DSO D, when its get uploaded to DSO X, it will look up the DSO A, DSO B and DSO C and feed the values in DSO X.
Regards,
Ashish
Hi Ashish,
Sorry that I still need some clarification from you, since I am quite new to BI
You said:
Create DSO D, which will contain DOC_ID as key field and
Date, Timesheet as Data field.
Did you refer to current date and timestamp with "Date" & "Timesheet" mentioned above? So, DSO D will only contain these 3 InfoObjects?
Now your Design will be
DSO A, DSO B and DSO C will feed the data to DSO D.
Which data to be fed to DSO D? DSO C does not have doc_id and how can data records from these 3 DSO (A, B, C) combined via standard transformation without routine to DSO D?
Here between DSO D to DSO X transformation you can use expert routine, which will look up all three DSOs -
DSO A, DSO B and DSO C.
Why expert routine? Is it because I can access source fields from the 3 DSOs?
Could you please elaborate in more details for newbies like me
Your help is greatly appreciated!!!
Regards,
Meng
hi,
A pseudo example:
DSO A: doc_id, item_id, amount
DSO B: doc_id, customer_id, delivery_add
DSO C: doc_id, contract_id, discount_rate
DSO X: doc_id, item_id, customer_id, delivery_add, amount, contract_id, discount_rate
In your Pseudo example as can be seen that the keys will be as follows:
DSO A: Doc_id, item_id
DSO B: Doc_id
DSO C: Doc_id
DSO X: Doc_id, item_id
Now in your case you can create new transformation from DSO B & DSO C to DSO X so that independent Delta's can flow to the target. In the transformation mark only the values coming from source rest fields should be set to no update.
In the transformations lookup at DSO A for particular doc_id and get its item_id and populate in the transformation.
regards,
Arvind.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Meng,
I can see DSO B, DSO C is like simple master data having docid as key if so load this data to docid master data infoobject (create one infoobject) Use this docid info object in the transformation from DSO A to DSO X , or you can use navigational attributes of doc id.This will solve ur purpose
Or
If you design DSOX in such a way , so that you can also create a mapping from DSO C, DSO B so that the changes are reflected. Otherwise you always need to do a full load in this case. In Worst case you can go for an infoset while checking the keyfgures multiplicity.
Regards
vamsi
Edited by: vamsi talluri on Jun 30, 2011 3:32 PM
Edited by: vamsi talluri on Jun 30, 2011 3:33 PM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Vamsi,
thanks for the quick reply. Sorry for giving a confusing pseudo example in the previous post. It is unfortunately not so simple in my case. The example below is much more real.
DSO A keys: doc_year, doc_id, settlement_id
DSO B keys: doc_year, doc_id, item_id
DSO C keys: contract_id
DSO X keys: doc_year, doc_id, item_id, valid_from
I use DSO A as the base for the consolidation and data enrichment as well as transformation. What makes it more complicated is that I need to keep enable versioning based on validity of the contracts (in DSO C) for records in DSO X (valid_from as key in this case).
Thanks,
Meng
Hi Meng,
Can you tell me how are the lookup conditions done from DSO B,C in DSOA to DSO X.
Also you don't have item id,valid from in DSO A Key fields , but you have them in DSO D, how are you getting them.
If possible please put an example from the DSO's. Then we can accordingly plan delta if possible
Regards
Vamsi
Hi Vamsi,
i don't have item_id as key in DSO A but as foreign key. The valid from is determined with post_date in DSO A. Here is an example:
DSO A:
doc_year (key), doc_id (key), settlement_id (key), item_id (foreign key), contract_id (foreign key), amount, post_date...
2010, 4711, 5, 1, 10045, 200 USD, 01.04.2010, ...
DSO B:
doc_year (key), doc_id (key), post_id (key), type, ...
2010, 4711, 1, C, ...
DSO C:
contract_id (key), valid_from (key), valid_to, employee_id, lock_indicator, ...
10045, 01.01.2009, 01.05.2011, 0123456, X
DSO X:
doc_year (key), doc_id (key), settlement_id (key), valid_from (key), valid_to, amount, type, contract_id, lock_indicator....
2011, 4711, 5, 01.01.2009, 01.05.2011, 200 USD, C, 10045, X, ...
Looking forward to your reply.
Thanks,
Meng
Edited by: Joon Meng Goh on Jun 30, 2011 2:27 PM
Hi Meng,
Please correct me if am wrong....
From DSO A to DSO X for fields doc year, doc id , settlement id,amount, posting date are one to one....
Item id in DSO A is nothing but post id if i am not wrong
From DSO A using doc year,doc id and item id you make look up to DSO B for type
From DSO A using contract id you make up look up to DSO C for valid to and valid from. and lock indicator...
I could see from DSO B , DSO C you are bringing only some chars like some master data from DSO B and Time dependent master data from DSO C....
There should be only delta problem in case of contract id and its validity and there wont be changes in DSO B if i am not wrong...So only the changes in DSO C should be captured in DSO X.
Create time dependent contract id with attributes with the fiekds in dso C,
send the data from dso c to contract id..... and then use this time dependent attribute in DSO X .... and there is no need to have look up from DSO C....so DSO A to DSO X can be made delta even if changes are made in DSO C...
if there any changes in DSO B, plan similarly.....
Regards
vamsi
Hi Vamsi,
it's great that you have understood the scenario quite well Yes, I am getting and versioning some of the contract master data from DSO C. The reason why I did not go for time-dependent attributes was due to the huge amount of data. It would probably cause performance problem during reporting because I am getting 30-40 fields from DSO C and many are used for navigation / filtering in reporting. So the idea was to integrate the master data with transaction data.
How would be the solution if time-dependent attribute is not an option?
Another question regarding the changes in DSO B. There would also be changes or correction in DSO B as well as A which contain transaction data e.g. amount or type..These changes should be reflected in the consolidated DSO X as well...
Regards,
Meng
Edited by: Joon Meng Goh on Jun 30, 2011 3:00 PM
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.