Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Aqua Data Studio - the most productive all in one database management solution


By:   |   Read Comments (3)   |   Related Tips: More > Database Administration

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

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?

Solution

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.

Aqua Data Studio - Description: Main Aqua Data Studio screen

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.

Visual Query Builder - Description: Visual Query Builder Start

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.

Drag onto Query Grid - Description: Drag onto Query Grid

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.

SQL Query in Grid - Description: SQL Query in Grid

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.

Join - Description: Join Window

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.

Criteria - Description: Criteria Window

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.

Keys - Description: Keys screen

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.

Derive Table - Description: Derived Table

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.

Derived Table Result - Description: Derived Table Result

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.

Run Query - Description: Run Query

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.

View in Spreadsheet - Description: View in Spreadsheet
Spreadsheet Result - Description: Spreadsheet Result

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.

Clear Grid - Description: Clear Grid

ER Modeler

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.

New Model - Description: New ModelNew ERD DBMS - Description: New ERD DBMS

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.

New Table - Description: 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.

Add Tables to ERD - Description: Add Tables to ERD

Subsequently, you can add the relationships between the tables by right clicking and selecting New Relationship.

New Relationship - Description: 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.

Relationship Properties - Description: Relationship Properties

Now the relationship shows within the ERD.

Relationship Results - Description: Relationship Results

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.

ERD Region - Description: ERD Region

Once the design is finished, a script can be generated to build the actual script in the desired database system. 

Generate ERD Script - Description: Generate ERD Script

The script generation tools allow for the selection of specific schemas and objects, if desired.

Generate Script Object Selection - Description: Generate Script Object Selection

The script can be sent to a file or previewed within Aqua Data Studio.

ERD Final Script - Description: ERD Final Script

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. 

Generate from Schema - Description: Generate from Schema
Choose Database - Description: Choose Database

Next the schema and objects to be included in the diagram are selected.

Select tables to generate - Description: Select tables to generate

The ER Diagram will be generated automatically based on the current constraints, relationships, keys, and attributes.

Final ERD Generate Result - Description: Final ERD Generate Result

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.

Table Data Editor1 - Description: Table Data Editor1 Table Data Editor2 - Description: Table Data Editor2

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.

Highlight Differences - Description: Highlight Differences

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.

Incorrect Data Type - Description: Incorrect data type highlight

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.

Autofill - Description: Autofill

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.

Find Replace - Description: Fine Replace

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.

Find Replace Window - Description: Find Replace Window
Find Replace Result - Description: Find Replace Result

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.

Grid Filter - Description: Grid filter

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.

Primary Key Tab - Description: Primary Key Tab

As changes are made to the Edit Grid, the actual SQL for the changes are accumulated and recorded within the Preview SQL Tab.

Preview SQL Tab - Description: 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.

insert delete rows - Description: insert delete rows

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
Clear marks - Description: Clear marks

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.

Save Changes - Description: Save Changes

Visual Analytics

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.

New Visual Analytics - Description: New Visual Analytics
New Visual Grid - Description: New Visual Grid

In the below example, an existing query is used, and the Visual Analytics window opens with the fields already divided into dimensions and measures.

Existing Query Grid - Description: Existing Query Grid

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.

New Visual Analytics  - Description: New Visual Analytics

The visual that was initially created can easily be changed to a different chart type using the chart properties option.

Chart Properties - Description: Chart Properties

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.

Pivot Table - Description: Pivot Table

We could also straightforwardly flip the chart to a pie chart by size of the part.

Pie Chart - Description: Pie Chart

Visual Analytics features some advanced visual capabilities such as creating dual axis charts.

Dual Axis Chart - Description: Dual Axis Chart

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.

Add Calculated Field - Description: Add Calculated Field

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.

Dashboard - Description: 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.

New Schema Compare - Description: New Schema Compare

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.

Sync Tool Options - Description: Sync Tool Options

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.

Schema Compare - Description: Schema compare

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.

Compare Results - Description: Compare Results

Syncing the two schemas requires you to select the objects to sync and then clicking on the Synchronize Schema button in the toolbar.

Intiate Sync - Description: Initiate sync

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.

Schema sync warning - Description: sync warning

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.

sync script - Description: sync script

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.

date compare - Description: data compare

Based on the comparison, the below illustration shows one row has a difference in the AddressLine1 column.

date compare results - Description: date compare results

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:

Databases

  • SQL Server
  • Oracle
  • MongoDB
  • MySQL
  • PostreSQL
  • Excel
  • and more

Operating Systems

  • Windows
  • Linux
  • OSX
  • 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
Next Steps

MSSQLTips.com Product Editorial sponsored by Idera



Last Update:


signup button

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, November 09, 2017 - 11:31:51 AM - John G Back To Top

I saw this product at PASS 2017 and can't wait to try it out. This walkthrough will be a big help in the evaluation process. Thanks for the tip!


Thursday, November 09, 2017 - 9:05:49 AM - David Rubin Back To Top

Our company standardized on ADS in '12 and never looked back. Fantastic software, reasonably priced with fantastic support. Version updates frequently posted online. All the tools you'll need for all the platforms you need without having to pay for each and every platform. Makes so much sense especially when you realize that they have a great ERD tool included!! We suggest this tool to all our clients!


Tuesday, November 07, 2017 - 5:41:44 AM - Mark Allen Back To Top

 I have been a using ADS  HEAVILY since 2012. I love it and personally after trying many other SQL clients it is head and shoulders above anything else I have tried

 

 


Learn more about SQL Server tools