Set up Oracle SQL Developer for IR group

This article shows how to set up Oracle SQL Developer and connect to CSRPT. It also shows how to connect to Power BI. This function is limited to a specific group of employees.

Detailed Information 

Before starting to set up, make sure your access has been approved and your credentials have been created for Oracle. The user ID and password are different from your MaineStreet access. 

Instant Client

  1. Download the version of the Oracle Instant Client and the ODBC package matching your operating system, both found here:https://www.oracle.com/database/technologies/instant-client/downloads.html
  2. Extract all zipped files to a folder on your hard drive (e.g. C:\Users\YOUR.NAME\Oracle)
  3. Add the tnsnames.ora file in the previous step. You can ask DARTS@maine.edu for the file. 
  4. In the start menu of your computer, click on the search bar and type "View Advanced System Settings"
  5. Click the "Edit Environment Variables" button
  6. Use "New" or "Edit" to ensure there is an environment variable called PATH that points to the instant client folder

Use "New" or "Edit" to ensure there is an environment variable called TNS_ADMIN that points to the instant client folder (can be another folder if you saved your tnsnames.ora file elsewhere, but must point to the directory containing that file).

Variable Value
PATH e.g. C:\Users\YOUR.NAME\Oracle
TNS_ADMIN e.g. C:\Users\YOUR.NAME\Oracle
  1. Click OK

SQL Developer

  1. Download the version of Oracle SQL Developer matching your operating system

https://www.oracle.com/database/sqldeveloper/technologies/download/

  1. Extract all zipped files to a folder on your hard drive (e.g., C:\Users\YOUR.NAME\Oracle)
  2. In the extracted folder, open the application called sqldeveloper. 

NOTE: To make it easier to locate the application going forward, you can right-click the application and pin the application to the Start menu or create a desktop shortcut.

Oracle connection in Microsoft Power BI 

Before connecting to Power BI, run your SQL in Oracle SQL Developer and make sure it runs.

  1. Open Microsoft Power BI Desktop
  2. Click "Get Data"
  3. Choose "Oracle database" and click "Connect"

Showing the Get Data screen in Power BI with the Oracle Connection highlighted.

  1. Type "CSRPT" on the Server and paste the SQL code in the SQL statement box.  Note: Table names in SQL code have to have SYSADM schema.

Showing the Oracle Database pop-up window with CSRPT entered as the Server name.

  1. You might have to sign in under the "Database" connection. The username and password are the same as those for Oracle SQL Developer, which may not be the same as SSO. Make sure that your VPN is connected.
  2. When you need to set up a gateway for data refresh in Power BI Service, please contact DARTS@maine.edu

Environment

  • Oracle CSRPT
  • IR group
Print Article