When connecting Power BI to Oracle Business Intelligence Enterprise Edition (OBIEE), the users could encounter performance issues for certain queries.

In this blog post, we’ll see how you can optimize the query performance when connecting Power BI to OBIEE (or Oracle Analytics Cloud (OAC) / Oracle Analytics Server (OAS) ) via BI Connector.

Let’s dive into the details!

Fine-tune BI Connector Query Limit parameters

You can see the following 3 parameters in the Query Limits section of the BI Connector Advanced Settings window:

1. Maximum number of rows per report
2. Maximum number of rows per fetch
3. Maximum number of columns per query

The query limits section is highlighted in the below image.

Now let’s take a quick look at each of these parameters:

• Maximum number of rows per report

The parameter, Maximum number of rows per report, help you to restrict the maximum number of rows you want BI Connector to fetch for a query.

For example, let’s consider an OBIEE report you want to analyze has more than 1,000,000 rows. But you want to analyze just the first 100,000 rows.

In that case, you can set the Maximum number of rows per report as 100,000. So, BI Connector would fetch only the first 100,000 rows for all your queries, and ignore the rest of the rows, if there’s any.

• Maximum number of rows per fetch

The parameter, Maximum number of rows per fetch, will help you avoid OBIEE time-out errors smartly.

For example, let’s say you want to analyze an OBIEE report with 100,000 rows. In this case, you can set the Maximum number of rows per fetch as 50,000. Therefore, BI Connector will fetch the first 50,000 rows in the first batch, and the remaining in the next batch.

The setting will help you avoid time-out errors as well. But you should ensure that the maximum number of rows per fetch is set to be less than the ResultRowLimit specified in your OBIEE instance config file.
If you’re not aware of the limit set in the ResultRowLimit parameter, you can have a chat with your OBIEE administrator, and get that information.

• Maximum number of columns per query

The parameter, Maximum number of columns per query, will help you restrict the number of columns BI Connector fetches from your OBIEE for your analysis.
Always have the Maximum number of columns per query value to be set more than the number of columns you want to analyze.

If the value you set in this parameter is less than the number of columns in your OBIEE report, you’ll see an error message. BI Connector’s recommendation is to reduce the number of columns as much as possible to improve query performance.

With these 3 settings in BI Connector: Advanced Settings window, you can easily optimize your query performance when connecting Power BI to OBIEE or OAC or OAS.

Another simple technique (that’s often overlooked) to improve your query performance is to apply filters! You can easily apply filters, and narrow down to the data that you need for your analysis.

Important Note: From BI Connector v5.3, the Maximum number of rows per report and Maximum number of Columns per query parameters are deprecated. For more info, please refer to this post.

In fact, applying filters and the 3 parameters we just saw are a part of the 9 best practices we curated for connecting Power BI to OBIEE (in a recent webinar), after analyzing of 1000s of customer support tickets and 100s of customer interactions.