3 Replies Latest reply on Oct 29, 2013 11:52 AM by delostilos

    Split table via transformation script

    delostilos

      Hi,

       

      Is it possible to to call the split table function in a transformation script?

      I like to try to script the transformation of a relational 3NF model to a Data Vault model.

       

      Regards,

      JJ.

        • 1. Re: Split table via transformation script
          delostilos

          Hi,

           

          The plan is:

          - reverse engineer a physical model (sakila) to a relational model

          - engineer the relational Sakila model to a logical model

          - surrogate the model via this transformations script:

           

          // set the surrogate for each entity

          entities = model.getEntitySet().toArray()

          for (var i = 0; i<entities.length;i++){

              entities[i].createSurrogateKey = true

              model.getAppView().log("Surrogate key set for entity: " + entities[i]);

          }

          // set the relations to use the surrogate key

          relations = model.getRelationSet().toArray()

          for (var i = 0; i < relations.length ; i++) {

              relations[i].useSurrogateKey = true

              model.getAppView().log("Use surrogate key set for relation: " + relations[i]);

          }

           

          - engineer the logical model to a new relational model "Sakila Data vault"

          - split the tables until we have a data vault like model

          - add the audit fields via a template

           

          Then we shuold have a mapping from "Sakila (Rel) <-> Sakila (log) <-> Sakila Data Vault". Which would make it possible to generate the ETL mapping needed to fill the "Sakila Data Vault" from the "Sakila (rel)".

          It's just an idea I have. That's why I would like to know if it is possible to call the 'split table' function in the relational model. I hope it also keeps the mappings.

           

          Regards,

          JJ.

          • 2. Re: Split table via transformation script
            Philip Stoyanov-Oracle

            Hi,

             

            there is no split table method that you can use. "Split table" wizard in Data Modeler move or copy columns depending on user selection (no automatic function). Unfortunately mapping info is not retained - I logged a bug for that.

            Which would make it possible to generate the ETL mapping needed to fill the "Sakila Data Vault" from the "Sakila (rel)".

            It's interesting how you are going to generate that - I know Data Modeler do not generate such result. Otherwise you can track such mappings using "Impact analyses" functionality.

             

            Philip

            1 person found this helpful
            • 3. Re: Split table via transformation script
              delostilos

              Hi,

               

              Thanks for the answer. Then I'll have to write a transfomation script to do the split table.

              I was thinking of writing a script in the logical model that can see the mappings to both relational models.

              If that does not work I'll create a reporting repository and see if I can use that to do the trick.

               

              But to be able to generate a Data Vault model from a 3NF model in Data Modeler via scripts would be a nice achievement.

               

              Made a shorter version of my 'Surrogate script':

               

              function log(message) {
                  model.getAppView().log(message)
              }
              // set the surrogate for each entity
              for each(entity in model.entitySet.toArray()){
                  entity.createSurrogateKey = true
                  log("Surrogate key set for entity: " + entity);
              }
              // set the relations to use the surrogate key
              for each(relation in model.relationSet.toArray()) {
                  relation.useSurrogateKey = true
                  log("Use surrogate key set for relation: " + relation);
              }


              Regards,

              JJ