8 Replies Latest reply: Jan 17, 2013 9:52 AM by 965271 RSS

    Un Expected Result in IKM Slowly chaning Dimension Module

    965271
      Hi Gurus,

      I am working in ODI for a simple POC where I need to reflect Intial Load and then Slowly Changing Dimension Type I & Type II handling by ODI. I am using EMP as my source and a TEMP table which is a replica of EMP with additional filed needed in IKM Slowly changing dimension e.g. START_DATE, END_DATE, CURRENT_FLAG ETC.

      In my Intital Load I am using IKM Oracle Incremental Update. I am passing the following values for addional fields which will be required later in IKM slowly changing dimension.

      START_DATE = SYSDAE-1
      END_DATE = TO_DATE('31-12-2113','DD-MM-YYYY')
      CURRENT_FLAG= 0

      This load work fine till over here

      When I modifed the same interface in order to handle Type I and Type II. I made all the required changes e.g. ENAME will be Overwrite and Sal update will refelect Add new record.

      1. Soruce JKM module Oracle simple selected
      2. At Target table select IKM Slowly changind dimension
      3. Added the source table to CDC
      4. Start the Jounalizing
      5. In Interface Only Journalized data box checked.
      6. All coulmns maps for Type I and Type II.

      When I run the interface and try to update enmae column, its not updating its inserting new record in the target. Which is very consfusing to me. Later if I update the same record again in source than it work fine. Why the SCD is not working fine any idea?

      More over if I do my Intial Load with IKM Slowly changing dimension and then modify this for SCD Type I and Type II. Things works fine. Is it required that in the begining I do need to use the same IKM for Initial Load and Then for CDC Type I and Type II ?

      Regards
        • 1. Re: Un Expected Result in IKM Slowly chaning Dimension Module
          JeromeFr
          Hi,

          In order to use the IKM Oracle Slowly Changing Dimension, you have to define a property on each column of your datastore.


          1. In the Model Pane on the left side, expand your datastore to see the columns
          2. Double click each column, go to the Description tab and choose the right value for Slowly Changing Dimensions Behavior



          In this knowledge module, the value for the "infinity" end date is 01-01-2400. The flag for the current record is set to 1 when it is the current record and 0 if it is not..
          If you want to change that, you'll have to change the KM.


          Hope it helps.

          Regards,
          JeromeFr
          • 2. Re: Un Expected Result in IKM Slowly chaning Dimension Module
            965271
            Hi JeromeFr,

            Thanks for your valued time, I already map the columns for IKM slowly changing dimension as you told me. Like ENAME I set in the Description tab Overwrite on Change and SAL column set to Add New Record property.

            The problem is even with these setting its inserting the new record, If my earlier initial load took place with another IKM Oracle Incremental Update.

            But if I use the same IKM Oracle Slowly Changing Dimension for both the cases. Like Initial Load the same IKM and than in Type I and Type II implementation the same IKM then the things work fine when I update the interface. This thing was bit confusing for me. My confusion is do I need to use the same IKM from the beginning in order to avoid any problem or I can achieve this with two different IKM like for my first Initial Load I will use IKM Oracle Incremental Update and then for CDC and Type I, Type II implementation I will use IKM Oracle Slowly changing Dimension.

            If from the beginning as I said I use IKM Oracle Slowly changing dimension than the things work fine.

            Regards
            • 3. Re: Un Expected Result in IKM Slowly chaning Dimension Module
              JeromeFr
              This behaviour is very strange.

              So you have two interfaces : One for the initial load (using IKM Oracle Incremental Update) and another one for the following loads (using IKM Oracle Slowly Changing Dimension, with the same target). Right ?

              Could you check in the operator that the second run has the same steps as when you double click on the IKM Oracle Slowly Changing Dimension on the left pane (some steps may be missing)?
              What are the figures (# insert, #update, ...) for the "update old rows" steps?
              • 4. Re: Un Expected Result in IKM Slowly chaning Dimension Module
                965271
                Hi JeromeFr,


                Thanks, Your understanding is close to my POC the only difference is after running the initial load I am updating the same interface in order to recycle the error records.

                Time is running out :) ......I will check out your suggestion and verify the steps for both IKMs.......At least things are working with some limitations

                I will appreciate if you can put some light how can I control deletion in CDC case when I am using IKM Oracle Slowly changing dimension. As per my understanding as the standard module for DWH it doesn't allow deletion at target side due to Data Ware House project.

                When I delete some record at source its showing me data in the Journal table with FLAG=D but not deleting anything from target. Instead of Deleting record at target I just want a update statement to mark this record as Deleted when ever something deleted in source. How can I achieve this?

                Regards
                • 5. Re: Un Expected Result in IKM Slowly chaning Dimension Module
                  JeromeFr
                  I've still no idea for the original issue.


                  For the last point, you can probably do this by editing your KM :
                  Duplicate the "update old rows" step and slightly modify the new step to only update your marker column. Change the where clause as well so it's only done on rows flagged with a D.
                  • 6. Re: Un Expected Result in IKM Slowly chaning Dimension Module
                    965271
                    Hi JeromeFr,

                    Many thanks for your kind lead. Yup I modified my IKM Oracle Slowly Changing Dimension, I added another step Delete Records Under the Insertion. As you told I copied the update code and modified according my need. Now its working fine. Posting the code it may help others..


                    <% if (odiRef.getColList("", "A", "", "", "(SCD_UPD and REW)").length()>0) { %>
                    update     <%=odiRef.getTable("L", "TARG_NAME", "A")%> T
                    set     ACTIVE_RECORD = 'D'
                    where     (<%=odiRef.getColList("", "T.[COL_NAME]", ", ", "", "SCD_NK")%>)
                         in     (
                              select     <%=odiRef.getColList("", "S.[COL_NAME]", ", ", "", "SCD_NK")%>
                              from     <%=odiRef.getTable("L", "INT_NAME", "A")%> S
                              where     S.IND_UPDATE     = 'D'
                              )
                    <%=odiRef.getColList("and\t", "T.[COL_NAME]\t= 1", "\nand\t", "", "SCD_FLAG")%>
                    <%=odiRef.getColList("and\t","[COL_NAME]\t= to_date ('01-01-2400', 'mm-dd-yyyy')","\nand\t","","SCD_END")%>
                    <%}%>

                    Warm Regards for all your kind support...
                    • 7. Re: Un Expected Result in IKM Slowly chaning Dimension Module
                      JeromeFr

                      Great, thanks for sharing the code!

                      Everything is working or you still have problem with the insertion on the second run ?

                      If everything is fine, please mark the question as solved.



                      Regards,
                      JeromeFr

                      • 8. Re: Un Expected Result in IKM Slowly chaning Dimension Module
                        965271
                        Hi JeromeFr,

                        Thanks for all your kind support.

                        Regards
                        Umer Farooq

                        Edited by: 962268 on Jan 17, 2013 7:52 AM