Oracle Cloud Infrastructure Documentation

Set Up Microsoft Power BI as a Data Visualization Tool.

Download and install Microsoft Power BI and the Simba ODBC driver.

Follow these steps to download and install Microsoft Power BI and the Simba ODBC driver to connect Microsoft Power BI to a SQL Endpoint:

  1. Download Microsoft Power BI and the Simba ODBC driver, and install them.
  2. Create a data source name.
  3. Connect Power BI to the Data Flow SQL Endpoints.

Downloading and Installing Microsoft Power BI and the Simba ODBC Driver

Follow these steps to download and install Microsoft PowerBI and the Simba ODBC driver.

  1. Download the Microsoft Power BI executable from its download site. Select version 2.105.1143.0 or later.
    Note

    Microsoft Power BI is only available for Windows and Linux.
  2. Install it.
    Note

    You must create a Microsoft Power BI account, as it's not accessible using the Oracle accounts.
  3. Download the Simba ODBC driver from the Console to the Windows instance.
    1. In the global search bar, search for Data Flow and select Data Flow Service.
    2. Select SQL Endpoints in the leftmost side navigation.
    3. Select any SQL Endpoint from the list. The details page for that SQL Endpoint is displayed.
    4. Under Connect, select Drivers.
    5. Under ODBC, select Download ODBC driver.
    6. Select Windows. The download begins.
    7. When the download completes, extract the downloaded zip file.
  4. Select a 32-bit or 64-bit driver. Double-click the one which matches the the Microsoft Power BI installation.
    The Simba OCI Spark ODBC Driver Setup dialog box is shown.
  5. Select Next.
  6. Select the checkbox to accept the terms of the License agreement.
  7. Select Next.
  8. (Optional) To change the installation location, select Change. Browse to the correct location. Select OK.
  9. Select Next to confirm the installation location.
  10. Select Install.
  11. When the installation completes, select Finish.

Creating a Data Source Name (DSN)

Follow these steps to create a data source name for the ODBC driver.

  1. From the Start menu, select ODBC Data Sources. Select 32-bit or 64-bit as appropriate.
  2. Select the 32-bit or 64-bit application that matches the ODBC driver you installed in Downloading and Installing Microsoft Power BI and the Simba ODBC Driver.
  3. Open the ODBC Data Source Administrator.
  4. Select the Drivers tab.
  5. Confirm that the Simba OCI Spark ODBC Driver appears in the alphabetical list of ODBC connectors that are installed on the system.
  6. If a suitable DSN exists, open it and skip to step 11.
  7. If no DSN is available, you must create one.
    • To create a DSN that only the user currently logged in Windows can use, select the User DSN tab.
    • To create a DSN that all users who log in to Windows can use, select the System DSN tab.
  8. Select Add.
    The Create New Data Source dialog box is shown.
  9. Select Simba OCI Spark ODBC Driver from the list of drivers.
  10. Select Finish.

    The Simba OCI Spark ODBC Driver DSN Setup dialog box opens.

  11. Change Host to the hostname of the Data Flow SQL Endpoint.
    If you don't know the host name, you can find it from the Console:

    1. Navigate to the SQL Endpoint details page.

    2. Select the Drivers.
    3. Select Show details under ODBC drivers. A pop-up appears.
    4. Select Show in front of Host Name.
    5. Select Copy.
    6. The Host Name is copied to the clipboard.
  12. Select DFI Options.
  13. (Optional) Set OCI Profile to the name of the OCI profile to use for the connection.
    If no profile is specified, the connector tries to use the profile named DEFAULT from the configuration file.
  14. To OCI Config File add the path of the configuration file that uses an API signing key for authentication.
    Important

    If you don't add a file to OCI config file, then token-based authentication (through the browser) is used instead, which might lead to many authentications.
  15. Check Ignore SQL_DRIVER_NOPROMPT.
  16. Select OK.
  17. Test the connection by selecting Test.
    You're asked to log in to authenticate the ODBC driver.
  18. Select OK.

Connecting Microsoft Power BI to a Data Flow SQL Endpoint Using ODBC

Follow these steps to connect Microsoft Power BI to a Data Flow SQL Endpoint with a Simba ODBC driver.

  1. Log in to Microsoft Power BI.
  2. Select Get Data.
  3. In the resulting dialog box, search for ODBC.
  4. Select ODBC.
  5. Select Connect.
  6. For the Data source name (DSN), select the DSN you created in Creating a Data Source Name (DSN).
  7. Select OK.
    If a dialog box appears for entering a username and password, you can enter any username and password, because the actual authentication is done using OCI config file or through the browser.
  8. If the connection is successful, select the tables you want to use from the leftmost menu and click Load to load the data from them.