9 Best Practices for Connecting Power BI to OBIEE and Oracle Analytics Cloud (OAC)
Using BI Connector

1 Introduction

The data visualization limitations of OBIEE and Oracle Analytics Cloud (OAC) are effectively complemented by self-service BI tools like Power BI.

With BI Connector, it is easy to connect and use Power BI and OBIEE in a bimodal approach. After establishing connectivity from Power BI to OBIEE using BI Connector, the users can derive the best value of BI Connector by following the 9 simple best practices.

Based on our interactions with customers (both IT teams and end-users) and careful research, the BI Connector team has developed these 9 best practices to help users experience a hassle-free visualization of OBIEE data in Power BI.

Some of these best practices are BI Connector specific, while the others are either Power BI or OBIEE specific. But all 9 are important for the users to experience a smooth self-service visualization of OBIEE data in Power BI through BI Connector!

2 The 9 Best Practices of BI Connector

Here’s the list of the 9 best practices:

1. 1. Know your OBIEE
2. 2. Start with OBIEE Reports
3. 3. Clear cache in Power BI
4. 4. Choose the right connection type (between Import and Direct Query)
5. 5. Apply filters
6. 6. Use GZID for retaining Subject Area Joins
7. 7. Fine-tune for long-running and large queries
8. 8. Troubleshoot connectivity issues in a click
9. 9. Implement Data Security in Cloud

Now let’s take a look at each of them in detail.

9 Best Practices for Connecting Power BI to OBIEE and Oracle Analytics Cloud (OAC)

Grab a PDF copy

2.1. Know your OBIEE

It is important to know the data source well before analyzing the data. This best practice is applicable not just to OBIEE but also for other data sources, in case you are planning to blend OBIEE with other data sources for your analysis.

You must be well-acquainted with the OBIEE data that you want to analyze in Power BI. To help users achieve this, the BI Connector team highly recommends them to open OBIEE and Power BI side by side when beginning with BI Connector. Then, open an OBIEE report, and visualize it in Power BI. Verify if the results get tallied.

If you’re analyzing Subject Areas, you should be able to differentiate the fact and dimension tables and identify the columns and the data formats in each table.

When you’re clear with your OBIEE dataset, take a look at this quick video!

How to configure OBIEE DSNs using BI Connector?

You can set up both OBIEE Subject Areas and Reports DSNs using BI Connector. It is always good to configure separate DSNs for test and production, based on your needs.

2.2. Start with OBIEE Reports

The OBIEE Reports use the existing joins and aggregations in the underlying OBIEE Subject Areas data and are readily available as Tables for analysis in Power BI.

Hence, it is easy to analyze data from an OBIEE Report rather than analyzing subject areas data. For analyzing Subject Areas data, the users must learn a few additional steps as a standard workaround to retain the joins using BI Connector.

Therefore, the users must start with analyzing OBIEE reports (rather than creating visualizations from the Subject Areas data), as this will help users get familiarized with using BI Connector.

If the data you require for analysis isn’t available in any existing OBIEE report, it is recommended to create the Report in OBIEE first and then to visualize the Report’s data in Power BI.

Here’re the quick videos on how to connect Power BI to OBIEE reports through BI Connector via Import and Direct Query!

Importing OBIEE Reports Data to Power BI

All you need to do is just to open Power BI, select BI Connector under Get Data, enter the Reports DSN, choose Import and give your OBIEE credentials. The OBIEE Reports data is now imported for your analysis. It’s as simple as that!

Connecting Power BI to OBIEE Reports via Direct Query

All the steps used for importing OBIEE Reports data are applicable for the Direct Query as well, except that you’ll need to choose Direct Query instead of Import.

2.3. Clear cache in Power BI

One of the common issues that users face in Power BI is that visualizations are not updated with the latest data. The reason behind this issue is Power BI’s cache in most occasions.

Hence, the users must clear the Power BI cache before each analysis as a standard practice. This simple best practice is highly effective, as it successfully eliminates needless confusion.

2.4. Choose the right connection type

The users must make the right choice between the Import and Direct Query modes of Power BI. If the dataset is large or if you want to limit the dataset in the Power BI end, Direct Query is the best choice.

On the other hand, you can use the Import option if faster performance is required. However, it is important to limit the imported dataset by applying filters and reducing the number of columns for analysis.

