Running Database Designer with Management Console

In this tutorial, you'll create a comprehensive design with Database Designer through the Management Console interface. If, in the future, you have a query that you want to optimize, you can create an enhanced (incremental) design with additional projections. You can tune these projections specifically for the query you provide.

You can choose to create the design manually or use the Management Console wizard, as described below.

Follow these steps to to create a comprehensive design with the Management Console wizard:

1. Log in to Management Console.

2. Verify that your database is up and running.

3. Choose the database for which you want to create the design. You can find the database under the Recent Databases section or by clicking Existing Infrastructure to reach the Databases and Clusters page.

   The database overview page opens.

4. At the bottom of the screen, click the Design button.

5. In the New Design dialog box, enter the design name.

6. Click Wizard to continue.

7. Create an initial design. For Design Type, select Comprehensive and click Next.

8. In the Optimization Objective window, select Balance Load and Performance to create a design that is balanced between database size and query performance. Click Next.

9. Select the schemas. Because the VMart design is a multi-schema database, select all three schemas (public, store, and online_sales) for your design in the Select Sample Data window. Click Next.

   If you include a schema that contains tables without data, the design could be suboptimal. You can choose to continue, but Vertica recommends that you deselect the schemas that contain empty tables before you proceed.

10. Choose the K-safety value for your design. The K-Safety value determines the number of buddy projections you want Database Designer to create.

11. Choose Analyze Correlations Mode. Analyze Correlations Mode determines if Database Designer analyzes and considers column correlations when creating the design.

    • Ignore: When creating a design, ignore any column correlations in the specified tables.

    • Consider existing: Consider the existing correlations in the tables when creating the design. If you set the mode to 1, and there are no existing correlations, Database Designer does not consider correlations.

    • Analyze missing: Analyze column correlations on tables where the correlation analysis was not previously performed. When creating the design, consider all column correlations (new and existing).

    • Analyze all: Analyze all column correlations in the tables and consider them when creating the design. Even if correlations exist for a table, reanalyze the table for correlations.

    Click Next.

12. Submit query files to Database Designer in one of two ways:

    • Supply your own query files by selecting the Browse button.

    • Click Use Query Repository, which submits recently executed queries from the QUERY_REQUESTS system table.

    Click Next.

13. In the Execution Options window, select any of the following options:

    • Analyze statistics: Select this option to run statistics automatically after design deployment, so Database Designer can make better decisions for its proposed design.

    • Auto-build: Select this option to run Database Designer as soon as you complete the wizard. This option only builds the proposed design.

    • Auto-deploy: Select this option for auto-build designs that you want to deploy automatically.

14. Click Submit Design. The Database Designer page opens:

    • If you chose to automatically deploy your design, Database Designer executes in the background.

    • If you did not select the Auto-build or Auto-deploy options, you can click Build Design or Deploy Design on the Database Designer page.

15. In the My Designs pane, view the status of your design:

    • When the deployment completes, the My Design pane shows Design Deployed.

    • The event history window shows the details of the design build and deployment.

To run Database Designer with Administration Tools, see Running Database Designer with administration tools in this guide.