Connect Power BI and Microsoft Data Tools to Autonomous Database on Dedicated Exadata Infrastructure

Oracle Client for Microsoft Tools (OCMT) is a graphical user interface (GUI) native Microsoft Software installer (MSI) that simplifies ODP.NET setup and Oracle database connectivity to multiple Microsoft data tools.

OCMT supports the following Microsoft tools:
  • Power BI Desktop (unmanaged ODP.NET)
  • Power BI service (unmanaged ODP.NET)
  • Excel (unmanaged ODP.NET)
  • SQL Server Analysis Services (unmanaged ODP.NET)
  • BizTalk Server (unmanaged ODP.NET)
  • SQL Server Data Tools (managed ODP.NET)
  • SQL Server Integration Services (managed ODP.NET)
  • SQL Server Reporting Services (managed ODP.NET)

To begin with, you must download this tool from the Oracle Client for Microsoft Tools page by clicking the 64-bit Oracle Client for Microsoft Tools link. Save the Oracle-Client-for-Microsoft-Tools.exe file to your Windows machine and double-click the file to run the GUI installer.

By default, OCMT installs with both the managed and unmanaged ODP.NET providers. However, you can choose to have only managed ODP.NET or unmanaged ODP.NET driver while installing the Oracle client. The ODP.NET driver type each Microsoft data tool uses is shown in parenthesis in the above list.

Managed ODP.NET works with both 64-bit and 32-bit MS tool runtimes. In the case of unmanaged ODP.NET provider, OCMT installs the 64-bit driver. Thus, it supports 64-bit tools, such as 64-bit Power BI Desktop and 64-bit Excel, but not their 32-bit counterparts. 32-bit MS tools can continue using 32-bit Oracle Data Access Client (ODAC) 19c xcopy installs to set up 32-bit unmanaged ODP.NET as before.

Irrespective of the Microsoft tool that you want to connect to your Autonomous Database, you must follow a sequence of steps to use OCMT. They are:
  • Provision an Autonomous Database. Refer to Create an Autonomous Database for instructions.
  • Download database credentials to the Windows client. Refer to Download Client Credentials for instructions.
  • Depending on the Microsoft (MS) tool that you want to connect to the Autonomous Database, you can install any of the following MS tools to the Windows client:
    • Visual Studio and Microsoft Reporting Services extension
    • Visual Studio and Microsoft Analysis Services extension
    • Visual Studio with the SQL Server Integration Services extension, which also supports SQL Server Data Tools
    • Power BI On-premises Data gateway
    • Power BI Desktop
  • Install and configure ODP.NET on the Windows client
  • Validate the MS tool connection to Autonomous Database
Below are step-by-step connectivity tutorials for each of the MS tools listed above. These tutorials show how to connect these MS tools with either on-premises Oracle databases or Oracle Autonomous Database:

Related Topics