If you’re planning to connect to OBIEE in Direct Query mode and import the data from other data sources or vice-versa, it is important to ensure the composite connectivity model works for you.

2.5. Apply filters

The users can boost Power BI’s performance just by applying filters and narrowing down to the required dataset for analysis.

In Power BI, it is easy for users to narrow down to the data required for analysis just by applying filters like time, location, any other dimension.

Power BI also offers Report-level, Page-level, and Visual-level filters to help users improve their dashboard performance.

2.6. Use GZID for Retaining OBIEE Subject Area joins

You may get a question – What’s this GZID? Well, GZID is a virtual ID created by BI Connector and shows up as a separate column in the Fact and Dimension tables of the connected Subject Area. The GZID just assigns serial numbers for the rows in each table.

Ok, but what does this GZID do? It helps users to avoid recreating joins in Power BI from the scratch by retaining the existing joins in OBIEE. It also eliminates the need for exposing any primary key-foreign key relationships in the OBIEE presentation layer.

Sub-topics shortcut

1. 2.6.1. Importing OBIEE Subject Areas data and retaining joins in Power BI →
2. 2.6.2 Connecting Power BI to OBIEE Subject Areas and retaining joins via Direct Query →

Back to all topics ↑

2.6.1. Importing OBIEE Subject Areas data and retaining joins in Power BI

When importing data from OBIEE subject area to Power BI, the users have to execute the following steps for each dimension table, one at a time in the Merge Query window (which opens when you choose the Edit Queries before loading):

1. Set the join between GZID of the Fact table to the GZID of the dimension table
2. Set the Join Kind as Left Outer

Take a look at this quick video explaining the steps!

Importing OBIEE Subject Areas Data to Power BI

You can remove the columns that are not required for your analysis, and apply filters to narrow down to the data you need for improving the performance. It’s not mandatory, but just the application of the best practice – Apply Filters.

2.6.2. Connecting Power BI to OBIEE Subject Areas and retaining joins via Direct Query

When connecting Power BI to OBIEE subject area via the Direct Query mode, you’ll just need to execute the following steps for each dimension table, one at a time:

1. Join the GZID in the Fact table to the GZID in the dimension table
2. Set the cardinality of relationships as Many to One (since the relationship is from Fact to Dimension, and not the other way around)
3. Set the cross filter direction as Single

Take a look at this quick video explaining the steps!

Connecting Power BI to OBIEE Subject Areas via Direct Query

You can also apply the best practice of narrowing down to the data you need by adding filters and improve the performance.

2.7. Fine-tune for long-running and large queries

The users can optimize the performance by just adjusting certain parameters in BI Connector’s Advanced Settings window.

If queries run for several minutes and return with an empty dataset, you’ll need to run the same report in OBIEE and check the time it takes to complete. If the time is more than the custom time-out setting in BI Connector, you’ll need to increase the value in the setting accordingly to avoid errors.

When querying a large volume of data, the users can also fix the maximum number of rows and maximum rows per fetch for analysis.

For example, if a table has more than a million rows, and you want just the first 100,000 rows for analysis, you can just set the maximum number of rows as 100,000. If you set the maximum rows per fetch as 25,000, then BI Connector will fetch the data four times at 25,000 rows per fetch. The maximum rows per fetch is very useful to avoid OBIEE time-out errors.

The users can also set the maximum number of columns per query for optimizing the performance.

2.8. Troubleshoot connectivity issues in a click

When the users face connectivity issues with OBIEE DSNs, they can just click the Run Diagnostics button to see the type of error causing the connectivity issue.

The users can then troubleshoot the issue themselves if the credentials or the server name or port number is entered incorrectly, without any additional troubleshooting assistance.

For more information, please refer to this post.

2.9. Implement Data Security in Cloud

Since Power BI currently doesn’t support a Cloud user to login to OBIEE using their own OBIEE credential, it is not possible to retain the existing OBIEE Security framework into Power BI Service using BI Connector.

Hence, it is vital for the IT teams to implement data governance policies, and set up data access privileges for the cloud users manually in Power BI service based on the organization’s needs like for any other data source.

3 Conclusion

With these simple best practices, your organization can experience hassle-free visualization of OBIEE and OAC data with Power BI through BI Connector.

And here’s what’s next:

Need troubleshooting assistance? Head to our support portal, and file a ticket.

Having questions or suggestions? You’re welcome to post them in the community forum.