ODC Appreciation Day: Oracle SQL Developer Data Modeler

I know this is too obvious coming from me but Data Modeler is my favourite tool and I use it every single day. Without Data Modeler I could not do my work and design databases well and efficiently and without it I could not show other people how easy and important it is to design databases. 18.3 just came out and it is ever better than the previous versions!

https://www.oracle.com/technetwork/developer-tools/datamodeler/downloads/index.html

Thank you ODC for Oracle SQL Developer Data Modeler!

Advertisements

Finding objects that have a certain property “null”/”empty”

How can I find all the tables in SQL Server 20112 physical model that has the Text image  defined empty?

Right-click on the Browser on the physical model you want to search. Select Search. Select Advanced Mode. Select Table, press Add Property and select Text image fg as a property. Press Search.

TextImage

These are all the tables having Text image property null/empty.

The same works for any searches on logical, relational or physical property searches.

 

Setting a property to “empty” for several objects

In many cases I need to find those properties that are not “empty” and set the property to “empty” to all of those. One example is when I’m desiging a data warehouse and the source tables (which I will reverse engineer as a base for the DW tables) have default values. I do not want to have default values for my DW!

Let’s see an example.

First I need to find the entities (all tables or physical tables) that has a default value.

Start Search either by pressing the Search icon in the Tool bar or right-click on the Browser on the logical model and select Search.

Select Advanced Mode. Select Attribute. Press Add Property. Select Default value. Choose Not.

Empty

And press Search.

Empty2

Then press Properties.

Empty3

Select Default value and press the arrow pointing to left, select “empty string”. Press Apply.

Empty4

Now the default value property for all the attributes listed as Serach result have been updated to empty.

 

Empty5

You can use this same method for changing any properties of logical, relational or physical model to empty.

 

 

 

 

Oracle SQL Developer Data Modeler 17.4

This is a version I have been waiting: so many important bug fixes! I downloaded it immediatelly the evening it was available and finished one of my projects using it.

If you have not downloaded it yet, do it today: http://www.oracle.com/technetwork/developer-tools/datamodeler/downloads/index.html

Why did I waited for it? Because of, for instance, these bug fixes:

27245333 – IMPORT OF DDL AS ORACLE 12CR2 DOESN’T RECOGNIZE DATA TYPES
27182476 – SQL SERVER 2012 DDL GENERATION FAILS WITH CLASS CAST EXCEPTION
26985782 – STANDARD REPORT – COLUMN DATA TYPE IS NOT INCLUDED WHEN COLLECTION
27040952 – DESIGN PROPERTIES DIALOG DOESN’T WORK
26988362 – IMPORTING LEGACY PHYSICAL MODEL RESULTS IN TABLE ITEMS LISTED RED IN HIERARCHY
27041167 – REVERSE CLAUSE FOR INDEX IS NOT PROCESSED DURING IMPORT FROM DATABASE
26918584 – SYNC TO DB CANNOT RECOGNIZE TABLES WHEN PROXY USER IS USED
26919656 – PROBLEMS WHEN EDITING UDP FOR ATTRIBUTES IN ENTITY DIALOG
26750611 – INVALID INSERT STMT ON ‘USE QUOTED IDENTIFIERS’ CHECKED AT DDL PREVIEW SCRIPT
26920080 – “INCLUDE SCHEMA IN DDL” SETTING IS NOT CONSIDERED FOR PROCEDURES
26932946 – ANNOYING BEHAVIOR WHEN CREATING COLUMNS

On my way HOME…

I am at the Birmingham airport on my way home from UKOUG Tech 2017. This was my last event for this year. Cannot wait to be home to celebrate the independence of Finland tomorrow. 100 years independent!

My year was busier than ever, I kind of hope next year will be easier but it is not an easy wish because I really enjoyed this year a lot. In addition to my everyday work I have been teaching at the university, studying, researching and speaking in different kinds of events about many different topics. In 2017 I spoke at

UKOUG, Birmingham, UK

