By: Scott Murray | Last Updated: 2017-11-08 | Comments (3) | Database Administration
How can I make interaction with my databases and servers more efficient and effective, no matter if I am a developer, a DBA, or even a data or business intelligence analyst? And how can I connect to a whole sundry of database management systems?
Anyone who has worked in technology is familiar with the ever-increasing array of tasks that we encounter in our daily work. Job fields connected to anything data related tend to expand exponentially in their scope and depth. Whether you are a database developer, a BI developer, a DBA, or a data analyst, utilizing tools to increase your effectiveness and efficiency allows you to provide a higher level of service to your ultimate customer and business. One such product is Aqua Data Studio (ADS) by AquaFold (a division of Idera, Inc.). This tool addresses many of the pain points that database professionals face on a daily basis. Aqua Data Studio contains a wide array of features all within a single IDE.
For this particular tip, we are going to focus on several of the high value features that Aqua Data Studio provides. These features include:
- Visual Query Builder
- Visual Analytics
- ER Modeler
- Table Data Editor
- Compare and Sync
- Variety of supported database platforms and operating systems
In addition to these features we will review in this tip, Aqua Data Studio contains other helpful features such as Execution History, Automation tasks (scripting), and Object Search. Please see all the features here.
Visual Query Builder
Of course, the first stop for most database professionals in dealing with data is to write a query. For many folks query writing can be a thankless task, but using Aqua Data Studio’s Visual Query Builder simplifies the task and reduces the time it takes to build a query. As shown below Query Builder provides quick access to build a query.
Once you are connected to a particular server and database, a list of tables and views that are available to select from appears on the left side of the workspace.
On the workspace, you can easily add full tables to the design grid. Adding items to the various components of the SQL query, such as “order by”, “group by”, or the “criteria”, is as simple as dragging the field to the appropriate box on the workspace. As you add tables to the workspace, you will notice that the actual SQL Query is generated in the lower third of the screen.
Creating a join is as simple as clicking on the field in one table and dragging the connection to the field in the related table. In the above example two joins were created as inner joins, but clicking on the join symbol enables altering the join type to a left, a right, or a full outer join using the checkboxes.
Dragging a field to the Where clause pane opens a second screen which permits the addition of specifics for the criteria such as =, >, <, or IN along with the values to be evaluated.
Also helpful is the opportunity to review the specifications about the tables that you are working with. You will notice that the primary and foreign keys are shown in the object explorer area for easy reference.
Additionally, you may need to use a derived table as part of your SQL query. The derived table is easy to create through the GUI interface by right mouse clicking on the workspace.
The derived table is created on the workspace in a similar fashion to any other query; however, you will notice in the below screen print a new tab was created for the derived table. The table acts as any other table at this point; in our example it defaults to a cross join, but it can easily be changed to an inner join or left / right join.
Once you are satisfied with the query, you can execute it by clicking on the green arrow in the toolbar. The query results appear in the lower portion of the pane.
One innovative option is to send the results to Excel. Selecting the View as Spreadsheet option opens an Excel spreadsheet with the result set and formatted column headers.
Finally, within a Query Builder you actually have the option to clear the entire worksheet or to save the query for later use. You should note a worksheet is comparable to a project for the query you are building.
The next feature to be highlighted is the ER Modeler. Similar to the Visual Query Builder. The ER Modeler allows you to easily create a completely new database model from scratch, or you can use the modeler to reverse engineer an existing database which contains no ERD.
To create a completely new model, you can select ER Modeler from the toolbar and then New. You are then given the option to select what database system and version to use within the model. Aqua Data Studio supports a variety of database platforms including relational, nosql and cloud to suit different needs.
Next, you would begin adding your tables, attributes, and relationships. You first add a table by right clicking in the workspace and select new table.
Subsequently, you double click on the table to add the individual columns / attributes. Not only are you defining the names of fields, but also the data types and key information.
Subsequently, you can add the relationships between the tables by right clicking and selecting New Relationship.
For the relationship, you can define a name, define the relationship, select the table and fields which are to be related, and whether to enforce the constraints for the relationships.
Now the relationship shows within the ERD.
If needed, the tables can be categorized into a group or region. The region permits you to easily color code your tables for easy viewing and identification.
Once the design is finished, a script can be generated to build the actual script in the desired database system.
The script generation tools allow for the selection of specific schemas and objects, if desired.
The script can be sent to a file or previewed within Aqua Data Studio.
As an alternative, an existing database can be used to reverse engineer an ER diagram. The first step is selecting the Generate option and then selecting the desired database.
Next the schema and objects to be included in the diagram are selected.
The ER Diagram will be generated automatically based on the current constraints, relationships, keys, and attributes.
Furthermore, you will have a list of tables on the left side of the workspace from which to select for future use.
At this point, changes can be made to the design and saved. The design changes can also be implemented by again generating a script. This feature also allows the script to be generated in any of the supported database systems which allows for simpler transition to a new DBMS.
Table Data Editor
Up to this point we focused mostly on the database definition (DDL), but the next feature to be highlighted is the Table Data Editor. The Table Data Editor provides a way to modify data within a table in a simple and often Excel-like fashion and includes features such as find and replace, color coding of changes, clear or undo changes, and quick filters, among others. The Table Data Editor can be initiated directly from two main screens: either the table list pane using the edit option or it can be executed from the query builder screen.
Once the Query Editor opens, changes can be made directly to the grid. One of the first items that you will notice upon making changes is that the cell is bolded to notate a change has been made to that cell. Also, the row is highlighted in blue.
Another highlight that occurs is the red cell outline. The red outline indicates that an invalid value has been entered into a field, such as in the below example where a number was entered into a date field.
Next, if a mass change is needed, the auto fill option can be used. The auto fill method works in a somewhat similar way to Excel and allows you to auto fill from an originating cell. In order to complete the fill, you must first change the first cell to be changed and then select all the cells to be auto filled including the original cell. Following the selection of cells, right mouse click and select Fill > Down (or right / left / up). Now all the cells will reflect the original cell value.
Likewise, the Table Data Editor includes a Find and Replace option which again functions similar to the find and replace that you would see in most applications.
Thus, the find and replace will change all instances of the Find word and of course replace it with the Replace word, e.g. Red with RedREALLYRED in the below example.
If the need arises to further filter the data grid, a quick filter value can be added to the filter box in the upper right corner of the deck.
As you are doing your editing, you may need to review a table’s design; therefore, the Primary Key tab provides a view of the table design for quick reference.
As changes are made to the Edit Grid, the actual SQL for the changes are accumulated and recorded within the Preview SQL Tab.
In addition to editing rows, rows can easily be inserted before or after the current selected row, the left two buttons in the below figure. Alternately, the selected row(s) can effortlessly be deleted using the button with the big red X. The last button, on the far right, allows the currently selected row to be cloned, another neat feature to speed up input.
Please note that at this point in the data edit process, the changes we have made so far have NOT been saved back to the table. In fact, several options exist for us to clear one, some, or all the changes which have been done so far. The three buttons noted in the below illustration do the following, from left to right:
- The left-most button clears ALL changes
- The middle button clears changes only in the selected row
- The right-most button clears changes only in the selected cell
Therefore, changes are not committed to the tables until you save the changes. The three save buttons allows you to Save and Refresh, Save and Close, or Save to Script. Note the third option saves the SQL to a script and does not execute.
In addition to querying and changing data, it is also natural to want to visualize the data you are reviewing. Viewing data in summarized graphical format always moves data analysis to the next level. The Visual Analytics feature provides drag and drop functionality to effortlessly create a number of visuals. Furthermore, the Visual Analytics deck uses the concept of Worksheets and Dashboards. Worksheets are the objects that make up a dashboard. To get started with Visual Analytics, you can either open a completely new Visual Analytics tab from the main Aqua Data Studio window or it can be opened directly from the Query Results tab.
In the below example, an existing query is used, and the Visual Analytics window opens with the fields already divided into dimensions and measures.
We can immediately begin to drag and drop fields onto the column and row area and a chart will automatically appear based on the fields selected.
The visual that was initially created can easily be changed to a different chart type using the chart properties option.
As shown below, the chart was actually changed to a crosstab, very similar to a pivot table. Also, the font color for the measures was changed to green.
We could also straightforwardly flip the chart to a pie chart by size of the part.
Visual Analytics features some advanced visual capabilities such as creating dual axis charts.
Moreover, calculated fields can be added to your field list either as a dimension or a measure. You can also integrate R into your data sets by using a calculated field that initiates a R command with the results being posted back to the worksheet.
Furthermore, you can combine multiple worksheets into a single dashboard. Upon creating the dashboard, worksheets containing the charts can simply be dragged onto the Dashboard. Once on the dashboard, each object can be moved to the appropriate panel on the dashboard.
Compare and Sync
The final feature of the Aqua Data Studio that I would like to emphasize in this tip is the Compare and Sync capability. The tool allows comparisons not only between databases on the same DBMS, but also across different platforms. Furthermore, not only is a comparison executed, but a sync between two schemas can be processed to be sure the two schemas match completely. To start the comparison, the Tools options is selected, then Compare Tools.
To invoke the Schema Synchronization, the two databases and schemas must be selected (all schemas can be compared as shown below). Next the object types to be compared must be selected along with various options for the comparison. Finally, individual objects can be selected or unselected as desired.
The comparison will run through the object list looking for differences between the selected objects. The differences will show in a separate row. In the example below, the compare shows a difference in the address type table between the two databases.
By selecting the row containing the difference, the script viewer in the bottom half of the screen shows that the table on the right side of the comparison has an extra field called TestAdd.
Syncing the two schemas requires you to select the objects to sync and then clicking on the Synchronize Schema button in the toolbar.
As you move through the synchronization wizard screens, you will see one window which appropriately includes various warnings. In the below illustration, the warning conveys that a column is being dropped.
Finally, the Aqua Data Studio Query Analyzer opens with the change script loaded into the SQL Editor. You can now decide to run the change or save it for later.
The outcome from two query results can also be compared. First two queries will need to be executed, and then by right clicking on one of the result tabs, and selecting Result Compare, the Data Comparison tool will be invoked.
Based on the comparison, the below illustration shows one row has a difference in the AddressLine1 column.
The sync tool also extends to being able to make comparison of not only schema and data, but you can also invoke the tool to compare schemas across data platforms or across open query files.
Variety of supported database platforms and operating systems
Aqua Data Studio supports many different database platforms and operating systems making it a great choice when managing any environment. Some of the support database platforms and operating systems include:
- SQL Server
- and more
- and more
Aqua Data Studio contains many other features that are helpful to most any data analyst, database developer, or DBA. Some additional items for you to explore going forward would include:
- SQL History
- Automate Features
- Import and Export Data
- Object Search
MSSQLTips.com Product Editorial sponsored by Idera
Last Updated: 2017-11-08
About the author
View all my tips