Power BI Desktop integration to Oracle Autonomous Database

Previous article here.

Github project here.

This article assumes that you already have an autonomous database on Oracle Cloud. If you don’t have it, click here to read the previous article where I teach how you can create an Oracle Cloud account and an autonomous database. You can read the full documentation for the next steps here.

Steps
1 - ODAC (Oracle Data Access Components) installation
2 - Connect database to Power BI
3 - Creating a simple dashboard with database data.

1 - ODAC (Oracle Data Access Components) installation

Download the corresponding Oracle ADB credentials zip file to the system that has or will have Power BI Desktop installed. These credential files will be used to connect Power BI Desktop to ADB.

Open Power BI Desktop and create a project.

Select Database > Oracle database > Connect to attempt to connect to an Oracle database.

If the error indicates it is trying to use Oracle.DataAccess.dll assembly, then set up Power BI Desktop with unmanaged ODP.NET. If the error says it is trying to use Oracle.ManagedDataAccess.dll, then use managed ODP.NET.

If you need to use unmanaged ODP.NET, determine if Power BI is either 32-bit or 64-bit. To look up Power BI Desktop’s bitness, select File > Help > About.

In the image above, we see that 64-bit Power BI Desktop is being used. That means 64-bit unmanaged ODP.NET must be installed and configured for Power BI to connect to ADB. If 32-bit Power BI Desktop was being used, then a 32-bit unmanaged ODP.NET would be required. The following instructions cover all scenarios, whether you are using managed or unmanaged ODP.NET or you are using 32-bit or 64-bit Power BI.

If you require managed ODP.NET or 64-bit unmanaged ODP.NET, download 64-bit ODAC 19.3 from the ODAC Xcopy section in the middle of this web page. If you require 32-bit unmanaged ODP.NET, download 32-bit ODAC 19c from the ODAC Xcopy section.

Now, we will install ODP.NET. Installation instructions are the same for managed ODP.NET, 32-bit unmanaged ODP.NET, and 64-bit unmanaged ODP.NET. Unzip the download contents to a staging directory (e.g. c:\xcopy64-install or c:\xcopy32-install).

Open a Windows command prompt in administrator mode. Navigate to the staging directory, then execute the next command to install ODP.NET:

install.bat odp.net4 <instalation directory>  odp64

Note: Enter the installation location (e.g. c:\odp64 or c:\odp32) for the directory parameter

Configuration instructions differ between managed ODP.NET and unmanaged ODP.NET. In the same command prompt with administrator privileges, navigate to the installation subdirectory, \odp.net\bin\4. Then, execute the following command:

a) To configure unmanaged ODP.NET:

OraProvCfg /action:gac /providerpath:"Oracle.DataAccess.dll"

OraProvCfg /action:config /product:odp /frameworkversion:v4.0.30319 /providerpath:"Oracle.DataAccess.dll"

Note: Please validate that you are using the correct path for Oracle.DataAccess.dll file.

b) To configure managed ODP.NET:

OraProvCfg /action:gac /providerpath:"../../managed/common/Oracle.ManagedDataAccess.dll"

For the next:

OraProvCfg /action:config /product:odpm /frameworkversion:v4.0.30319 /providerpath:"../../managed/common/Oracle.ManagedDataAccess.dll"

(For unmanaged ODP.NET only) Edit the Windows environment variables by adding the path value of the 64- bit Oracle Client directory (e.g. c:\odp64) or 32-bit Oracle Client directory (e.g. c:\odp32) depending on the version Power BI will use.

To ensure this directory path setting has precedence over existing Oracle Homes, move the setting up to the highest possible level in the directory order with the “Move Up” button.

Navigate to where you downloaded the Oracle ADB credentials on your Windows machine. Unzip the contents to a directory.

In the Windows environment variables dialog, create the TNS_ADMIN variable. Set its value to the directory location where you unzipped the ADB wallet contents.

Note: The tnsnames.ora net service names will be used to connect to ADB’s.

If you are connecting to one ADB instance, open the SQLNET.ORA configuration file in the wallet directory in a text editor. You will see the following line:

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin")))

Set the DIRECTORY value to the ADB wallet directory location on the machine, such as:

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY= C:\DATA\WALLET\Wallet_ADWBI)))

