It’s tough to know if you’re using a product to its fullest capabilities, especially if you’re relatively new to a product. As you get up to speed with ER/Studio, our award winning data modeling tool, I strongly recommend using the evaluation guide we have on our website. We’ve tuned thing puppy over the years to not only work for people evaluating the product with a fresh set of eyes, but also help gain more exposure to the meat of the product and most of the functionality you’ll use on a daily basis.
–
The evaluation guide covers the essentials like how to easily navigate large data models, using shift+click on an entity to enable on-screen editing for rapid entity attribution, submodeling/subject area creation, running macros to automate common tasks, denormalization in the physical model and even getting started with the ER/Studio Enterprise.
–
As you get more comfortable with the product you’ll peel back the layers of the onion to uncover more and more features you can use in your arsenal. Here I’d like to discuss some powerful features in ER/Studio that don’t often get used by our user base. Even if you’re a seasoned veteran with the product, I bet some of these will surprise you.
–
1. Submodel Usage in the Where Used tab of an entity
The submodel usage is a tremendously helpful aid when working with large data models that have a large number of submodels. The main model may become a tangled mess and the only way to make sense of an entity and its relationships is via browsing the submodels that said entity is a member. ER/Studio will automatically display this info under the submodel usage node in the Where Used tab of the Entity/Table editor (see figure 1).
Figure 1 - Submodel Usage
That way it is right at your finger tips any time you’re viewing the properties of the entity. In the example above we’re seeing that the PRODUCT entity is a member of the Production, Inventory, Bill of Materials, and Product Details submodels. Another important note about Figure 1 is that you can nest submodels to further help organize them which is also very helpful as your data model and the number of submodels grow. This information is also available via the HTML reports you can generate with just a few clicks of a button through the Reports Wizard.–
2. Attachments/Security Property searching in UNU
Attachments and security properties when leverage properly in ER/Studio can be an extremely powerful tool to extend the metadata you’re capturing in your data model (I’ll save the full rundown of attachments and security properties for a later date). Short story is if there is “anything else” you’d like to capture for an object in a data model besides a definition or note, the attachments system can be used to accomplish that. For example, say you have a list of stewards responsible for different sections of your models and you want to make sure that anyone else viewing a report of a model knows who to call if they have a question/comment about what their looking at, well, you can create an attachment called Data Steward with a valid value list of the team members.
Then, you can assign those stewards to anything in the data model. The data security properties are just an implementation of the attachments system specifically geared toward capturing compliance and security classification metadata. So if you have privacy levels of specific data or personally identifiable information (PII) tags you want to capture, you can use the data security properties for that. Both are located under the data dictionary tab (second tab from the left below the explorer tree).
–
So what gives? Well after you’ve applied this metadata to objects in a data model, we have a search and replace utility that will allow you to isolate those objects very easily (see Figure 2).
Figure 2 - Results set for Regulated Customer Data
What I’ve done is go to Tools > Universal Naming Utility (fancy name for a search and replace utility). Here I’m looking for “Regulated Customer Data,” which is a data security property assigned to entities and views in both the logical and physical models. Instantaneously, the information is brought back and from there I can report or even create a submodel to isolate search results.
–
3. “Apply To Submodel(s)” tabs in the Explorer Tree
This is another gem. Say you add a new entity to a large data model that has a bunch of submodels. Well, after some deliberation you decide that new entity related to quite a few of them. You can simply right click on that entity, select Apply to Submodels(s) (see Figure 3), then a UI pops up allowing you select where you want to place it. What could have taken many steps is now automagically done in one step.
Figure 3 - Apply To Submodel Option for Entities
-
4. View Bindings Right Mouse Click on domains/dictionary objects
If you’re a loyal user of domains, you’ll no doubt want to see where their used across a dm1 file in both the logical model or any physical models. You can, of course, access this information and bind/unbind the domain (or other dictionary objects) from the binding information tab within the editor, but as often the case, it’s nice to have that information with one click rather than several. If you right click on a domain (or any other dictionary object), you’ll see a View Domain Bindings option at the bottom of the menu. This will present a UI (see Figure 4) that will allow you to:
- See where the domain is used across the dm1 file
- If any properties differ from what was set in the domain (seen by expanding each node).
Figure 4 - View Bindings for a Domain
One thing to note is if you are using the Enterprise Edition, which introduces the collaborate feature such as model security, versioning and collaborative check in/out version history, you also have at your disposal the enterprise data dictionary system. This allows you to share a data dictionary across any model in the repository. To see the usage of enterprise data dictionary objects across the entire repository, you can go to Repository > Data Dictionary > Enterprise Dictionary Bindings. I’ll need to cover that another day though.
–
5. Macro Shortcuts
The automation interface (VBA API) is one of the more heavily used aspects of the tool across the user base. Many organizations have leveraged the macro interface to access the products innards and mold it to do whatever they need. On the flip side, I don’t see a whole lot of people using the macro short cuts. They’re a great way to integrate your favorite macros into your daily workflow.
We have many sample macros that will allow you to dump an entity’s properties to Excel, name the primary or foreign constraints, update storage parameters, add a table prefix, etc. Many of these also work on selected objects so you can execute the same task on many objects to save a bunch of time. To setup the macro shortcuts, just right click on the desired macro and go to Add/Remove to Shortcuts, then select the object type you want, i.e., entity, view, relationship, etc. Next time you right click on that object type, you’ll have that macro available (see Figure 5).
Figure 5 - Macro shortcut for entities
Well, that’s it for now. Hope you found these helpful. If you have any secrets you’re uncover, please drop a comment below…
–
And for more on database tools and developer software, including free trial downloads check out Embarcadero Technologies (www.embarcadero.com)
available as podcast
