EDW Configuration

Last Updated: September 20, 2024

Here are the steps you need to configure the Microsoft SQL Server and the EDW Utility.

Requirements

The following is required to configure EDW:

  • SQL server - version 2016 SP1 or above. Microsoft SQL Server 2019 is also required if you use Windows 11.
  • Windows 2016+ server, Windows 10, or Windows 11.
  • Admin access to your Microsoft SQL database.
  • Download the Enterprise Data Warehouse Utility.

We recommend access to Microsoft SQL Server Management Studio.

Configure Microsoft SQL Server

We used Microsoft Windows Server 2019 with SQL Server 2019 Standard. Additionally, we recommend use of SQL Server Configuration Manager tool.

Microsoft SQL Server Management Studio Settings

  1. Open the Microsoft SQL Server Management Studio, and connect to your SQL server.
  2. In the left menu, select Security.

  3. Right-click on Logins, and select New Login.

    MS SQL Server Management Studio connectoed to a database and the Security Login context menu.

  4. On the Login - New window, complete the following:
    1. Login name — enter a unique name.
    2. SQL Server authentication — use the radio button to select this option.
    3. Password — enter a password to use for this login.

    4. Confirm password — reenter the password to confirm.

      Login - New configuration window.

  5. On the Login - New window, select Server Roles from the Select a page section on the left.

    1. Check the boxes for public and sysadmin.

    2. Select OK.

      Server Roles page with public and sysAdmin options selected.

  6. Right-click on the database in the Object Explorer on the left, and select Properties.

    MS SQL Server Management Studio connected to a database and showing the selected database context menu.

  7. On the Server Properties window, Select Security from the Select a page section on the left.
    1. Under Server authentication, select the option for SQL Server and Windows Authentication mode.

    2. Select OK.

      Server properties window, Security page with the SQL Server & Windows Authentication mode selected.

SQL Server Configuration Manager Settings

  1. Open your SQL Server Configuration Manager app.

  2. Expand SQL Native Client 11.0 Configuration

  3. Select Client Protocols.

  4. Ensure that Shared Memory, TCP/IP and Named Pipes are all Enabled.

    SQL server configuration manager window with the SQL Native Client 11 Configuration's sub-option for Client Protocols selected.

Confirm Services are Running

If you have any issues with data flowing to the database, ensure the services are running by doing the following:

  1. Open your Services app.
  2. The following services should have the Status set to Running and the Start-up Type set to Automatic:
    • SQL Server
    • SQL Server Agent
    • SQL Server Browser
    • EDW_Service

    Services app window showing the EDW_Service, SQL Server, SQL Server Agent, and SQL Server Browser services running.

Install the Enterprise Data Warehouse Utility

If not previously downloaded, download the utility here: EDW Utility download

  1. Run the downloaded executable file to install the EDW Utility with your file location preferences.

Generate Token

  1. In the Admin Console, navigate to Tools then Tokens then API Keys.

  2. On the Generate API Key screen, select the Generate button.

    Generate API Key screen showing the Generate button section

  3. In the Active API Keys section, select View tokens.

  4. Copy the token. You'll need it to configure the EDW Utiity.

    Active API Keys section the generated token.

Configure the Enterprise Data Warehouse Utility

In this section you can learn how to set up your EDW Utility.

Optional Parameters

In the EDW Utility console, you have the option to configure additional parameters using the Optional Params field. Here are some helpful details:

The following parameters are comma-separated, key-value pairs, with the key and value separated by a colon (:). Valid keys are:

  • port — (integer) TCP port used to connect to MS SQL (default: 1433).
  • timeout — (integer) connection time out in seconds (default: 30).
  • debug — (bool) true for debug logging, false otherwise (default: false).
  • log_overwrite — (bool) true to overwrite SQLPackage diagnostic log, false otherwise (default: false).
  • pl_edw_refresh_interval — (integer) data refresh interval in minutes (default: 15).

Keys are case-insensitive.

For Boolean values, all of these are valid for TRUE (these are also case-insensitive).

  • ENABLED
  • ENABLE
  • TRUE
  • 1

Examples

Port: 1433, Timeout: 60, log_overwrite: true

timeout: 10, debug: True, log_overwrite: 1

Configuration Steps

The following steps guide you through the configuration process.

  1. Open the Enterprise Data Warehouse Utility console.

  2. In the EDW URL field, open the drop-down and ensure https://edw.<VA Instance URL> is selected. Then modify it to include your VA URL. For example, https://edw.test.mycompany.com.

  3. In the Generated EDW Token field, enter the token you copied from the Admin Console.

  4. Select the first Test button to confirm that the EDW Utility is connected with your Virtual Appliance instance.

  5. In the SQL Server Host or IP Address field, enter the IP address or SQL server host to use for your printing data.

  6. In the Username and Password fields, enter the credentials you set in the Microsoft SQL Server Management Studio in the first section.

  7. In the Optional Params field, you can set any additional parameters. See above for details.

  8. Select the second Test button to confirm you are connected to the SQL database.

  9. Select Add as New Instance to start the process of building the EDW database.

    The Add as New Instance / Update Selected Instance button saves the configuration and updates the database.
    If you are not ready to update the database, you can save the configuration parameters using the Save button on the right.

The Enterprise Data Warehouse Utility Console.

You can repeat the steps to add any additional Virtual Appliance instances.

Keyboard Shortcuts

You can use the Enterprise Data Warehouse Utility console more effectively by making use of keyboard shortcuts or hot keys.

  1. Press the Alt key to display the hotkeys.
  2. Press Alt + <underlined letter> to set the focus on the specified field or button.

    1. Press the space bar to select / deselect values.

      EDW Utility console displaying the keyboard hot keys.

Exit the Utility Console

To exit the Enterprise Data Warehouse Utility console, select the power button Power button. on the lower-left corner.

Confirm the Build

  1. Return to your Microsoft SQL Server Management Studio.

  2. Under Databases, confirm that Virtual Appliance EDW is listed.

    It may take a second cycle for the data to show. However, you can manually restart the EDW_Service to force the cycle to start.

You can now begin to query the database.