The Power BI Service is Microsoft's easy to the cloud based Microsoft’s analytical suite that hosts Power BI Reports and dashboards. Cloud is very convenient and scalable, and it is very cost effective for small to medium scale organizations. Nevertheless, certain organizations still prefer that the data and reporting remains within their premises due to various concerns. Power BI Report server is an on-premise software that enables hosting Power BI Reports and traditional SQL Server Reporting Services (SSRS) reports in the same environment. (Power BI report server comes free with SQL Server Enterprise Edition or Power BI Premium.)
Power BI Report Server is still new. It was initially released only for Power BI Reports sourced from Analysis Services. Then, Microsoft expanded its capabilities gradually, and now it has been announced that the Power BI Report Server supports Power BI reports with data sourced from Oracle database as well. These Power BI reports sourced from Oracle data can either be in-memory or direct query mode reports. I am lucky I got an opportunity to experiment on this new capability as part of an initial proof of concept to one of our clients.
Prerequisites: A development machine with Power BI Desktop Latest version, SQL Server Data Tools and Oracle Data Access Component (ODAC) (Both 32 bit and 64 bit) installed, connectivity to an Oracle database 11g or higher, a computer with SQL Server 2017 Analysis Services (Tabular Mode), latest version of Power BI Report Server and ODAC installed. (32 bit version of ODAC is required for Visual studio\ SQL Server Data Tools, and 64 bit version is required for Power BI.)
Once ODAC 32 bit and 64 bit versions are installed, please add "tnsnames.ora" files in the \product\<version>\....\Network\Admin folders which got created as part of ODAC installations.
Add a TNS entry similar as follows in the tnsnames.ora files to connect to the oracle database:
<TNS name>=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=<IP address of the oracle server>)
(PORT=<Port Number>)
)
(CONNECT_DATA=
(SERVICE_NAME=<Oracle database service name>)
)
)
We may have multiple entries as the above for each Oracle database we would like to connect.
Next, configure Power BI Report Server. Locate "Microsoft Power BI Report Server" -> "Report Server Configuration Manager" from start menu to launch the report server configuration tool as below.
Type the correct computer name where the Power BI report server is installed, and select the Power BI Report server instance, and connect. (You may have other instances of SQL Server reporting services in the same computer.) Now, configure each tab in Report Server Configuration Manager. Make sure web service URL, report database and web portal URL are unique in the computer.
Now, launch Power BI Desktop and create a new report connecting to Oracle database using import mode. Use the TNS name as per the tnsnames.ora file.
Enter user name and password under database credentials. Select the tables to load data to the report, and once loaded create relationships and add some visuals to the report. Save the Power BI file as "OracleImportMode". Now, the report is ready.
Launch Power BI again create another report with a direct query for the same Oracle database, repeat the above steps and create similar visuals.
Save the Power BI report as "OracleDirectQueryMode".
Next, create an analysis services tabular project named "AnalysisServicesFromOracle" using SQL Server data tools or visual studio. Click Model -> "Import from Data source". Connect to the above mentioned Oracle database and add some tables. Create relationships between the tables as necessary, rename columns in a user freidnly manner, create hierarchies, measures etc. Now, select the model from the solution explorer, and set the "Direct Query Mode" to "On" from properties window as below.
Now, you can build the project, and deploy in the analysis service tabular instance. Once the deployment is successful, process the model with full process option. Browse the model and test.
Create a new Power BI file which is sourced from the above created SQL Server Analysis Services model with "Connect live" option.
Select the Model from Analysis services once prompted. Add some visuals to the report and save as "DirectQuerySSASSourceOracle". Now, we have 3 Power BI Files ready for the experiment.
Launch Power BI Report Server from a web browser using the Power BI Report Server web portal URL configured in the Report Server Configuration Manager. Create appropriate folders, and grant permissions to users.
Now, open one of the folders you created by clicking on it. Click on the upload icon in the report server shown below to upload the Power BI files created.
The uploaded files will be available as below.
Click on the elispses (…) icons in each uploaded report, and click “Manage” to configure each.
In the data sources tab, set the authentication type as “Basic” and enter the database credentials for the 2 Power BI files named "OracleDirectQueryMode" and "OracleImportMode" as below.
Leave the "DirectQuerySSASSourceOracle" file's data source authentication type to windows. as below.
Now, for the ImportMode file, please add a schedule of data refresh as below.
Power BI Report data will be reloaded from the Oracle database as per the time frequency specified in the schedule. You can later view the last refreshed date and time as below.
Deployed Power BI reports can be viewed from the web browser.
Design your Power BI reports elegantly, and grant access to your stake holders. You can grant access from Power BI Report server web portal in a similar way how it is done in traditional SSRS reports.
In this blog, we have deployed the Power BI reports from Power BI Report Server web portal. However, by downloading and installing "Power BI Desktop optimized for Report server", we can deploy the Power Reports directly from Power BI Desktop. It is detailed in the below link.
https://powerbi.microsoft.com/en-us/documentation/reportserver-install-powerbi-desktop/
Conclusion: Power BI Report Server which is the on-premise version of Power BI Service can be used to easily to host Analysis Services tabular Models sourced from Oracle database. In addition to that, now we can deploy and view Power BI reports that are sourced from Oracle database in Power BI Report Server in both direct query and import modes.