DOAG, Nuremberg, Germany

Devoxx Morocco, Casablanca, Morocco

Nordic ACE Tour
Denmark
Sweden
Norway
Finland

HrOUG, Rovinj, Croatia

SIOUG, Portoroz, Slovenia

Oracle Open World, San Francisco, USA

Tajikistan TechDay, Dushanbe, Tajikistan

POUG, Krakow, Poland

Oracle Code Seoul, South Korea

APEX Day, Oracle House, Finland

Oracle Code Bengaluru, India

KScope17, San Antonio, USA

E4 2017, Dallas, USA

BGOUG, Bulgaria

OTN EMEA Tour
Baku, Azerbaijan
Madrid, Spain

Riga DevDays 2017, Riga, Latvia

APEX Connect, Berlin, Germany

BIWA Summit, Redwood City, Oracle HQ, USA

 

If I counted correctly that would be 23 events in a year. Now it is time to take a break for holidays. Next year does not start very slowly because I have already agreed to go to Israel in January and Ireland in March. But until that: enjoy December. Let’s hope it will be a white Christmas and that Santa (from Finland!) pays a visit 🙂

 

Cheers,

Heli

 

 

Reporting with Data Modeler 17.3

In version 17.3 there are some changes in the reporting user interface. The reporting itself works as before.

The reporting form has been devided into two tabs: General and Objects.

General includes things that define how the report looks like, including templates and output formats:

reporting1

You can also define the Company name, some reporting preferences, Reports Directory and Saxon XSLT 2.0 jar file path in the General tab. The defaults can be set in Preferences as before:

reporting3

And Objects tab include selecting the source (open design/reporting repository) and configurations (which objects will be included to the report):

reporting2

 

Default System Types Directory

Why would I define the Default System Types Directory in Preferences?

SystemTYpes

If that directory has been defined you do not need to worry about domains etc. when upgrading Data Modeler. All the system type kind of things are automatically available even though you upgrade.Things like:

Report templates
Report configurations
Saved searches
Custom report templates
Domains
RDBMS Sites
Custom Scripts
Types
Global Design Level Settings

And you can also add this directory to version control to be able to keep track of the changes…

 

Sharing Design Properties/Design Level Settings

I have defined the Naming Standards, Templates etc for one design and I would like to have those same settings for another design I am working on. How can I do that?

You can do that using the Export/Import functionality. Open the design that has the right settings. Go to either Design Properties or Design Level Settings and select Export.

Export.jpg

Save the file. Remember to add the extension xml.

Open the design you want to implement those settings. Select Import and find the file with the exported settings. Press Open. Now the Design Properties/Design Level Settings are the same as in the original design.

 

P.S. If you want ALL your designs having the same design level settings simply check the “Use global design level settings” box and no need to do any export/import.

P.S.2. You might be interested in this too: helifromfinland.blog/2017/10/13/default-system-types-directory/

 

Design Level Settings

I am sure you remember there are Preferences and Design Properties in Data Modeler. Preferences affect all the designs in a Data Modeler installation (in that computer) whileas Design Properties affect only that design they are defined for.

 

Now in Data Modeler 17.3 you can find those Design Properties also under Tools menu by a name Design Level Settings:

DesignL0

 

These are the sames you can still find under Design Properties too and you should definitely check them (either under Design Properties or Design Level Settings), because they are important.

DesignL1

 

 

P.S. You might be interested in this too: helifromfinland.blog/2017/10/13/default-system-types-directory/

 

 

Where are my domains?

I just upgraded Data Modeler to the latest release/installed Data Modeler to a new computer but all my domains are gone. Is there any way to get them back or do I need to define them manually again? Of course there is a way!

Select File, Import, Domains.

Domains1

Then find the file your domains are saved to using Open Domain File.

Domains2

Import.

Domains3

Go and check the domains.

Domains4

And there they are.

P.S. You might be interested in this too: helifromfinland.blog/2017/10/13/default-system-types-directory/