Save the file and proceed to the next step.

If you are connecting to multiple ADBs from the same machine with a different wallet for each one, add the parameter MY_WALLET_DIRECTORY to the connect descriptor with each descriptor’s specific wallet location in TNSNAMES.ORA. For example:

adwptr_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-phoenix1.oraclecloud.com))(connect_data=(service_name=bk8ui2h_adwptr_high.adwc.oraclecloud.com))(security=(s sl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com, OU=Oracle BMCS US, O=Oracle Corporation, L=Redwood City, ST=California, C=US")(MY_WALLET_DIRECTORY=C:\DATA\WALLET\Wallet_ADWPTR))) 
adwbi_low = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.usphoenix- 8 1.oraclecloud.com))(connect_data=(service_name=bk8uqvi2h_adwbi_low.adb.oraclecloud.com))(security=(ssl _server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com, OU=Oracle BMCS US, O=Oracle Corporation, L=Redwood City, ST=California, C=US")(MY_WALLET_DIRECTORY=C:\DATA\WALLET\Wallet_ADWBI)))

Open the TNSNAMES.ORA file in the wallet directory to see which ADB net service names are available to connect to. Below you see three different ones: adwptr_high, adwptr_low, and adwptr_medium. Your ADB net service names will likely be named differently.

2 - Connecting the database to Power BI

Steps
1 - ODAC (Oracle Data Access Components) installation✔
2 - Connecting database to Power BI
3 - Creating a simple dashboard with database data.

Open Power BI Desktop again and click ‘Get data’, select ‘Oracle’, and connect using one of your net service names.

Congratulations! Your Power BI Desktop instance should now be connected to ADB. Open in Navigator the tables that you need data to create your own Microsoft Power BI Desktop Document (.pbix) and load the data.

It can take longer depending on the size of your dataset. once the data has been loaded it will be faster if you have to open this file again.

3 - Creating a simple dashboard with database data.

Steps
1 - ODAC (Oracle Data Access Components) installation✔
2 - Connecting database to Power BI✔
3 - Creating a simple dashboard with database data.

Alright, our data are ready. Let’s make a dashboard to show some information about them.

Our data are on the right, you can drag and drop any data to the blank space (center of the screen). Be curious and don’t be afraid, if something goes wrong just ctrl+z.

You are free to make your own dashboard, create templates and etc. If you want some tips just follow me on the steps below:

Follow these clicks to change your page view (I particularly prefer the view this way).

The image above shows us how to change our dashboard template, you can select that you liked or can customize the current theme.

Here you can insert some shapes, images, buttons, and textbox.

On the right, there is a tab ‘Visualizations’. Here you can select graphs, tables, cards and etc. Just drag and drop and that’s it!

I will show an example of how we can add some items to our dashboard, select the following:
- a card
- a table
- a slicer

Drag and drop the data to objects:
- deputies names and party to the table
- year to slicer
- expenses value to card

Now when you click on deputy name the value card will be changed to only value for that deputy in the current period, in this case, from 2019 to 2022. Before, the value showed is the sum of expenses of all deputies in the same period! you can change the names or dates as you want. Remember, be curious about your data.

Now follow the clicks below:

For the next:

We turned off the card background and changed the font color to white. You can change visuals for every element on your dashboard with some clicks. On the tabs below (visual and general), you can change font size, font color, effects, shadows, titles, labels, etc.

Alright, feel free to add items, forms, shapes, graphs, and whatever you want to show on your dashboard use this to practice your creativity. I will show my finished work and point out each item below.

I build a 2 pages dashboards, the first one to show expenses for each political party:

1- Rectangle shape
A simple rectangle where I turn off the background color and select a white border.

2- Textbox
A simple textbox with the text: ‘Here you select the year’ in Portuguese.

3- Slicer
The slicer with the year as a filter on the horizontal orientation.

4- Rectangle shape
A simple rectangle shape like the previous.

5- Textbox
Another simples textbox.

6- Slicer
A slicer with the party name data.

1- Textbox
Another simple textbox without background color.

2- Card
A card with the party name.

3- Card
A card with a simple metric with the sum of value expenses.

total = CALCULATE(SUM(DATA_TAB[VALORDOCUMENTO]))

4- Table
A table with deputy name and value expenses.

1- Rectangle shape
Another rectangle with a white background this time.

2- Card
Card with a metric about vehicle rent.

vei 1 = CALCULATE(SUM(DATA_TAB[VALORDOCUMENTO]), DATA_TAB[TIPODESPESA] = "LOCAÇÃO OU FRETAMENTO DE VEÍCULOS AUTOMOTORES")
vei 2 = CALCULATE(SUM(DATA_TAB[VALORDOCUMENTO]), DATA_TAB[TIPODESPESA] = "LOCAÇÃO OU FRETAMENTO DE EMBARCAÇÕES")
vei 3 = CALCULATE(SUM(DATA_TAB[VALORDOCUMENTO]), DATA_TAB[TIPODESPESA] = "LOCAÇÃO OU FRETAMENTO DE AERONAVES")
aluguel de veiculos = CALCULATE([vei 1] + [vei 2] + [vei 3])+0

3- Card
Card with metrics about Parlament promotion.

divulgacao = CALCULATE(SUM(DATA_TAB[VALORDOCUMENTO]), DATA_TAB[TIPODESPESA] = "DIVULGAÇÃO DA ATIVIDADE PARLAMENTAR.")+0

4- Card
Card with accommodation expenses.

HOSPEDAGEM = CALCULATE(SUM(DATA_TAB[VALORDOCUMENTO]), DATA_TAB[TIPODESPESA] = "HOSPEDAGEM ,EXCETO DO PARLAMENTAR NO DISTRITO FEDERAL.")+0

5- Card
Card about airline ticket expenses.

pass 1 = CALCULATE(SUM(DATA_TAB[VALORDOCUMENTO]), DATA_TAB[TIPODESPESA] = "PASSAGEM AÉREA - SIGEPA")+0
pass 2 = CALCULATE(SUM(DATA_TAB[VALORDOCUMENTO]), DATA_TAB[TIPODESPESA] = "PASSAGEM AÉREA - RPA")+0
pass 3 = CALCULATE(SUM(DATA_TAB[VALORDOCUMENTO]), DATA_TAB[TIPODESPESA] = "PASSAGEM AÉREA - REELBOLSO")+0
pass 4 = CALCULATE(SUM(DATA_TAB[VALORDOCUMENTO]), DATA_TAB[TIPODESPESA] = "PASSAGENS TERRESTRES, MARÍTIMAS OU FLUVIAIS")+0
passagem = [pass 1]+[pass 2]+[pass 3]+[pass 4]+0

6- Card
Card with fuel expenses.

combustivel = CALCULATE(SUM(DATA_TAB[VALORDOCUMENTO]), DATA_TAB[TIPODESPESA] = "COMBUSTÍVEIS E LUBRIFICANTES.")+0

7- Card
The last card with the expenses of office maintenance.

escritorio = CALCULATE(SUM(DATA_TAB[VALORDOCUMENTO]), DATA_TAB[TIPODESPESA] = "MANUTENÇÃO DE ESCRITÓRIO DE APOIO À ATIVIDADE PARLAMENTAR")+0

8- Graphbar
Simple bar-type graph. X-axis: Expenses type, Y-Axis: value

For page 2 we have:

1- Simple image
The image of the deputy originated from the photo URL column.

1- Table
For last we have a table with the supplier and invoice that we can click and consult the expense.

Steps
1 — ODAC (Oracle Data Access Components) installation✔
2 — Connecting database to Power BI✔
3 — Creating a simple dashboard with database data.✔

Dashboard finished! For now, you can share it with your friends, boss, etc. Or you can publish on the web with the tool of Power BI ‘Publish’. Just click and in a few seconds your dashboard will be published on the web, thus, just share the webpage link on your social pages and get the likes s2

The final dashboard can be accessed here.

In the next article, we will try to build a new dashboard with Google Data Studio, free software from google. If everything goes right we will publish this new dashboard on the web for free ♪┏(・o・)┛♪┗ ( ・o・) ┓♪

See ya!

Next article here.(coming soon)

Previous article here.

Github project here.