I've been trying to configure PBI dataflow in PBI service. Dataflow should be connected…
- User
I've been trying to configure PBI dataflow in PBI service. Dataflow should be connected to Oracle database. Please recommend me how to configure it.
Assistant
To configure a Power BI Dataflow connected to an Oracle database, follow these steps:
Prerequisites
-
Oracle Client Software
- Install the 64-bit Oracle Data Provider for .NET (ODP.NET) on the machine where the Power BI Gateway is installed (if connecting to an on-premises Oracle DB).
- Ensure the Oracle client version is compatible with your Oracle server (e.g., Oracle 11g/12c/19c).
- Download from Oracle's website.
-
Connection Details
- Server Name: Use the TNS name (e.g.,
ORCL) or EZ Connect format:
hostname:port/service_name(e.g.,mydb.example.com:1521/ORCL). - Credentials: Username and password with read access to the Oracle schema.
- Server Name: Use the TNS name (e.g.,
-
On-Premises Gateway (If Oracle is On-Prem)
- Install and configure the Power BI On-Premises Data Gateway.
- Register the gateway in the Power BI Service under Settings → Gateways.
Step-by-Step Configuration in Power BI Service
-
Create a New Dataflow
- Go to Power BI Service.
- Navigate to Workspaces → Create → Dataflow.
- Select Add new entities to start the setup.
-
Choose Oracle as the Data Source
- In the dataflow editor, click Add data → Oracle Database.
- Enter connection details:
- Server: TNS name or EZ Connect string (e.g.,
mydb.example.com:1521/ORCL). - Connection path: Optional (leave blank if not using TNS).
- Authentication method: Basic (username/password).
- Enter credentials (check "Store" for future use).
- Server: TNS name or EZ Connect string (e.g.,
- Click Next.
-
Select Tables/Views or Write a Custom Query
- Choose tables/views from the Oracle schema or write a custom SQL query.
- Use Advanced Editor (if needed) to modify the Power Query M code.
-
Configure Gateway (If Oracle is On-Prem)
- In the dataflow editor, go to Settings → Gateway connection.
- Select the gateway registered in your workspace.
- Ensure the gateway machine has the Oracle client installed.
-
Set Refresh Schedule
- Navigate to Settings → Scheduled refresh.
- Configure refresh frequency (e.g., daily/weekly).
- Ensure credentials are stored securely.
Troubleshooting Tips
- Connection Errors:
- Verify the TNS name or EZ Connect string is correct.
- Check firewall rules to allow traffic from the gateway machine to the Oracle server (port 1521).
- Test connectivity using Power BI Desktop first (File → Get Data → Oracle → Enter details).
- Driver Issues:
- Reinstall ODP.NET if you see "Oracle client not found" errors.
- Ensure the Oracle client version matches the server architecture (64-bit).
- Permissions:
- Ensure the Oracle user has
SELECTaccess to the tables/views.
- Ensure the Oracle user has
Example Connection String (EZ Connect Format)
mydb.example.com:1521/ORCLReplace
mydb.example.com,1521, andORCLwith your server’s hostname, port, and service name.
By following these steps, your Power BI Dataflow will connect to the Oracle database. Let me know if you encounter specific errors!
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.