Dt:5 June 2016
If you have been wondering as to why I have stopped writing about OBIA in the recent past , then here is the answer to that.I have moved on from OBIA to a different project which involves support & maintenance of an old datawarehouse which is around 10 years old.This DWH was built using ODI on a teradata target.
In the past I have always worked with building new datawarehouses or in the worst case maintaining young datawarehouses which were 2-3 yrs old.Usually most of us run away from support projects considering that they are pretty boring and have routine steps & processes. However if you wish to put your mind & soul into it then maintenance of old datawarehouses can be fun.
This is provided that there is some amount of freedom given to you to make your choices.The DWH that we were maintaining recently got migrated from Teradata DB to MSSQL server .This migration process ended up being a learning process in terms of what is supported in teradata & what features are supported in MSSQL .Also we got a insight into the difference in how Teradata handles certain standard features against how MSSQL does it. For example teradata rejects a subquery SQL which tries to update a same record using two records from the subquery. However MSSQL picks up the highest record & uses that for the update.So similar query in Teradata fails however it does not fail in MSSQL.
Also there are other intances as to how Teradata handles string overflow by automatically truncating characters but MSSQL will throw you a string overflow error.
This weekend we also came across an interesting case where we wanted to use a IKM to insert records into a target table based on whether a composite column primary index was violated or not and in the same IKM we wanted to update the records based on only a single key from the composite column index.This kind of requirement is unusual however we still wanted to get this done as a part of migration fix. My team came back to me saying that this is not possible in ODI. I looked at the query and asked them to modify the IKM.
Modification suggested.
Usually all ODI IKMS use the inbuilt APIs to build their queries. One need not understand all the functionality of these APIs but if you look at the KM query & then the translated query in ODI operator logs then you get a fair idea of what the APIs are doing.For our case I asked the team to use the UD10 flag and mark it for all columns on which they wanted to write a NOT EXISTS query in the insert part of the KM.We then used the UD10 flag to retrieve the column list used to reject records based on the composite column primary index.The updates were still driven by the UK flag.In this way we seperated the inserts logic from the update logic(Columns used) in the same IKM.
Once you learn the art of modifying IKMs then ODI becomes a lot more interesting
If you have been wondering as to why I have stopped writing about OBIA in the recent past , then here is the answer to that.I have moved on from OBIA to a different project which involves support & maintenance of an old datawarehouse which is around 10 years old.This DWH was built using ODI on a teradata target.
In the past I have always worked with building new datawarehouses or in the worst case maintaining young datawarehouses which were 2-3 yrs old.Usually most of us run away from support projects considering that they are pretty boring and have routine steps & processes. However if you wish to put your mind & soul into it then maintenance of old datawarehouses can be fun.
This is provided that there is some amount of freedom given to you to make your choices.The DWH that we were maintaining recently got migrated from Teradata DB to MSSQL server .This migration process ended up being a learning process in terms of what is supported in teradata & what features are supported in MSSQL .Also we got a insight into the difference in how Teradata handles certain standard features against how MSSQL does it. For example teradata rejects a subquery SQL which tries to update a same record using two records from the subquery. However MSSQL picks up the highest record & uses that for the update.So similar query in Teradata fails however it does not fail in MSSQL.
Also there are other intances as to how Teradata handles string overflow by automatically truncating characters but MSSQL will throw you a string overflow error.
This weekend we also came across an interesting case where we wanted to use a IKM to insert records into a target table based on whether a composite column primary index was violated or not and in the same IKM we wanted to update the records based on only a single key from the composite column index.This kind of requirement is unusual however we still wanted to get this done as a part of migration fix. My team came back to me saying that this is not possible in ODI. I looked at the query and asked them to modify the IKM.
Modification suggested.
Usually all ODI IKMS use the inbuilt APIs to build their queries. One need not understand all the functionality of these APIs but if you look at the KM query & then the translated query in ODI operator logs then you get a fair idea of what the APIs are doing.For our case I asked the team to use the UD10 flag and mark it for all columns on which they wanted to write a NOT EXISTS query in the insert part of the KM.We then used the UD10 flag to retrieve the column list used to reject records based on the composite column primary index.The updates were still driven by the UK flag.In this way we seperated the inserts logic from the update logic(Columns used) in the same IKM.
Once you learn the art of modifying IKMs then ODI becomes a lot more interesting
