How do I use the erwin DM R9 Query Tool? (4300759)

The Query Tool

The Query Tool provides an easy way to execute SQL SELECT queries against a model in erwin Data Modeler, a model in a mart, or against a database catalog. You can execute a query against the objects and properties in the model, the metadata defining those objects and properties, and the action log that records the changes that are made to your model during the modeling session.

You can create new queries or paste text from another source (such as a *.sql file) into a query.

You can save a query to a .*qry file for later execution. Query results can be saved to a *.csv (comma-separated values) file.

Although the Query Tool provides a built-in query interface in erwin Data Modeler, you can also use any third-party reporting tool that has ODBC capability to query erwin Data Modeler modeling data.

Steps to Create a Query

You can create a query (SELECT statement) by entering text directly in the Query box, or by pasting text from another source.

To create a query

1. Click Query Tool on the Tools menu.

The Query Tool opens.

2. Enter your query in the Query box or click the paste button on the toolbar to paste a query from another source.

Your query is ready for execution. Use other features in the Query Tool to execute and save the query.

Note: Before you can execute a query you must be connected to erwin ODBC or to a database.

 Steps to Open a Query

You can work with queries you saved to a *.qry file in a previous session, or you can optionally use a *.sql, or Microsoft SQL Server Query file.

To open a query

1. Click Query Tool on the Tools menu.

The Query Tool opens.

2. Click the Open Query icon on the toolbar.

The Open dialog opens.

3. Select a *.qry or *.sql file and click open.

The query text opens the Query box and is ready for execution.

Note: Before you can execute a query you must be connected to erwin ODBC or to a database.

Steps to Save a Query

After you have created a new query or made changes to an existing query in the Query Tool, you can save your query to a *.qry file for future use.

To save a query

1. Click the Save Query icon on the toolbar in the Query Tool.

The Save As dialog opens.

2. Select a file name and location for your query, then click Save.

Your query is saved and the Save As dialog closes.

3. Click Close.

The Query Tool closes.

Steps to Execute a Query

You can execute a new or saved query against the model or against a database catalog.

Use the following process to execute a query:

1. Open the Query Tool from the Tools menu.
2. Create a new query or open an existing query (*.qry) file.
3. Connect to erwin ODBC or to a database.
4. (Optional) Select the option to restrict row count and enter a number to indicate the number of rows by which to restrict results.
5. Click the Execute icon on the toolbar.

After your query executes, the results display in the Results box. Use other features in the Query Tool to save the query or save your result set.

Steps to Execute a Query Against a Database

You can run your query against a database catalog. To do this, you connect to the database from the Query Tool and then execute your query.

Follow this process to run a query against a database:

1. Open the Query Tool from the Tools menu.
2. Create a new query or open an existing query (*.qry or *.sql) file.
3. Click the Connect icon on the toolbar.
4. Enter the database-specific connection criteria in the Connect dialog.
5. (Optional) Select the Restrict row count to check box and enter a number to limit the number of rows returned.
6. Click the Execute icon on the toolbar.

After your query executes, the results display in the Results box. Use other features in the Query Tool to save the query or save your result set.

Note: You cannot be connected to erwin ODBC and a database at the same time in the Query Tool. If you are connected to erwin ODBC, the (database) Connect icon is dimmed. Click the Disconnect icon on the toolbar to disconnect your erwin ODBC connection before you attempt to connect to a database. Also, before you can make a database connection, the appropriate database client software must be installed. See Database Connection Overview for more information.

Steps to Execute a Query Against erwin ODBC

To run a query against the current erwin Data Modeler model, you must first activate the ODBC connection. The connection may already be active, if you selected the option to start the ODBC driver on the Integrations tab in the Options dialog.

To connect to Erwin ODBC

1. Click Query Tool on the Tools menu.

The Query Tool opens.

2. Open a query (*.qry or *.sql) file or create a new query.

Query text displays in the Query box and is ready for execution.

3. Click the Connect to erwin ODBC icon on the toolbar.

The ODBC connection is established. The Disconnect icon becomes active. Use other features in the Query Tool to execute and save the query and result set.

Steps to Restrict Row Count in Query Results

When executing a query against a large model or database, you may want to restrict the row count for the query. This causes the Query Tool to process only the first n number of rows indicated in the number field. This may be helpful if you have executed a query that returns lengthy results, but the section of the results that you would like to see is contained in the first 100 rows, for example. In this case, enter 100 in the Restrict row count to number field, and the query will only return the first 100 rows.

To restrict row count in query results

1. Click Query Tool on the Tools menu.

The Query Tool opens.

2. Select the Restrict row count to check box and enter a number in the adjoining box.

When your query runs, the results that display are restricted to the first n number of rows you indicate.

Save a Result Set to a CSV File

After you have executed a query and the results display in the Results box, you can save the results to a *.csv (comma-separated values) file to work with it in other reporting tools, or to share with your modeling team.

To save a result set to a *.csv file

1. Click Query Tool on the Tools menu.

The Query Tool opens.

2. Open a query file or create a new query and click the Execute icon on the toolbar.

Your query results display in the Results box.

3. Click the Save Result Set as CSV icon displayed next to the Restrict row count to box.

The Save As dialog opens.

4. Enter a file name and location for your file and click Save.

The file is saved, the Save As dialog closes, and you are returned to the Query Tool.

PS: Please note that this was decommissioned in erwin DM 2018 R1 and 2019 R1.