ODBC

ODBC (Open Database Connectivity) is a standard for accessing database management systems (DBMS). It allows applications to access different databases in a consistent and standardized manner.

Prerequisites for ODBC Connection

To establish an ODBC connection, ensure that the following requirements are met:

• The database information must be available. If the database does not yet exist, it can be created using Data Studio.

• The user must belong to the same group as the database they wish to access.

• The IP used for the connection must be allowed on the Whitelist.

When accessing ODBC in Skyone Studio, there are two areas: Users and Whitelist.

Below, we detail each of them.

Users

The “Users” section allows you to manage connections between the database and Skyone Studio. When accessing this area, you will find:

• User Listing: View all registered users.

• Search: Use the search bar to find a specific data consumer.

• Selection: Select one or more users for simultaneous deletion.

• Editing and Deletion: Edit or delete each user individually.

Creating a consumer

Click "Create consumer” and fill in the following fields:

• User: Choose the username.

• Password: Set a password.

• Groups: Select one or more groups. By default, the user has access to the "Default" group.

Add user screen

To finalize, click "Save user”.

The user must belong to the same group as the database they wish to access. Learn more about Groups.

Whitelist

The “Whitelist” section manages access permissions based on IP addresses. When accessing this area, you will find:

• Whitelist Listing: View all registered entries, including name, IP address, and status.

• Activation/Deactivation: Enable or disable individual whitelist connections.

• Editing and Deletion: Edit or delete each whitelist entry independently.

Creating a whitelist

To create a whitelist, fill in the following fields:

• Name: Enter the whitelist name.

• IP Address: Provide the IP address that will be granted access.

• Status: Set the connection status.

Create a whitelist screen

To finalize, click "Save”.

The IP address can be found in the error message. Learn more.

Using ODBC with Power BI

Requirements:

Before using ODBC with Power BI, follow these steps:

1. Install the PostgreSQL driver.

2. Configure the driver, filling in the required fields.

Steps to connect:

1. Click “Get Data” in Power BI.

2. Search for “ODBC”.

3. Select the previously configured database and click “OK”.

4. On the first access, enter the password.

5. Open the Power BI navigator and connect using the appropriate option.

Alternative option: Using SQL

1. In Power BI, click “Get Data”

2. Search for “ODBC”.

3. Select the previously configured database.

4. Click “Advanced options” and enter the SQL command to retrieve the data.

5. Click “OK” to view the retrieved information.

Managing IP Connection Errors

1. Connection error due to IP

If a connection error occurs due to an IP restriction, follow these steps:

1. Check the error message displayed, which will indicate the IP used in the connection attempt.

2. Copy the IP provided.

3. Add the IP to the Whitelist to grant access..

Adding the IP to the Whitelist does not automatically enable resources. This action only grants access to the necessary information to establish the connection.

2. How to Find the IP

If you are unsure of the IP address, follow these steps:

1. Attempt to establish the ODBC connection.

2. In the error message, locate the IP used in the connection attempt.

3. Add the identified IP to the Whitelist.