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 click Data Flow Service.
    2. Click SQL Endpoints in the leftmost side navigation.
    3. Click any SQL Endpoint from the list. The details page for that SQL Endpoint is displayed.
    4. Under Connect, click Drivers.
    5. Under ODBC, click Download ODBC driver.
    6. Click Windows. The download begins.
    7. When the download completes, extract the downloaded zip file.
  4. Choose 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. Click Next.
  6. Click the check box to accept the terms of the License agreement.
  7. Click Next.
  8. (Optional) To change the installation location, click Change. Browse to the correct location. Click OK.
  9. Click Next to confirm the installation location.
  10. Click Install.
  11. When the installation completes, click 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, click 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. Click 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, click the User DSN tab.
    • To create a DSN that all users who log in to Windows can use, click the System DSN tab.
  8. Click Add.
    The Create New Data Source dialog box is shown.
  9. Select Simba OCI Spark ODBC Driver from the list of drivers.
  10. Click 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. Click the Drivers.
    3. Click Show details under ODBC drivers. A pop-up appears.
    4. Click Show in front of Host Name.
    5. Click Copy.
    6. The Host Name is copied to the clipboard.
  12. Click 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. Click OK.
  17. Test the connection by clicking Test.
    You're asked to log in to authenticate the ODBC driver.
  18. Click 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. Click Get Data.
  3. In the resulting dialog box, search for ODBC.
  4. Select ODBC.
  5. Click Connect.
  6. For the Data source name (DSN), select the DSN you created in Creating a Data Source Name (DSN).
  7. Click 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.