11 Replies Latest reply: Aug 27, 2013 5:54 AM by user13430807 RSS

    Move view to another relational model in the same design

    user13430807

      Hello,

       

      is it possible to move  a view to another relational model in the same design? It is possible to copy the view, but this creates a total new instance of the view and the connection to the underlying tables is lost.

       

      Joop

        • 1. Re: Move view to another relational model in the same design
          Philip Stoyanov-Oracle

          Hi Joop,

          what does mean move? What is the idea behind?

          and the connection to the underlying tables is lost.

          If those tables appear in the model you can open the view in Query builder and connections to tables will be restored

           

          Philip

          • 2. Re: Move view to another relational model in the same design
            user13430807

            Hello Philip,

             

            The idea behind it is the following.

            I have a lot of objects in one relational model. In my case these are imported from Designer. I want to split up a part of the objects to another relational model since they form a logical group or they belong to different schema's. By splitting them up I create logical groups.

             

            How I create it now is the following:

            - create new rel model

            - copy object and underlying objects to new rel model. They are links to the original objects.

            - for each object: copy to new object (becomes <name>V1), delete linked object, rename <name>v1 to original name, delete original object

            A move of the object from the original rel model to the new model would help this process speeding up. Another advantage is that I don't have to copy underlying objects, and the original oject ID is preserved.

             

            I hope this explains the question.

             

            Joop

            • 3. Re: Move view to another relational model in the same design
              Kent Graziano

              Why do you not just use sub views to break up the model into logical "display" groups? I use that extensively in order to make the model more understandable. That is way easier than trying to maintain separate models and deal with the shared objects.

               

              Having multiple relational models in one design is great if you have one Logical ERD and want to deploy it to different physical designs like a SQL Server version and an Oracle 11g version (for example). I would not try to use it to split objects that really are part of one design.

              • 4. Re: Move view to another relational model in the same design
                user13430807

                Hello Kent,

                 

                Splitting up in subviews is nice for making overview pictures. But if I put all objects in one relational model then:

                - the list of tables and views in the browser is becoming very large. This doesn't work easy in this case. Of course, if you work subview based then this is a smaller problem.

                - the bigger problem is the size of the relational model is becoming so big that loading the model takes to much time. If you use several relational models you can disable the loading of relational models during the opening of the design and this saves a lot of time because you only open the relevant relational models.

                 

                Joop

                • 5. Re: Move view to another relational model in the same design
                  HansJK

                  Hi Joop

                   

                  I am following Philip's recommendation (as discussed in https://forums.oracle.com/thread/2568875) to split the objects into relational models based on their owning schema.  Links to objects in other schemas then become remote objects in the referencing schema, which are easily updated through the "update remote objects" action.

                   

                  As you will see in above-mentioned thread this splitting will work best with the EA4.01 version (some stuff not yet handled in 3.3).

                  I cannot use EA4.01 for more reasons than this specific issue, so I am still on 3.3.

                  So I had to do some manual stuff on the physical model (for new objects not yet in DB) and just imported from data dictionary (the existing objects) into the respective relational models to ensure the physical models contain all needed info.

                   

                  So far it has been going ok.  I concur on your experience of the impact that the size has on load and save times though.  This whole thing is checked into SVN so that adds overhead too.

                  (Would have loved to be able to swop out the SVN client in the tool for the newer one in version 4, since that will improve the SVN experience.)

                   

                  I would not just rely on sub-views (to make a single huge/complex model simpler). 

                  I only use them to make the respective relational model easier to view/work on.

                  (Have not yet figured out why there are sub-views and displays, i.e. what the actual difference is between them.)

                   

                  (For me it is an issue that there is now only one logical model as in my scenario the logical model would be huge (we have 19 separate schemas, equating to roughly 17 different applications/modules.  I would ideally like 17 Logical modules in this usage scenario, but that is apparently something which 'may' be done somewhere in the future.)

                   

                  I hope this helps in some way.

                   

                  Regards

                  Hans

                  • 6. Re: Move view to another relational model in the same design
                    Philip Stoyanov-Oracle

                    Hi Joop,

                     

                    just to clarify your steps:

                    1) create new model

                    2) move view and underlying objects it depends on to that model as remote objects

                    3) using copy/paste you create editable copy of remote object representing the view

                    4) remove remote object representing the view and rename editable version of view

                    5) leave other remote objects created in step 2) as remote objects

                    6) remove original view

                     

                    Here Re: Advice/Recommendation please? How to best handle big models with various schemas? I explained how "Create new models based on schema names" wizard works.

                    What was intended to be done but slipped away is approach for processing foreign keys and referred tables in another schema to be applied for views and objects they depend on belonging to another schema.

                    Your approach(if I understand it correctly) is different - you want in new model some editable views and all objects they depend on to be represented as read-only remote objects. We need to clarify what you want and then can log enhancement request for that specific processing.

                    And you want those editable objects to retain the same ID as their original representation.

                     

                    Philip

                    • 7. Re: Move view to another relational model in the same design
                      user13430807

                      Hello Philip and Hans,

                       

                      first I  want to thank you both for your help. I am giving a reaction on Philip to keep the discussion clear.

                       

                      The splitting up of a relational model based on schema is a nice feature. I didn't know this and it works.

                       

                      But the biggest drawback of using schema's is that the the views become invalid in the Query Builder. As soon as you assign the views/tables to schema's, the view definition needs to have the schema name in front of the referencing table/view. You can see this in the graphical editor after you apply schema's: the underlying table/view is represented as a box without content. As soon as you add the schema prefix and press 'Apply' the box is replaced with the column names of the table/view. So views have to be corrected after adding the schema's. This can be done fairly easy, but this schema name is also taken over in the DDL generation. and I don't want schema names in the DDL since we use synonyms.

                       

                      So I came up with my solution as described above, which also has drawbacks:

                      - you have to make links to all underlying objects. This pollutes since I do not really need them there as a link, they need to be only there for the editor.

                      - the original object ID is lost.

                       

                      So that's why I came up with a move of the objects.

                      Another solution could be the following: In the Query Builder make it possible to refer to objects of schema's from other relational models in the same design.

                       

                      Joop


                      • 8. Re: Move view to another relational model in the same design
                        Philip Stoyanov-Oracle

                        Hi Joop,

                         

                        As soon as you assign the views/tables to schema's, the view definition needs to have the schema name in front of the referencing table/view.

                        if view and the objects it refers are in the same schema then schema prefix will be removed from objects in select statement - you need to open view in query builder for that or use parse/validate if status (yellow/red) allows that.

                        Exception from that rule is if you check "Include schema name in query" for that view (that property is available in view dialog).

                         

                        Philip

                        • 9. Re: Move view to another relational model in the same design
                          user13430807

                          Hello Philip,

                           

                          that works. The view gets validated after pressing 'validate selected views'. Now the select contains the schema name.

                           

                          But the problem is the DDL generation: it also contains the schema name. And I have a synonym for that object, so I don't want to have the schemaname in front of the object.

                           

                          Joop

                          • 10. Re: Move view to another relational model in the same design
                            David Last-Oracle

                            Hi Joop,

                             

                            Have you tried unsetting the "Include Schema in DDL" option in the Data Modeler/DDL Preferences?

                            This will stop the schema name being added in the CREATE statements.

                             

                            David

                            • 11. Re: Move view to another relational model in the same design
                              user13430807

                              Hello David,

                               

                              in the view create statements the schemaname is still in after unsetting the mentioned option. I think the select statement is taken over 1:1 in views.

                               

                              Maybe you can consider this a bug in the DDL generation of views that this option is not taken into account.

                               

                              Joop