Instructions for Direct Database Integration

Suggest Edits

Prerequisites:

  1. SSH Client: Install the PuTTY SSH client on your PC.
  2. Postgres ODBC Driver: Download and install the Postgres ODBC Driver on your PC.
  3. Static IP: Ensure you have a static IP or static IP range from which you will access the database.

Provided by Mosaic:

  1. SSH Key: A private SSH key for PuTTY, corresponding to a public key on Mosaic's bastion host.
  2. Connection Details: Database hostname, port, username, and password, along with the IP Address of the Mosaic bastion.

Part 1: Setting Up the SSH Tunnel Using PuTTY

  1. Launch PuTTY:

    • In the "Session" section, enter the public IP address of the Mosaic bastion.
    • Set the port to 22 (default for SSH).

  2. Configure SSH Key for Authentication:

    • Navigate to Connection > SSH > Auth > Credentials.
    • Click "Browse" to select the provided private key file.

  3. Set Up Port Forwarding:

    • Go to Connection > SSH > Tunnels.
    • Enter 5433 in the "Source port" field.
    • In the "Destination" field, type the database hostname followed by :5432.
    • Select "Local" and "Auto", then click "Add".
    • You should see your newly added port forwarding in the “Forwarded ports” section, after clicking “Add”.

  4. Start the SSH Session:

  • Return to the "Session" page, save the session as “Mosaic DDI Connection”.
  • Click "Open" to initiate the session. Enter ec2-user when prompted for a username.

🚧

Note:

Keep the PuTTY session open to maintain database access through the secure connection.

Part 2: Configuring PostgreSQL ODBC Driver

  1. Open ODBC Data Source Administrator and click “Add”.
  1. Select “PostgreSQL Unicode” from the list and click “Finish”.
  1. Configure the connection:
    1. Data Source Name: Mosaic
    2. Description: Database connection for Mosaic
    3. Database: Provided by Mosaic
    4. SSL Mode: require
    5. Server: localhost
    6. Port: 5433
    7. Username and Password: Provided by Mosaic
  1. Click “Test” to verify the connection (ensure your PuTTY SSH session is active).

Part 3: Connecting Power BI to Mosaic Data via SSH Tunnel

  1. Launch Power BI and navigate to Report > Get Data > ODBC.
  1. Select your ODBC connection (e.g., Mosaic DDI).
  1. Enter the database Username and Password, and click "Connect".
  1. Proceed to import and utilize your data as needed.

Updated about 2 months ago