Another Quick Tip for SQL Developer Data Modeler
Not sure how I missed this little utility, but I did.
Ever need to quickly build a set of views on a set of tables to create a read-only data layer you could expose to some users or processes?
Well as I am developing a new data warehouse for my current client, we decided to control access by creating a read-only user that would hold views that pointed back to our main data warehouse schema. The BI tool points to the read only schema (for now anyway).
Anyway, under the Tools menu I found a Table to View generator.
Under the Tools menu look for the Wizard to create a view definition based on a table
Once you select it from the menu you get a dialog box with all the tables in your model selected. So with one push of a button you have views on all the tables.
Get a list of table to convert and Select or Deselect all
Then you can edit the views, if needed, using the view query builder.
Or you can select (or de-select) specific tables to build views on.
Even better – the tool applies a naming standard on the output view names (v_<table name>).
On top of this, if you happen to have some views (maybe for testing?) that you want to turn into tables and then populate with an ETL process, right below the first wizard is an option to create a table definition from the view definition.
Now granted most of you can easily do either of these tasks using plain old SQL, but imagine you need to do it for several hundred tables.
This little wizard would save you a ton of time (and testing).
And you will have a documented data model when you are done.
So go give it a try!
Later.
Kent
NB: I am taking a little downtime in early August so don’t look for any new posts until near the end of the month.
The Data Warrior 